Combining two SQL result sets, how to merge AND sort?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
axxn
Forum Newbie
Posts: 4
Joined: Sun Sep 09, 2007 8:37 pm

Combining two SQL result sets, how to merge AND sort?

Post by axxn »

Hi guys,

I am getting two sets of data:

Code: Select all

$query = "SELECT `id`, `category`, `recom_cat`, `headline` FROM `canned` ORDER BY `category`, `id`";
and:

Code: Select all

$query = "SELECT `id`, `category`, `recom_cat`, `headline` FROM `custom` ORDER BY `category`, `id`";
After running each query, I add the text to an array, $textDB, using the following:

Code: Select all

$textDB[$row["category"]][] = array($row["recom_cat"], $row["headline"], "XXXX", $row["id"]);
Where XXXX is either 'canned' or 'custom', depending on which query is being added.

Essentially what I end up with is the $textDB multi-dimensional array with the results from the first query, then the results from the second query. I need to sort the $textDB array, within each first-level index ($row["category"]) AND THEN by the first field within each array ($row["recom_cat"]).

So for example, I might end up with this originally:

Code: Select all

$textDB["cat1"] = array("1", "Testing", "Canned", 512)
$textDB["cat1"] = array("2", "Testing", "Canned", 513)
$textDB["cat1"] = array("4", "Testing", "Canned", 514)
$textDB["cat1"] = array("9", "Testing", "Canned", 515)
$textDB["cat1"] = array("3", "Testing", "Custom", 214)
$textDB["cat1"] = array("5", "Testing", "Custom", 215)
$textDB["cat1"] = array("8", "Testing", "Custom", 216)
$textDB["cat2"] = array("1", "Testing", "Canned", 512)
$textDB["cat2"] = array("2", "Testing", "Canned", 513)
(etc)
But, I need to sort this to end up with:

Code: Select all

$textDB["cat1"] = array("1", "Testing", "Canned", 512)
$textDB["cat1"] = array("2", "Testing", "Canned", 513)
$textDB["cat1"] = array("3", "Testing", "Custom", 214)
$textDB["cat1"] = array("4", "Testing", "Canned", 514)
$textDB["cat1"] = array("5", "Testing", "Custom", 215)
$textDB["cat1"] = array("8", "Testing", "Custom", 216)
$textDB["cat1"] = array("9", "Testing", "Canned", 515)
$textDB["cat2"] = array("1", "Testing", "Canned", 512)
$textDB["cat2"] = array("2", "Testing", "Canned", 513)
(etc)
I've tried various sorting in PHP, but I can't seem to figure it out.

Any suggestions would be greatly appreciated! (Maybe there's a way to directly do this in SQL??)

Thanks!

PS. I'm running PHP 4.3.2 and mySQL 3.23.58.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Is there a reason these tables are not combined so this wouldn't be a problem?
josa
Forum Commoner
Posts: 75
Joined: Mon Jun 24, 2002 4:58 am
Location: Sweden

Post by josa »

I have to ask the same question as feyd. Is there a reason why your database looks like this? Do you have the option to redesign? If not, this is how you would do it in sql:

Code: Select all

SELECT `id`, `category`, `recom_cat`, `headline` FROM `canned`
UNION
SELECT `id`, `category`, `recom_cat`, `headline` FROM `custom`
ORDER BY `category`, `id`
/josa
axxn
Forum Newbie
Posts: 4
Joined: Sun Sep 09, 2007 8:37 pm

Post by axxn »

There are a couple of other fields, unique to each of the canned and custom tables, which would probably make it messy to have to accomodate for storing both types of records in one table.

I'm on mySQL 3.23.58, which doesn't seem to support UNIONs. Which is why I thought of sorting after the data is read.

Any ideas?
User avatar
xpgeek
Forum Contributor
Posts: 146
Joined: Mon May 22, 2006 1:45 am
Location: Kyiv, Ukraine
Contact:

Post by xpgeek »

First idea is to update you mysql server to 4x or to 5x version.

Second, which function of sorting do you use and why it not helps you?
josa
Forum Commoner
Posts: 75
Joined: Mon Jun 24, 2002 4:58 am
Location: Sweden

Post by josa »

Here is one way to "simulate" unions in MySQL 3.x. I've just browsed through the article but it looks like something you could use in this case.

http://www.codewalkers.com/c/a/Database ... -MySQL-3x/

/josa
axxn
Forum Newbie
Posts: 4
Joined: Sun Sep 09, 2007 8:37 pm

Post by axxn »

I'm using Plesk, and I'm trying to find out how compatible Plesk 8.2 is with mySQL 4 or 5 ... not sure if that's an option or not!

I tried using sort(), but I don't know how to get it to sort on the first field within each array. Like, I don't know how you specify that it sorts on the $row["recom_cat"] field, versus another field.
josa
Forum Commoner
Posts: 75
Joined: Mon Jun 24, 2002 4:58 am
Location: Sweden

Post by josa »

Never used plesk myself, but on the homepage it says this for version 8:
MySQL 3.23.52 through 4.1."x" supported
Have a look at usort for making a customized sort.

/josa
axxn
Forum Newbie
Posts: 4
Joined: Sun Sep 09, 2007 8:37 pm

Post by axxn »

Aha! OK, I think usort (or uasort??) is what I want(?) but I still can't get it to work.

Here's what I've tried:

Code: Select all

function cmp($a, $b) {
   return strcmp($a[0], $b[0]);
}
uasort($textDB["cat1"], "cmp");
When I run it, I get: Warning: uasort(): The argument should be an array in ...

I tried running it as just $textDB and what I ended up seems to be sorting based on the first index of $textDB, in alphabetical order, and all of the arrays within cat1, cat2, cat3, and so on, remain unchanged. I don't get how to tell it to sort by cat1, cat2, cat3, AND the first variable in the array for each item.

Hopefully that makes sense!
phpravda
Forum Newbie
Posts: 2
Joined: Thu Sep 13, 2007 1:17 am

Post by phpravda »

Hi all:

I am having exactly the same problem as AXXN. With the same versions of MySQL and PHP. Is there no simple workaround to combine and sort these results?

Any help would be greatly appreciated!

Thanks!
phpravda
Forum Newbie
Posts: 2
Joined: Thu Sep 13, 2007 1:17 am

Help Please!

Post by phpravda »

Hi all:

I'm still stuck! Can anyone please offer some guidance?

Many thanks!

Pravda
Post Reply