Page 1 of 2

more mysql help

Posted: Mon Jul 24, 2006 12:36 pm
by yshaf13
feyd | Please use

Code: Select all

,

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

,

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 :arrow: 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.