SauloA Posted August 13, 2009 Share Posted August 13, 2009 I have a video table and a video category table. The tables are pretty much set up like the following: video_tbl ------------ video_id video_title video_cat_id video_cat_tbl ----------------- video_cat_id video_cat I have 2 recordsets, 1 for each table, that use the following queries video table SELECT video_cat, video_title FROM video_tbl, video_cat_tbl WHERE video_cat_tbl.video_cat_id = video_tbl.video_cat_id video category table SELECT * FROM video_cat_tbl ORDER BY video_cat ASC And I'm using the following PHP code to display the data: <?php do { ?> <table border="1" width="100%"> <tr> <td colspan="2"><?php echo $row_rsVideoCat['video_cat']; ?></td> </tr> <?php do { ?> <tr> <td><?php echo $row_rsVideo['video_title']; ?></td> </tr> <?php } while ($row_rsVideo = mysql_fetch_assoc($rsVideo)); ?> </table> <br /> <?php } while ($row_rsVideoCat = mysql_fetch_assoc($rsVideoCat)); ?> Using the code above the data displays like this Video Category 1 -------------------- Video 1 Video 2 Video 3 Video 4 Video Category 2 --------------------- But I want it to display like this: Video Category 1 -------------------- Video 1 Video 2 Video Category 2 --------------------- Video 3 Video 4 How do I display all the categories with with their specific Items under them? Quote Link to comment https://forums.phpfreaks.com/topic/170180-how-do-i-display-items-under-a-category-with-one-mysql-query/ Share on other sites More sharing options...
SauloA Posted August 14, 2009 Author Share Posted August 14, 2009 I've been doing some research and I think what I need to do is combine the 2 recordsets in an array. I still don't know what I'm doing though. I'll keep looking until I find the correct answer or somebody provides me the correct answer. Quote Link to comment https://forums.phpfreaks.com/topic/170180-how-do-i-display-items-under-a-category-with-one-mysql-query/#findComment-897775 Share on other sites More sharing options...
HoTDaWg Posted August 14, 2009 Share Posted August 14, 2009 <?php $connection = mysqli_connect('localhost','username','password','db_name') $query = "SELECT * FROM video_cat_tbl"; $result = mysqli_query($connection,$query); while($row = mysqli_fetch_array($result){ { echo $row['video_cat'] . '<br>'; $query2 = "SELECT * FROM video_tbl WHERE video_cat_id ='".$row['video_cat_id']."' LIMIT 2"; $result2 = mysqli_query($connection, $query2); while($row2 = mysqli_fetch_array($result2)){ echo $row2['video_title'] . '<br>'; } } ?> Based on your description, here is my interpretation. HoTDaWg Quote Link to comment https://forums.phpfreaks.com/topic/170180-how-do-i-display-items-under-a-category-with-one-mysql-query/#findComment-897783 Share on other sites More sharing options...
SauloA Posted August 14, 2009 Author Share Posted August 14, 2009 Thank you HoTDaWg. Your code has sparked the correct idea and achieved what I wanted to do. With your insight I was able to produce the following code: <?php do { ?> <table border="1" width="100%"> <tr> <td><?php echo $row_rsVideoCat['video_cat']; ?> <?php echo $row_rsVideoCat['video_cat_id']; ?></td> </tr> <?php mysql_select_db($database_connDB, $connDB); $query_rsVideo = "SELECT * FROM video_tbl WHERE video_cat_id ='".$row_rsVideoCat['video_cat_id']."'"; $rsVideo = mysql_query($query_rsVideo, $connDB) or die(mysql_error()); $row_rsVideo = mysql_fetch_assoc($rsVideo); $totalRows_rsVideo = mysql_num_rows($rsVideo); ?> <?php do { ?> <tr> <td><?php echo $row_rsVideo['video_title']; ?></td> </tr> <?php } while ($row_rsVideo = mysql_fetch_assoc($rsVideo)); ?> </table> <br /> <?php } while ($row_rsVideoCat = mysql_fetch_assoc($rsVideoCat)); ?> Also, the code you gave me had some errors. I believe it goes something like this: <?php $connection = mysqli_connect('localhost','username','password','db_name'); $query = "SELECT * FROM video_cat_tbl"; $result = mysqli_query($connection,$query); while($row = mysqli_fetch_array($result)) { echo $row['video_cat'] . '<br>'; $query2 = "SELECT * FROM video_tbl WHERE video_cat_id ='".$row['video_cat_id']."' LIMIT 2"; $result2 = mysqli_query($connection, $query2); while($row2 = mysqli_fetch_array($result2)){ echo $row2['video_title'] . '<br>'; }; }; ?> Quote Link to comment https://forums.phpfreaks.com/topic/170180-how-do-i-display-items-under-a-category-with-one-mysql-query/#findComment-897841 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.