Problems with queries

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

Moderator: General Moderators

Emmash
Forum Newbie
Posts: 13
Joined: Wed Oct 08, 2008 10:49 am

Problems with queries

Post by Emmash »

Hi,

I have a query like :

Code: Select all

SELECT no_document,titre_document FROM document WHERE no_document=3 OR no_document=1 OR no_document=2
and I have a loop like :

Code: Select all

while($ligne=mysql_fetch_array($requete)){
...
}
When I display the results in the loop, I get document 1, document 2 and document 3 displayed instead of document 3, document 1 and document 2 like in my where clause.

Is there a way to keep the order like the order in the WHERE clause because my results always seems to re-order automatically by id...

Thanks to help me!!!

Marie-Hélène
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Problems with queries

Post by VladSun »

Could you post your tables structure and some data.
There are 10 types of people in this world, those who understand binary and those who don't
Emmash
Forum Newbie
Posts: 13
Joined: Wed Oct 08, 2008 10:49 am

Re: Problems with queries

Post by Emmash »

Table document

no_document int(11) Non auto_increment
titre_document longtext latin1_general_ci Non
paragraphe1_document longtext latin1_general_ci Non
no_type_document int(11) Non
fichier1_document longtext latin1_general_ci Non
ind_actif varchar(5) latin1_general_ci Non false
Emmash
Forum Newbie
Posts: 13
Joined: Wed Oct 08, 2008 10:49 am

Re: Problems with queries

Post by Emmash »

Datas...

no_document titre_document paragraphe1_document no_type_document fichier1_document ind_actif
1 Document Description X 2 219.pdf true
2 Formulaire dsadsadsa 3 220.pdf true
3 Déclaration fdsfdsfds 1 221.pdf true
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Re: Problems with queries

Post by aceconcepts »

You could create an array with the order you want to display your results and then conditionally output the results according to the array values.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: Problems with queries

Post by onion2k »

You can't order them in any specific way using the WHERE clause, but you can order them in SQL... but it'll be pretty nasty code..

Code: Select all

SELECT no_document, titre_document, 
IF(no_document=3, 1, IF(no_document=1, 2, 3)) AS order_id
FROM document 
WHERE no_document=3 OR no_document=1 OR no_document=2
ORDER BY order_id ASC
Emmash
Forum Newbie
Posts: 13
Joined: Wed Oct 08, 2008 10:49 am

Re: Problems with queries

Post by Emmash »

aceconcepts wrote:You could create an array with the order you want to display your results and then conditionally output the results according to the array values.
How??

Because it can be order as :
Document 1, Document 2, Document 3
Or
Document 2, Document 3, Document 1
Or
Document 3, Document 1, Document 2
...
Etc.

The first document displayed is always the document selected by the user and the others are the others documents order alphabetically.
User avatar
papa
Forum Regular
Posts: 958
Joined: Wed Aug 27, 2008 3:36 am
Location: Sweden/Sthlm

Re: Problems with queries

Post by papa »

Order by name in mysql and save it as an array with id as key

Display the user selected document and then the rest of the documents in alphabetic order, skipping the selected one.
Emmash
Forum Newbie
Posts: 13
Joined: Wed Oct 08, 2008 10:49 am

Re: Problems with queries

Post by Emmash »

papa wrote:Order by name in mysql and save it as an array with id as key

Display the user selected document and then the rest of the documents in alphabetic order, skipping the selected one.
Yes I know that I can do it like this but I would have prefer to find another solution in only one query!
User avatar
papa
Forum Regular
Posts: 958
Joined: Wed Aug 27, 2008 3:36 am
Location: Sweden/Sthlm

Re: Problems with queries

Post by papa »

It's only one query...

Select id, name from table order by name
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Problems with queries

Post by VladSun »

Something like that:
[sql]SELECT     no_document,titre_document FROM     document WHERE     no_document  IN (1, 2, 3)ORDER BY     no_document = 3, no_document = 1, no_document=2 [/sql]

PS: Maybe you should use DESC - try and solve it :)
Last edited by VladSun on Wed Oct 22, 2008 9:59 am, edited 2 times in total.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: Problems with queries

Post by onion2k »

That was a sneaky edit Vlad. :wink:
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Problems with queries

Post by VladSun »

onion2k wrote:That was a sneaky edit Vlad. :wink:
:P
There are 10 types of people in this world, those who understand binary and those who don't
Emmash
Forum Newbie
Posts: 13
Joined: Wed Oct 08, 2008 10:49 am

Re: Problems with queries

Post by Emmash »

it doesn't work...
User avatar
papa
Forum Regular
Posts: 958
Joined: Wed Aug 27, 2008 3:36 am
Location: Sweden/Sthlm

Re: Problems with queries

Post by papa »

Emmash wrote:it doesn't work...
Show us what you've done so far...
Post Reply