Using Ajax with PHP and MySQL

Question

I would like to fetch information from a database with AJAX with html buttons, without having to refresh the browser.

I can make it work via HTML with the code below, but I want to do it via Ajax.

I have the following code in the same file, example.html;

<form action="?" method="get">
    <input id="Button1" type="hidden" name="q" value="%U/%Y"/>
    <input id="Button1" style="width: auto" type="button" value="Weekly stats">
</form>

<form action="?" method="get">
    <input id="Button2" type="hidden" name="q" value="%M/%Y"/>
    <input id="Button2" style="width: auto" type="submit" value="Monthly Stats">
</form>

<br>

<?php 

//execute a mysql query to retrieve all the users from users table
//if  query  fails stop further execution and show mysql error
if ($_GET['q'] == '') {
    $q = '%M/%Y';
}
else {
    $q = $_GET['q'] ;
}  
$query=mysql_query("SELECT DATE_FORMAT((post_date), '".$q."') 'Date',
                    SUM(balance) 'Balance'
FROM posts
GROUP BY Date
LIMIT 0, 25") or die(mysql_error());

//if we get any results we show them in table data
if(mysql_num_rows($query)>0):

?>

<table id="lastTips" class="main" cellspacing="0" width= "100%">
  <thead>
  <tr>
    <th align="center">Date</th>
    <th align="center">Balance</th>
  </tr>
  </thead>
  <tbody>
  <?php 
  //while we going through each row we display info
  while($row=mysql_fetch_object($query)):?>
  <tr>
    <td align="center"><?php echo $row->Date;?></td>
    <td align="center"><?php echo $row->Balance;?></td>
  </tr>
  <?php endwhile;?>
  </tbody>
</table>
<?php 
//if we can't get results we show information
else: ?>
<h3>No Results found.</h3>
<?php endif; ?>

I have tried several jquery functions without success, I have seen examples which call a separate file, but in my case I need to have the above code in the same file.

Can someone help me?

Thanks

Why do you need the code in the same file? When you make an ajax call, it should go to a file which only processes the request, and returns an object to the browser such as JSON. — Jessica, Jun 14 '13 at 13:08
You are using [an **obsolete** database API](http://stackoverflow.com/q/12859942/19068) and should use a [modern replacement](http://php.net/manual/en/mysqlinfo.api.choosing.php). You are also **vulnerable to [SQL injection attacks](http://bobby-tables.com/)** that a modern API would make it easier to [defend](http://stackoverflow.com/questions/60174/best-way-to-prevent-sql-injection-in-php) yourself from. — Quentin, Jun 14 '13 at 13:12
Is there a way to put it in the same file? It won't work if I do it in a separate file. I'm using wordpress and to run the query by author page, it needs to be inside the loop. — qebas, Jun 14 '13 at 13:13

score 1 · Accepted Answer · answered Jun 14 '13 at 13:53

I think you should split the code on two files. First "index.html" ( with html/js ):

<

script type="text/javascript">
$('#btn-week').click(function(){
  $.post("result.php", { date: "%U/%Y" }).done(function(data) 
    {
      formatResponse(data);
    }
  );
});
$('#btn-month').click(function(){
  $.post("result.php", { date: "%M/%Y" }).done(function(data) 
    {
      formatResponse(data);
    }
  );
});
function formatResponse( values ){
      var result = jQuery.parseJSON(data);//parse json response
      var element = $("#lastTips tbody");
      element.html('');//to clean previous result
      for ( var i in result ){  
        element.append('<tr><td align="center">' + values.date + '</td><td align="center">' + values.balance + '</td></tr>');//append to table
      }  
}
</script>

<input id="btn-week" style="width: auto" type="button" value="Weekly stats">
<input id="btn-month" style="width: auto" type="submit" value="Monthly Stats">    

<table id="lastTips" class="main" cellspacing="0" width= "100%">
  <thead>
  <tr>
    <th align="center">Date</th>
    <th align="center">Balance</th>
  </tr>
  </thead>
  <tbody>
  </tbody>
</table>

Second "result.php" with php:

<?php 

//execute a mysql query to retrieve all the users from users table
//if  query  fails stop further execution and show mysql error
if ($_POST['q'] == '') {
    $q = '%M/%Y';
}
else {
    $q = $_POST['q'] ;
}  
$query=mysql_query("SELECT DATE_FORMAT((post_date), '".$q."') 'Date',
                    SUM(balance) 'Balance'
FROM posts
GROUP BY Date
LIMIT 0, 25") or die(mysql_error());

echo json_encode(mysql_fetch_array($query));

I doesn't have tested the code. One warning, the html file cannot have two elements with same id ;)

I can't I'm using {global $post; $post_author = $post->post_author;} on wordpress to filter the table by author. If I separate the file, I can't get the author information. This file it's in the author-page and that's the only way to fetch the author's name. Hence I don't want to use json. — qebas, Jun 14 '13 at 14:00
Ok, but you can send the author as param on ajax request, something like { date: "%U/%Y", author: post_author;?> } — Rfps, Jun 14 '13 at 14:12
is, it's possible, one way (uggly but work) it's put the php code on top of the file and check with " if(!empty($_SERVER['HTTP_X_REQUESTED_WITH']) && strtolower($_SERVER['HTTP_X_REQUESTED_WITH']) == 'xmlhttprequest') " if it's ajax call and after process php response make a die(). Other options it's when you load page save all content on js var and the with js parse the content by filter. — Rfps, Jun 14 '13 at 14:20

score 0 · Answer 2 · answered Jun 14 '13 at 13:13

0

You should use AjaxForm plug-in for jQuery:

$('.my-ajax-form').ajaxForm(function(data){
    alert(data);
    // do some thing with data here.
})

answered Jun 14 '13 at 13:13

Rikky

515
3
8

Thanks Rikky, I tried the Ajax form but couldn't make it work. Can you please provide more details on how to use it? – qebas Jun 14 '13 at 13:17
AjaxForm is very useful if you have to upload Images or files in general. IMHO simple $.ajax(); call in jQuery is enough http://api.jquery.com/jQuery.ajax/ – ExoticSeagull Jun 14 '13 at 13:25
@Rikky, you didn't send any link to the sample – qebas Jun 14 '13 at 13:30
Thanks, but I need to have everything in the same file. In your example your calling ajax_server.php. Additionally, I don't want to echo text, it's just a button that changes the date format in a PHP mysql table, as shown in the code above. I tried Ajaxform and it doesn't work for me. – qebas Jun 14 '13 at 13:36
I don't understand your goal clearly. If you want to extract data from db, just store it in a json object, then hide them. When user click a button, show them. Why ajax here? – Rikky Jun 14 '13 at 13:47
@Rikky, this table dynamic and different for every author-page, hence I can't use a json file. I just want to use Ajax to call $_GET['q']. – qebas Jun 14 '13 at 13:55

Using Ajax with PHP and MySQL

2 Answers2