Filtering Database

[skoobi]

Hi im having a few issues trying to filter my database. Im new to php but im getting there slowly....

 

Anyway what im tyring to do is display 2 different tables one has the user information and the other has the order status and what they've ordered and quantity.

 

What i want to do is have a drop down list on the top of the table which i can filter the order status and product id.

 

Here is the code i have so far which works but hasnt got the filters in.

 

<?php

//Database Connection
$con = mysql_connect("localhost","my_user","my_password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("my_database",$con);

// The '$result', is just a variable, so you don't have to type the function again
$result = mysql_query("SELECT * FROM jos_vm_order_user_info INNER JOIN jos_vm_order_item ON jos_vm_order_item.order_id = jos_vm_order_user_info.order_id");


$order=mysql_fetch_array($result, MYSQL_ASSOC);
   echo $order['order_id'];

// HTML table to display the user information

echo "<table border='1'>
<tr>
<td><strong>Order ID</strong></td>
<td><strong>Order Status</strong></td>
<td><strong>Product Id</strong></td>
<td><strong>Product Qty</strong></td>
<td><strong>Last Name</strong></td>
<td><strong>First Name</strong></td>
<td><strong>Email</strong></td>
<td><strong>Post Code</strong></td>
</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['order_id'] . "</td>";
  echo "<td>" . $row['order_status'] . "</td>";
  echo "<td>" . $row['product_id'] . "</td>";
  echo "<td>" . $row['product_quantity'] . "</td>";
  echo "<td>" . $row['last_name'] . "</td>";
  echo "<td>" . $row['first_name'] . "</td>";
  echo "<td>" . $row['user_email'] . "</td>";
  echo "<td>" . $row['zip'] . "</td>";
  echo "</tr>";
  }
echo "</table>";

mysql_close($con);
?>

 

Any help on this would be greatful.

 

Thanks

Chris

[skoobi]

Also while i remember the order status displays as a 'C' how do i change that to display 'Complete'  ???

 

Thank you

[kickstart]

Hi

 

This isn't really a MySQL issue.

 

You can filter the data returned by MySQL easily with something like this:-

 

$result = mysql_query("SELECT * FROM jos_vm_order_user_info INNER JOIN jos_vm_order_item ON jos_vm_order_item.order_id = jos_vm_order_user_info.order_id WHERE order_status = $FilterOrderStatus");

 

The problem is that you will need to provide a drop down list to select how to filter it (so you will need the SQL to populate these drop down lists) and also turn your current display into a form which can return the filter items to redisiplay the data once filtered. This is pretty simple but it more php than MySQL.

 

An alternative depending on the amount of data would be to set the page up with all the data in an array in Javascript, and then use Javascript to display the page and filter the info. Or you could do something similar with Ajax.

 

Sorry, probably not really the answer you were looking for.

 

All the best

 

Keith