Count = 0 ? Should be 1

[micky007]

Hi Guys,

 

My count is echoing the $count variable as 0 when it should be 1. When i run the MySQL Query myself in PHPMYADMIN it returns a row. So why when i echo $count is it showing as 0? Any help would be great. Thanks in advance!

 

<?php

require("./databaseconnection.php"); //Get Database Login Information

//Do ISP Check
mysql_connect(localhost,$username,$password) or die(mysql_error());
@mysql_select_db($database) or die( "Oops theres an error, our highly trained monkeys have been notified.");
$query = "SELECT * FROM `leads` WHERE `IPCheck` = 'NO'"; 
$result=mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result))
{
$ipcountry=$row['IPCountry'];
$ipcc=$row['IPCC'];
$ipisp=$row['IPISP'];
$leadid=$row['LeadID'];
$ProgramID=$row['ProgramID'];

//Get Valid Consumer ISP
$query2 = "SELECT * FROM `ValidISP` WHERE `ISP` = '$ipisp'";
$result2=mysql_query($query2);
echo $query2."<br/>";
$count=mysql_num_rows($result2);
echo $count."<br/>";
var_dump($result2);
echo "<br />";
if($count==1){
while($row2 = mysql_fetch_array($result2))
{
$validisp=$row2['ISP'];
$validispstatus=$row2['Status'];
}
}else {
$validispstatus="MANUALCHECK";
}
//End

if ( $validispstatus == "PASS" ) {
$query3 = "UPDATE leads SET IPCheck='PASS' WHERE LeadID='$leadid'";
mysql_query($query3);
} elseif ( $validispstatus == "FAIL" ) {
$query3 = "UPDATE leads SET IPCheck='FAIL' WHERE LeadID='$leadid'";
mysql_query($query3);
} else {
$query3 = "UPDATE leads SET IPCheck='MANUAL' WHERE LeadID='$leadid'";
mysql_query($query3);
 }
}
mysql_close();
//End
echo "ISP Checks completed!";

?>

 

As you can see if used var_dump($result2); and thats echoing this out "resource(4) of type (mysql result)" I've no idea why its showing that as when i run the Query in PHPMYADMIN it displays a row of data. See the images below.

 

Example:

 

 

WHEN I TRY IT IN PHPMYADMIN IT RETURNS A ROW:

 

 

Any help would be great please.

[cpd]

You can't display a resource from a mysql_query(). To display the results you need to use the mysql_fetch_array() or other similar methods. This will retrieve the first row in the result set and move the pointer forward by 1, by default.

 

Calling queries inside while loops which are cycling through result sets often means your carrying out the task in an efficient way as well. Look into JOINs to make it far more efficient. I'm a little stumped as to why you aren't getting anything back if the query is exactly the same which it appears to be.

 

Final point, get away from the mysql functions and start using mysqli or PDO. Can't say it enough times.

[Muddy_Funster]

there's too much wrong with that script. I suggest you bin it and start again, paying attention to the followiing advice:

 

Do NOT run queries inside loops

Do not use SELECT *

Learn about SQL JOIN syntax

 

If after that you are still having problems, explain your desired outcome and post up your table structures along with updated code and we'll have another look.

[micky007]

You can't display a resource from a mysql_query(). To display the results you need to use the mysql_fetch_array() or other similar methods. This will retrieve the first row in the result set and move the pointer forward by 1, by default.

 

Calling queries inside while loops which are cycling through result sets often means your carrying out the task in an efficient way as well. Look into JOINs to make it far more efficient. I'm a little stumped as to why you aren't getting anything back if the query is exactly the same which it appears to be.

 

Final point, get away from the mysql functions and start using mysqli or PDO. Can't say it enough times.

 

Hey CPD, I'm still new to PHP and not an expert at it, so this JOINs thing is new to me & not sure what it is.

 

I've just updated the server to the newest PHP and added MySQLi. I've now changed my code for it to work with MySQLi but now nothing is working. All its doing now is just echoing "ISP Checks completed!".

 

<?php


require("./databaseconnection.php"); //Get Database Login Information


//Do ISP Check
$mysqli = new mysqli(localhost,$username,$password, $database);


$result = mysqli_query($link, "SELECT * FROM `leads` WHERE `IPCheck` = 'NO'");


$numberrows=mysqli_num_rows($result);
echo $numberrows;


while($row = mysqli_fetch_array($result))
{
  $ipcountry=$row['IPCountry'];
  $ipcc=$row['IPCC'];
  $ipisp=$row['IPISP'];
  $leadid=$row['LeadID'];
  $ProgramID=$row['ProgramID'];

  //Get Valid Consumer ISP
 $result2 = mysqli_query($link2, "SELECT * FROM `ValidISP` WHERE `ISP` = '$ipisp'");
 echo $link2."<br/>";
 $count=mysqli_num_rows($result2);
 echo $count."<br/>";
 var_dump($result2);
 echo "<br />";
 if($count==1){
  while($row2 = mysqli_fetch_array($result2))
  {
   $validisp=$row2['ISP'];
   $validispstatus=$row2['Status'];
  }
 }else {
  $validispstatus="MANUALCHECK";
  }
  //End

 if ( $validispstatus == "PASS" ) {
  mysqli_query($link3, "UPDATE leads SET IPCheck='PASS' WHERE LeadID='$leadid'");  
 } elseif ( $validispstatus == "FAIL" ) {
  mysqli_query($link4, "UPDATE leads SET IPCheck='FAIL' WHERE LeadID='$leadid'");
 } else {
  mysqli_query($link5, "UPDATE leads SET IPCheck='MANUAL' WHERE LeadID='$leadid'");
     }
}
mysqli_close();
//End
echo "ISP Checks completed!";
?>

 

I'm really stuck as i dont know whats wrong. What im trying to do is "SELECT * FROM `leads` WHERE `IPCheck` = 'NO'". I need it then to grab the value of the column called IPISP and also the column called LeadID of each record that matches the Query.

 

While it grabs the data of each record i then need it do perform the following:

 

"SELECT * FROM `ValidISP` WHERE `ISP` = '$ipisp'" - $ipisp is the data it has just got from a record from the column IPISP.

I need it then to count how many rows come up from that query.

If theres 1 row (there should really only be 1 or 0 rows) then i need it to grab the data from the above query from the column Status

If theres 0 rows then i need $validispstatus set as MANUAL

 

I then need the below code to be ran, im sure you can work out what im trying to do from the code:

 

if ( $validispstatus == "PASS" ) {
  mysqli_query($link3, "UPDATE leads SET IPCheck='PASS' WHERE LeadID='$leadid'");  
 } elseif ( $validispstatus == "FAIL" ) {
  mysqli_query($link4, "UPDATE leads SET IPCheck='FAIL' WHERE LeadID='$leadid'");
 } else {
  mysqli_query($link5, "UPDATE leads SET IPCheck='MANUAL' WHERE LeadID='$leadid'");
     }

 

Do you understand fully what im trying to do here? Help would be much appreciated please.

 

Thank you!

[kicken]

If I am understanding your requirements correctly, and have deduced your table structure correctly, then your entire script can be replaced by this query:

UPDATE leads l
LEFT JOIN ValidISP v ON l.IPISP=v.ISP
SET
l.IPCheck=COALESCE(v.Status, 'MANUAL')
WHERE
l.IPCheck='NO'

 

What that will do is go through each row in leads where IPCheck='NO' and match it up with a row from ValidISP using the condition leads.IPISP=ValidISP.ISP (ie, the IPISP column's value is equal to the ISP columns value).  Then it will assign the value of leads.IPCheck to whatever ValidISP.Status is currently set to, or 'MANUAL' if no matching row can be found.