如何使用php ajax和mysql动态更改内容

Question

I have created a page that populates a sidebar with links(categories) using a database table. The table contains names(names of the categories) and their ids.

     e.g 
           | **id** |**name**|
           |  1     |  Men   |
           |  2     |  Women |

I need to do the following things:

• When a link is clicked, a corresponding h1 tag changes it's value to the name of the link(the category name).

• A div tag, that displays product images below the h1 tag, also changes. I have created a table to hold product data. It contains the following data fields:

     **id**(id for the product)-INT[PRIMARY KEY]
     **category_id**(category id)-INT[FOREIGN KEY]
     **name**(product name)-VARCHAR
     **image**(image file name e.g "blu_dress.png" )
  
     e.g 
   | **id** |**categoty_id**|   **name**  |   **image**  |
   |  1     |       2       |black blouse |bla_blouse.png|
   |  2     |       2       |  blue dress | blu_dress.png|
   |  3     |       1       | brown shirt | bro_shirt.png|
   |  4     |       2       | blue blouse |blu_blouse.png|
  

PHP CODE TO POPULATE SIDEBAR

    <?php 
$dynamicList = "";

$sql = mysql_query("SELECT * FROM categories ORDER BY id ASC");

$productCount = mysql_num_rows($sql); // count the output amount

if ($productCount > 0) 
{
    while($row = mysql_fetch_array($sql)){ 
             $id = $row["id"];
             $category_name = $row["name"];
             $sql2 = mysql_query("SELECT * FROM categories WHERE id='".$id."'");
             $dynamicList .= '

                <li id="categegoryLink">
                    <a href="category.php?ID="'$category_name.'"" onclick="">   
                         '.$category_name.'
                    </a>
                </li>

                ';
    }
} 
else 
{
    $dynamicList = "We have no categories listed in our store yet";
}
mysql_close();
?> 

AJAX CODE

<script>
function loadXMLDoc()
{
var xmlhttp;
if (window.XMLHttpRequest)
  {// code for IE7+, Firefox, Chrome, Opera, Safari
  xmlhttp=new XMLHttpRequest();
  }
else
  {// code for IE6, IE5
  xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
  }
xmlhttp.onreadystatechange=function()
  {
  if (xmlhttp.readyState==4 && xmlhttp.status==200)
    {
    document.getElementById("title").innerHTML=xmlhttp.responseText;
    }
  }
xmlhttp.open("GET","category.php",true);
xmlhttp.send();
}
</script>

category.php

<?php echo $category_id?>

FURTHER INFO I can't post images since I don't have the reputation but I have an image that explains what I need further, if you need it.

Answer 1

I'll recommend using jQuery and for database use PDO instead of using mysql. The populated sidebar data is displayed as a list.

$options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION);

$db = new PDO('mysql:host=localhost;dbname=data','root', 'passwd', $options);
$sql = $db->query("SELECT * FROM categories ORDER BY id ASC"); // you can also use prepared statement.
 // displaying categories 
<ul id="category">
   <?php foreach ($sql as $category) : ?>
      <li data-id="<?php echo $category['category_id']; ?>"><?php echo $category['name']; ?></li>
   <?php endforeach; ?>
 </ul>
 <div id="results"></div>

And in your jQuery check for the clicked list item

$("#category li").click(function() {
           var id = $(this).data('id');
          $.post('get.php',{category_id:id},function(data){
              $('#results').html(data);
          });
     }); 

the code for the get.php file

<?php
$options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION);

$db = new PDO('mysql:host=localhost;dbname=data','root', 'passwd', $options);
$sql = $db->query("SELECT * FROM categories ORDER BY id ASC"); // you can also use prepared statement.
 if(isset($_POST['category_id'])){
$id = $_POST['id'];
$category = $db-query("SELECT * FROM categories WHERE id = $id");
}
 ?>
  <h1><?php    echo $category['name']; ?></h1>

    <!--display the results of the query here-->

Here when the user clicks any of the list items jQuery will get the value of the data-id attribute 0f which is the id of the category, post this value to get.php file and select data from the database based on id of the category. And return results form the database. The returned data is appended to the div with id results.