如何动态更新sql查询 - jquery

Question

I am trying to figure out how I can update my sql query dynamically.

On the main page, I have a pagination script that counts how many pages there will be:

<?php
function generate_pagination($sql) {
  include_once('config.php');
  $per_page = 3;

  //Calculating no of pages
  $result = mysql_query($sql);
  $count = mysql_num_rows($result);
  $pages = ceil($count/$per_page);

  //Pagination Numbers
  for($i=1; $i<=$pages; $i++)
  {
    echo '<li class="page_numbers" id="'.$i.'">'.$i.'</li>';
  }
}
?>

I then have in the body of the same page this line of code to populate the page numbers:

<?php generate_pagination("SELECT * FROM explore WHERE category='marketing'"); ?>

So, that line is displaying the necessary amount of page numbers to be displayed for just the 'marketing' category.

The problem that I am having is with that single line of code. I want to make the category dynamic, so instead of it being hardcoded to 'marketing' I would like jquery to get the id of an element and place it in.

The element would be this link that I have on the same page:

<a href="#" class="category" id="marketing">Marketing</a>

So, when the user clicks that link, I am trying to place the id of the link in the category section of the query using jquery.

I hope that made sense, and if anyone can assist me on this that would be great.

Answer 1

First, the PHP side:

<?php
function generate_pagination($sql) {
  include_once('config.php');
  $per_page = 3;

  //Calculating no of pages
  $result = mysql_query($sql);
  $count = mysql_num_rows($result);
  $pages = ceil($count/$per_page);

  //Pagination Numbers
  for($i=1; $i<=$pages; $i++)
  {
    echo '<li class="page_numbers" id="'.$i.'">'.$i.'</li>';
  }
}
?>

<?php generate_pagination("SELECT * FROM explore WHERE category='" . mysql_real_escape_string ( $_POST ['category'] ) . "'"); ?>

Then in your jquery post:

$("a.category").click(function() {
  $.post("test.php", { category: $(this).attr("id") },
    function(data){
      //Load your results into the page
    });
});

On click we take the ID, pass it to the server as a post variable category, then on the server, grab it, escape it properly for security, and use that query. Load your results the same way you are now, that part doesn't change.