[Solved] Display html table based mySQL result

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
User avatar
wzcocoon
Forum Newbie
Posts: 8
Joined: Fri Jan 18, 2008 1:21 am
Location: CyberSpace

[Solved] Display html table based mySQL result

Post 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
Last edited by wzcocoon on Sun Jan 20, 2008 11:21 pm, edited 1 time in total.
User avatar
ddragas
Forum Contributor
Posts: 445
Joined: Sun Apr 18, 2004 4:01 pm

Re: Display html table based mySQL result

Post 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>";
 
 
Last edited by ddragas on Fri Jan 18, 2008 2:48 am, edited 1 time in total.
User avatar
wzcocoon
Forum Newbie
Posts: 8
Joined: Fri Jan 18, 2008 1:21 am
Location: CyberSpace

Re: Display html table based mySQL result

Post 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 |
+---+
User avatar
ddragas
Forum Contributor
Posts: 445
Joined: Sun Apr 18, 2004 4:01 pm

Re: Display html table based mySQL result

Post 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>";
 
User avatar
wzcocoon
Forum Newbie
Posts: 8
Joined: Fri Jan 18, 2008 1:21 am
Location: CyberSpace

Re: Display html table based mySQL result

Post 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>
User avatar
wzcocoon
Forum Newbie
Posts: 8
Joined: Fri Jan 18, 2008 1:21 am
Location: CyberSpace

Re: Display html table based mySQL result

Post 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
User avatar
wzcocoon
Forum Newbie
Posts: 8
Joined: Fri Jan 18, 2008 1:21 am
Location: CyberSpace

Re: Display html table based mySQL result

Post 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 |
+-------------+-------------+-------------+
 
User avatar
jimthunderbird
Forum Contributor
Posts: 147
Joined: Tue Jul 04, 2006 3:59 am
Location: San Francisco, CA

Re: Display html table based mySQL result

Post 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.
User avatar
wzcocoon
Forum Newbie
Posts: 8
Joined: Fri Jan 18, 2008 1:21 am
Location: CyberSpace

Re: Display html table based mySQL result

Post 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.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: Display html table based mySQL result

Post 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.
User avatar
wzcocoon
Forum Newbie
Posts: 8
Joined: Fri Jan 18, 2008 1:21 am
Location: CyberSpace

Re: Display html table based mySQL result

Post 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

Code: Select all

$catID          = 2;
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
User avatar
wzcocoon
Forum Newbie
Posts: 8
Joined: Fri Jan 18, 2008 1:21 am
Location: CyberSpace

Re: Display html table based mySQL result

Post 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";
Post Reply