how to fetch data with same id in a row but different value in another column and display into table in php

Question

I have following data in my SQL table:

id      code     day     time    year
 1     PRC-001    0       t-1     2017
 2     PRC-001    1       t-2     2017
 3     PRC-002    0       t-3     2017
 4     PRC-002    1       t-4     2017
 5     PRC-003    0       t-5     2017
 6     PRC-003    1       t-6     2017

Output should be like this:

day0     day1     code      
 t-1      t-2    PRC-001
 t-3      t-4    PRC-002    
 t-5      t-6    PRC-003

How can I do this? I was trying something like the following code. But I didn't get any desire output. Here is my code:

$query1 = "SELECT * FROM routine AS tab1,"; 
$query1.= " GROUP BY code";

$rs = mysql_query($query1);
$numOfRows=mysql_num_rows($rs);
$printed = array();
$resultset = array();

while($row = mysql_fetch_assoc($rs)) {
    $resultset[] = $row;
    #print_r($row);
}

$q = "<table id='ash'>";
$q.= "<tr id='grey'>";
$q.= "<th rowspan='2'>day0</th>";
$q.= "<th rowspan='2'>day1(s)</th>";
$q.= "<th rowspan='2'>code</th></tr>";
$q.= "</tr>";


foreach ($resultset as $row){ 
    $q.= "<tr>";
    $q.= "<tr><td id='clist'>".$row["time"]."</td>";
    $q.= "<td id='clist'>".$row["time"]."</td>";
    $q.= "<td id='clist'>".$row["code"]."</td></tr>";
} 
$q .= "</table>";
echo $q;

yeah I think it can be done by if else condition. But can't find the exact way. — Tanzila Islam, Jan 04 '17 at 08:10
Just an FYI but you should try and avoid using `mysql_` functions. There are the `mysqli_` functions (http://php.net/manual/en/mysqli.query.php) which share a very similar api though. http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php — Rwd, Jan 04 '17 at 08:26
That query will obviously trigger an error. And PHP's mysql_ API was effectively deprecated years ago. — Strawberry, Jan 04 '17 at 08:31

score 1 · Accepted Answer · answered Jan 04 '17 at 08:43

Firstly, as mentioned in the comments you should look at using something other than the mysql_ functions.

Second, with you're query you need to remove the GROUP BY.

Then you could do something like:

$rs = mysql_query("SELECT * FROM routine");

$results = [];

while ($row = mysql_fetch_assoc($rs)) {

    $code = $row['code'];

    if (!isset($results[$code])) {
        $results[$code] = [
            'day0' => '-',
            'day1' => '-',
        ];
    }

    $results[$code]['day' . $row['day']] = $row['time'];

}

?>

<table>
    <thead>
    <tr id="grey">
        <th rowspan="2">Day0</th>
        <th rowspan="2">Day1(s)</th>
        <th rowspan="2">code</th>
    </tr>
    </thead>

    <tbody>
    <?php foreach ($results as $code => $result) : ?>
        <!--You shouldn't have multiple elements using the same ids-->
        <tr>
            <td id='clist'><?php echo $result['day0'] ?></td>
            <td id='clist'><?php echo $result['day1'] ?></td>
            <td id='clist'><?php echo $code ?></td>
        </tr>
    <?php endforeach ?>
    </tbody>
</table>

Hope this helps!

thanks. it works :) I have another query. If I want to set the value of fetched data, how can I write a function for that? For example: t-1=> "09:30 A.M. - 10:30 A.M." t-2=> "10:40 A.M. - 11:40 A.M." t-3=> "11:50 A.M. - 12:50 P.M." — Tanzila Islam, Jan 04 '17 at 11:44
@TanzilaIslam As it's a different question it goes outside the scope of this one. Please could you open a new question and I'll happily have a look :) — Rwd, Jan 04 '17 at 11:56

score 0 · Answer 2 · answered Jan 04 '17 at 08:40

according to your desired table, the columns in a row are not in a row in database table! so you can not build table with just a foreach through query results.

for example, t-1, t-2 and PRC-001 are not in a row in database. if day0 is t-1 then day1 would be empty and vice versa.

solution:

you have to put empty or zero for one of days in final table to make sense , and you don't need groupby:

$query1 = "SELECT * FROM routine AS tab1"; 


$rs = mysql_query($query1);
$numOfRows=mysql_num_rows($rs);
$printed = array();
$resultset = array();

while($row = mysql_fetch_assoc($rs)) {
    $resultset[] = $row;
    #print_r($row);
}

$q = "<table id='ash'>";
$q.= "<tr id='grey'>";
$q.= "<th rowspan='2'>day0</th>";
$q.= "<th rowspan='2'>day1(s)</th>";
$q.= "<th rowspan='2'>code</th></tr>";
$q.= "</tr>";


foreach ($resultset as $row){ 

    if($row['day'] == 0){
        $q.= "<tr>";
        $q.= "<tr><td id='clist'>".$row["time"]."</td>";
        $q.= "<td id='clist'>"EMPTY!"</td>";
        $q.= "<td id='clist'>".$row["code"]."</td></tr>";
    } else {
        $q.= "<tr>";
        $q.= "<tr><td id='clist'>"EMPTY!"</td>";
        $q.= "<td id='clist'>".$row["time"]."</td>";
        $q.= "<td id='clist'>".$row["code"]."</td></tr>";   
    }

} 
$q .= "</table>";
echo $q;

how to fetch data with same id in a row but different value in another column and display into table in php

2 Answers2