I'm very stuck with my case. Here is I will give that stuck :
I have an table :
form_no | model_name | status | date_check
Now I have an data :
1 | mouse | OK | 26-Apr-2013
2 | mouse | NG | 27-Apr-2013
Now I want to show it in my page using PHP code. We see that model_name have a same name, but have different status and date_check.
What I want to do is, how to make it to be 1 information without looping data. So the data will show in my page like this :
No | Model Name | Status Yesterday | Status Today
1 | mouse | OK | NG
-
<table>
<td>No</td><td>Model Name</td><td>Status Yesterday</td><td>Status Today</td>
<tr>
<?php
$query = "SELECT * FROM t_prod";
$result = mysql_query($query);
while ($data = mysql_fetch_array($result))
{
$model_name = $data['model_name'];
?>
<td>1</td><td><?php echo $model_name; ?></td><td>Status yesterday</td><td>Status Today</td>
<?php
}
?>
</table>
We see that in column Status yesterday and Status Today still not have a PHP code, cause I confused how to make it show based on yesterday and today.
Last time I just make it like this :
I add
$status_yesterday = $data['status'];
$status_today = ?
But it will just for yesterday, and if I add status_today like status yesterday it will be same data.
You can do a LEFT JOIN in your query, and create a new column - status_yesterday
SELECT
t.form_no, t.model_name, t.status as status_today, y.status_yesterday
FROM
t_prod t
LEFT JOIN
(SELECT model_name, status AS status_yesterday FROM t_prod WHERE date_check = '2013-04-26') y
ON t.model_name = y.model_name
WHERE date_check = '2013-04-27'
GROUP BY model_name
see a SQLFiddle. http://sqlfiddle.com/#!2/e2de8/3
Note I have hardcoded the dates in the query, but you can modify it using NOW() and NOW() - INTERVAL 1 DAY