database driven table

[jrobles]

I have a table that I want to convert into a dynamic mysql driven table. The table below is the static table that I want to change. It needs to be the same width and #of columns and rows. For now I have 6 products but want when I add more to the db i want them to show up with pagination for my layout only allows for 6 products at a time

 

 

<table width='580' border='0' align='center' cellpadding='2' cellspacing='0' id='nfpcatalog'>

          <tr>

            <td colspan='2' valign='top' width='193'><div align='center'><a href='#'><img name='Item' border='0' src='images/cream-thumb.jpg' width='100' height='100' alt='Intensive Anti-Aging Night Cream' title='Intensive Anti-Aging Night Cream' style='background-color: #CCCCCC' /></a></div></td>

            <td colspan='2' valign='top' width='193'><div align='center'><a href='#'><img name='Item' border='0' src='images/lotion-thumb.jpg' width='100' height='100' alt='Daily Rewind Lotion' title='Daily Rewind Lotion' style='background-color: #CCCCCC'></a></div></td>

            <td colspan='3' valign='top' width='193'><div align='center'><a href='#'><img name='Item' border='0' src='images/milk-thumb.jpg' width='100' height='100' alt='Transforming Cleansing Milk' title='Transforming Cleansing Milk' style='background-color: #CCCCCC'></a></div></td>

          </tr>

          <tr>

            <td valign='top'><div align='right'><a href='#'><img src='images/moreinfo4.png' alt='More Info' title='More Info' width='50' height='35' border='0' /></a></div></td>

            <td valign='top'><div align='left'><a href='cart.php?action=add&id=2'><img src='images/addtocart4.png' alt='Add to Cart' title='Add to Cart' width='50' height='35' border='0' /></a></div></td>

            <td valign='top'><div align='right'><a href='#'><img src='images/moreinfo4.png' alt='More Info' title='More Info' width='50' height='35' border='0' /></a></div></td>

            <td valign='top'><div align='left'><a href='cart.php?action=add&id=2'><img src='images/addtocart4.png' alt='Add to Cart' title='Add to Cart' width='50' height='35' border='0' /></a></div></td>

            <td valign='top'><div align='right'><a href='#'><img src='images/moreinfo4.png' alt='More Info' title='More Info' width='50' height='35' border='0' /></a></div></td>

            <td valign='top'><div align='left'><a href='cart.php?action=add&id=2'><img src='images/addtocart4.png' alt='Add to Cart' title='Add to Cart' width='50' height='35' border='0' /></a></div></td>

          </tr>

          <tr>

            <td colspan='2' valign='top' width='193'><div align='center' class='style2'>Intensive Anti-Aging Night Cream</div></td>

            <td colspan='2' valign='top' width='193'><div align='center' class='style2'>Daily Rewind Lotion</div></td>

            <td colspan='3' valign='top' width='193'><div align='center' class='style2'>Transforming Cleansing Milk</div></td>

          </tr>

          <tr>

            <td colspan='2' valign='top' width='193'><div align='center'>$75.00</div></td>

            <td colspan='2' valign='top' width='193'><div align='center'>$75.00</div></td>

            <td colspan='3' valign='top' width='193'><div align='center'>$65.00</div></td>

          </tr>

          <tr>

            <td colspan='8' valign='top'><hr></td>

          </tr>

          <tr>

            <td colspan='2' valign='top' width='193'><div align='center'><a href='#'><img name='Item' border='0' src='images/masque-thumb.jpg' width='100' height='100' alt='Facial Renewal Clay Masque' title='Facial Renewal Clay Masque' style='background-color: #CCCCCC'></a></div></td>

            <td colspan='2' valign='top' width='193'><div align='center'><a href='#'><img name='Item' border='0' src='images/toner-thumb.jpg' width='100' height='100' alt='Balancing Marine Toner' title='Balancing Marine Toner' style='background-color: #CCCCCC'></a></div></td>

            <td colspan='3' valign='top' width='193'><div align='center'><a href='#'><img name='Item' border='0' src='images/scrub-thumb.jpg' width='100' height='100' alt='Purifying Marine Scrub' title='Purifying Marine Scrub' style='background-color: #CCCCCC'></a></div></td>

          </tr>

          <tr>

            <td valign='top'><div align='right'><a href='#'><img src='images/moreinfo4.png' alt='More Info' title='More Info' width='50' height='35' border='0' /></a></div></td>

            <td valign='top'><div align='left'><a href='cart.php?action=add&id=2'><img src='images/addtocart4.png' alt='Add to Cart' title='Add to Cart' width='50' height='35' border='0' /></a></div></td>

            <td valign='top'><div align='right'><a href='#'><img src='images/moreinfo4.png' alt='More Info' title='More Info' width='50' height='35' border='0' /></a></div></td>

            <td valign='top'><div align='left'><a href='cart.php?action=add&id=2'><img src='images/addtocart4.png' alt='Add to Cart' title='Add to Cart' width='50' height='35' border='0' /></a></div></td>

            <td valign='top'><div align='right'><a href='#'><img src='images/moreinfo4.png' alt='More Info' title='More Info' width='50' height='35' border='0' /></a></div></td>

            <td valign='top'><div align='left'><a href='cart.php?action=add&id=2'><img src='images/addtocart4.png' alt='Add to Cart' title='Add to Cart' width='50' height='35' border='0' /></a></div></td>

          </tr>

          <tr>

            <td colspan='2' valign='top' width='193'><div align='center' class='style2'>Facial Renewal Clay Masque</div></td>

            <td colspan='2' valign='top' width='193'><div align='center' class='style2'>Balancing Marine Toner</div></td>

            <td colspan='3' valign='top' width='193'><div align='center' class='style2'>Purifying Marine Scrub</div></td>

          </tr>

          <tr>

            <td colspan='2' valign='top' width='193'><div align='center'>$85.00</div></td>

            <td colspan='2' valign='top' width='193'><div align='center'>$65.00</div></td>

            <td colspan='3' valign='top' width='193'><div align='center'>$55.00</div></td>

          </tr>

      </table>

 

Any help would be greatly appreciated

[monkeypaw201]

Well, I would start by taking a look at a simple query tutorial (http://www.w3schools.com/PHP/php_mysql_select.asp) and looping through each row (s) you want repeated and fill them with data.

 

Then take a look at pagination using MySQL Offsets (http://www.petefreitag.com/item/451.cfm)

[Tranceflux]

Hi Jrobles,

 

You might wanna consider the script that I use for my website to generate a dynamic table. As for pagination, I'm afraid you would have to seek help in other tutorials because I haven't included that in my website yet.

 

<?php
//Setting up connection and selecting database//
include "connection.php";

//Defining array holders//

$item_id = array();
$item_image = array();
$item_name = array();
$item_colour = array();
$item_price = array();
$item_account = array();
$item_category = array();
$item_likes = array();

//Defining how many cells to show ($length; you can set it or let it figure out how many to show, see below) and how many per row ($itemperrow)

if(!isset($itemperrow))
$itemperrow = 5;
if(isset($length))
$length = $length + 1;

//Get information needed from database//

$sql = "SELECT * FROM item ORDER BY item_likes DESC";
$results = mysql_query($sql,$con);
$length = mysql_affected_rows() + 1;

//Loop cell generation, I don't start with $i=0 because there will be a problem when you want to end the row, see all the way below

echo('<table><tr>');
for($i=1;$i<$length;$i++)
{
$row = mysql_fetch_array($results);
$item_id[$i] = $row['item_id'];
$item_name[$i] = $row['item_name'];
$item_colour[$i] = $row['item_colour'];
$item_price[$i] = $row['item_price'];
$item_account[$i] = $row['item_account'];
$item_category[$i] = $row['item_category'];
$item_likes[$i] = $row['item_likes'];
$imageid = $row['item_img'];

//Selecting the corresponding image from image table (I store my images of product in another table, which is why I need the code below, feel free to alter the code to suit how you store your images
$sql = "SELECT * FROM image WHERE image_id = '$imageid'";
$result = mysql_query($sql,$con);
$row = mysql_fetch_array($result);
$grabpath = $row['image_path'];
$item_image[$i] = $grabpath;

//Selecting the account id from account table, as I want to display who uploaded it
$sql = "SELECT * FROM account WHERE account_name = '$item_account[$i]'";
$result = mysql_query($sql,$con);
$row = mysql_fetch_array($result);
$account_id = $row['account_id'];

//This is the important cell generation part, tweak the HTML according to what you want
echo('<td><a href="item.php?id=' . $item_id[$i] . '"><img src="' . $item_image[$i] . '" id="image" onload="resize(\'image\')" /></a></td>');

        //Detects if a new row needs to be created, using the modulus function to find if there is any remainder. Eg. 3 divide by 3 (the function has generated the 3rd cell, the itemperrow is 3, so the next cell should be on a new row. Remainder gives 0, thus echo out a new row
if($i%$itemperrow==0)
{echo('</tr><tr>');}
}
//End of the demonic table
echo('</tr></table>');

?>

 

I hope this helps. As for the width etc etc, I would suggest that you use a CSS spreadsheet to define all these as it will make the whole code easier to work with.

 

Cheers!