categorizing items in a list with edit and delete buttons

[ggw]

Hello!

 

We our creating an admin page to display a list of all the items within a mysql database, these items are within a table called "item". Each item is categorized, these categories are in a table called "category" each category has an ID. There is then a table called "links" which links the items to their category using ID's.

 

We want to display each item by their category with an edit and delete button. We have it so all the items display with the two buttons in no specific order however we are not too sure how to make it so they all display via their category.

 

Here is the code we are currently using:

<?php

$itemlist = mysql_query("SELECT item.item_name, item.item_description, item.item_image, item.item_price, item.item_id FROM item ORDER BY item.item_id ASC");

$item_count = 0;

?>

<?php while( $row = mysql_fetch_array($itemlist) ){?>

<?php if(($row['item.item_name'] == 1) && (isset($item_count) && $item_count < 1)) { ?>
<?php
$item_count ++;
} ?>

<li class="title">

<h3><?php echo $row['item_name'];?></h3><br />

<form action="ourworkadmin.php" method="get">

<input type="hidden" name="item_id" value="<?php echo $row['item_id'];?>" />
<input name="edit" value="Edit" type="submit" />
<input name="remove" value="Remove" type="submit" />

</form>

</li>

<?php } ?>

</ul> <!--Close-items_list-->
       	
</div>

 

 

Any help would be appreciated! Thank you!!!

[micah1701]

there are several ways to do this, including in the SQL directly using a join statement, but for your sake since this code is kinda a mess I would suggest nesting your loops and doing multiple queries.

 

something like this (this is not tested code, just showing you what I mean)

<?php
//first, look up all the categories and loop through them
$categories = mysql_query("SELECT * FROM `category` ORDER BY `category_name` ");
while( $category = mysql_fetch_assoc($categories) ){
?>
  <h1><?php echo $category['category_name'] ?></h1>
<?php  
   //now look up all the items associated with this category
   $items = mysql_query("SELECT * FROM `item` WHERE category_id = ". $category['id'] ." ORDER BY `item_name` ");
   while( $item = mysql_fetch_assoc($items){
?>  
     Your HTML HERE about this particular $item array
<?php
   } // end the loop through all of the items in this category
} // end the loop through all of the categories
?>

 

hope that helps