Page 1 of 1

Help with sorting multidimensional array!

Posted: Sun Jul 18, 2004 10:06 pm
by Jay Eff
I have two tables (mySQL) as follows:
id (primary auto_incr)
type - 'update' is for the news posts
pgid - timestamp
pgtitle - title of post
pgdisc - empty for updates
lastedit - empty for updates
sub - poster of update
page - update post

Both tables (named jayeffcomnews and remnants) are identical, and what I want to do is load the news from both of the tables in one query and have them ordered by pgid (timestamp). How can I do this? I cannot find it anywhere.

PS
In the query it also must load only where type is 'update' and only load 5 entries.

Thank you in advance.

Posted: Mon Jul 19, 2004 2:34 am
by scorphus
Check these links:-- Scorphus

Posted: Mon Jul 19, 2004 6:12 am
by kettle_drum

Code: Select all

SELECT table.id, table2.id, table2.field FROM table, table2 WHERE table1.some_field = 'rahh';

Posted: Mon Jul 19, 2004 8:56 am
by liljester
you'll prolly want to try a union, not a join.

Posted: Fri Jul 23, 2004 6:37 am
by Jay Eff
їcode]SELECT * FROM remnants, jayeffcomnews WHERE remnants.type = 'update' LIMIT 5ї/code]
This is being attempted, but the results are 5 rows returned from the jayeffcomnews table, and none from the remnants table. There is only one row in the jayeffcomnews table, so I don't know why it is being returned multiple times. As for the UNION command, after it failing a few times, I realized it is 4.0 only, and I am still running 3.23.56 on my server, and have no way to change it.
Can somebody please assist me?

Posted: Fri Jul 23, 2004 10:52 pm
by Jay Eff
Anyone?

Posted: Fri Jul 23, 2004 11:41 pm
by lolpix

Code: Select all

(SELECT * FROM `jayeffcomnews` WHERE type = 'update' ORDER BY pgid DESC)
UNION
(SELECT * FROM `remnants` WHERE type = 'update' ORDER BY pgid DESC)
ORDER BY pgid DESC LIMIT 5;

Posted: Sat Jul 24, 2004 12:25 am
by lolpix
Sorry, I just noticed the 3.x issue and tried toying around with the problem. I could not come up with a single-query solution. I would most likely end up querying each table seperately and feeding the output into a single array, then sorting that array by pgid for display. I am sure someone will have a far more efficient solution.

Posted: Sat Jul 24, 2004 12:05 pm
by Jay Eff
Okay, so I now have this:

Code: Select all

<?
$query1 = "SELECT * FROM `remnants` WHERE type='update'";
$query2 = "SELECT * FROM `jayeffcomnews` WHERE type='update'";
$result = mysql_query($query1); //Loads all updates from remnants
$rows1  = mysql_fetch_array($result); //Puts it into an array
$result = mysql_query($query2); //Loads all updates from jayeffcomnews
$rows2  = mysql_fetch_array($result); //Puts it into an array
$rows   = array_merge($rows1,$rows2); //Merges two arrays into one.
?>
Now how do I sort them numericly by the Unix Timestamp held in pgid?

Posted: Sat Jul 24, 2004 10:42 pm
by feyd
[php_man]usort[/php_man]