php sorting sql records

Question

I'm looping through records in a table, and in each loop it opens another table to pull data from it. I need to ORDER the main loop ($rs) by a field from the 2nd table.

I'm somewhat a beginner to php and sql so go easy on me :P

code example:

$sql_result = mysql_query("SELECT * FROM table1", $db); // i want this to ORDER BY data from table2
while($rs = mysql_fetch_array($sql_result)) { 
    $sql_result2 = mysql_query("SELECT * FROM table2 WHERE id='$rs[data]'", $db); 
    $rs2 = mysql_fetch_array($sql_result2);

    //get data from table 2

we need more information, specially regading the tables and what exactly you're trying to accomplish. — Paulo Santos
– Paulo Santos, Commented Sep 8, 2011 at 23:28

Christian Specht · Accepted Answer · 2011-09-08 23:51:05Z

1

Looping through a resultset and executing a second query for each row is bad practice and causes unnecessary load on the database.
It looks like you need an INNER JOIN:

    SELECT t1.*
      FROM table1 t1
INNER JOIN table2 t2 ON t2.id = t1.data
  ORDER BY t2.field

This way, you can get all the data with one single query.

EDIT:
Generally you should try to avoid returning all columns (SELECT *) and select only the columns that you really need:

SELECT t1.id, t2.field1, t1.field2, ...

This will not only reduce the load on the database (less data to select and to transfer over the network), but it will also help to eliminate double column names because double column names cause problems when you want to display them (as you found out yourself).

You can do two things to avoid this problem:

if both tables have columns with identical names and identical content (e.g. if these columns are used to join the tables), just select one of them (no matter which one, because they are identical).
if both tables have columns with identical names and different content and you need both of them, the easiest way would be to give one of them an alias:
```
SELECT t1.id, t2.id AS AnotherId, ...
```
This will cause the second column to be named "AnotherId", so you can get it with $rs[AnotherId].
Honestly, I'm no PHP guru so I don't know if PHP understands $rs[table.field] as well, but there are enough data access technologies which have problems when a query returns two columns with identical names...so aliasing duplicate columns can never be wrong.

edited Sep 8, 2011 at 23:51

answered Sep 8, 2011 at 23:28

Christian Specht

36.6k16 gold badges132 silver badges190 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

user880356 Over a year ago

thank you. and how would i call the fields for an echo statement, i usually use $rs[field], would it be the same or would I have to do $rs[table.field] ?

Fred Sobotka Over a year ago

Column names don't automatically change when they're returned in a result set, so you should be able to continue referring to them by name. Grabbing every column via SELECT * is inefficient unless your application actually requires every column in the result set, so you'd be better off specifying exactly which columns you want from t1 and t2, which will also give you the ability to rename/relabel any columns that have the same name in both tables.

Christian Specht Over a year ago

The t1 stuff is just an alias for the table name, exactly like the alias for the columns that I just edited into my answer. When you have to write the table name multiple times (like when you join several tables), it's easier to write t1.field instead of LongTableName.field.

user880356 Over a year ago

Thanks a lot, you've been a huge help

Christian Specht Over a year ago

@user880356: to express your gratitude even more, you could also upvote my answer ;-) (I see in your profile that you never voted at all yet)

|

Collectives™ on Stack Overflow

php sorting sql records

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related