ryanfc Posted August 24, 2007 Share Posted August 24, 2007 Ok I have 3 tables in my database: The client table holds the client name, address, city, state, zip, sub_category and category (and a few other fields but that is not important). In the sub_category and category fields in the client table are just numbers which match the id numbers in the other 2 tables. the category table holds the id number and title the sub_category table holds the id number, title and category (which is just a number that matches the id number in the category table. Hopefully that all makes sense. Now what the problem I have run into is I have a table on my page that loads all businesses for a category. So if a user clicks on the Dining button on the home page a list of all restaurants will display in my table. Well I want the table to show the sub_category the business belongs to, however the field in the client table is just a number. How do I get it to show the text (title) of that sub_category? Here is the code for that page: <!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> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /> <title>myevvspace.com | premium local listings</title> <link href="myevvspace_style.css" rel="stylesheet" type="text/css" /> <script src="navigation.js" type="text/javascript" language="javascript"></script> <?php //comment out database connection $category_num = (int)$_GET['category']; // Collects data from "friends" table $data = mysql_query("SELECT * FROM client WHERE category = '$category_num' order by name") or die(mysql_error()); $sub_list = mysql_query("SELECT * FROM sub_category WHERE category = '$category_num' order by title") or die(mysql_error()); $categorycrumb = mysql_query("SELECT title FROM category WHERE id = '$category_num'") or die(mysql_error()); $location = mysql_query("SELECT title FROM category WHERE id = '$category_num'") or die(mysql_error()); mysql_close ($conn); ?> </head> <body> <div id="header"><?php include("includes/header.php"); ?></div> <div id="navigation"><?php include("includes/navigation.php"); ?></div> <div id="banner"><?php include("includes/ads/banner.php"); ?></div> <div id="leftcontent"><p><form class="filter" method="GET" action="client_filter.php"> <?php $cat1 = "Select a Cuisine:<br />"; $cat2 = "Select a Bar Type:<br />"; $cat3 = "Select your Entertainment:<br />"; $cat4 = "Select an Art:<br />"; $cat5 = "Select a Lodging:<br />"; $cat6 = "Select a Community:<br />"; $cat7 = "Select a Real Estate:<br />"; $cat8 = "Select a Service:<br />"; $current_category = $category_num; if ($current_category == 1) { echo $cat1; } elseif ($current_category == 2) { echo $cat2; } elseif ($current_category == 3) { echo $cat3; } elseif ($current_category == 4) { echo $cat4; } elseif ($current_category == 5) { echo $cat5; } elseif ($current_category == 6) { echo $cat6; } elseif ($current_category == 7) { echo $cat7; } elseif ($current_category == { echo $cat8; } echo "<select class=\"filter\" name=subcat>\n"; while($sub = mysql_fetch_array( $sub_list )) { echo "<option name=\"sub_category\" value=\"".$sub['id']."\">".$sub['title']."</option>\n"; } echo "</select><input type=\"hidden\" name=\"category\" value= \"$category_num\" />\n<br /><div align=\"right\"><input class=\"filter\" type=\"submit\" name=\"Search\" value=\"Search\" /></div>"; ?></form></p></div> <div id="centercontent"> <div id="breadcrumb" align="right"><?php Print "<a href=index.php class=breadcrumb>Home</a> :: "; while ($crumb1 = mysql_fetch_array( $categorycrumb)) { echo "{$crumb1['title']}"; } ?></div> <p><img src="images/welcome.gif" alt="Welcome" width="106" height="21" /></p> <p>Below you will find a list of all businesses under the <?php while ($userloc = mysql_fetch_array( $location)) { echo "{$userloc['title']}"; } ?> category. Those companies that are paid advertisers can be clicked on for more detailed information.</p> <p>If you want to refine your search for a certain type of business just click on the list to the left.</p> <p>If you would like to become a paid advertiser and list your hours, location, menus, coupons and more please give us a call at (812) 402.1490.</p> <p> </p> <p> <?php $prev_row =''; echo '<table class=results cellpadding=2 cellspacing=0><tr height=22><td width=25% align=center bgcolor=#D5CB9A><b>Business Name</b></td><td width=25% align=center bgcolor=#D5CB9A><b>Address</b></td><td width=25% align=center bgcolor=#D5CB9A><b>Phone Number</b></td><td width=25% align=center bgcolor=#D5CB9A><b>Type</b></td></tr>'; while($info = mysql_fetch_array( $data )) { $letter = strtoupper(substr($info['name'],0,1)); if ($letter != $prev_row) { if($count % 1) { for ($i = ($count % 1); $i < 1; $i++) echo '<td class=results width=25%> </td>'; echo '</tr>'; $count =0; } $prev_row = $letter; echo '<tr bgcolor=#E7E7E7><td colspan=\"4\"><font color=#993833><b>',$letter,'</b></font></td></tr>'; } if ($count % 1 == 0) echo '<tr>'; $count++; if ($info['paid'] == 1) echo "<td class=results width=25%><a href=\"client_detail.php?category={$info['category']}&sub_category={$info['sub_category']}&client={$info['id']}\">", $info['name'], '</a></td><td class=results width=25%>', $info['address'], '</td><td width=25% class=results>', $info['phone'], '</td><td width=25% class=results>', $info['id'], '</td>'; else echo '<td class=results width=25%>', $info['name'], '</a></td><td class=results width=25%>', $info['address'], '</td><td class=results width=25%>', $info['phone'], '</td><td class=results width=25%>', $info['id'], '</td>'; if ($count % 1 == 0) echo '</tr>'; } if($count % 1) { for ($i = ($count % 1); $i < 1; $i++) echo '<td class=results width=33%> </td>'; echo '</tr>'; } echo '</table>'; ?></p> </div> <div id="searchbox" align="center"><form method="POST" action="search.php"><img src="images/spacer.gif" width="10" height="8" /><br /><input type="text" id="keyword" name="keyword" value="search" class="search" /></form></div> <div id="rightcontent" align="center"><p>text</p></div> </body> </html> If you need any more information from me just let me know. Thanks. Here is the link to the page to see how it looks. http://myevvspace.com/client_list.php?category=1 Link to comment https://forums.phpfreaks.com/topic/66510-solved-selecting-and-displaying-information-from-2-tables/ Share on other sites More sharing options...
lemmin Posted August 24, 2007 Share Posted August 24, 2007 I would suggest trying to combine all those queries into one statement, it is a lot more efficient. For example, (and your problem) you can get the sub_category name and the stuff from the client table in the same query. "SELECT *, (SELECT title FROM sub_category WHERE ID = client.sub_category) as subcategory FROM client WHERE category = '$category_num' order by name" You might need to replace "*" with the actual names for the sub query to recognize "slient.sub_category," I'm not sure. Anyway, the way this works is like an embedded loop. The outer query gets the selection with the criteria and the inner query can use what was returned as criteria. Link to comment https://forums.phpfreaks.com/topic/66510-solved-selecting-and-displaying-information-from-2-tables/#findComment-333144 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.