Portal Home > Knowledgebase > Articles Database > shorten mysql select
shorten mysql select
| Posted by ti_nhatrang, 01-20-2012, 06:34 AM |
| Hi all,
I have a mental block when it comes to joining statements, please help me so I can learn a few new things. I have attached the statements I'm doing now, and hoping to see if I can just put it in one query:
|
| Posted by n3r0x, 01-20-2012, 03:34 PM |
| First off never use "select *" unless you´re going to use all columns in the table, wasting resources. So change it to "select country"
Second the code doesn´t make sense.. You select rows where PLID = 0, but update where PLID=3?? If you could clarify what it is and what you need it to do i´ll be able to write you a query in a few sec/min
|
| Posted by josephgarbett, 01-21-2012, 06:26 AM |
| MySQL isn't difficult Just write in boxes and you'll be able see what your trying to do.
|
| Posted by ti_nhatrang, 01-21-2012, 08:17 AM |
| i pretty much want to pull the costs out from PLID=0 and if the costs matches my WHERE, then I want to update the column active=1 for all PLID=3 where country = country in PLID=0
|
| Posted by jdilegge, 01-21-2012, 02:18 PM |
| 1. Try to get used to using single quotes and then working inward with doubles, it saves time later by not having to backslash double quotes being used in the query.
2. mysql_fetch_assoc() is slower than mysql_fetch_row().
3. You forgot the closing curly bracket for the while block.
4. I added the if statement to check if there are results to prevent a NOTICE when there are no results.
|
| Posted by YUPAPA, 01-21-2012, 11:57 PM |
| I think you could do all these in one go by using nested query.
Something like:
|
| Posted by jdilegge, 01-22-2012, 11:44 AM |
| Actually, that is a "subquery". It uses just as much resources as the forementioned query. It is still technically multiple queries, you just saved coding time and have the two running at closer intervals of time.
|
| Posted by ti_nhatrang, 01-23-2012, 04:14 AM |
| sub or not, I am interested in learning how to do this in one.
|
| Posted by jdilegge, 01-23-2012, 02:49 PM |
| Subqueries: http://dev.mysql.com/doc/refman/5.0/en/subqueries.html
JOIN: http://dev.mysql.com/doc/refman/5.0/en/join.html
|
| Posted by hodgeemory, 01-28-2012, 03:16 AM |
| if you shorten a string column, values may be truncated.
|
Add to Favourites 
Print this Article
Also Read
