PHP MYSQL ARRAY

[darren2005]

Hi, I am trying to create a script which searches through a MySQL column which contains URLs and check the URLs to see if they are up OR down. At the moment I am getting no results added into the database e.g. 1 = UP, 0 = DOWN.

Can someone please see where I’m going wrong on the below code?

<?php
include 'db.php';

function Visit(){
       $agent = "Mozilla/4.0 (compatible; MSIE 5.01; Windows NT 5.0)";$ch=curl_init();
       curl_setopt ($ch, CURLOPT_URL,$url );
       curl_setopt($ch, CURLOPT_USERAGENT, $agent);
       curl_setopt ($ch, CURLOPT_RETURNTRANSFER, 1);
       curl_setopt ($ch,CURLOPT_VERBOSE,false);
       curl_setopt($ch, CURLOPT_TIMEOUT, 5);
       curl_setopt($ch,CURLOPT_SSL_VERIFYPEER, FALSE);
       curl_setopt($ch,CURLOPT_SSLVERSION,3);
       curl_setopt($ch,CURLOPT_SSL_VERIFYHOST, FALSE);
       $page=curl_exec($ch);
       //echo curl_error($ch);
       $httpcode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
       curl_close($ch);
       if($httpcode>=200 && $httpcode<300) return true;
       else return false;
}


$query = sprintf("SELECT client_ID, website FROM clients") or die(mysql_error());
$result = mysql_query($query);
while ($row = mysql_fetch_assoc($result)) {
echo $row['website'];
echo $row['client_ID'];
    $visitid =  $row['client_ID'];
    $visiturl =  $row['website'];
    $visitstatus = Visit($visiturl)? 1 : 0;
//echo $status;

$upquery = sprintf("update clients set status=%d where id=%d", $visitstatus, $visitid);
    $upresult = mysql_query($upquery);

}

?>

[adam.adamski.96155]

There could be a problem with your UPDATE statement.
I recommend using error checking with all database transactions, for a great guide look here: mysqli with error checking
If your variables are of string type, your update should look like this:
"UPDATE tablename SET column='value' WHERE id='value'"
I know you are inserting integers, and they don't need to be quoted, but it is possible that $visitid is of string type as we can't see the MySQL column type that supplies the value.
You can try quoting your variables to see if it helps as it is a minimal overhead for the database server to convert variable type with such a small and simple query.

I'd output the data to the screen from the while loop to see what you're actually getting, e.g.

while ($row = mysql_fetch_assoc($result)) {

    $visitid =  $row['client_ID'];
    $visiturl =  $row['website'];
    $visitstatus = Visit($visiturl)? 1 : 0;

    echo "website: " . $row['website'] . " status: " . $visitstatus . "<br />";
}

I would offer some advice too on the UPDATEs. I'd collect all the info into an array and use the VALUES syntax to send just one query. E.g.

while ($row = mysql_fetch_assoc($result)) {

    $visitstatus = Visit($visiturl)? 1 : 0;
    $r[] = "({$row['client_ID']},$visitstatus)";

}

if(isset($r)){
    $listString = implode(',',$r);
    $upresult = mysql_query("UPDATE clients (`client_ID`, `status`) VALUES ($listString)";
}

I think a problem you had was the client_ID and the id fields in the two queries - shouldn't they have the same name?

Also, how many sites are you checking? cURL is quite heavy. If there are hundreds of clients, you could look to run a cron job at certain 'quiet' times and then perhaps do incremental updates, e.g. have a last_checked column, which would enable you to just check the next 20 clients for example if outside the update period since the last_checked. Just a thought.

[darren2005]

Thanks guys! The main issue was "client_ID and the id fields in the two queries". I'll take into account the issue with large CURL requests. At the moment it’s only a small amount of queries and have activated CRON to run the above code at set frequencies.