Hi
So I have a div with 6 links(categories of products), the beside this I have another div which I want to fill with the products for a chosen category when a link is clicked.
I have a mysql table containing all the products with each category having a unique product_id. If category 1 is click then I need all the products with product_id=1 to show.

Can anyone offer me advice on doing this??
As alway, thanks for looking

Glen

Recommended Answers

All 24 Replies

Hey.

What you are describing is pretty much the core of how all Web 2.0 applications work; the pages are populated from a data source based on the user's choice of content.

Basically, what you want to do is make the links pass the ID of the categories to PHP via the GET protocol. That's pretty simple. You just set the link query string to include the ID, like: showProducts.php?cat_id=N, where N is the ID number you want to pass. So, for a HTML link, that would look like:

<ul>
    <li><a href="showProducts.php?cat_id=1">Category 1</a></li>
    <li><a href="showProducts.php?cat_id=2">Category 2</a></li>
</ul>

Then, in the "showProducts.php" file, you fetch the ID passed to the page using the $_GET["cat_id"] super-global. Then you use that to fetch the data from the database. Here is an example of how you can do that.

<?php
// Always make sure the ID that you are trying to use
// was actually sent! Never assume user input is present
// and valid until you've verified that it really is.
if (!empty($_GET["cat_id"])) {
    // Convert the passed ID into an integer. This will
    // make sure it's either a valid number or a 0. Then
    // you can test that to find out if it's valid.
    // There are also methods like "is_int" and "is_numeric"
    // but in the case of IDs, this suffices.
    $id = (int)$_GET["cat_id"];
    if ($id > 0) {
        // Open a DB connection using PDO. Remember that the
        // "mysql_connect" and "mysql_query" functions are old
        // and deprecated. They should NOT be used for new
        // code. Use PDO or MySQLi instead.
        $dsn = "mysql:host=localhost;dbname=myDB";
        $dbh = new PDO($dsn, "DB User", "DB Pass");

        // Prepare a query to fetch the products matching
        // the ID of the category. Note that this method;
        // preparing a query and binding user input to it
        // like this, is the safest way to use user input
        // in a SQL query.
        $sql = "SELECT id, name, something
                FROM products
                WhERE category_id = :catid";
        $stmt = $dbh->prepare($sql);
        $stmt->bindValue(":catid", $id);

        // Execute the statement and do stuff with the
        // result set.
        if ($stmt->execute()) {
            foreach ($stmt as $row) {
                // Print each row here, or something!
                printProduct($row["id"], $row["name"]);
            }
        }
        else {
            echo "Failed to execute query!";
        }
    }
    else {
        echo "Invalid Category ID!";
    }
}
else {
    echo "No Category ID passed!";
}

Another variation is to use Ajax. The difference form Atli's approach is that the page does not get refreshed each time you click a link, only the content in the div changes. Te easiest way to do it is to use jQuery and it's ajax methods.

http://api.jquery.com/jQuery.ajax/
http://www.w3schools.com/jquery/jquery_ref_ajax.asp

With the chosen ajax method you call your php script that retrieves the contents from the DB and returns HTML code for displaying in the div.

Thanks for that guys!
I am trying to use the ajax aproach.

How do I get a mysql value into a variable, then pass that variable to another php file?

Thanks

Glen..

Please post the code you have so far (the page with links) and structure of the table(s).

I finally figured out how to get a value from mysql in a variable and pass it to another file using sessions!
But i still ave a problem, I will comment my code to explain

<?php
               include'connect.php';
               $q = "SELECT * FROM cat_images WHERE cat_id=1"; 

               if($r = mysql_query($q)){
                       session_start();

                       while($row=mysql_fetch_array($r)){
                       $_SESSION['id'] = $row['product_id']; //This makes $_SESSION['id] alway have the value of the last links product_id, how do i make it have the value of the product_id of whatever links is clicked
                          echo "<div class='image'>
                                <a>
                                <img class='cat_image' name='cat_image' src='{$row['imagepath']}' alt='{$row['name']}.Image' title='{$row['name']}' />
                                <div class='imgLabel'>{$row['name']}
                                </div></a></div>";
                        }
                    }
                    else{
                       echo mysql_error();
                    }

The table is set up like this:
id int(10) primary autoincrement
imagepath varchar(200)
name varchar(200)
cat_id int(10)
product_id int(10)

Thanks

This is when ajax comes in. Links will have a class, I'll name it category. jquery will catch clicks on all links having that class and sending a request to the php file that will retrieve the data and return it formated as html.

This is the file with links (the whole lot, containing the jquery bit):

<!DOCTYPE html>
<html>
<head>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.9.0/jquery.min.js"></script>
</head>
<body>

<!-- href attributes are the values of categories -->
<a href="1" class="category">Category 1</a>
<a href="2" class="category">Category 2</a>
<a href="3" class="category">Category 3</a>
<a href="4" class="category">Category 4</a>
<a href="5" class="category">Category 5</a>
<a href="100" class="category">Category 100</a>

<!-- This div will display the product data -->
<div id="product-data">
</div>
<?php
?>
<script>

// whenever a link with category class is clicked
$('a.category').click(function(e) {

    // first stop the link to go anywhere
    e.preventDefault();

    // you can get the text of the link by converting the clicked object to string
    // you something like 'http://mysite/categories/1'
    // there might be other methods to read the link value
    var linkText = new String(this);

    // the value after the last / is the category ID
    var categoryValue = linkText.substring(linkText.lastIndexOf('/') + 1);

    // send the category ID to the getProductData.php script using jquery ajax post method
    // send along a category ID
    // on success insert the returned text into the product-data div
    $.post('getProductData.php', {category: categoryValue}, function(data) {

        $('#product-data').html(data);
    });
});
</script>

</body>
</html>

And this is the script that reads data and echoes it back:

<?php
// if no category was sent, display some error message
if(!isset($_POST['category'])) {

    die('No category has been chosen');
}

// cast the category to integer (just a little bit of basic security)
$category = (int) $_POST['category'];

// this will be the string that you will return into the product-data div
$returnHtml = '';

include'connect.php';

$q = "SELECT * FROM cat_images WHERE cat_id='$category'";

if($r = mysql_query($q)) {

    // construct the html to return
    while($row = mysql_fetch_array($r)) {

        $returnHtml .= "<div class='image'><a><img ";
        $returnHtml .= "class='cat_image' ";
        $returnHtml .= "name='cat_image' ";
        $returnHtml .= "src='{$row['imagepath']}'";
        $returnHtml .= "alt='{$row['name']}' ";
        $returnHtml .= "title='{$row['name']}' />";
        $returnHtml .= "<div class='imgLabel'>{$row['name']}</div></a></div>";
    }
}

// display the html (you actually return it this way)
echo $returnHtml;
?>

BTW: in this case you do not need session variables to carry values arround so I didn't start it.

The problem now is this

I have set the href attribute

href='{$row['product_id']} //(1)

but when I now run this I just get the messeage mysite/1 does not exist on this server

Have you copied the above javascript code exactly as I posted it? Does it contain the line

e.preventDefault();

Your links should not go anywhere when you click on them (the above code prevents that) since you choose to use ajax. In this case links are there just to get people click on them but you allways stay on the same page, only the contents of the div changes.

I have everything exactly as you put it. But now the links do nothing, the content of the div does not change!

Silly mistake! I never changed the div id from your example to the id of my div!
All is working as it should be now!7

Thank you so much for your help, I appreciate it!

You are welcome, mate. Just to suggest an minor improvement: if the page grows and has more elments that might take some time to load, or if you change the triggers for ajax you might want to enclose the whole javascript/jquery code in $(document).ready handler which will ensure that functionality is available after the DOM is ready:

<script>
$(document).ready(function() {

    $('a.category').click(function(e) {

        ...

});
</script>

But that might not be absolutely neccessary in all cases.

I dont want my div to grow at all, it holds 9 images without growing(3 rows of 3). I have done sites with pagination in a gallery, I was going to do this. Will pagination work on a div(I usd it on whole pages) Should I still put my jquery code in a $(document).ready handler if I use pagination?

The ready handler will not change the functionality at all it will just make sure the functionality is loaded once the DOM is ready which in most cases takes just a fraction of second. And it affects only the page with jquery script not the ajax processing script (getProductData.php). In other words: you do not have to use the ready handler at this stage.

Pagination will work. In the getProductData.php page you have to prepare the html code for pagination and send it back into product-data div.

I'm sorry to bother you even more mate, but could you help me with my pagination?

This is what I have so far

<?php
include 'connect.php';
// if no category was sent, display some error message
if(!isset($_POST['category'])) {
    die('No category has been chosen');
}

                    $per_page = 12;
                    $pages_query = mysql_query("SELECT COUNT(`id`) FROM `products`") or die(mysql_error());
                    $pages = ceil(mysql_result($pages_query, 0) / $per_page);

                    $page = (isset($_GET['page']) AND (int)$_GET['page'] > 0) ? (int)$_GET['page'] : 1;
                    $start = ($page - 1) * $per_page;
                    $last = ($pages - 1) / $per_page;
                    $prev = $page - 1;                                                      
                    $next = $page + 1; 
                    if ($page >= 1 && $page < $pages){ 
                       echo "<span class='next'>";
                       echo "<a href=\"?page={$next}\">Next page</a> ";
                       echo "</span>";
                       if ($page >=2){
                       echo "<span class='prev'>";
                       echo "<a href=\"?page={$prev}\">Previous page</a> ";
                       echo "</span>";
                       }
                    }
                    else if ($page == $pages && $page > 1){
                       echo "<span class='prev'>";
                       echo "<a href=\"?page={$prev}\">Previous page</a> ";
                       echo "</span>";
                    }           
                    else if ($page > $pages){
                       echo 'No more images in the database';
                    }
// cast the category to integer (just a little bit of basic security)
$category = (int) $_POST['category'];
// this will be the string that you will return into the product-data div
$returnHtml = '';
$q = "SELECT * FROM products WHERE product_id='$category' ORDER BY id DESC LIMIT $start, $per_page";
if($r = mysql_query($q)) {
    // construct the html to return
    while($row = mysql_fetch_array($r)) {
        $returnHtml .= "<div class='image1'><a><img ";
        $returnHtml .= "class='cat_image1' ";
        $returnHtml .= "name='cat_image' ";
        $returnHtml .= "src='{$row['imagepath']}'";
        $returnHtml .= "alt='{$row['product_name']}' ";
        $returnHtml .= "title='{$row['product_name']}' />";
        $returnHtml .= "<div class='imgLabel1'>{$row['product_name']} <br />&pound {$row['price']}</div></a></div>";
    }
}
// display the html (you actually return it this way)
echo $returnHtml;
?>

This doesnt give me pagination(wont go to the next page, just returns to the divs origional content) and always display the 'next page'link.

This won't work since you use links for your pagination which refresh the whole page which in turn defeats the purpose of ajax. If you want to keep the ajax approach then you have to do the pagination with ajax too (not using querystrings and $_GET) but this might be more complicated. If you want to use traditional non-ajax approach you will have to drop the ajax functionality just implemented.

And what is the difference and which approach to choose? When using non-ajax approach the page will refresh on each click on any link while with ajax approach only the contents of div will refresh. But ajax is meant to be used when you want to refresh a small portion f your page (i.e. the shopping basket contents) and you do not want to refresh the whole page. In your case the most of page will get refreshed anyway so it is maybe better to use non-ajax approach which is less complicated and easier to mantain.

If you want to stick with ajax approach I am happy to help but have never done this way of pagination through ajax calls so I am not sure how difficult it is. It might take some time. If you decide to do it non-ajax way than again I do not have much experience with coding pagination since I always used library for taht (PEAR Pager is a package that does this job nicely.

I do like how ajax loads the contents into the div without a page refresh!
I will have a look and see if I can find any tuts online for this.

Thank you

I'm no further with this! Could you help me out, or even point me to some tutorials?

Thanks...

I've been trying to implement the plugin on your first link. I cant get it to work!
I have followed the instructions and it isnt working, I'm not getting any pagination. Do you think i woukd need to change my existing code?

Heres both my files.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head>

   <link href="../css/template.css" rel="stylesheet" type="text/css" />
   <link href="../css/cat_image.css" rel="stylesheet" type="text/css" />
   <link href="../css/index.css" rel="stylesheet" type="text/css" />

   <script src="../jquery/jquery.js"></script>
   <script type="text/javascript" src="../jquery/jquery.simplePagination.js"></script>


   <title>Adult Toys</title>
</head>

   <body>
      <div class="wrap">
         <div id="header">
            <div id="logo">
               <a href="home.php"><img src="../images/siteimages/logo.gif" alt="Logo image" /></a>
            </div> 
         </div>
          <div id="menu">
             <div id='cssmenu'>
               <ul>  
                     <li><a href='home.php'>link1</a></li>
                     <li><a href='forher.php'>link2</a> </li>
                     <li><a href='forhim.php'>link3</a></li>

                 </ul
              </div>
           </div>
         <div id="content">
            <div class='categories'>

               <?php
               include'connect.php';
               $q = "SELECT * FROM cat_images WHERE cat_id=1"; 

               if($r = mysql_query($q)){

                       while($row=mysql_fetch_array($r)){

                          echo "<div class='image'>
                                <a href='{$row['product_id']}' class='category'>
                                <img class='cat_image' name='cat_image' src='{$row['imagepath']}' alt='{$row['name']}.Image' title='{$row['name']}' />
                                <div class='imgLabel'>{$row['name']}
                                </div></a></div>";
                        }
                    }
                    else{
                       echo mysql_error();
                    }
            ?>
               <div style="clear: both;">&nbsp;</div>
            </div>
            <div id="info">  
            hi
            <script>
               $(document).ready(function() {
               // whenever a link with category class is clicked
               $('a.category').click(function(e) {
               // first stop the link to go anywhere
               e.preventDefault();
              // you can get the text of the link by converting the clicked object to string
             // you something like 'http://mysite/categories/1'
             // there might be other methods to read the link value
             var linkText = new String(this);
            // the value after the last / is the category ID
            var categoryValue = linkText.substring(linkText.lastIndexOf('/') + 1);
           // send the category ID to the getProductData.php script using jquery ajax post method
           // send along a category ID
          // on success insert the returned text into the product-data div
          $.post('showproducts.php', {category: categoryValue}, function(data) {
          $('#info').html(data);
         });
         });
        });
   </script>
   <script>
       $(function() {
    $(selector).pagination({
        items: 100,
        itemsOnPage: 10,
        cssStyle: 'light-theme'
    });
});
   </script>
            </div>




        </div>

        <div id="footer">
        footer text.................
        </div>
     </div>
   </body>
</html>

And my showproducts.php file

<?php
include 'connect.php';

// if no category was sent, display some error message
if(!isset($_POST['category'])) {
    die('No category has been chosen');
}
// cast the category to integer (just a little bit of basic security)
$category = (int) $_POST['category'];
// this will be the string that you will return into the product-data div
$returnHtml = '';
$q = "SELECT * FROM products WHERE product_id='$category' ORDER BY id DESC";
if($r = mysql_query($q)) {
    // construct the html to return
    while($row = mysql_fetch_array($r)) {
        $returnHtml .= "<div class='image1'><a><img ";
        $returnHtml .= "class='cat_image1' ";
        $returnHtml .= "name='cat_image' ";
        $returnHtml .= "src='{$row['imagepath']}'";
        $returnHtml .= "alt='{$row['product_name']}' ";
        $returnHtml .= "title='{$row['product_name']}' />";
        $returnHtml .= "<div class='imgLabel1'>{$row['product_name']} <br />&pound {$row['price']}</div></a></div>";
    }
}
// display the html (you actually return it this way)
echo $returnHtml;
?>

Any ideas?

Thanks..

If you use this plugin you have to do a few more things:

  • get the number of total pages for that particular product (using COUNT(*) WHERE product_id=...) and store it into items varialble
  • prepare a php script that will return the records for one page, using itemsOnPage parameter (or modify existing script)
  • use onPageClick function which will call above mentioned php script to fetch pages

But documentation for this plugin is not very comprehensive. So maybe you try this one, which has a nice tutorial along with it.

I have never done any jquery/ajax pagination so I can not give you any finished code. I used PEAR Pager class for this purpose and it was doing it's job very well.

Hi,
So I have been doing a bit of reading about pear pager. To be honest I'm none the wiser!

Can it put pagination on a div? Will it work with my existing code?

I'm really struggling to paginate this div!

Thanks

Yes, but then you have to forget all the ayax stuff. This means that on each click (either on one of the links for categories or links for pages) the wholepage will be refreshed/reloaded. This in your case is OK since about 90% of page contents gets refreshed anyway.

To use Pager for paginating DB results see this link (from the maintainer of the package) and use method #2 which is simpler.

If you do not have many results for each div you can also fake pagging. You read all the product data but display only a portion of it and hide the rest (using CSS display:none). On clicking page links you can then unhide another portion and hide previous.

I eventually got this sorted.

This is what my final code looks like

<script>
// whenever a link with clategory class is clicked
$('a.category').click(function(e) {
// first stop the link to go anywhere
e.preventDefault();
//get the text of the link by converting the clicked object to string
var linkText = new String(this);
// the value after the last / is the category ID
var categoryValue = linkText.substring(linkText.lastIndexOf('/') + 1);
// send the category ID to the getProductData.php script using jquery ajax post method
// send along a category ID
// on success insert the returned text into the chosen div
$.post('showproducts.php', {category: categoryValue}, function(data) {
//find total number of records
var totalRecords = $(data).length;
//define how many records shown per page
var pageSize = 8;
//work out number of pages needed to hold records
var numOfPages = Math.ceil(totalRecords / pageSize);
//make page links
var i,
  pageLinks = '<div class="pageLinks">';
for (i = 0; i < numOfPages; i++) {
  pageLinks += '<a href="#" onclick="showToyPage(' + i + ');return false;">' + (i + 1) + '<\/a> ';
}
pageLinks += '<\/div>';
//display returned data and page links in chosen div ('info)
$('#info').html(pageLinks + data);
showToyPage(0);
});
});
//function to slice up records into pages
function showToyPage( pageNo ) { 
    var perPage = 8; 
    var start = pageNo * perPage; 
    var end = start + perPage; 
    $('.image1').hide().filter(function(index) { 
        return ( (index > (start-1)) && ( index < end ) ); 
    } ).show(); 
}

</script>
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.