Help with sorting multidimensional array!
Moderator: General Moderators
Help with sorting multidimensional array!
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.
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.
- scorphus
- Forum Regular
- Posts: 589
- Joined: Fri May 09, 2003 11:53 pm
- Location: Belo Horizonte, Brazil
- Contact:
Check these links:
- http://dev.mysql.com/doc/mysql/en/SELECT.html (look for 'SELECT' and 'WHERE')
- http://dev.mysql.com/doc/mysql/en/SELECT.html#IDX1541 (look for 'ORDER BY')
- http://dev.mysql.com/doc/mysql/en/SELECT.html#IDX1554 (look for 'LIMIT')
- http://dev.mysql.com/doc/mysql/en/UNION.html (understand how 'UNION' works)
-
kettle_drum
- DevNet Resident
- Posts: 1150
- Joined: Sun Jul 20, 2003 9:25 pm
- Location: West Yorkshire, England
Code: Select all
SELECT table.id, table2.id, table2.field FROM table, table2 WHERE table1.some_field = 'rahh';ї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?
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?
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;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.
Okay, so I now have this:
Now how do I sort them numericly by the Unix Timestamp held in pgid?
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.
?>