display rows from MYSQL in table form

[rmmo]

Hi all,

 

i have a table in my data base called 'product' it contains 4 columns 'prod_id','prod_name','prod_desc' and 'prod_price' so my goal is to display all rows from the table on my page in a tabular format.

 

i have already had a stab at this... below is the code i came up with... only problem is that the table shows up with the headers only... no data... and only the cells for the headers are correct the others dont show... so does that mean there is a problem with the <td>s and <tr>s??? another thing that may be stopping the data showing is the <DBQUERY> tag... the book i got that from is UBER old... so it may be depreciated.. anyway you guys will know better than me...

 

// connect to and select db
   $conn = mysql_connect($host, $dbuser, $dbpass) or trigger_error("SQL", E_USER_ERROR);
   $db = mysql_select_db($dbname, $conn) or trigger_error("SQL", E_USER_ERROR);

if($db)
{
echo "<table border bgcolor ='green'>";
echo "<tr><th>prod_id<th><th>prod_name<th><th>prod_desc<th><th>prod_price</th>";
echo "<dbquery q> select * from product";
echo "<dbrow><tr><td><? q.prod_id></td><td><? q.prod_name></td><td><? q.prod_desc></td><td><? q.prod_price></td></tr>";
echo "</dbquery>";
echo "</tr>";
echo "</table>";
}
else 
{
echo "sorry could not retreive product listings";
}
?> 

 

 

any help would be greatly appreciated 

 

RMMO

 

[mbeals]

maybe it's just not syntax I'm used to, but I've never seen queries handled like that.  Personally, I would do like:

 

<?
//database connection stuff
if($db){
  $query = "select * from product";
  $result = mysql_query($query);

?>
<table border bgcolor ='green'>
<tr>
  <th>prod_id</th>
  <th>prod_name</th>
  <th>prod_desc</th>
  <th>prod_price</th>
</tr>
<?
  while($row = mysql_fetch_array($result) ){  ?>
<tr>
  <td><?=$row['prod_id'] ?></td>
  <td><?=$row['prod_name'] ?></td>
  <td><?=$row['prod_desc'] ?></td>
  <td><?=$row['prod_price'] ?></td>
</tr>
<?}  //end while
   }else die("sorry could not retreive product listings"); ?>

 

basically, I perform the search, build the table structure and headings (static data), then fill in the dynamic stuff with a while loop.  Does that make sense?

You are also missing your / on the closing <th> blocks in the original code.

 

 

 

[rmmo]

yeah that worked great.... thanks alot for that!

 

like i said im using a ancient book... so thats my products displayed! do you have any idea how i could have checkboxes next to each row so that a user could select some rows to pass to an 'orders' page?

 

thanks alot for all the help!

 

RMMO

[mbeals]

<tr>
  <td><?=$row['prod_id'] ?></td>
  <td><?=$row['prod_name'] ?></td>
  <td><?=$row['prod_desc'] ?></td>
  <td><?=$row['prod_price'] ?></td>
  <td><input type="checkbox" name="<?=$row['prod_id'] ?>" value="1" /></td>
</tr>

 

then wrap the whole table in a form tag.  If prod_id isn't unique to each product, then just replace the 'name' attribute with something that is.

This will give you a $_POST array with all prod_id's as keys and '1' as values for the ones that were checked.  Just iterate through the $_POST array to get all 'checked' values.  To see what I mean, direct the form to a php page with

 

print_r($_POST);

 

at the top and you will see how that table is being broken down.