Help with sorting multidimensional array!

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
Jay Eff
Forum Newbie
Posts: 20
Joined: Sat Oct 26, 2002 11:35 am

Help with sorting multidimensional array!

Post 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.
Last edited by Jay Eff on Sat Jul 24, 2004 9:37 pm, edited 1 time in total.
User avatar
scorphus
Forum Regular
Posts: 589
Joined: Fri May 09, 2003 11:53 pm
Location: Belo Horizonte, Brazil
Contact:

Post by scorphus »

Check these links:-- Scorphus
kettle_drum
DevNet Resident
Posts: 1150
Joined: Sun Jul 20, 2003 9:25 pm
Location: West Yorkshire, England

Post by kettle_drum »

Code: Select all

SELECT table.id, table2.id, table2.field FROM table, table2 WHERE table1.some_field = 'rahh';
User avatar
liljester
Forum Contributor
Posts: 400
Joined: Tue May 20, 2003 4:49 pm

Post by liljester »

you'll prolly want to try a union, not a join.
Jay Eff
Forum Newbie
Posts: 20
Joined: Sat Oct 26, 2002 11:35 am

Post 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?
Jay Eff
Forum Newbie
Posts: 20
Joined: Sat Oct 26, 2002 11:35 am

Post by Jay Eff »

Anyone?
lolpix
Forum Commoner
Posts: 41
Joined: Sat Jul 17, 2004 2:20 am

Post 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;
lolpix
Forum Commoner
Posts: 41
Joined: Sat Jul 17, 2004 2:20 am

Post 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.
Jay Eff
Forum Newbie
Posts: 20
Joined: Sat Oct 26, 2002 11:35 am

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

[php_man]usort[/php_man]
Post Reply