boo_lolly Posted November 17, 2006 Share Posted November 17, 2006 i have a mysql table with a few columns. some of the rows in this table might contain the same content in the same column. for instance. my column titles are 'uID' 'product' 'item' 'need' 'yah' 'blah' 'blah'... here's what i need i need some advice on how to design a loop (or set of loops) to output an HTML. i need to match all the rows in the table that have the same conent in the 'product' column. i do this by putting "ORDER BY product" in the sql command. the difficulty is involved in the display of the content. i want to write a loop that will output HTML tables. each HTML table will have a title that spans across the whole table. that title will be the product. then, display the rest of the information below it (still inside the HTML table). as soon as the mysql_fetch_array comes across a row in the sql table that has a new name for the 'product' column, end the HTML table, and start again. this time, display the NEXT product name as the title of the new HTML table. this loop will run until there are no more rows left in the mysql table. here's what i got so far...[code] $row = mysql_query("SELECT * FROM ". $regID ."") or die(mysql_error()); $num_rows = mysql_num_rows($row); echo "Total Number of Items in this registry: ". $num_rows .""; echo "<TABLE BORDER=1><TR><TH COLSPAN=7 ALIGN=left>Category: ". $row['category'] ."</TH></TR>";// BEGIN SOME TYPE OF LOOP HERE echo "<TR><TH>Item</TH><TH>Quantity Requested</TH><TH>Still Needs</TH><TH>Price</TH><TH>View</TH><TH>Quantity</TH><TH>Buy</TH></TR>";// EXECUTE THIS LOOP (or some loop like it) for($i=0; $i < $num_rows; $i++) { $row = mysql_fetch_array($row); echo "<TR><TD>". $row['item'] ."</TD><TD>". $row['qty_req'] ."</TD><TD>". $row['still_needs'] ."</TD><TD>VAR price</TD><TD>VAR link</TD><TD>Input Field</TD><TD>Add to Cart</TD></TR>"; }// UNTIL IT REACHES A ROW WITH A NEW NAME IN ITS 'product' COLUMN THEN END TABLE... echo "</TABLE><br /><br />"; //DO THE WHOLE THING OVER AGAIN (back up to first loop)// WHEN THE WHOLE THING IS COMPLETELY DONE, CLOSE DATABASE CONNECTION... mysql_close($db);[/code]any help? Quote Link to comment Share on other sites More sharing options...
btherl Posted November 17, 2006 Share Posted November 17, 2006 If I understand correctly, you can do it like this.[code=php:0]// Start first table$last_product = null;for ($i = 0; $i < $num_rows; $i++) { if ($row['product'] !== $last_product && $last_product !== null) { // End previous table and start new table $last_product = $row['product']; } // Display row}// End final table[/code] Quote Link to comment Share on other sites More sharing options...
jsladek Posted November 17, 2006 Share Posted November 17, 2006 You sure that SQL works? Is your table name $regID ?I always do this..[code] //create sql $sql_user_data="SELECT * FROM table_name"; #print("$sql_user_data"); // this statement actually executes the sql $result = mysql_query($sql_user_data); //access record info and build form while($row = mysql_fetch_array($result)) {print("$row[image]<br>");}John[/code] Quote Link to comment Share on other sites More sharing options...
boo_lolly Posted November 17, 2006 Author Share Posted November 17, 2006 maybe this will shed some light on the situation... my SQL table looks like this:[code]+--------------+------+-------------+------------------+ | CATEGORY | ITEM | QTY_REQ | STILL_NEEDS |+--------------+------+-------------+------------------+| kitchen | pan | 5 | 2 |+--------------+------+-------------+------------------+| drapes | sheet| 2 | 1 |+--------------+------+-------------+------------------+| kitchen |glass | 4 | 2 |+--------------+------+-------------+------------------+| office | pens | 3 | 1 |+--------------+------+-------------+------------------+[/code]i need to write a loop to output an HTML table that looks like this...[code]+-------------------------------------------------------+ | CATEGORY: drapes |+-------------------------------------------------------+ | ITEM | QTY_REQ | STILL_NEEDS |+-----------------+------------------+------------------+| sheet | 2 | 1 |+-----------------+------------------+------------------++-------------------------------------------------------+ | CATEGORY: kitchen |+-------------------------------------------------------+ | ITEM | QTY_REQ | STILL_NEEDS |+-----------------+------------------+------------------+| glass | 4 | 2 |+-----------------+------------------+------------------+| pan | 5 | 2 |+-----------------+------------------+------------------++-------------------------------------------------------+ | CATEGORY: office |+-------------------------------------------------------+ | ITEM | QTY_REQ | STILL_NEEDS |+-----------------+------------------+------------------+| pens | 3 | 1 |+-----------------+------------------+------------------+[/code]get it now? Quote Link to comment Share on other sites More sharing options...
komquat Posted November 17, 2006 Share Posted November 17, 2006 Is someone selecting the catergory somewhere? Quote Link to comment Share on other sites More sharing options...
komquat Posted November 17, 2006 Share Posted November 17, 2006 How about something like this. I did not test, just threw this together quick.[code=php:0]//create array$cat_array = (cat1, cat2, cat2);$num_cat = 3;//Start you loop here$i = 1;for ($i <= $num_cat; $i++)//create sql $display = "<table><tr><td colspan='3'>$cat_array[$i]</td></tr>"; $sql_user_data="SELECT * FROM table_name WHERE catergory = '$cat_array[$i]'"; $result = mysql_query($sql_user_data); while($row = mysql_fetch_array($result)) { $f1 = $row['f1']; $f2 = $row['f2']; $f3 = $row['f3'];$display .= "<tr><td>$f1</td><td>$f2</td><td>$f3</td></tr>"; }$display .= "</table>"; }[/code] Quote Link to comment Share on other sites More sharing options...
craygo Posted November 17, 2006 Share Posted November 17, 2006 Try This[code]// Print Headersprint '<table width="500" align="center" cellspacing=0 cellpadding=0>';print '<tr bgcolor="F98F5B">';print '<td width=300 align=center>ITEM</td>';print '<td width=100 align=center>QTY_REQ</td>';print '<td width=100 align=center>STILL_NEEDS</td>';print '</tr>';print '</table>';// Set initial group values$lastcat = '';// Query database$sql = "SELECT * FROM ". $regID ." ";$sql .= "GROUP BY CATAGORY, ITEM"; $res = mysql_query($sql); $num_rows = mysql_num_rows($res);if($num_rows > 1){// Set initial row color$bgcolor = "FFFFFF";// Start Loop while ($rows = mysql_fetch_assoc($res)){// Print Group Headerif ($rows['CATAGORY'] != $lastcat) {// Print Group Nameprint '<table width="500" align="center" cellspacing=0 cellpadding=0>';print '<tr bgcolor=83EA44>';print '<td colspan=3 align=left><strong>Catagory: '.$rows['CATAGORY'].'</strong></td>';print '</tr>';print '</table>';}// Alternate row colorif ($bgcolor == "#E0E0E0"){ $bgcolor = "#FFFFFF";} else { $bgcolor = "#E0E0E0";}// Print Database Detailsprint '<table width=500 align=center cellspacing=0>';print '<tr bgcolor='.$bgcolor.'>';print '<td width=300 align=center>'.$rows['ITEM'].'</td>';print '<td width=100 align=center>'.$rows['QTY_REQ'].'</td>';print '<td width=100 align=center>'.$rows['STILL_NEEDS'].'</td>';print '</tr>';print '</table>';// Reset group values$lastcat = $rows['CATAGORY'];}} else {// Print No data messageprint '<table width=650 align=center>';print '<tr>';print '<td align=center><strong>NO ITEMS!!</strong></td>';print '</tr>';print '</table>';}?>[/code]Ray Quote Link to comment Share on other sites More sharing options...
komquat Posted November 17, 2006 Share Posted November 17, 2006 Craygo, you are the best! Quote Link to comment Share on other sites More sharing options...
boo_lolly Posted November 17, 2006 Author Share Posted November 17, 2006 no one is selecting a category. the SQL table will always be changing (admin adding, editing, removing data), and i will never know exactly what's in there or isn't in there. so this loop needs to be 'intelligent' enough to recognize a change on it's own. either you guys don't understand what i'm trying to do, or i don't understand that you guys are helping me greatly... haha, either way, there's miscommunication =) Quote Link to comment Share on other sites More sharing options...
komquat Posted November 17, 2006 Share Posted November 17, 2006 Cryago's above should work for you. I made a quick little variation that should make it look just like you want, give it a shot, or else play with it to get it looking like you want, it should work.[code=php:0]// Set initial group values$lastcat = '';// Query database$sql = "SELECT * FROM ". $regID ." ";$sql .= "GROUP BY CATAGORY, ITEM"; $res = mysql_query($sql); $num_rows = mysql_num_rows($res);if($num_rows > 1){// Set initial row color$bgcolor = "FFFFFF";// Start Loop while ($rows = mysql_fetch_assoc($res)){// Print Group Headerif ($rows['CATAGORY'] != $lastcat) {// Print Group Nameprint '<table width="500" align="center" cellspacing=0 cellpadding=0>';print '<tr bgcolor=83EA44>';print '<td colspan=3 align=left><strong>Catagory: '.$rows['CATAGORY'].'</strong></td>';print '</tr>';print '<tr bgcolor="F98F5B">';print '<td width=300 align=center>ITEM</td>';print '<td width=100 align=center>QTY_REQ</td>';print '<td width=100 align=center>STILL_NEEDS</td>';print '</tr>';}// Alternate row colorif ($bgcolor == "#E0E0E0"){ $bgcolor = "#FFFFFF";} else { $bgcolor = "#E0E0E0";}// Print Database Detailsprint '<tr bgcolor='.$bgcolor.'>';print '<td width=300 align=center>'.$rows['ITEM'].'</td>';print '<td width=100 align=center>'.$rows['QTY_REQ'].'</td>';print '<td width=100 align=center>'.$rows['STILL_NEEDS'].'</td>';print '</tr>';print '</table>';// Reset group values$lastcat = $rows['CATAGORY'];}} else {// Print No data messageprint '<table width=650 align=center>';print '<tr>';print '<td align=center><strong>NO ITEMS!!</strong></td>';print '</tr>';print '</table>';}?>[/code] Quote Link to comment Share on other sites More sharing options...
boo_lolly Posted November 17, 2006 Author Share Posted November 17, 2006 i can't tell you guys how much i appreciate all of your help and input. but everytime i try it gives me an error.[b]Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /../../../../registry.php on line 37NO ITEMS!![/b][code]<?php//registry.php @ $db = mysql_connect("yah", "blah", "blah"); if(!$db) { echo "Error: Could not connect to the database. Please try again later."; exit; } mysql_select_db("registry_DB", $db); $query = "SELECT * FROM ". $regID ." ORDER BY category"; $res = "mysql_query($query) or die(mysql_error().': '.$query)";$lastcat = '';$num_rows = mysql_num_rows($res); //<-- LINE 37if($num_rows > 1){// Set initial row color$bgcolor = "FFFFFF";// Start Loop while ($rows = mysql_fetch_assoc($res)){// Print Group Headerif ($rows['CATAGORY'] != $lastcat) {// Print Group Nameprint '<table width="500" align="center" cellspacing=0 cellpadding=0>';print '<tr bgcolor=83EA44>';print '<td colspan=3 align=left><strong>Catagory: '.$rows['CATAGORY'].'</strong></td>';print '</tr>';print '<tr bgcolor="F98F5B">';print '<td width=300 align=center>ITEM</td>';print '<td width=100 align=center>QTY_REQ</td>';print '<td width=100 align=center>STILL_NEEDS</td>';print '</tr>';}// Alternate row colorif ($bgcolor == "#E0E0E0"){ $bgcolor = "#FFFFFF";} else { $bgcolor = "#E0E0E0";}// Print Database Detailsprint '<tr bgcolor='.$bgcolor.'>';print '<td width=300 align=center>'.$rows['item'].'</td>';print '<td width=100 align=center>'.$rows['qty_req'].'</td>';print '<td width=100 align=center>'.$rows['still_needs'].'</td>';print '</tr>';print '</table>';// Reset group values$lastcat = $rows['category'];}} else {// Print No data messageprint '<table width=650 align=center>';print '<tr>';print '<td align=center><strong>NO ITEMS!!</strong></td>';print '</tr>';print '</table>';}?>[/code] Quote Link to comment Share on other sites More sharing options...
craygo Posted November 17, 2006 Share Posted November 17, 2006 if it is giving you that error then the query is not running or the sql statement is wrong. I see you echo'd out the sql statement, what is it saying??are you sure $regID is being set??$regID is the table name... why??? isn't the information always coming from the same table?? Also it is not going to work unless you group the info not order it.Ray Quote Link to comment Share on other sites More sharing options...
boo_lolly Posted November 17, 2006 Author Share Posted November 17, 2006 you're right the query WAS wrong, but i fixed it and it STILL didn't work. $regID is the name of the SQL table. i have a search/results page that returns a list of... well... results. haha. and EACH result is followed by a link. when a user clicks this link, it takes them to the page that you and i have been having problems with (registry.php). each search result has an affiliated table in the SQL database. the $regID is the name of the SQL table that matches the result clicked on the search/results page. as in, there will ALWAYS be SQL tables added and deleted and edited 'on the fly' with the CMS i'm building for this. these pages are designed for public use. i'm building an administrative CMS to edit the content of the SQL tables, therefore, changing what the public views. Quote Link to comment Share on other sites More sharing options...
craygo Posted November 20, 2006 Share Posted November 20, 2006 When you echo the sql what are teh results. Sorry but don't check the forums on the weekend! :)Ray Quote Link to comment 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.