Page 1 of 2

Problems with queries

Posted: Wed Oct 22, 2008 9:06 am
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

Re: Problems with queries

Posted: Wed Oct 22, 2008 9:11 am
by VladSun
Could you post your tables structure and some data.

Re: Problems with queries

Posted: Wed Oct 22, 2008 9:15 am
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

Re: Problems with queries

Posted: Wed Oct 22, 2008 9:16 am
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

Re: Problems with queries

Posted: Wed Oct 22, 2008 9:19 am
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.

Re: Problems with queries

Posted: Wed Oct 22, 2008 9:23 am
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

Re: Problems with queries

Posted: Wed Oct 22, 2008 9:29 am
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.

Re: Problems with queries

Posted: Wed Oct 22, 2008 9:37 am
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.

Re: Problems with queries

Posted: Wed Oct 22, 2008 9:47 am
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!

Re: Problems with queries

Posted: Wed Oct 22, 2008 9:50 am
by papa
It's only one query...

Select id, name from table order by name

Re: Problems with queries

Posted: Wed Oct 22, 2008 9:56 am
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 :)

Re: Problems with queries

Posted: Wed Oct 22, 2008 9:57 am
by onion2k
That was a sneaky edit Vlad. :wink:

Re: Problems with queries

Posted: Wed Oct 22, 2008 9:58 am
by VladSun
onion2k wrote:That was a sneaky edit Vlad. :wink:
:P

Re: Problems with queries

Posted: Wed Oct 22, 2008 10:12 am
by Emmash
it doesn't work...

Re: Problems with queries

Posted: Wed Oct 22, 2008 10:14 am
by papa
Emmash wrote:it doesn't work...
Show us what you've done so far...