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]