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);
}
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.
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.
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.)
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.
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.
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!";
?>
@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?
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:
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.”)
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!";
?>
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();
