Ascendent order, but of two rows

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

Moderator: General Moderators

Post Reply
pirolilla
Forum Newbie
Posts: 13
Joined: Sat Jan 24, 2004 1:24 am

Ascendent order, but of two rows

Post by pirolilla »

My query:

Code: Select all

$result=mysql_query("SELECT * FROM names WHERE month01=January OR month02=January ORDER BY day01 AND day02 ASC");
I need all results table "names" in database with word "January" in rows "mes01" and "mes02", in in ascendent order of the respective rows "day01" and "day02".

That is to say, if there are two entries with "January", one in the row "month01" and another one in the row "month02", that one has number "18" in the column "day01" and the other has number "10" in the column "day02", I need that it lists them of this form:

- The first entrie with day02=10
- Soon the entrie with day01=18

This case is of a table with names and their dates of anniversary, and some names have more than a date. In the example listed all the anniversaries of the month of January, and we want they appear in numerical order of dates.

I hope that it has been explained clearly.

Thanks.
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Post by Stryks »

I'm not entirely sure that I understand your question, but it seems that you are storing multiple sets of data in a single table, and then trying to sort across multiple columns.

Eg. Table 1
name
event01
month02
day01
event02
month02
day02
etc.

Besides having sorting problems, this also limits how many events a person can have, based on how many rows you set up.

A better solution would to be to have multiple tables to allow this kind of one to many relationship.

Table 1:
person_ID
name

Table 2:
event_ID
person_ID
event_name
day
month

This way, you create a user once, and then for each anneversary they create, you add one row to the second table. This allows unlimited events for each person, and allows for an easy search.

SELECT table1.person_ID, table1.name
JOIN table2 ON (table1.person_ID = table2.person_ID)
WHERE (table2.month = 'January')
ORDER BY table2.day

That would give the ID and name of all people with events in order of the day they would happen in January. You could even add LIMIT 2 to the end to just pull the first two as you mentioned.

Hope this helps, let me know if you want more info.
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

For a start, i think you order by syntax is incorrect.

You dont use AND...just do this...

Code: Select all

$result=mysql_query("SELECT * FROM names WHERE month01=January OR month02=January ORDER BY day01, day02 ASC");
pirolilla
Forum Newbie
Posts: 13
Joined: Sat Jan 24, 2004 1:24 am

Post by pirolilla »

Thanks. :D
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Post by Stryks »

I'd run a few different values through that query.

I dont think that you're going to get the results you are expecting under every circumstance.

Unless I misunderstand the purpose. Which is totally possible. :D
Post Reply