Jump to content

Splitting multidimensional array based on values


684425

Recommended Posts

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?

ss.PNG

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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...
}

 

Link to comment
Share on other sites

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                                                      |
+-----------+--------------------------------------------------------------+

 

Link to comment
Share on other sites

  • 3 weeks later...
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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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  |            |            |
+------------+------------+------------+

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.