[SOLVED] order of queries.

[Ninjakreborn]

I have heard that some things need a specific order.

$lastbills = "SELECT * FROM transactions ORDER BY submitted DESC WHERE type = 'Bill' AND userid = '" . $_SESSION['cymember'] . "' LIMIT 1;";

For example, what goes first.  I need in this 3 query 3 components

1. I need to get the data

2. I need it to order by the submitted field in desc order (which is a unix timestamp)

3. I need to only get it where the type is of the "bill" type, and where the userid is the id of that specific person. 

4. I need to limit the number to only 1.

In essence, I need to pull out the last transaction that happened that was a bill, and display this amount.  This right here returns nothing, any advice.

[CanMike]

You've got a problem with the structure of your SQL:

 

try this:

 

SELECT * from TRANSACTION

WHERE  type = 'Bill'

    AND  userid = '" . $SESSION['cymember']. "'

ORDER BY submitted DESC

LIMIT 1;

 

Note that the WHERE clause must precede the ORDER BY

 

I tested this against a library database and it returned exactly what I wanted.

 

Hope this was helpful.

 

Mike, Toronto, Canada

[Ninjakreborn]

That was what it was.

I had read somewhere that that order by must come after where, but I tried re-finding it to see exactly what it said and never did.

Do you have a link that I could book mark, I have re-ran into this related problems multiple times and always forget where I found the description at.

I know you probably read that somewhere at one point do you have a link of where I can see that, so I never lose track of that information again, thanks.

[veridicus]

http://docforge.com/wiki/SQL

 

[Ninjakreborn]

Ok, looking at this link I can tell that there is a certain order to some of the elements.

I need to study up on the structure of an sql query, and try to break down hte sections into components so I make sure I know which one's can go into which order, however you helped me remember the reason for my current problem.

Thanks again.

[CanMike]

Be careful with the "SELECT * from table_name". Although convenient, a SELECT statement like on a table without an index uses a ton of resources.  Imagine a database with 100,000 or over a million rows of data and your asking the database engine to parse every row.  My guess would be that your client would not be too happy with the delay in response time.

 

Try to limit you select to columns you intend to operate on and better yet, select columns that are index or a ORDER BY clause on a index column.  I know that this may not be possible all the time, but ask the database administrator for a copy of the database schema (sorry it's my Oracle DBA background that keeps creeping into my MySQL explanations).

 

Do this and you'll look like a superstar to your client.

 

Mike, Toronto, Canada

[Ninjakreborn]

Hmm, those are very valid points. 

I am going to start specifically trying to do that, it makes good since.

I normally try to limit them, and set the id's as indexed. However I think I could take that another level, I normally select * instead of selecting what I need.

Thanks for the input.