How to update sql query with jquery

Question

Hello, I am still trying to update my sql query more dynamically but finding it difficult since im new to both PHP and jquery.

I have one main page (pagination.php) that looks like this:

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

  //Calculating no of pages
    $result = mysql_query($sql);
    $count = mysql_fetch_row($result);
    $pages = ceil($count[0]/$per_page);

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

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.1/jquery.min.js"&gt;&lt;/script&gt;
<script type="text/javascript" src="jquery_pagination.js"></script>

<div id="loading" ></div>
<div id="content" data-page="1"></div>
<ul id="pagination">


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


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

<a href="#" class="category" id="automotive">Automotive</a>

<a href="#" class="category" id="sports">Sports</a>

As you can see the top part of the script is calculating the number of pages for the pagination and then displays the numbers based on how many results in the database from this query:

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

This is were I'm having difficulty, I want to make the category part of the above query more dynamic. So, when the user clicks any of the three links, I want the id of the link clicked to be placed in the category part of the query using jquery.

Here is my jquery code:

$("#pagination a").click(function () {

    Display_Load();

    var this_id = $(this).attr('id');
    var pageNum = $('#content').attr('data-page');
    $("#content").load("filter_marketing.php?page=" + pageNum +'&id='+this_id, Hide_Load());    
});

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

Answer 1

There are two things that I see that need to be changed. First, the GET "id" variable needs to be in your sql statement, see the following:

<?php generate_pagination("SELECT COUNT(*) FROM explore WHERE category='".mysql_real_escape($_GET['id'])."'"); ?>

The other thing is that when you load the new content, you also need to change the pagination based on the new content information. This will require either (1) another script (like pagination_links.php) or (2)an adjustment of your pagination script to only return the contents of the generate_pagination function.

Other than these two things, it should work conceptually. Messy...but it should work.

Answer 2

Keep in mind, you need to be careful when doing this if it's going to be an external site. If you're building a SQL query with JS, it's EXTREMELY easy for someone to do SQL injection unless you're doing some checking in the backend. My advice would be to just pass certain parameters and use PHP to build the query itself.