[RESOLVED] Display html table based mySQL result

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

+-----+
| th  |
+-----+-----+
|td   | th  |
+-----+-----+
|td   | th  |
+-----+-----+
|td   |
+-----+

this is what I need

+-----+-----+-----+
| th  | th  | th  |
+-----+-----+-----+
| td  | td  | td  |
+-----+-----+-----+

here is my codes

<?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

Kudose

You need to loop your table headings then loop your table data.

Currently you run your data loop in the headings loop, which is jacking it all up.

wzcocoon

I try this but it still does not work

<?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'];
		echo "		<th>".$subCatName. "</th>";
	}

echo "	</tr><tr>";
echo "		<td>";

$sqlPages 		= "SELECT * FROM pages WHERE catID='$catID'";
$queryPages 	= mysql_query($sqlPages,$connect) or die ("pages");
	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>";
?>

this is what I get

<table border="1" cellpadding="10">
	<tr>
		<th>General Information</th>
		<th>Restoration</th>
		<th>Volunteer/Staff</th>
	</tr><tr>
		<td>
			<li><a href="pages.php?pageID=2">Aboutus</a></li>
			<li><a href="pages.php?pageID=12">Information</a></li>
			<li><a href="pages.php?pageID=14">Statement</a></li>
			<li><a href="pages.php?pageID=15">International</a></li>
			<li><a href="pages.php?pageID=16">Outreach</a></li>
			<li><a href="pages.php?pageID=17">Annual festival</a></li>
			<li><a href="pages.php?pageID=18">our Headquarters</a></li>
			<li><a href="pages.php?pageID=19">Relief Fund</a></li>
			<li><a href="pages.php?pageID=24">The Glory</a></li>
			<li><a href="pages.php?pageID=25">the Stones</a></li>
			<li><a href="pages.php?pageID=26">Volunteer in Israel</a></li>
			<li><a href="pages.php?pageID=27">Position</a></li>
			<li><a href="pages.php?pageID=28">Volunteer</a></li>	
		</td>
	</tr>
</table>

basically I am getting all ly results in ONE TD and not in 3 like the #'s of TH.

Kudose

You need to put that into the code. Right now it is looping through each result like you asked it.

You should put in some sort of delimiter, say every 5 records, end the table cell and make a new one.

eg:

$cnt = 0;
while ($r2     = mysql_fetch_array($queryPages))
    {    

        echo "            <li><a href=\"pages.php?pageID=".$r2['pageID']."\">" .$r2['pageTitle']. "</a></li>";
if(++$cnt % 5 == 0) echo '</td><td>';
    }

Of course, if you have too many record, then this method won't work right.

How are you looking to separate the data?

wzcocoon

The thing is that I have let say 3 categories each as plus or minus items in them one as 2 the other as 4 the 3rd as 6 what I need is for the code to:

go to subCategories table and bring me ALL the fields with the correct subCatID

the build a table with the subCatName in the TH
then under each TH build me the list of the pages that have that subCatID

+------------------+-------------------+------------------+-------------------+
| subCatName1 | subCatName2 | subCatName3 | subCatName4 |
+------------------+-------------------+------------------+-------------------+
| subCat1 Item1 | subCat2 Item1 | subCat3 Item1 | subCat4 Item1 |
| subCat1 Item2 | subCat2 Item2 | subCat3 Item2 | subCat4 Item2 |
| subCat1 Item3 | subCat2 Item3 | subCat3 Item3 | subCat4 Item3 |
| subCat1 Item4 | subCat2 Item4 | subCat3 Item4 | subCat4 Item4 |
+------------------+-------------------+------------------+-------------------+

Gigantor

what does your table schema look like?
are you sure the DB is normalised?

like Kudose said, the problem is in your loop logic, you have the <TD> tag, then a while() loop, then when the loop is done, you close the <TD> tag.

this is going to create a cell, loop through the array echoing data, then close the cell. If you want each array element echo'd to be in its own cell, just change it to..

    while ($r2     = mysql_fetch_array($queryPages))
    {    

        echo "<td><li><a href=\"pages.php?pageID=\".$r2['pageID'].\"\">\" .$r2['pageTitle'].\ "</a></li></td>";
    }

and remove the original <td> tags that are outside the loop.

wzcocoon

what does your table schema look like?
are you sure the DB is normalised?

I have 2 tables one called
subCategories

id     int(11)
catID     int(11)
subCatID     int(11)
subCatABV     varchar(10)
subCatName     varchar(255)

the other called pages

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

if my DB is normalized or not I have no idea this is my first php site.
I will try your code

thanks

wzcocoon

Kudose wrote:
...
You should put in some sort of delimiter, say every 5 records, end the table cell and make a new one.

eg:
$cnt = 0;
while ($r2     = mysql_fetch_array($queryPages))
    {    

        echo "            <li><a href=\"pages.php?pageID=".$r2['pageID']."\">" .$r2['pageTitle']. "</a></li>";
if(++$cnt % 5 == 0) echo '</td><td>';
    } 
...

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

// 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";