Count = 0 ? Should be 1

[AdWarm]

@StarLion
@guido2004

Thanks for the help so far both of you, how ever I'm still having a problem with this and iv been trying to work it out myself but my mind is completely baffeled as to why its not working

Can i be cheeky and ask for a bit more help please?

Thank you.

[StarLion]

What records do you get when you run this query on your database?
SELECT * FROM leads LEFT OUTER JOIN ValidISP ON leads.IPISP = ValidISP.ISP WHERE leads.IPCheck = "NO"'

[AdWarm]

What records do you get when you run this query on your database?
SELECT * FROM leads LEFT OUTER JOIN ValidISP ON leads.IPISP = ValidISP.ISP WHERE leads.IPCheck = "NO"'

Thanks for your help! I get the following:

MySQL returned an empty result set (i.e. zero rows). ( Query took 0.0005 sec )

[AdWarm]

Sorry i forgot to set the IPCHECK as NO since last time i ran it it set them all to MANUAL. I've set 4 of them to NO and now it get this:

Showing rows 0 - 3 ( 4 total, Query took 0.0259 sec)

Which is the 4 i set to NO.

[StarLion]

Sorry i forgot to set the IPCHECK as NO since last time i ran it it set them all to MANUAL. I've set 4 of them to NO and now it get this:

Showing rows 0 - 3 ( 4 total, Query took 0.0259 sec)

Which is the 4 i set to NO.

Sorry, let me be more precise;
What value is in the Status column in those records?

[AdWarm]

Sorry, let me be more precise;
What value is in the Status column in those records?

If your talking about the value of IPCheck then those 4 are set as NO.

[StarLion]

No, that query should have returned a Status column as well, as part of the join with ValidISP....

[AdWarm]

Yea sorry abotu that, my database has a good number of columns so i didn't see them as they are at the end, they all say NULL.

[StarLion]

So the query did what it was supposed to do. Now you need to figure out why those entries have NULL for their match to ValidISP...
I can now tell you that your database is either missing data, or your data is not matching for some reason (I keep looking at the extraneous space characters on the ends of those names.....)

[AdWarm]

So the query did what it was supposed to do. Now you need to figure out why those entries have NULL for their match to ValidISP...
I can now tell you that your database is either missing data, or your data is not matching for some reason (I keep looking at the extraneous space characters on the ends of those names.....)

I've checked, checked and double checked. I cant see any difference on the data in the database, no spaces before of after each data entry. I'm at a lost as to why, if i give you access to the database can you take a look at it for me please? i trust you.

Thanks

[StarLion]

I've checked, checked and double checked. I cant see any difference on the data in the database, no spaces before of after each data entry. I'm at a lost as to why, if i give you access to the database can you take a look at it for me please? i trust you.

Thanks

I refer back to the post where you pasted this:

SELECT * FROM `ValidISP` WHERE `ISP` = 'HUTCHISON 3G UK LIMITED '
1
resource(4) of type (mysql result)
ISP Checks completed!

Look closer at the end of that string. There's a space between "LIMITED" and the closing '. It implies that the value in the ValidISP table has a space at the end, where the value in the leeds table does not.
Try this query:
SELECT CONCAT("Thisisthestart",`ISP`,"ThisIstheEnd") FROM `ValidISP`;
and see if that helps you see any spaces on the front/end of the values.

[AdWarm]

I refer back to the post where you pasted this:

Look closer at the end of that string. There's a space between "LIMITED" and the closing '. It implies that the value in the ValidISP table has a space at the end, where the value in the leeds table does not.

I understand that, but i checked the data in the database and there is no space at the end of the data. As what we have been doing is just SQL it cant be the PHP code as there is none, iv checked the records, made sure theres no space at the end and PHPMYADMIN is saying there is no space at the end of the records. I'm really stuck here

[StarLion]

If your database/PMA is open to the web, send me a PM with your login credentials and I will take a look. I'm telling you there are spaces on those fields that are causing the mismatch, though :P

[StarLion]

Well, we were both right.

There are no spaces there.
However, there IS a carriage return or newline character on the end of the ISPIP values of LeadID's 10, 11, and 12. This would cause a mismatch. You need to trim your values. (probably need to trim every string in the database, if this has been going on for a while.)

I chopped the extraneous character off of LeadID 10, and it now returns a value of PASS instead of NULL when the query is executed.

LeadID 9 will still return NULL (and then be set to manual)... this is the correct behavior, as it does not have a matching entry in the ValidISP table.

[AdWarm]

Well, we were both right.

There are no spaces there.
However, there IS a carriage return or newline character on the end of the ISPIP values of LeadID's 10, 11, and 12. This would cause a mismatch. You need to trim your values. (probably need to trim every string in the database, if this has been going on for a while.)

I chopped the extraneous character off of LeadID 10, and it now returns a value of PASS instead of NULL when the query is executed.

Hmmm to the eye it doesnt look like there was a space as the cursor was at the end of the data, but if i press the arrow right then its still at the end of the data so your right about there being a space after it, which is strange. So that leaves 2 things then as to why a space is added at the end. Either the data in the table called geo has a space at the end of every record (just checked and can confirm there is no space). So then that leaves the PHP code adding the lead to the database, which iv also noticed another problem, dont know if you noticed but in the leads table under the Mobile field you can see they all have a ' in it and it shouldn't. Now my page is quite a long script, so below i have listed the code that i think only you need to.

// Quote variable to make safe  
function quote_smart($value)  
{  
   // Stripslashes  
   if (get_magic_quotes_gpc()) {  
       $value = stripslashes($value);  
   }  
   // Quote if not a number or a numeric string  
   if (!is_numeric($value)) {  
       $value = "'" . mysql_real_escape_string($value) . "'";  
   }  
   return $value;  
} // end make safe

//Get ISP Of IP Address
list($ip1, $ip2, $ip3, $ip4) = explode(".", $ip);
$ipnum = $ip1*(256*256*256)+$ip2*(256*256)+$ip3*256+$ip4;
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Oops theres an error, our highly trained monkeys have been notified.");
$query = sprintf("SELECT * FROM `geo` WHERE %s BETWEEN `IP1` AND `IP2`",
            quote_smart($ipnum));
        
$result=mysql_query($query);
while($row = mysql_fetch_array($result))
  {
  $ipcountry=$row['Country'];
  $ipcc=$row['Code'];
  $ipisp=$row['ISP'];
  }
//mysql_close();
//End

//Add Lead To Database
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Oops theres an error, our highly trained monkeys have been notified.");

$query2 = sprintf("INSERT INTO leads (ProgramID, ProgramName, AffID, SubID, AdID, ClickIP, LeadIP, Status, Referer, Payout, NetPayout, MerchantPayout, Title, FirstName, LastName, CompanyName, Address1, Address2, City, County, PostCode, Country, Telephone, Mobile, EmailAddress, TermsAgreed, EmailContact, TelephoneContact, PostContact, MobileSubmit, MobileOS, OS, Browser, UserAgent, IPISP, IPCC, IPCountry, POFCheck, EmailCheck, IPCheck, UserAgentCheck, FraudCheck, CountryCheck, TelephoneCheck, DuplicationCheck, UniqueID) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,'%s','%s',%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",
            quote_smart($ProgramID),
            quote_smart($ProgramName),
            quote_smart($affid),
            quote_smart($subid),
            quote_smart($adid),
            quote_smart($clickip),
            quote_smart($ip),
            quote_smart($status),
            quote_smart($referer),
            quote_smart($affpayout),
            quote_smart($netpayout),
            quote_smart($merchantpayout),
            quote_smart($title),
            quote_smart($firstname),
            quote_smart($lastname),
            quote_smart($companyname),
            quote_smart($address1),
            quote_smart($address2),
            quote_smart($city),
            quote_smart($county),
            quote_smart($postcode),
            quote_smart($country),
            quote_smart($telephone),
            quote_smart($mobilen),
            quote_smart($emailaddress),
            quote_smart($termsagreed),
            quote_smart($emailcontact),
            quote_smart($telephonecontact),
            quote_smart($postcontact),
            quote_smart($mobilesubmit),
            quote_smart($mobileosoutput),
            quote_smart($osoutput),
            quote_smart($browseroutput),
            quote_smart($useragent),
            quote_smart($ipisp),
            quote_smart($ipcc),
            quote_smart($ipcountry),
            quote_smart($pofcheck),
            quote_smart($emailcheck),
            quote_smart($ipcheck),
            quote_smart($useragentcheck),
            quote_smart($fraudcheck),
            quote_smart($countrycheck),
            quote_smart($telephonecheck),
            quote_smart($duplicationcheck),
            quote_smart($uniqueid));
            //print $query2;
mysql_query($query2) or die(mysql_error());
mysql_close();
//End 

Can you see why theres a space added for IPISP?

EDIT: looks like PHPMYADMIN doesnt show the extra space at the end, only MySQL Workbench shows it so since i was using PHPMYADMIN thats why i couldnt see the space at the end.

[StarLion]

Well, i cant see why it would be adding anything to your string, because i cant see where you define all those input variables

but i'd do this:

function quote_smart($value)  
{  
//INSERT THIS
$value = trim($value); 

that'll get rid of extraneous spaces / tabs / newlines on the ends of the string/number.

[aaarrrggh]

This code is unreadable crap.

Why are you naming queries things like $query1 and $query2? And why do you use seemingly random strings to check statuses of things? It's ridiculous. Do you even know what a boolean is?

This kind of code is why I hate working on projects made by other people. If I started working for a company with code like this, I'd probably resign before dinnertime on the first day.

You shouldn't be using the mysql_ functions btw. At all. Why are you using them? Argh... this code is so crap.

[aaarrrggh]

I couldn't edit my above post, so some more comments from me:

Why are you writing queries like this: SELECT * FROM `ValidISP` WHERE `ISP` = '$ipisp'";

Shouldn't you be assigning an ID to the ISP data, so you can search for an ISP by ID rather than a string? Searching for companies like this in your general queries is poor - you should be using a unique id, not a string.

I hope this code isn't for anything important, as on a larger scale it'll very quickly turn into an unmaintable mess.

I'd suggest reading a book like this: http://www.amazon.co.uk/Clean-Code-H.../dp/0132350882

Seriously.

[AdWarm]

Thanks StarLion for all the help you have given me

@aaarrrggh Not being funny but you criticise me for my 'crap' coding, yes im still learning PHP and yes once i get better and learn from my mistakes then ill get better. No point in coming here and telling people how bad they are, people come on these forums to get help and be taught the right way of doing things. If your sick of people doing things the wrong way when people are learning then maybe this forum is not for you. Also reading the whole topic before replying first would of helped save both your and mine time reading your 'crap' responses, as if you had read them you would of seen my issues have been resolved.

Someone (@HAWK ) ban this 'crap' user.

[HAWK]

Someone (@HAWK ) ban this 'crap' user.

Hearing you. Tone it down please @aaarrrggh ; - this is a forum for people of ANY level and they have the right to non-judgemental support.

[aaarrrggh]

Hearing you. Tone it down please @aaarrrggh ; - this is a forum for people of ANY level and they have the right to non-judgemental support.

My problem though is that we constantly see really poor code being posted here, and instead of giving them real advice about how to get better, people just take on board the problem with the crap code and solve it, instead of giving real advice about how to improve things.

I stand by everything I said. I'd rather be banned than not be able to speak properly.

[aaarrrggh]

Thanks StarLion for all the help you have given me

@aaarrrggh Not being funny but you criticise me for my 'crap' coding, yes im still learning PHP and yes once i get better and learn from my mistakes then ill get better. No point in coming here and telling people how bad they are, people come on these forums to get help and be taught the right way of doing things. If your sick of people doing things the wrong way when people are learning then maybe this forum is not for you. Also reading the whole topic before replying first would of helped save both your and mine time reading your 'crap' responses, as if you had read them you would of seen my issues have been resolved.

Someone (@HAWK ) ban this 'crap' user.

I've also given you some constructive criticism in the previous email, and it was criticism you should pay attention to, because it will make your code better. I also linked you to a book that would help you out massively.

Your code IS crap, but that doesn't mean you can't improve it.

I didn't realise you were such a beginner, and for that reason maybe my tone was a bit harsh - the stuff I said was still correct though.

It's more an issue I have in general with places like this. There are people giving advice here that don't really give advice that will help you improve the quality of your code over time. Just fixing the issue someone is having a problem with isn't necessarily going to give you the help you need.

As an example: a few months ago, someone posted on this forum regarding a mysql query that wasn't working. Everyone in the thread just posted help regarding the query itself - totally ignoring how terrible his code was, and how it was also open to something called a "sql injection" attack. I posted a response explaining the nature of the threat, and telling him to focus on that FIRST, before addressing the issue he had with his query. I even sent him a private message showing him the exact string I could append to his URL to delete his user table database (and it would have worked, too) - the response from people in this forum? Stuff like: "That's not the question he was asking, please stop posting"

Your code is poorly written, however if you're just learning that's fair enough. You should still pay attention to people like me though, because I know for a fact that code written in the way you've written it currently will be difficult if not impossible to work with over time.

I apologise for the tone, simply because I thought you were more experienced than you obviously are. Everyone has to learn somewhere and I appreciate that.

Do yourself a favour and buy this book: http://www.amazon.co.uk/Clean-Code-H.../dp/0132350882

[HAWK]

My problem though is that we constantly see really poor code being posted here, and instead of giving them real advice about how to get better, people just take on board the problem with the crap code and solve it, instead of giving real advice about how to improve things.

I stand by everything I said. I'd rather be banned than not be able to speak properly.

I'm not suggesting for a moment that shouldn't be honest... just polite. Insulting someone is not the way to get your point across. Thanks for your apology to the OP.