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

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

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