PHP script when fails to connect to SQL

Question

what i am trying to do... is a simple script that connects to sql server, picks some info from database and posts it in small table in the website. I've faced the problem that if sql server is offline (it happens) then it timeouts in 60 secs and shows an error. By that time, website is unavailable for few minutes. What i want to do is to give timeout value from 60secs to example 2 and if it fails, to show an message like "Could not connect" The script looks like

    <?php
$dbserver="[IP-ADDRESS]"; 
$dblogin="root";       
$dbpass="[PASSWORD]";     
$dbname="[DATABASENAME]";    

            mysql_connect($dbserver, $dblogin, $dbpass);
            mysql_select_db($dbname);
            mysql_query("SET NAMES 'utf8'");

            $vypis =  mysql_query("SELECT * FROM characters WHERE (accesslevel < '1') order by pkkills desc LIMIT 5"); 
            echo '<table style="text-align: center; width: 220px;" border="0"><tr><td><center>Nick</center></td><td></td><td>Kills</td></tr>';
     while($row = mysql_fetch_array($vypis))
                {
            echo '<tr><td><b><font color="white">';
            echo $row["char_name"];
            echo '</font></td><td>&nbsp;&nbsp;</td><td><font color="white">';
            echo $row["pkkills"];
            echo '</font></td></tr></b>';

echo '</table>';
?>

Any help or tip is highly appreciated.

First 'mysql_connect' is deprecated, use [PDO](http://php.net/manual/en/ref.pdo-mysql.php) instead. — spencer, Jul 30 '15 at 14:09
Maybe this helps: http://php.net/manual/en/mysql.configuration.php#ini.mysql.connect-timeout — t.h3ads, Jul 30 '15 at 14:11
Please note that `mysql` is not supported anymore. Use `pdo` or `mysqli` instead. — Nytrix, Jul 30 '15 at 14:12
is this live? Im asumming you are allowing us to connect to your database correct? — CodeGodie, Jul 30 '15 at 14:16
mysql_connect($dbserver, $dblogin, $dbpass) or die("could not connect"). And yes use mysqli or PDO. — Harish Lalwani, Jul 30 '15 at 14:19
decrease timeout time in php.ini or in mysql server, for php do `ini_set('mysql.connect_timeout', 2);` in your php code and check if mysql_connec is successfull or not and show the message. — manjeet, Jul 30 '15 at 14:22

score 0 · Answer 1 · edited May 23 '17 at 11:51

First thing, try to avoid mysqland prefer using PDO or mysqli instead, as Nytrix said.

<?php
/*increase time to connect*/ 
ini_set('mysql.connect_timeout', 2);

$dbserver="foo_server"; 
$dblogin="root";       
$dbpass="foo";     
$dbname="foo2"; 

/* tries to connect, if it fails it echo 'could not connect to the database' */
try
{
    $db = new PDO('mysql:host='.$dbserver.';dbname='.$dbname, $dblogin, $dbpass);
}
catch(Exception $e)
{
    echo('Could not connect to the database');
}

$stmt = $db -> prepare("SELECT * FROM characters WHERE (accesslevel < '1') order by pkkills desc LIMIT 5"); 
$stmt -> execute(array());
/*or $stmt -> query("SELECT * FROM characters WHERE (accesslevel < '1') order by pkkills desc LIMIT 5"); */

echo '<table style="text-align: center; width: 220px;" border="0"><tr><td><center>Nick</center></td><td></td><td>Kills</td></tr>';

while($row = $stmt->fetch())
{
    echo '<tr><td><b><font color="white">';
    echo $row["char_name"];
    echo '</font></td><td>&nbsp;&nbsp;</td><td><font color="white">';
    echo $row["pkkills"];
    echo '</font></td></tr></b>';
}

echo '</table>';
?>

Note that I prepared and the executed the statement while it was not needed, but I thought it was important to show the prepare/execute statement since it is widely used, and also I find it being a better practice. For more infos, see this stackoverflow question.

Finally, I've not tried but I presume you put your real passwords,login, etc. in your code example, think about removing it !

PS : I think your problem was not the connection was slow, but rather that you had a bracket } missing in your while.

PHP script when fails to connect to SQL

1 Answers1