Count = 0 ? Should be 1

[AdWarm]

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!

//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/>";
        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);
               } 

Example:

ispcheck1.jpg
ispcheck2.jpg

[StarLion]

have you checked the value of $ipisp ?

[AdWarm]

Hey StarLion,

Yep, if you take a look at the 2 images at the bottom of my first post you can see the query and then the value of $count under it. The photo after that is the same Query but ran in PHPMYADMIN and it returns a row a data.

Any help would be great please.

Thanks

[StarLion]

I cant look at the photos until they're approved by Advisors.

[AdWarm]

Ah, I'll PM them you

[StarLion]

var_dump $result2, just make sure it's a resource.

Also, are you sure you've set your connection up correctly? (Using the right DB springs to mind)

[AdWarm]

Your right, i dont understand whats gone wrong though:

SELECT * FROM `ValidISP` WHERE `ISP` = 'BE UN LIMITED '
0
resource(4) of type (mysql result)
SELECT * FROM `ValidISP` WHERE `ISP` = 'ECLIPSE NETWORKING LIMITED '
0
resource(5) of type (mysql result)
SELECT * FROM `ValidISP` WHERE `ISP` = 'HUTCHISON 3G UK LIMITED '
0
resource(6) of type (mysql result)
ISP Checks completed!

PS: I'll be back online in around and hour and 20mins. Time to drive home from work.

[StarLion]

Show me the whole file, including the database connector (* out your password); and identify which database(s) this data is being pulled from.

[AdWarm]

I'll PM you the whole file. Theres only a few extra lines.

Thanks for the help so far

[StarLion]

The only thing i can think of off the top of my head is that you've got more than one database, and have filled the wrong table. This code should work. (Though it's inefficient - you should be running a single query to do all your pulling, and at most 3 queries to do all your updating. You're currently doing 1+2*N queries, instead of 4.)

[AdWarm]

I only have one database, iv been doing some testing and got rid of all the code and placed the code that wasn't working on its own page so its this

<?php

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

$ipisp="HUTCHISON 3G UK LIMITED ";

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.");

        $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 />";
        
mysql_close();
//End
echo "ISP Checks completed!";
?>

Its working when its own its own and showing theres 1 row but still getting that resource error (see below).

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

I cant work out whats wrong with my code as iv checked, checked, and checked again and i can see anything wrong with it

[StarLion]

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

uhm... that worked.

There's no error showing there. The type SHOULD be a resource. (If it was an error, you would have seen type BOOLEAN(FALSE) ).

I'm still gonna suggest you change your structure to do a single pull, that might fix this issue.

[AdWarm]

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

uhm... that worked.

There's no error showing there. The type SHOULD be a resource. (If it was an error, you would have seen type BOOLEAN(FALSE) ).

I'm still gonna suggest you change your structure to do a single pull, that might fix this issue.

What do you mean by a single pull sorry?

Its weird though as when i run this in phpmyadmin:

SELECT leads.LeadID, leads.IPISP, leads.IPCheck, ValidISP.ISP FROM leads, ValidISP WHERE leads.IPISP = ValidISP.ISP

It comes back with 0 results, when it should. I'm so confused!

Can you see from my PHP code what im trying to do though and can you try and put me in the right direction on how i should be doing it please. As much as help be much appreciated.

Thank you.

[StarLion]

Absolutely.

First, i'd like to start with a blank page. We'll try it this way, and if you dont like it, you can always try and incorporate it into your code - but i'm going to try and streamline it as much as possible. Also not gonna give you the code straight out, want to see if you can follow my logic!

Establish your database link.

Here's the SELECT query i would use;

SELECT l.leadid,s.STATUS 
FROM `leads` AS l 
LEFT JOIN `ValidISP` AS s 
ON l.IPISP = s.ISP AND l.IPCheck = 'NO';

Note the Left Join here. This makes sure all leads are pulled, even if they dont have a matching ISP. You probably want to add an ORDER BY too, but we're not overly stressed about the order of items atm.

foreach row in the result set
All rows should have one of three results in the 'Status' column; 'PASS', 'FAIL', or NULL.
If status is null, make it MANUAL.
assign the leadid to a new element one of three elements of a new array - i'm gonna call it $update. (hint: the keys of the elements should match your status). This will result in a 2-dimensional array.
EFE; we are now done selecting our data.

Now to update it!
foreach element of $update;
if the count of elements of this key is greater than 0,

UPDATE leads 
SET IPCheck = '<elementname>' 
WHERE LeadID IN ('<imploded list of elements of this element, seperated by commas. no quotes around this.>');

EFE

Done.

EDIT: Added an if to counter the 0-updates, and formatting on the queries to make them more readable.
I'm also moderately sure you could do this whole script in a single query, but i'm gonna have to tap @guido2004 and make sure i'm not sticking my foot in my mouth again.

[guido2004]

but i'm gonna have to tap @guido2004 and make sure i'm not sticking my foot in my mouth again.

I tried to read through the thread, but I'm afraid you lost me somewhere along the way

[AdWarm]

Hi,

Thanks for the help, im a little stuck on where im supposed to put the $leadid in an array, if im honest i dont really use arrays so there new to me. Am im going in the right lines so far?

<?php

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

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.");

$query2 = "SELECT l.leadid,s.STATUS 
FROM `leads` AS l 
LEFT JOIN `ValidISP` AS s 
ON l.IPISP = s.ISP AND l.IPCheck = 'NO';";


$result2=mysql_query($query2);

while($row2 = mysql_fetch_array($result2))
{
    $validisp=$row2['ISP'];
    $validispstatus=$row2['Status'];
    $leadid=$row2['LeadID'];
    
    if ( $validispstatus == "NULL" ) {
        $validispstatus="MANUAL";
        }
    
    $new_array = array();
    foreach ($var as $object)
    {
        $temp_object = clone $object;
          unset($temp_object->$leadid);
          $new_array[$object->$leadid] = $temp_object;
    }
}
                    
mysql_close();
echo "ISP Checks completed!";
?>

[StarLion]

@guido2004 : abstracting it a bit.... UPDATE Table1, Left join Table 2 SET Table1.SomeField = IF (Table2.Someotherfield IS NULL) THEN "MANUAL" ELSE Table2.Someotherfield

Is this doable, or am i stretching the boundaries of SQL again? Would it need a subquery?

[StarLion]

Hi,

Thanks for the help, im a little stuck on where im supposed to put the $leadid in an array, if im honest i dont really use arrays so there new to me. Am im going in the right lines so far?

<?php

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

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.");

$query2 = "SELECT l.leadid,s.STATUS 
FROM `leads` AS l 
LEFT JOIN `ValidISP` AS s 
ON l.IPISP = s.ISP AND l.IPCheck = 'NO';";


$result2=mysql_query($query2);

while($row2 = mysql_fetch_array($result2))
{
    $validisp=$row2['ISP'];
    $validispstatus=$row2['Status'];
    $leadid=$row2['LeadID'];
    
    if ( $validispstatus == "NULL" ) {
        $validispstatus="MANUAL";
        }
    
    $new_array = array();
    foreach ($var as $object)
    {
        $temp_object = clone $object;
          unset($temp_object->$leadid);
          $new_array[$object->$leadid] = $temp_object;
    }
}
                    
mysql_close();
echo "ISP Checks completed!";
?>

You're on the right track.
$new_array should be defined outside the loop, otherwise each time you loop through you'll blank out the loop.
$new_array = array('PASS' => array(),'FAIL' => array(),'MANUAL' => array());

Here's what i'd like it to look like when completed:

$new_array => Array(
 ['PASS'] => Array(
   [0] => 3,
   [1] => 4
 )
 ['FAIL'] => Array(
   [0] => 1,
   [1] => 5
 )
 ['MANUAL'] => Array(
  [0] => 2
 )
)

Defining a new numerical element of a 2-dimensional array:
$new_array[<the right key>][]
(the empty [] means "Put this on the end of the array, and give it the next numerical value.")

[AdWarm]

I know this is not right, but now im stuck as ivnever really worked with arrays so using arrays are new to me.

<?php

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

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.");

$query2 = "SELECT l.leadid,s.STATUS 
FROM `leads` AS l 
LEFT JOIN `ValidISP` AS s 
ON l.IPISP = s.ISP AND l.IPCheck = 'NO';";

echo 'UPDATE leads, Left join ValidISP SET leads.ISPCheck = CASE (ValidISP.ISP IS NULL) THEN "MANUAL" ELSE ValidISP.Status END CASE';

$result2=mysql_query($query2);

while($row2 = mysql_fetch_array($result2))
{
    $validisp=$row2['ISP'];
    $validispstatus=$row2['Status'];
    $leadid=$row2['LeadID'];
    
    if ( $validispstatus == "NULL" ) {
        $validispstatus="MANUAL";
        }
}

$new_array = array('PASS' => array(),'FAIL' => array(),'MANUAL' => array());
$new_array[$leadid][];

foreach ($new_array as &$value) {
    if ( count($new_array, COUNT_RECURSIVE) >0 ) {
        $query3="UPDATE leads 
        SET IPCheck = '<elementname>' 
        WHERE LeadID IN ('<imploded list of elements of this element, seperated by commas. no quotes around this.>');";
    } else {
        //do nothing
    }
}
        
mysql_close();
echo "ISP Checks completed!";
?>

[StarLion]

okay, you're close enough to it that i'll guide you out from here

$query2 = "SELECT l.leadid,s.STATUS 
FROM `leads` AS l 
LEFT JOIN `ValidISP` AS s 
ON l.IPISP = s.ISP AND l.IPCheck = 'NO';";

$result2=mysql_query($query2);

//Put this up here, because we need it to store the values we are about to get.
$new_array = array('PASS' => array(),'FAIL' => array(),'MANUAL' => array());

while($row2 = mysql_fetch_array($result2))
{
    // $validisp=$row2['ISP'];  This doesnt exist. Why do you need this?
    $validispstatus=$row2['Status'];
    $leadid=$row2['LeadID'];
    
    if ( is_null($validispstatus) ) { //I prefer is_null to an equals condition, but you can do either. Keep in mind that NULL is not the same as 'NULL'.
        $validispstatus="MANUAL";
        }

   //Scoot this up, and put the value into our array...
  $new_array[$validispstatus][] = $leadid;
}

//Now we have the values in the array, time to make our updates..

foreach ($new_array as $key => $value) { //We need the key too.
    if ( count($value) >0 ) { //We only care about the current set of values.
        $query3="UPDATE leads 
        SET IPCheck = '$key'   //Here's where our key was needed.
        WHERE LeadID IN (".implode(',',$value).");"; //Smash the values all together with commas between.
        mysql_query($query3);
    } 
}
        
mysql_close(); 

[AdWarm]

Thank you very much for your help, how ever the script doesnt seem to have worked hehe.

I've ran the script and checked my database but there 3 rows of data that say NO still say NO.

http://www.loudnewmedia.com/ispcheck3.jpg

What information do you need from me to see what went wrong?

Just so you know ill be offline for the next 5hrs or so, just dont want to keep you hanging around waiting for me to reply

[StarLion]

Might want to throw an echo mysql_error(); just below the update query execution. Does it spit anything out? (also, make sure you've got the latest version of that paste... i had to edit a couple of times)

[guido2004]

@guido2004 : abstracting it a bit.... UPDATE Table1, Left join Table 2 SET Table1.SomeField = IF (Table2.Someotherfield IS NULL) THEN "MANUAL" ELSE Table2.Someotherfield

Is this doable, or am i stretching the boundaries of SQL again? Would it need a subquery?

That would become something like:

UPDATE Table1
LEFT OUTER JOIN Table 2 
ON Table1.joincolumname = Table.joincolumnname 
SET Table1.SomeField = COALESCE(Table2.Someotherfield, "MANUAL")

[StarLion]

That would become something like:

UPDATE Table1
LEFT OUTER JOIN Table 2 
ON Table1.joincolumname = Table.joincolumnname 
SET Table1.SomeField = COALESCE(Table2.Someotherfield, "MANUAL")

So to take what guido has provided that into your case, AdWarm, I believe that would make your whole script reducable to...

<?php

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

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.");

mysql_query('UPDATE leads LEFT OUTER JOIN ValidISP ON leads.IPISP = ValidISP.ISP SET leads.IPCheck = COALESCE(ValidISP.Status, "MANUAL") WHERE leads.IPCheck = "NO"');
echo mysql_error(); //just in case.
?>

(PS: Strongly consider moving into mysqli or PDO, as mysql (library) is being taken out of PHP (deprecated as of 5.5))

EDIT: forgot the WHERE.

[AdWarm]

So to take what guido has provided that into your case, AdWarm, I believe that would make your whole script reducable to...

<?php

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

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.");

mysql_query('UPDATE leads LEFT OUTER JOIN ValidISP ON leads.IPISP = ValidISP.ISP SET leads.IPCheck = COALESCE(ValidISP.Status, "MANUAL") WHERE leads.IPCheck = "NO"');
echo mysql_error(); //just in case.
?>

(PS: Strongly consider moving into mysqli or PDO, as mysql (library) is being taken out of PHP (deprecated as of 5.5))

EDIT: forgot the WHERE.

Hi Guys,

I really appreciate the help your giving me, I'v just tried the above code but it just sets everything to MANUAL for some reason

Thank you again.