Mysql Question: Loading database info from more than one table.

[Hall of Famer]

well I know the standard way of retrieving mysql data was through the following codes:

 

	$query = "SELECT * FROM {$tablename} WHERE columnmame = '{$var}'";
	$result = mysql_query($query);
                $row = mysql_fetch_array($result);

 

This will return all properties inside a table row by an associative array indexed by column names. I am, however, wondering if there is an easier way to retrieve database info from more than one table. For now, what I am doing is:

 

                $result = mysql_query( "SELECT * FROM {$tablename} WHERE columnmame = '{$var}'");
                $row = mysql_fetch_array($result);

                $result2 = mysql_query( "SELECT * FROM {$tablename2} WHERE columnmame2 = '{$var2}'");
                $row2 = mysql_fetch_array($result2);

 

which is a bit tedious and can cause problems when two or more coders work on the same project(it will be difficult to tell what is $row1, $row2 and $row3...). Is there away to write a simpler code than the one above? I mean, if it is possible to run mysql_fetch_array only once and retrieve database info from multiple tables?

[MasterACE14]

$result = mysql_query("SELECT * FROM `table1`, `table2` WHERE `table1`.`columnname`='something' AND `table2`.`columnname`='somethingelse'");

[jcbones]

Yes, it is possible, and one of the key features of mysql.  MySQL is a relational database, so pulling a query from tables that 'relate' to each other is a feature.

 

JOIN is the syntax you are looking for.

 

[Hall of Famer]

Thanks for both of your kind replies, I will give a try later. I have another question though... What if the two tables have columns that share the same name? For instance, both table1 and table 2 may have the column 'id', while the contents in columns 'id' will be significantly different.

[Psycho]

Thanks for both of your kind replies, I will give a try later. I have another question though... What if the two tables have columns that share the same name? For instance, both table1 and table 2 may have the column 'id', while the contents in columns 'id' will be significantly different.

 

I think you need to explain what, exactly, you are trying to achieve. If you have tow separate tables that are not related and you need a record from each table, then why do you not want the data in two separate variables? As to your original question:

it will be difficult to tell what is $row1, $row2 and $row3...

 

That is why you SHOULD NOT use variables such as $row1, $row2, etc. Give your variables meaningful names so that you, or anyone else, will ahve a good idea of what the variable holds. And, mysql_fetch_array() returns more than an "associative array indexed by column names". It also contains all the fields numerically indexed. I've never seen a reason to use mysql_fetch_array(), IMHO I think mysql_fetch_assoc() is a better alternative - it ONLY returns an array indexed by the field names.

 

Using your example above, I would do something like this:

$query = "SELECT * FROM {$usertable} WHERE user_id = '{$userid}'";
$result = mysql_query($query);
$userdata = mysql_fetch_assoc($result);

$query = "SELECT * FROM {$clienttable} WHERE client_id = '{$clientid}'";
$result = mysql_query($query);
$clientdata = mysql_fetch_assoc($result);

 

If you have multiple people working in the code all of them should understand the difference between $userdata  and $clientdata. That is why it is important to have coding standards in an organization. Although using classes would make all of this pointless.

[Muddy_Funster]

Thanks for both of your kind replies, I will give a try later. I have another question though... What if the two tables have columns that share the same name? For instance, both table1 and table 2 may have the column 'id', while the contents in columns 'id' will be significantly different.

 

Here is where you would alias your field names, and not use SELECT *

SELECT table1.id AS table1_id, table2.id AS table2_id ... FROM table1, table2 WHERE ...

[Hall of Famer]

Alright, thanks for your kind replies. One last question in this thread... Is there a simple way for me to update a certain column among all rows in a table? I am trying to update every user's password to a more encrypted and secured version. The old password was only encrypted with a single md5($password), but now I've added peppers, salts and SHA1 encryption following this md5 encryption to generate a longer and more complicated hash. Id really like to know if its possible to update all users passwords stored in a table with a simple code rather than running through loops, thanks. 

[jcbones]

Well, you could update them all at once, inside of MySQL.  If you are processing the passwords in PHP, then you would have to run loops.

[Psycho]

If the passwords are already hashed with MD5() how do you plan to update them since you don't (or shouldn't) have the original value? The only way I can see this working is if the first part of your new process is to do an MD5() hash and then do additional modifications. So, when you update all the current passwords you would perform just the additional modifications and not the initial MD5().

 

By the way, from my understanding, running multiple hashes on the same value can lead to a less secure value. So, you might want to check some sites for best practice processes before you go through with this change.

 

 

I just thought of something. If doing a mass update will not work for the new process you want to implement there is another solution you can do. Update your login script to update the hashed passwords when the user's log in.

 

1. Create a new column in the user table called "updated_hash" set the default value to 1, but then modify all the current records to have a value of 0. That way when new user accounts are created they will have a value of 1 (since they will be hashed with the new method) but existing users will have a value of 0.

 

2. Modify the login script so it converts the entered passwords in both formats and checks for a matching record using both values AND have it return the value for "updated_hash".

 

3. If authentication passes and the value of "updated_hash" is 1 no addition steps are needed. But, if the value is 0, then you continue as follows:

 

4. Update the stored password hash for the user record with the hashed value using the new method and change the value of "updated_hash" to a 1.

 

As users log in their passwords will be updated to the new hashed format automatically. You could then wait a few weeks/months to see if all of the records have been updated and then remove the "updated_hash" column and the additional functionality in the authentication script.