more mysql help

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

yshaf13
Forum Commoner
Posts: 72
Joined: Mon Apr 03, 2006 7:59 pm

more mysql help

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

Post by feyd »

MySQL question :arrow: Databases. :?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
yshaf13
Forum Commoner
Posts: 72
Joined: Mon Apr 03, 2006 7:59 pm

re

Post by yshaf13 »

yes i am using php what kind of array sorting?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

asort() or any other of the useful array sorting functions.
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Re: more mysql help

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

That's a neat idea. Never thought of that.
yshaf13
Forum Commoner
Posts: 72
Joined: Mon Apr 03, 2006 7:59 pm

re

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

Post by feyd »

Your version doesn't support subqueries.
yshaf13
Forum Commoner
Posts: 72
Joined: Mon Apr 03, 2006 7:59 pm

re

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

I think subqueries are only availabe in 4.1+. Have you given any thought to using a code-side array sorting function?
yshaf13
Forum Commoner
Posts: 72
Joined: Mon Apr 03, 2006 7:59 pm

re

Post 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?
yshaf13
Forum Commoner
Posts: 72
Joined: Mon Apr 03, 2006 7:59 pm

re

Post by yshaf13 »

so basicly my question now is how to sort an array by a "column"-a number of a key in the array.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

bdlang
Forum Contributor
Posts: 395
Joined: Tue May 16, 2006 8:46 pm
Location: Ventura, CA US

Re: re

Post 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.
Post Reply