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>";
}
?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