Page 1 of 1
[Solved] Display html table based mySQL result
Posted: Fri Jan 18, 2008 1:31 am
by wzcocoon
Good morning,
I am new to php and I want to display the results from mySQl in an HTML table.
just to let you know, it is now 3 days I am working on it and I am about 45 to 50 cups of coffee and close to &^%*&$%^##$@#$...
for the moment this is what I get from my codes
Code: Select all
+-----+
| th |
+-----+-----+
|td | th |
+-----+-----+
|td | th |
+-----+-----+
|td |
+-----+
this is what I need
Code: Select all
+-----+-----+-----+
| th | th | th |
+-----+-----+-----+
| td | td | td |
+-----+-----+-----+
here is my codes
Code: Select all
<?php
// Connect to the database
include("_connect.php");
$catID = 2;
$sqlSub = "SELECT * FROM subCategories WHERE catID=$catID";
$querySub = mysql_query($sqlSub,$connect) or die ("subcategories");
echo "<table border=\"1\" cellpadding=\"10\">";
echo " <tr>";
while ($r1 = mysql_fetch_array($querySub)) {
$subCatName = $r1['subCatName'];
$subCatID = $r1['subCatID'];
$sqlPages = "SELECT * FROM pages WHERE catID='$catID' AND subCatID='$subCatID'";
$queryPages = mysql_query($sqlPages,$connect) or die ("pages");
echo " <th>".$subCatName. "</th>";
echo " </tr><tr>";
echo " <td>";
while ($r2 = mysql_fetch_array($queryPages)){
echo " <li><a href=\"pages.php?pageID=".$r2['pageID']."\">" .$r2['pageTitle']. "</a></li>";
}
echo " </td>";
}
echo " </tr>";
echo "</table>";
?>
please help me.
thank you
Re: Display html table based mySQL result
Posted: Fri Jan 18, 2008 2:27 am
by ddragas
try this
(NOT TESTED)
Code: Select all
include("_connect.php");
$catID = 2;
$sqlSub = "SELECT * FROM subCategories WHERE catID=$catID";
$querySub = mysql_query($sqlSub,$connect) or die ("subcategories");
echo "<table border=\"1\" cellpadding=\"10\">";
$r1 = mysql_fetch_array($querySub);
$subCatName = $r1['subCatName'];
$subCatID = $r1['subCatID'];
$sqlPages = "SELECT * FROM pages WHERE catID='$catID' AND subCatID='$subCatID'";
$queryPages = mysql_query($sqlPages,$connect) or die ("pages");
echo "<tr><th>".$subCatName. "</th></tr>";
while ($r2 = mysql_fetch_array($queryPages))
{
echo "<tr><td><li><a href=\"pages.php?pageID=".$r2['pageID']."\">" .$r2['pageTitle']. "</a></li></td></tr>";
}
echo "</table>";
Re: Display html table based mySQL result
Posted: Fri Jan 18, 2008 2:41 am
by wzcocoon
nop here is what I get with your script
Code: Select all
+---+
| [b]TH[/b] |
+---+
| td |
+---+
| td |
+---+
| td |
+---+
| [b]TH[/b] |
+---+
| td |
+---+
| td |
+---+
| [b]TH[/b] |
+---+
| td |
+---+
| td |
+---+
| td |
+---+
| td |
+---+
| td |
+---+
Re: Display html table based mySQL result
Posted: Fri Jan 18, 2008 2:49 am
by ddragas
sorry my mistake
I have edited my previous code. please try it
also try to use this code also
it retrives all data from table
Code: Select all
$query = "select * from some_table";
$result = mysql_query($query) or die (mysql_error());
$number_cols = mysql_num_fields($result);
//layout table header
echo "<table width=\"100%\" border=\"1\" cellspacing=\"0\" cellpadding=\"0\">";
echo "<tr>";
for ($i = 0; $i < $number_cols; $i++)
{
echo "<th>" . mysql_field_name($result, $i) . "</th>";
}
echo "</tr>\n";//end table header
//layout table body
while ($row = mysql_fetch_row($result))
{
echo "<tr >";
for ($i = 0; $i < $number_cols; $i++)
{
echo "<td >";
if (!isset($row[$i])) //test for null value
{
echo "";
}
else
{
echo $row[$i];
}
echo "</td>";
}
echo "</tr>\n";
}
echo "</table>";
Re: Display html table based mySQL result
Posted: Fri Jan 18, 2008 3:27 am
by wzcocoon
I try your edited code but here is what I am getting
Code: Select all
<table border="1" cellpadding="10">
<tr>
<th>General Information</th>
</tr>
<tr>
<td>
<li><a href="pages.php?pageID=14">Statement</a></li>
</td>
</tr>
<tr>
<td>
<li><a href="pages.php?pageID=15">International Director</a></li>
</td>
</tr>
<tr>
<td>
<li><a href="pages.php?pageID=16">Director of Outreach</a></li>
</td>
</tr>
</table>
Re: Display html table based mySQL result
Posted: Fri Jan 18, 2008 4:02 am
by wzcocoon
I also have edited part of your code but I am not yet their
Code: Select all
<?php
include("_connect.php");
$catID = 2;
$query = "SELECT * FROM subCategories WHERE catID=$catID";
$result = mysql_query($query) or die (mysql_error());
$number_cols = mysql_num_rows($result);
//layout table header
echo "<table width=\"100%\" border=\"1\" cellspacing=\"0\" cellpadding=\"10\">";
echo " <tr>";
for ($i = 0; $i < $number_cols; $i++)
{
$r1 = mysql_fetch_array($result);
$subCatName = $r1['subCatName'];
$subCatID = $r1['subCatID'];
echo " <th>" . $subCatName . "</th>";
}
echo " </tr>\n";
//end table header
$sqlPages = "SELECT * FROM pages WHERE catID='$catID' AND subCatID='$subCatID'";
$queryPages = mysql_query($sqlPages,$connect) or die ("pages");
//layout table body
//while ($row = mysql_fetch_row($result))
while ($row = mysql_fetch_array($queryPages))
{
echo "<tr >";
for ($i = 0; $i < $number_cols; $i++)
{
echo "<td >";
if (!isset($row[$i])) //test for null value
{
echo "";
} else {
echo " <li><a href=\"pages.php?pageID=".$row['pageID']."\">" .$row['pageTitle']. "</a></li>";
//echo $row['pageID'] . "-" . $row['pageTitle'];
//echo $row[$i];
}
echo "</td>";
}
echo "</tr>\n";
}
echo "</table>";
what it does not is
Code: Select all
+----------+----------+---------+
| cat1 | cat2 | cat3 |
+----------+----------+---------+
| cat3itm1 | cat3itm1 | cat3itm1 |
+----------+----------+---------+
| cat3itm2 | cat3itm2 | cat3itm2 |
+----------+----------+---------+
| cat3itm3 | cat3itm3 | cat3itm3 |
+----------+----------+---------+
but in the td it gave me only the item from the last cat
Re: Display html table based mySQL result
Posted: Fri Jan 18, 2008 4:07 am
by wzcocoon
this is what I am realy looking for
Code: Select all
+-------------+-------------+-------------+
| cat1 | cat2 | cat3 |
+-------------+-------------+-------------+
| cat 1 itm 1 | cat 2 itm 1 | cat 3 itm 1 |
| cat 1 itm 2 | cat 2 itm 2 | cat 3 itm 2 |
| cat 1 itm 3 | | cat 3 itm 3 |
| | | cat 3 itm 4 |
+-------------+-------------+-------------+
Re: Display html table based mySQL result
Posted: Fri Jan 18, 2008 8:40 am
by jimthunderbird
This should be easy... I'm thinking maybe use <div> or nested table when displaying each set of sub items under cat1, cat2,cat3, this way the display logic is closer to the application logic.
Re: Display html table based mySQL result
Posted: Fri Jan 18, 2008 9:50 am
by wzcocoon
I am not sure it is that easy. I have been working on this for the past 3 days and I cannot figure it out. now if you can help you are more then welcome.
Re: Display html table based mySQL result
Posted: Fri Jan 18, 2008 12:24 pm
by John Cartwright
wzcocoon wrote:this is what I am realy looking for
Code: Select all
+-------------+-------------+-------------+
| cat1 | cat2 | cat3 |
+-------------+-------------+-------------+
| cat 1 itm 1 | cat 2 itm 1 | cat 3 itm 1 |
| cat 1 itm 2 | cat 2 itm 2 | cat 3 itm 2 |
| cat 1 itm 3 | | cat 3 itm 3 |
| | | cat 3 itm 4 |
+-------------+-------------+-------------+
Can you explain (in english) the logic behind this? Posting your SQL table structure will help too.
Re: Display html table based mySQL result
Posted: Fri Jan 18, 2008 10:44 pm
by wzcocoon
what I want to optain is this
I have 2 tables one with subCategories in it and the other with pages and all the info about them
le loop get the Category ID from the PHP
then I need to connect to the
subCategories and get ALL the
subCategories that have this
CatID based on this result I need to connect to the pages table where every items as both a
catID and a
subCatID
now I need to build a table that will contain the
subCat name in the heather and under each
subCat Name the list of the pages with the same
subCatID
The heather comes from the
subCategories table and the list comes from the
pages table
this is how my subCategories table look like
Code: Select all
id int(11)
catID int(11)
subCatID int(11)
subCatABV varchar(10)
subCatName varchar(255)
and this is how my pages table look like
Code: Select all
pageID int(11)
catID int(11)
subCatID int(11)
menuID int(11)
pageTitle varchar(255)
pageKeywords text
pageDescription text
pageFlash text
pageContent text
pageNews text
the why I was mixing both of my loop was because at the second connection I was getting the subCatID from the previous loop and this ID changes every loop
Code: Select all
[color=#FF0000]$subCatID[/color] = $r1['subCatID'];
$sqlPages = "SELECT * FROM pages WHERE catID='$catID' AND subCatID='[color=#FF0000]$subCatID[/color]'";
I hope this make some sens
thanks
Re: Display html table based mySQL result
Posted: Sun Jan 20, 2008 11:20 pm
by wzcocoon
Thank you very much the code now works a bit different but it does the job I need it for thank you again for ALL your help and patience.
in case it can help someone else here it is
Code: Select all
// faire la loop
$sqlSub = "SELECT * FROM subCategories WHERE catID=$catID";
$querySub = mysql_query($sqlSub,$connect) or die ("Cannot Conect to: Menu Sub Categories");
$number_of_subCat = mysql_num_rows($querySub);
if ($number_of_subCat < 4 ){
$td_width = round(100/$number_of_subCat);
} else {
$td_width = round(100/4);
}
echo "<div id=\"menu_nav\"valign=\"top\">\n";
echo "<table border=\"1\" cellpadding=\"10\" width=\"100%\">\n";
echo "\t<tr>\n";
$cnt = 0;
while ($r1 = mysql_fetch_array($querySub)) {
echo "\t\t<td class=\"menu_nav\" width=".$td_width."%\">\n";
$subCatName = $r1['subCatName'];
$subCatID2 = $r1['subCatID'];
$sqlPages = "SELECT * FROM pages WHERE catID='$catID' AND subCatID='$subCatID2'";
$queryPages = mysql_query($sqlPages,$connect) or die ("pages");
echo "\t\t\t<h4>".$subCatName. "</h4>\n";
while ($r2 = mysql_fetch_array($queryPages)){
echo "\t\t\t<li><a href=\"pages.php?pageID=".$r2['pageID']."\">" .$r2['pageTitle']. "</a></li>\n";
}
if(++$cnt % 4 == 0){
echo "\t\t</td>\n";
echo "\t</tr>\n";
echo "\t<tr>\n";
} else {
echo "\t\t</td>\n";
}
}
echo "\t</tr>\n";
echo "</table>\n";
echo "</div>\n";