Splitting multidimensional array based on values

[684425]

I am trying to get rows from MYSQL table (attachment) based on:

1. Select last five rows where status_id = 1
2. Select last two rows where status_id = 2

*I did use UNION with two SELECT queries to get whole data as one array.

Now on PHP side...

I have first block for rows having status_id 1 so if there are rows having status_id 1 then PHP should display the data otherwise if there is no row having status_id 1 then PHP should print NO DATA only once.

I have second block for rows having status_id 2 so if there are rows having status_id 2 then PHP should display the data otherwise if there is no row having status_id 2 then PHP should print NO DATA only once.

I did use foreach loop then within loop i did use if condition to check status_id of rows. it works fine when i omit NO DATA part but when i add it. the result shows both rows and NO DATA in first block when there are one or more rows having status_id 1 but no row having status_id 2

Which made me believe that i was doing it the wrong way.

Please guide me

*PS: I know its PHP section the thread should be related to PHP problem but... is it right to use UNION in query? or should i post a new thread in relevant section for guidance?

[mac_gyver]

you would need to post the offending code to get the most direct help with what is wrong with it.

however, you can probably solve this by indexing/pivoting the data, using the status_id value as the index, when you retrieve it. this will give you zero, one, or two sub-arrays of rows of matching data, depending on if there was no data at all, data for one or the other status_id value, or for both status_id values. you can then test/loop over the indexed/pivoted data to produce the output for none, one, the other, or both status_id values.

[mac_gyver]

example -

<?php

// index/pivot the data
// note: if you are using the PDO extension, there's a fetch mode that will do this for you
$data = [];
while($row = whatever_fetch_statement_you_are_using)
{
	$data[ $row['status_id'] ][] = $row;
}

// define output heading information
$headings = [];
$headings[1] = ['label'=>'Heading for status id 1 section'];
$headings[2] = ['label'=>'Heading for status id 2 section'];

// produce the output
foreach($headings as $key=>$arr)
{
	// start a new section
	echo "<h3>{$arr['label']}</h3>";
	if(!isset($data[$key]))
	{
		echo "<p>NO DATA</p>";
	}
	else
	{
		foreach($data[$key] as $row)
		{
			// reference elements in $row to produce the output...
			echo '<pre>'; print_r($row); echo '</pre>';
		}
	}
	// any code needed to finish a section goes here...
}

 

[Barand]

One problem in this type of app, where you want to output something when there is no data, is you are asking the question "Hands up everyone who isn't here?". Your query needs to know all status_id values to look for.

So you need a status table

+-----------+
| status    |
+-----------+
| status_id |
| descrip   |
+-----------+

Example (I added a status 3 as there weren't any of those)

USER                                        STATUS
+---------+-----------+----------+          +-----------+----------+
| user_id | status_id | username |          | status_id | descrip  |
+---------+-----------+----------+          +-----------+----------+
|       1 |         1 | peterd   |          |         1 | Status A |
|       2 |         1 | lauran   |          |         2 | Status B |
|       3 |         2 | tomd     |          |         3 | Status C |
|       4 |         1 | cheggs   |          +-----------+----------+
|       5 |         2 | pollyv   |
|       6 |         2 | pollys   |
|       7 |         1 | tomc     |
|       8 |         2 | comet    |
|       9 |         2 | cupid    |
|      10 |         1 | donner   |
|      11 |         1 | blitzen  |
|      12 |         2 | dancer   |
|      13 |         2 | prancer  |
|      14 |         1 | dasher   |
|      15 |         1 | vixen    |
+---------+-----------+----------+

query

SELECT s.status_id
     , coalesce(group_concat(u.username order by user_id separator ', '), 'NO DATA') as users
FROM status s 
     LEFT JOIN user u USING (status_id)
GROUP BY status_id;

results

+-----------+--------------------------------------------------------------+
| status_id | users                                                        |
+-----------+--------------------------------------------------------------+
|         1 | peterd, lauran, cheggs, tomc, donner, blitzen, dasher, vixen |
|         2 | tomd, pollyv, pollys, comet, cupid, dancer, prancer          |
|         3 | NO DATA                                                      |
+-----------+--------------------------------------------------------------+

 

[684425]

On 5/19/2021 at 5:31 PM, mac_gyver said:

example -

<?php

// index/pivot the data
// note: if you are using the PDO extension, there's a fetch mode that will do this for you
$data = [];
while($row = whatever_fetch_statement_you_are_using)
{
	$data[ $row['status_id'] ][] = $row;
}

// define output heading information
$headings = [];
$headings[1] = ['label'=>'Heading for status id 1 section'];
$headings[2] = ['label'=>'Heading for status id 2 section'];

// produce the output
foreach($headings as $key=>$arr)
{
	// start a new section
	echo "<h3>{$arr['label']}</h3>";
	if(!isset($data[$key]))
	{
		echo "<p>NO DATA</p>";
	}
	else
	{
		foreach($data[$key] as $row)
		{
			// reference elements in $row to produce the output...
			echo '<pre>'; print_r($row); echo '</pre>';
		}
	}
	// any code needed to finish a section goes here...
}

 

Thanks for guiding sir. I have one problem that my script is divided in multiple files. i will have to check if i can apply it there.

[684425]

On 5/19/2021 at 5:33 PM, Barand said:

One problem in this type of app, where you want to output something when there is no data, is you are asking the question "Hands up everyone who isn't here?". Your query needs to know all status_id values to look for.

So you need a status table

+-----------+
| status    |
+-----------+
| status_id |
| descrip   |
+-----------+

Example (I added a status 3 as there weren't any of those)

USER                                        STATUS
+---------+-----------+----------+          +-----------+----------+
| user_id | status_id | username |          | status_id | descrip  |
+---------+-----------+----------+          +-----------+----------+
|       1 |         1 | peterd   |          |         1 | Status A |
|       2 |         1 | lauran   |          |         2 | Status B |
|       3 |         2 | tomd     |          |         3 | Status C |
|       4 |         1 | cheggs   |          +-----------+----------+
|       5 |         2 | pollyv   |
|       6 |         2 | pollys   |
|       7 |         1 | tomc     |
|       8 |         2 | comet    |
|       9 |         2 | cupid    |
|      10 |         1 | donner   |
|      11 |         1 | blitzen  |
|      12 |         2 | dancer   |
|      13 |         2 | prancer  |
|      14 |         1 | dasher   |
|      15 |         1 | vixen    |
+---------+-----------+----------+

query

SELECT s.status_id
     , coalesce(group_concat(u.username order by user_id separator ', '), 'NO DATA') as users
FROM status s 
     LEFT JOIN user u USING (status_id)
GROUP BY status_id;

results

+-----------+--------------------------------------------------------------+
| status_id | users                                                        |
+-----------+--------------------------------------------------------------+
|         1 | peterd, lauran, cheggs, tomc, donner, blitzen, dasher, vixen |
|         2 | tomd, pollyv, pollys, comet, cupid, dancer, prancer          |
|         3 | NO DATA                                                      |
+-----------+--------------------------------------------------------------+

 

Thank you sir for guiding me. The result i want from query is like...

+---------+-----------+----------+
| user_id | status_id | username |
+---------+-----------+----------+
|      14 |         1 | user14   |
|      13 |         1 | user13   |
|       8 |         1 | user8    |
|       7 |         1 | user7    |
|       6 |         1 | user6    |
|      12 |         2 | user12   |
|      11 |         2 | user11   |
+---------+-----------+----------+

I had to modify my table. added more columns now. so i need to get values in separate columns instead of rows

[Barand]

Do you mean ...

+------------+------------+------------+
| Status 1   | Status 2   | Status 3   |
+------------+------------+------------+
| 14  user14 | 12  user12 | NO DATA    |
| 13  user13 | 11  user11 |            |
|  8  user8  |            |            |
|  7  user7  |            |            |
|  6  user6  |            |            |
+------------+------------+------------+