Page 1 of 2
more mysql help
Posted: Mon Jul 24, 2006 12:36 pm
by yshaf13
feyd | Please use Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
Code: Select all
$sql1="SELECT 'administration', ad_id, ad_date, ad_title, ad_short_desc
FROM cgi_admin
UNION SELECT 'activities', act_id, act_date, act_title, short_desc
FROM cgi_activities
works great but the problem comes up when i want to sort by a specific column - if i add "sort by" to any of the querys then it will sort them only in that query itself -- if i sort by ad_date it will show all the results from the first select and then start over for the second select. how do i sort them all?
feyd | Please use Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
Posted: Mon Jul 24, 2006 12:38 pm
by feyd
MySQL question

Databases.

Posted: Mon Jul 24, 2006 12:40 pm
by RobertGonzalez
Are you using this query with PHP code? If you are, you can use the array sorting functions before outputting to get the sorting issue worked out.
re
Posted: Mon Jul 24, 2006 1:33 pm
by yshaf13
yes i am using php what kind of array sorting?
Posted: Tue Jul 25, 2006 1:42 am
by RobertGonzalez
asort() or any other of the useful array sorting functions.
Re: more mysql help
Posted: Tue Jul 25, 2006 2:46 am
by GM
yshaf13 wrote:
Code: Select all
$sql1="SELECT 'administration', ad_id, ad_date, ad_title, ad_short_desc
FROM cgi_admin
UNION SELECT 'activities', act_id, act_date, act_title, short_desc
FROM cgi_activities
works great but the problem comes up when i want to sort by a specific column - if i add "sort by" to any of the querys then it will sort them only in that query itself -- if i sort by ad_date it will show all the results from the first select and then start over for the second select. how do i sort them all?
You can always wrap another SELECT around it:
Code: Select all
SELECT * FROM (
SELECT 'administration' as type, ad_id as id, ad_date as my_date, ad_title as title, ad_short_desc as description
FROM cgi_admin
UNION SELECT 'activities', act_id, act_date, act_title, short_desc
FROM cgi_activities
) ORDER BY my_date;
should work - I haven't tested it though.
Posted: Tue Jul 25, 2006 2:52 am
by RobertGonzalez
That's a neat idea. Never thought of that.
re
Posted: Tue Jul 25, 2006 10:42 am
by yshaf13
thanks so much for your help. i tried that exact query and got this error message:
#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT 'administration' AS type , ad_id AS id , ad_date AS my_d
any idea why?
Posted: Tue Jul 25, 2006 10:47 am
by feyd
Your version doesn't support subqueries.
re
Posted: Tue Jul 25, 2006 11:05 am
by yshaf13
thats pretty weird, i'm using PhpMyAdmin shouldn't it have up to date support? anyway if it doesn't any other ideas on my original question?
update: this is what i got from mysql_get_server_info(): 4.0.27-max-log
Posted: Wed Jul 26, 2006 12:55 am
by RobertGonzalez
I think subqueries are only availabe in 4.1+. Have you given any thought to using a code-side array sorting function?
re
Posted: Wed Jul 26, 2006 9:25 am
by yshaf13
i have given it thought though i might just use a whole different aproach. anyway if i were to use a array sorting function what would i use?
re
Posted: Wed Jul 26, 2006 7:08 pm
by yshaf13
so basicly my question now is how to sort an array by a "column"-a number of a key in the array.
Posted: Wed Jul 26, 2006 11:39 pm
by RobertGonzalez
Re: re
Posted: Thu Jul 27, 2006 12:08 am
by bdlang
yshaf13 wrote:thats pretty weird, i'm using PhpMyAdmin shouldn't it have up to date support? anyway if it doesn't any other ideas on my original question?
update: this is what i got from mysql_get_server_info(): 4.0.27-max-log
Please note phpMyAdmin is
not MySQL; the two should not be confused. MySQL is the actual RDBMS server that allows you to connect from a client, such as 'mysql', 'mysqldump', PHP, etc. phpMyAdmin is simply a PHP application that uses one of PHP's built in methods to connect to MySQL.
Your MySQL server is unfortunately unable to perform subqueries (4.1+ as
Everah mentioned). 5.0 is the current production version, although most hosting outfits still offer 4.1. If you're stuck with 4.0 I would suggest looking for a new host.