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
– Mark Byers, Commented May 25, 2012 at 5:24
Looks like you don't take into consideration the answers you're given anyway. Oh, and what have you tried? — Damien Pirsy
– Damien Pirsy, Commented May 25, 2012 at 5:25
May be you use regular expressions? : stackoverflow.com/questions/2490310/… — Sergey
– Sergey, Commented May 25, 2012 at 5: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
– JackWill, Commented May 25, 2012 at 5: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
– JackWill, Commented May 25, 2012 at 5:40

Damien Pirsy · Accepted Answer · 2012-05-25 07:11:00Z

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, 2012 at 7:11

answered May 25, 2012 at 6:00

Damien Pirsy

25.5k8 gold badges72 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Damien Pirsy Over a year ago

It's a ternary operator, short for if <condition true> <option 1> else <option 2>

tim · Accepted Answer · 2012-05-25 05:53:12Z

0

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

edited May 25, 2012 at 5:53

answered May 25, 2012 at 5:47

tim

2,7363 gold badges33 silver badges49 bronze badges

1 Comment

Damien Pirsy Over a year ago

Uh? What do you achieve by counting the rows? (and why you cast it to string? echo 1; would works just fine)

eiji56.y1956 · Accepted Answer · 2012-05-25 06:38:02Z

0

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

answered May 25, 2012 at 6:38

eiji56.y1956

435 bronze badges

Collectives™ on Stack Overflow

Check availability of URLs in a MySQL Database using PHP?

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related