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
– Jessica, Commented Jun 14, 2013 at 13:08
You are using an obsolete database API and should use a modern replacement. You are also vulnerable to SQL injection attacks that a modern API would make it easier to defend yourself from. — Quentin
– Quentin, Commented Jun 14, 2013 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
– qebas, Commented Jun 14, 2013 at 13:13

Rfps · Accepted Answer · 2013-06-14 13:53:17Z

1

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

answered Jun 14, 2013 at 13:53

Rfps

363 bronze badges

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

3 Comments

qebas Over a year ago

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.

Rfps Over a year ago

Ok, but you can send the author as param on ajax request, something like { date: "%U/%Y", author: <?php echo $post->post_author;?> }

Rfps Over a year ago

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.

Rikky · Accepted Answer · 2013-06-14 13:13:52Z

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, 2013 at 13:13

Rikky

5153 silver badges8 bronze badges

6 Comments

qebas Over a year ago

Thanks Rikky, I tried the Ajax form but couldn't make it work. Can you please provide more details on how to use it?

Ludo237 Over a year ago

AjaxForm is very useful if you have to upload Images or files in general. IMHO simple $.ajax(); call in jQuery is enough api.jquery.com/jQuery.ajax

qebas Over a year ago

@Rikky, you didn't send any link to the sample

qebas Over a year ago

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.

Rikky Over a year ago

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?

|

Collectives™ on Stack Overflow

Using Ajax with PHP and MySQL

2 Answers 2

3 Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related