Check availability of URLs in a MySQL Database using PHP?

Question

I have three filed in my mysql table they are: id, url, status

How to check all urls from the column url and write either 1 (available) or 0 (unavailable) to the status column?

To just check urls manualy in php w/o mysql I could use this:

<?php
    function Visit($url){
    $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);
    $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;
    }
}
    if(Visit("http://www.google.com")){//maybe make it a variable from a result of a mysql select, but how to process it one by one?  
        echo "Website OK"; //maybe somesql here to wtite '1'
    }
    else{
        echo "Website DOWN";//maybe somesql here to wtite '0'
    }
?>

There's no need to write `if (foo) {return true;} else { return false; }` just write `return foo;`. — Mark Byers, May 25 '12 at 05:24
Looks like you don't take into consideration the answers you're given anyway. Oh, and what have you tried? — Damien Pirsy, May 25 '12 at 05:25
May be you use regular expressions? : http://stackoverflow.com/questions/2490310/regular-expression-for-checking-website-url — Sergey, May 25 '12 at 05:36
Mark is a NetBot. Sure it should be something simple... cannot figure out how to pass a list of urls to the script... — JackWill, May 25 '12 at 05:38
Not it must check out if a given url is available and make a mark in the column status. After this I'll either display or hide the url on a webpage. — JackWill, May 25 '12 at 05:40
@Sergey regex? for what? Did you read the code? OP just wants to loop over a list of url and mark each one as up or down in terms of availability (and update the 'status' field accordingly), he doesn't want to check if a url is correct or not — Damien Pirsy, May 25 '12 at 05:47

Damien Pirsy · Answer 1 · 2012-05-25T07:11:00.547

1

This is bad in terms of performance as doing queries in a loop is bad design, you should instead build a batch update and run the query in the end, but I've not enough time now to elaborate that. This is just to get you started:

$sql = "SELECT id,url FROM mytable";
$res = mysql_query($sql) or die(mysql_error());
if($res)
{
  while($row = mysql_fetch_assoc($res))
  {
    $status = visit($row['url']) ? '1' : '0';
    $id = $row['id'];
    $update = "UPDATE mytable SET status = $status WHERE id = $id";
    $res = mysql_query($update) or die(mysql_error());
  }
}

edited May 25 '12 at 07:11

answered May 25 '12 at 06:00

Damien Pirsy

25,319
8
70
77

It's a ternary operator, short for `if – Damien Pirsy May 25 '12 at 09:09

tim · Answer 2 · 2012-05-25T05:53:12.453

0

echo mysql_num_rows($result) ? '1' : '0';

edited May 25 '12 at 05:53

answered May 25 '12 at 05:47

tim

2,530
3
26
45

Uh? What do you achieve by counting the rows? (and why you cast it to string? `echo 1;` would works just fine) – Damien Pirsy May 25 '12 at 05:48

score 0 · Answer 3 · answered May 25 '12 at 06:38

You can use sql queries for example:

select id, url from urltable;
update urltable set status=1 where id=99;

So, how about use this code with your Visit function:

$link = mysql_connect('localhost', 'test', 'pppppp');
$db_selected = mysql_select_db('test', $link);
$query = "select id, url from urltable";
$result = mysql_query($query);
while ($row = mysql_fetch_assoc($result)) {
    $visitid =  $row['id'];
    $visiturl =  $row['url'];
    $visitstatus = Visit($visiturl)? 1: 0;
    $upquery = sprintf("update urltable set status=%d where id=%d", $visitstatus, $visitid);
    $upresult = mysql_query($upquery);
}

Check availability of URLs in a MySQL Database using PHP?

3 Answers3