Creating table from mysql data in PHP

Question

I am trying to make a project wherein it display the 3 previous monnth and the current month.. now my problem is I dont know where or how to reflect this mysql data into a table in php... can anyone taught me please?

Pardon me if I'm not good in explaining to you the system because I'm just a trying hard programmer..

this should be how the table will look like: http://www.fileden.com/files/2011/7/27/3174077//1.JPG

here is the php codes and mysql query that i want to put into a table:

<form action="" method="post" class="niceform">
<fieldset>
    <legend>Job Orders</legend>\
    <table>
<tr>
<th>SSA</th>
<th>Months</th>
</tr>

         <?php

$datefrom= $_POST['timestamp'];
$dateto=$_POST['timestamp1'];

$parsemonth="";
$parseday ="01";

$conditionmonth=$parsemonth-3;


//january
if ($conditionmonth == '1'){

$sql="SELECT
a.specialist_partner_ID
,count(CASE WHEN a.receivedDate between DATE_SUB('2011-01-01', INTERVAL 3 MONTH) and         DATE_SUB('2011-09-30', INTERVAL 3 MONTH) THEN a.job_order_number ELSE null END) As December,
count(CASE WHEN a.receivedDate between DATE_SUB('2011-01-01', INTERVAL 2 MONTH) and  DATE_SUB('2011-09-30', INTERVAL 2 MONTH) THEN a.job_order_number ELSE null END) As November
,count(CASE WHEN a.receivedDate between DATE_SUB('2011-01-01', INTERVAL 1 MONTH) and   DATE_SUB('2011-09-30', INTERVAL 1 MONTH) THEN a.job_order_number ELSE null END) As October 
,count(CASE WHEN a.receivedDate between '2011-01-01' and  '2011-01-30'THEN a.job_order_number ELSE null END) As Jauary
,count(job_order_number) As Total
FROM jo_partner a
WHERE a.receivedDate BETWEEN '2011-01-01' AND '2011-12-31'
GROUP BY a.specialist_partner_ID";
}

//february
else if ($conditionmonth == '2'){

$sql="SELECT
a.specialist_partner_ID
,count(CASE WHEN a.receivedDate between DATE_SUB('2011-02-01', INTERVAL 11 MONTH) and    DATE_SUB('2011-02-29', INTERVAL 3 MONTH) THEN a.job_order_number ELSE null END) As November
,count(CASE WHEN a.receivedDate between DATE_SUB('2011-02-01', INTERVAL 10 MONTH) and  DATE_SUB('2011-02-29', INTERVAL 2 MONTH) THEN a.job_order_number ELSE null END) As December
,count(CASE WHEN a.receivedDate between DATE_SUB('2011-02-01', INTERVAL 9 MONTH) and  DATE_SUB('2011-02-29', INTERVAL 1 MONTH) THEN a.job_order_number ELSE null END) As January
,count(CASE WHEN a.receivedDate between '2011-02-01' and  '2011-02-29'THEN a.job_order_number ELSE null END) As February
,count(job_order_number) As Total
FROM jo_partner a
WHERE a.receivedDate BETWEEN '2011-01-01' AND '2011-12-31'
GROUP BY a.specialist_partner_ID";
}

//march
else if ($conditionmonth == '3')
{

$sql="SELECT
a.specialist_partner_ID
,count(CASE WHEN a.receivedDate between DATE_SUB('2011-03-01', INTERVAL 3 MONTH) and       DATE_SUB('2011-03-31', INTERVAL 3 MONTH) THEN a.job_order_number ELSE null END) As December
,count(CASE WHEN a.receivedDate between DATE_SUB('2011-03-01', INTERVAL 2 MONTH) and  DATE_SUB('2011-03-31', INTERVAL 2 MONTH) THEN a.job_order_number ELSE null END) As Jauary
,count(CASE WHEN a.receivedDate between DATE_SUB('2011-03-01', INTERVAL 1 MONTH) and  DATE_SUB('2011-03-31', INTERVAL 1 MONTH) THEN a.job_order_number ELSE null END) As February
,count(CASE WHEN a.receivedDate between '2011-03-01' and  '2011-03-31'THEN a.job_order_number ELSE null END) As March
,count(job_order_number) As Total
FROM jo_partner a
WHERE a.receivedDate BETWEEN '2011-01-01' AND '2011-12-31'
GROUP BY a.specialist_partner_ID";
}

and so on and so forth... up to DECEMBER

while ($row = mysql_fetch_row($sql)
{

} 
?>
  </tr></table>
</fieldset>

the condition is when I select a month to view the report for that month, ex January, only data from January will only be visible and the last 3 consrctive month, ex december and november

Answer 1

I don't know if I understood correctly but heres my try.

Create the table with its headers:

<table>
<tr>
<th>Header 1</th>
<th>Header 2</th>
</tr>

Now, you can fill it up with the data you got from your MySql Query. I dont know how your data is structured but one way to do it would be like this:

<?php
    foreach($groupName as $group){
        echo "
            <tr>
                <td>".$group->a."</td>
                <td>".$group->b."</td>
            </tr>
        ";
    }
?>

Now just close the table.

Again, I didn't understand really well what you are aiming for but I hope this helps.

Answer 2

You should have another WHERE clause in the first mysql query, to specify the required date. Also, as suggested in comments, you should use mysql_real_escape_string() on all data incoming from user to prevent your database from getting hacked.

If your tables are storing an int with php time() when stored, you can substitute the three months from now (or whatever month you choose) and put it in the query, as shown here:

$january = 1293840000; // january 1st 00:00 unix timestamp
$SSAID = mysql_real_escape_string($SSAID); // injection..
$query = mysql_query("SELECT * FROM user WHERE SSA_ID = '$SSAID' AND timeAdded <= '$january'");

Then all you would have to do is loop through the data using

while ($row = mysql_fetch_row($result)
{

} 

and using heredoc. e.g.

$html = <<<HTML
<table>
 blahblah
</table>
HTML;

Hope this helps. Will provide code upon request, but these suggestions should be enough to produce what you want..

EDIT:

//looping through the mysql results and putting them in a table
$tables = "<table>";
while ($row = mysql_fetch_row($result)) {
    $tables .= "<tr>";   
    foreach ($row as $key => $value) {
        $tables .= <<<HTML
        <td>$value</td>
HTML;
        }
        $tables .= "</tr>";
}

Could you give us a var_dump($mysql_fetch_assoc($result)); result, as this would give us an idea of what your query returns as result, so we can provide an accurate answer (code rather)

Answer 3

Given that a table is build up using this kind of data:

<tr>
  <td>item1</td>
  <td>item2</td>
</tr>

It's really simple to build the table using SQL, just bracket the data using the table tags:

SQL only code, not recommended because it leaves open a XSS security hole!
Only use this if you are 100% sure the data inside your DB is safe

/*escape all data coming from a user */
/*or even better just escape all incoming data *period* */
$SSAID = mysql_real_escape_string($_SESSION['SESS_SSA_ID']);

/*don't use 2 queries if you can do it in one */  
$result = mysql_query("SELECT CONCAT('<tr>',GROUP_CONCAT(
                              CONCAT('<td>',ug.group_name,</td>), SEPARATOR ' ')
                              ,'</tr>') as tablestring 
                      FROM user_group ug
                      INNER JOIN user u ON (ug.usr_group_ID = u.user_group_id)
                      WHERE u.SSA_ID = ''$SSAID' )";

$row = mysql_fetch_row($result);
echo "here comes the table";
echo $row['tablestring'];

This code constructs the whole table in SQL. An other option is to loop though the values and piece together the table in a while loop:

Code example using a while loop, escapes the output, preventing XSS

$result = mysql_query("SELECT ug.group_name 
                      FROM user_group ug
                      INNER JOIN user u ON (ug.usr_group_ID = u.user_group_id)
                      WHERE u.SSA_ID = ''$SSAID' )";
$table = "<tr>";
while ($row = mysql_fetch_row($result));
{
  $table .= "<td>".htmlspecialchars($row['group_name'], ENT_QUOTES, 'UTF-8')."</td>";
}
$table .= "</tr>";
echo "here comes the table";
echo $table;

Points to remember

• Use mysql_real_escape_string() on all $vars you inject into a SQL-statement.
• Always escape data you echo onto the screen using htmlspecialchars() to prevent XSS.
• Get out of the select * habit, only select what you need.
• Don't use two queries and use php as a courier between them, use one query instead, see a tutorial on SQL joins.

Links
SQL-injection: How does the SQL injection from the "Bobby Tables" XKCD comic work?
XSS prevention: How to prevent XSS with HTML/PHP?
php-MySQL tutorial: http://www.tizag.com/mysqlTutorial/
group_concat: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat