Page 1 of 1

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

Posted: Sun Sep 09, 2007 8:48 pm
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.

Posted: Sun Sep 09, 2007 8:53 pm
by feyd
Is there a reason these tables are not combined so this wouldn't be a problem?

Posted: Mon Sep 10, 2007 1:27 am
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

Posted: Mon Sep 10, 2007 9:02 am
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?

Posted: Mon Sep 10, 2007 10:28 am
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?

Posted: Mon Sep 10, 2007 2:38 pm
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

Posted: Mon Sep 10, 2007 4:34 pm
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.

Posted: Mon Sep 10, 2007 4:55 pm
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

Posted: Mon Sep 10, 2007 8:11 pm
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!

Posted: Thu Sep 13, 2007 1:25 am
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!

Help Please!

Posted: Mon Sep 17, 2007 10:19 am
by phpravda
Hi all:

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

Many thanks!

Pravda