Reputation: 191
Heres A Sample Table:
info table
info_id name
1 john
2 peter
------------------------
details table
details_id log date
1 test log for john 2013-08-01
1 another log for john 2013-08-02
2 test log for peter 2013-08-02
Here's my Sample Query:
SELECT info.info_id, info.name, details.details_no, details.log, details.date
FROM info JOIN details ON details.details_id = info.info_id
GROUP BY info.info_id
And Here's display i want to achieve:
john
1 test log for john 2013-08-01
1 another test log for john 2013-08-02
peter
2 test log for peter 213-08-02
I have tried using foreach loop and then execute another foreach loop inside the first loop.
Please Help Guys
Upvotes: 4
Views: 15533
Reputation: 48100
To refine Ascherer's answer, you don't need to declare arrays before pushing data into them with []
syntax. If you were using array_push()
the array would need to be declared as a variable first.
With MySQLi, you can iterate the result set object directly with a foreach()
and unconditionally use the info_id
values as keys to group by.
$sql = "SELECT info.info_id,
info.name,
details.log,
details.date
FROM info
JOIN details ON details.details_id = info.info_id
ORDER BY info.info_id";
$result = [];
foreach ($mysqli->query($sql) as $row) {
$data[$row['info_id']][] = $row;
}
With PDO, you can use ->fetchAll(PDO::FETCH_GROUP) to create the same output without manually looping.
When generating your html markup...
<table>
<?php foreach ($result as $groupId => $rows) { ?>
<tr>
<td colspan='3'><?php echo $rows[0]["name"]; ?></td>
</tr>
<?php foreach($rows as $row) { ?>
<tr>
<td><?php echo $groupId; ?></td>
<td><?php echo $row['log']; ?></td>
<td><?php echo $row['date']; ?></td>
</tr>
<?php } ?>
<?php } ?>
</table>
Upvotes: 0
Reputation: 8093
You are probably going to have to take the data and make the array that you want.
$data = array();
foreach ($result as $item) {
$key = $item['name']; // or $item['info_id']
if (!isset($data[$key])) {
$data[$key] = array();
}
$data[$key][] = $item;
}
// Build your table with the new $data array
EDIT
This is just an example. As amaster507 points out, if your name
field isn't unique, you will need to build your array on a unique key. Not terribly different from this, as you could probably just change instances of $item['name']
to $item['info_id']
.
Upvotes: 4
Reputation: 2163
Try making your results into a multidimensional array like below
Note: I am assuming that details.details_no
is a primary key for the details
table and you want results similar to
john
1 test log for john 2013-08-01
2 another test log for john 2013-08-02
peter
3 test log for peter 213-08-02
Which you can retrieve with the following
...
$qry = "
SELECT
info.info_id,
info.name,
details.details_no,
details.log,
details.date
FROM
info
JOIN details ON (details.details_id = info.info_id)
";
$result = mysqli_query($your_db_link,$qry)
$data = array();
while($row = mysqli_fetch_array($result)){
$data[$row["info_id"]]["name"] = $row["name"];
$data[$row["info_id"]]["logs"][$row["details_no"]] = array(
"log"=>$row["log"],
"date"=>$row["date"],
);
}
Would result in an array like:
$data = array(
"1" => array(
"name" => "john",
"logs" => array(
"1" => array(
"log" => "test log for john",
"date" => "2013-08-01",
),
"2" => array(
"log" => "another test log for john",
"date" => "2013-08-02",
),
),
),
"2" => array(
"name" => "peter",
"logs" => array(
"3" => array(
"log" => "test log for peter",
"date" => "2013-08-02",
),
),
),
);
Then you could display like:
echo "<table>";
foreach($data as $value){
echo "<tr><td colspan='3'>".$value["name"]."</td></tr>";
foreach($value["logs"] as $subkey => $subvalue){
echo "<tr>";
echo "<td>".$subkey."</td>";
echo "<td>".$subvalue["log"]."</td>";
echo "<td>".$subvalue["date"]."</td>";
echo "</tr>";
}
}
echo "</table>";
Which would result similar to:
<table>
<tr>
<td colspan='3'>john</td>
</tr>
<tr>
<td>1</td>
<td>test log for john</td>
<td>2013-08-01</td>
</tr>
<tr>
<td>2</td>
<td>another test log for john</td>
<td>2013-08-02</td>
</tr>
<tr>
<td colspan='3'>peter</td>
</tr>
<tr>
<td>3</td>
<td>test log for peter</td>
<td>2013-08-02</td>
</tr>
</table>
Upvotes: 11
Reputation: 2841
Ok, first an explanation of the problem.
When you use GROUP BY
, you no longer get every result as a row, but instead, you get 1 row with grouped content, that is, you can use GROUP BY
to get something like the concatenation of both log
cells from john, for example.
To do what you want, remove the GROUP BY
to get all the rows, and then manually process them.
I recommend you to make easy the job, to use underscore.php which has a function called groupBy
which will return an array grouped in subarrays, based on this case, the info_id
column.
It will be used something like this:
$array_with_all_the_rows = $query->fetchAll(); // Or whatever you use to get all rows in 1 array.
$grouped_array = __::groupBy($array_with_all_the_rows, 'info_id');
That grouped array will be like this:
$grouped_array = Array(
"1" => Array(
"info_id"=> "1", "log"=>"test log for john", "date" => "2013-08-01",
"info_id"=> "1", "log"=>"another test log for john", "date" => "2013-08-02"
),
"2" => Array(
"info_id"=> "2", "log"=>"test log for peter", "date" => "2013-08-02"
)
)
Upvotes: 1
Reputation: 401
have you tried a foreach with a key/value pair? can you show us your for loops currently? a print_r/var_dump for the resulting array will make it easier for someone to help you.
foreach ($result as $key=>$value) {
print "Name Row\n";
for (each display row) {
That or as Ascherer suggests make a new multidimensional array that will make nested for loops unnecessary.
Upvotes: -1