Auto-refresh on MySQL change

**mattwidge** · 10-17-2009, 09:18 PM

I'm making a real-time feedback system & I need to know how to make the viewing page refresh automatically (or show new row) when a new row gets added to a MySQL database.

Here's what my system does:
- Somebody writes into a form (index.php)
- the data goes into a MySQL database
- I can view what they wrote (display.php)

I want to know how to make display.php automatically reload when somebody writes in the index.php forms.

**Mindzai** · 10-18-2009, 02:36 AM

You would have to check at a certain interval for new data. This is probably best done via AJAX to avoid constant page reloads. You could also insert new data into display.php without a reload this way too.

**mattwidge** · 10-18-2009, 04:02 AM

I've read that you can do it with AJAX, & I tried it, but it didn't work.

Here's my display.php file. Hope it's not too confusing, but could you show me where the AJAX parts would go?

PHP Code:


<?php

include('connect.php');

include('config.php');

$num = mysql_query("SELECT * FROM visitordata");

$num_rows = mysql_num_rows($num);



if(isset($_COOKIE['logged-in'])){

  echo "<div class=\"top\"><a href=\"logout.php\">Log out</a> | <a href=\"index.php\">View posting page</a> | Number of comments: $num_rows <div class=\"logo\"><img src=\"logo.png\" border=0></div></div>";

  echo "<div class=\"gap\"></div>";

  $result = mysql_query("select * from visitordata order by id desc limit 10");

  //the while loop

  $id=$_GET['id'];

  while($r=mysql_fetch_array($result))

  {

    //getting each variable from the table

    if ($r['id'] % 2 == 0) { echo "<div class=\"even\">"; } else { echo "<div class=\"odd\">"; }

    if($flag==$r['id']){ echo "<div id=\"flagged\">"; } else { echo "<div id=\"post\">"; }

    echo "<div class=\"info\"><a href=\"delete.php?delid=".$r['id']."\"><img src=\"delete.png\" border=\"0\"></a> ";

    if($flag==$r['id']){ echo "<a href=\"?flag=\"><img src=\"flag.png\" border=\"0\"></a>"; } else { echo "<a href=\"?flag=".$r['id']."\"><img src=\"flag.png\" border=\"0\"></a>"; }

    echo " | <div class=\"time\">".$r['time']."</div> | <div class=\"name\">".$r['name']."</div> | <div class=\"location\">".$r['location']."</div></div>";

    echo "<a href=\"mail.php?id=".$r['id']."\"><img src=\"email.png\" border=\"0\"></a> $block | <div class=\"message\">".$r["message"]."</div></div></div>";

  }

} else {

  echo "You do not have permission to access this page.";

}

?>

That's the part between the <body> tags I didn't bother with the rest since it was only a link to the CSS file.

**icreativelabs** · 10-18-2009, 04:43 AM

I think the display.php must return the xml or json, and you must put the last database id in the xml, and your ajax request must send the last id record that they have:

display.php xml

Code:

<data>
<rowsdata>your data in here</rowsdata>
<lastid>10</lastid>
</data>

And in the html view model you can put this

Code:

<div id='showtheresult'> <!-- we will use this div for getting the result -->
  your html code for show 
</div>

In javascript when success get the result from display.php you can use

Code:

$("#showtheresult").append("the rowsdata value from xml");

**mattwidge** · 10-19-2009, 01:17 AM

How would I implement this? I tried, but failed. It displayed everything & it still worked, but when I entered sent in data from the index.php file, it didn't reload. I left it for 5 minutes & nothing. So, how would I implement this? (display.php is written in an above post from me)