Improving polling with ajax and php

This is working, however I think it maybe resorce hungry, is there any way to improve it?开发者_StackOverflow社区

Adds callback to db(test.php):

extract($_REQUEST); 
$query = "INSERT INTO calls (
                        `timestamp`,
                        `fromnum`,
                        `tonum`
                        )
                        VALUES (
                        NOW(),
                        '".mysql_real_escape_string( $from )."',
                        '".mysql_real_escape_string( $to )."'
                        )";
                        $sql = mysql_query($query) or die(mysql_error());

Gets the last entry in the calls table (queue.php):

$result = mysql_query("SELECT fromnum FROM calls WHERE 
tonum = '$tonum' ORDER BY timestamp DESC LIMIT 1");

    while($row = mysql_fetch_array($result))
      {
      $fromnum = $row['fromnum'];
      }
    // Get caller info from contacts db
    $result = mysql_query("SELECT * FROM contacts WHERE phone = '$fromnum'");

    while($row = mysql_fetch_array($result))
      {
      echo '<table width="100%" border="0">
      <tr>
        <td width="33%">Account Number</td>
        <td width="33%">Contact Name</td>
        <td width="33%">Phone Number</td>
      </tr>
      <tr>
        <td>'.$row['number'].'</td>
        <td>'.$row['name'].'</td>
        <td>'.$row['phone'].'</td>
      </tr>
    </table>';
      }

Call the info from queue.php(listen.php):

var auto_refresh = setInterval(
function ()
{
$('#response').load('queue.php').fadeIn("slow");
}, 1000); // refresh every 10000 milliseconds
</script>
<div id="response"></div>

Thanks for looking, any help welcome!

Cheers, B.

---

Do NOT do extract($_REQUEST);. This basically recreates register_globals, with every single security hole/drawback/stupidity of old PHP versions restored to full blinding glory. It allows a user to polute your PHP namespace with arbitrary values/variables.

If that doesn't scare you, then back away from the computer and run home.

---

Efficient Transport

Your code does not refresh every 10 seconds, but every second(1000 milliseconds is equal to 1 second). Polling that fast is bad. You should as an efficient transport which does "wait" for the new information. The most simple transport that works in every browser is long-polling, but does not scale that good in PHP/Apache. Luckily you can use the free pusher plan which uses efficient transport called Websockets(which is the future for real-time communication). When you get big you should like into http://socket.io for example.

In-memory database

I also advice you to look into in-memory databases like Redis or Memcached(popular among twitter/facebook) to store your queries completely in memory instead of fetching from disc via SQL(which is slow compared to memory). I really like Redis because it is very fast and more feature complete compared to Memcached. You are even that lucky that redistogo does provide free plan to use Redis. This way you don't need to compile redis, which is very easy(I think easier than compiling memcached).

ByteCode Cache

When you can you install APC you should really because this will make your site a lot faster without even writting a single line of code. "This bytecode cache caches the compiled bytecode of PHP scripts to avoid the overhead of parsing and compiling source code on each request (some or all of which may never even be executed)".

SQL-injections.

I would advice you to look into PDO's prepared statements to prevent SQL-injections the right way. Furthermore PDO is very nice to test your code because you can use in-memory mode using SQLite.