optimizing some code

[Held]

Hello W3Schools Forum i just picked up webcoding again, after i just messed aroung with a little html and php in past. So i decided to build a little webpage for myself as practice (: Since im not aware of all tools that php and other languages can offer me some of the code i post might look unreasonable. But i will try to explain what the intension behind all of that mess was.So the reason for this post is, i want to optimize my code ( as the title should have told you already). First off to shorten and smooth everything and secondly to eventually learn some tricks and methods that will help to improve my overhaul coding.

// so i have a table including my users// and a table including all of the objects// every object in the objects table belongs to a certain user// every object has an entry owner which is a number, that number refers to the users id// so on the page where this code belongs to i want to show all of the objects in a table// but since the object table includes only numbers for the owners i want to put the corresponding username in there// so here we go:while($line = mysql_fetch_array($objects)){ // so the while loop is for creating the table $objects includes the mysqlquery belonging to the objects table$user = $line['owner']; //  here i take the number from the $line array which refers to the users id$owner =  mysql_query("SELECT username FROM users WHERE id=$user");  // now i go into my users table and take the data which includes the username where the id equals the owner numer, which i previously put into $user$select = mysql_fetch_array($owner); // now i put that query (which includes only the username) and put it into an array$owner = $select['username']; // at last i take that array and put its username value into the $owner variable, which is then used to print the username into the "owner" column //so the code itself works but i feel like i need way too many steps

so this is my first and only 'problem' for now, asking myself should i do an extra post for every future problem or can i just put all of them into this post ? edit: also is this the right forum ? because its more mysql as i see now ... but it all belongs together somehow so dunno where to put it also sry for my english im from germany (:

Edited June 30, 2012 by Held

[Ingolme]

You can save yourself the extra query if your tables have foreign keys. Then you can make a query like this:

SELECT u.username FROM users AS u, objects AS o WHERE o.owner=u.id

[Held]

i dont quite understand what u mean by foreign keysbut a code line like that would save me the whole process because the owner is replaced with the corresponding username right when i retrieve it ?So i put this

SELECT u.username FROM users AS u, objects AS o WHERE o.owner=u.id

right where i make my objects query and replace the values there ?so i put it somehow in here ?:

$objects = mysql_query("SELECT * FROM objects ORDER BY ID");

edit: so i looked up foreign keys x) i dont have them yet but since everything is still i ndevelopment im going to rebuild the tables now so i have them

Edited June 30, 2012 by Held

[Ingolme]

If you want the objects fields as well, you'll also have to add o.* to the query. This query will select all the fields of the "objects" table and the username field of the "users" table that's associated with the object.

SELECT o.*, u.username FROM users AS u, objects AS o WHERE o.owner=u.id ORDER BY o.id

o is an alias for "objects" and u is an alias for "users" which are assigned using "AS" When you create the table, you need to associate it with the other one using a FOREIGN KEY.

[Monckey100]

Minor detail but spacing always helps you quickly read through code if tabbing is not available

Edited July 1, 2012 by Monckey100

[Held]

Hey,so after some time i completely understood what your suggested code was doing and build it in and it saves me all of the lines a used previously (:big thx to you foxy

[justsomeguy]

Just because this is about optimizing, this query will be a little more efficient: SELECT o.*, u.username FROM users AS u INNER JOIN objects AS o ON o.owner=u.id ORDER BY o.id The WHERE clause should only be used to filter the result set, not build the result set. The join condition is part of building the result set, so stick that as part of the join to start with the least amount of data necessary instead of all combinations. The WHERE filters the results after the join has been done, putting the join condition before the WHERE will cause the joined result to be smaller, and then you can use WHERE to filter further if necessary.