Creating Table

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
big_c147
Forum Newbie
Posts: 15
Joined: Wed Jun 22, 2005 9:32 am

Creating Table

Post by big_c147 »

Hi Guys

Right I have a table in a mysql database which I want to use to create a table on a page. The table structure can be seen below

CompId ProductID CustomerID Tonnage
....1.............23.............. 3.......... 55151
....1.............23...............9............326
....2.............23...............9..........324324



The table above shows how much of a given product a supplier sells to a customer. I want that to turn into a table like..........

....................................Customer1 (customerID=3 ).....Customer2 (customerID=9 )
Company1 (compid=1)....................55151.......................................326
Company2 (compid=2)...................................................................324324

However when I have tried to make the table myself I get the following result instead....


.....................Customer1........Customer2
Company1.........55151.................326
Company2.......324324

When I retrieve the records from the database, if company2 doesn’t supply customer1 it doesn’t shift to the next column, instead in puts the value for the next company in its place.

How can I make it shift over if no records exist for that company

The code I am using can be seen below

Code: Select all

<?
$array = array();

$odbc=mysql_connect ("", "", "") or die ('I cannot connect to the database because: ' . mysql_error());	

$sql = "SELECT DISTINCT tbltonnage.CompID,tblcomp.CompID,tblcomp.CompanyName from tbltonnage,tblcomp WHERE tbltonnage.ProductID = '$prod' AND tblcomp.CompID = tbltonnage.CompID"; 
mysql_select_db ("Competitors");

$result = mysql_query($sql,$odbc) or die(mysql_error());

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) 
{
$name = stripslashes($row[CompanyName]);
 
 $array[] = $name; 
 
}


$odbc=mysql_connect ("", "", "") or die ('I cannot connect to the database because: ' . mysql_error());	

$sql = "SELECT DISTINCT tblcustomer.CustomerID,tblcustomer.CustomerName,tbltonnage.CustomerID FROM tblcustomer,tbltonnage WHERE tbltonnage.ProductID = '$prod' AND tblcustomer.CustomerID=tbltonnage.CustomerID ORDER BY tbltonnage.CustomerID ASC";

mysql_select_db ("Competitors");

$result = mysql_query($sql,$odbc) or die(mysql_error());

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) 

{
$name = stripslashes($row[CustomerName]);
echo"<td align=\"center\" width=\"102\"><b>$name</b></td>";
}
?>
   
      
    </tr>
    
     <? 
echo"<tr>";
foreach ($array as $i => $value) 
{
$value = $array[$i];


echo"<td align=\"center\" width=\"102\"><b>$value</b></td>";

$sql = "SELECT DISTINCT tbltonnage.CompID,tblcomp.CompID,tblcomp.CompanyName,tbltonnage.Tonnage from tbltonnage,tblcomp WHERE ProductID = '$prod' AND tblcomp.CompID = tbltonnage.CompID AND tblComp.CompanyName = '$value' ORDER BY tbltonnage.CustomerID ASC";

mysql_select_db ("Competitors");

$result = mysql_query($sql,$odbc) or die(mysql_error());

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) 
{
$ton=number_format($row[Tonnage]);
echo"<td align=\"center\" width=\"102\">$ton</td>";
}
echo"</tr>";
}
?
Just to explain.... the first connection to the database gets all the supplierID's from the table and looks up there name from another table and puts them into an array

The second connection to the database gets all the customerID's from the table, looks up there real names and then echo’s them all across the top row of the table

The third connect, takes the names stored in the array from connection1, retrieves all records figures from the table and outputs them into the table on the web page

Thanks
Craig
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

to display a recordset in that fashion, you often must cache the recordset into memory so you can run through it creating table cells. Here's the basic idea:
  1. perform the query required (should often be a single query)
  2. use an array to store all rows in the query
  3. use a loop to iterate over the array, you could either use a generic system of display (which requires you pre-processing this array's orientation) or a special loop that knows how to get this orientation without altering the array
functions and constructs involved in this process are: for/foreach, array(), array_keys()

Here's an untested attempt at the rotation routine:

Code: Select all

function changeOrientation($array)
{
  $result = array();
  $keys = array_keys($array);
  $rows = array_keys($array[$keys[0]]);
  foreach($rows as $i => $row)
  {
    $result[$row] = array();
    foreach($keys as $j => $key)
    {
      $result[$row][$key] = (isset($array[$key][$row]) ? $array[$key][$row] : null);
    }
  }
  return $result;
}
Post Reply