[Sayid Ahmed]

Whenever I try to fetch arrays from two mysql tables, I will always seem to run into trouble. If I use a while loop to display a list, the data from one table is displayed in one order, and the data from the other table is in a different order. it displays it in the order that I see it in the MySql table in phpadmin, which differs from table to table.

Here's an example code:

<?
$userId = $_SESSION['userId'];
$result1 = mysql_query("SELECT * FROM table1 WHERE userId1 !='$userId'") or die(mysql_error());  
$result2 = mysql_query("SELECT * FROM table2 WHERE userId2 !='$userId'") or die(mysql_error());  

while($row1 = mysql_fetch_array($result1) and $row2 = mysql_fetch_array($result2))
{
    echo $row1['somefield'] ;
    echo $row2['anotherfield'] ;
		 }
?>

Everything from row1 is displayed in it's own order, and the stuff from row2 is in another order, so the results don't match. Any idea how to overcome this?

This post has been edited by Sayid Ahmed: 29 June 2011 - 09:52 AM

[maniacalsounds]

I haven't tested this, but at first glane, it appears that you shouldn't use the word "and." I think you should use the bitwise and operator (&&) instead.

[edit]Although it might be easier to just google SQL JOIN. Because then you can search two tables with a single query, and not have to have to conditions in the while loop. [/edit]

This post has been edited by maniacalsounds: 29 June 2011 - 09:57 AM

[RPGonzo]

The JOIN would be ideal, but on a minimal stage if you want to insure the proper order of things use the ORDER BY clause in your queries.

This post has been edited by RPGonzo: 29 June 2011 - 10:21 AM

[CTphpnwb]

I never use a string directly in a query. Storing the string in a variable makes debugging easier. Here's what I would do with old style MySQL:

$query = "SELECT * FROM table1,table2 WHERE table1.userId1 !='$userId' and table2.userId2 !='$userId'";
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)) {

or with PDO:

$dsn = "mysql:host=localhost;dbname=test";
$username = "root";
$password = "root";
$query = "SELECT * FROM table1,table2 WHERE table1.userId1 !=? and table2.userId2 !=?";
$pd = new PDO($dsn, $username, $password);
$data = $pd->prepare($query);
$arr = array($userId, $userId);
$data->execute($arr);
while($result = $data->fetch(PDO::FETCH_ASSOC)) {

Neither is tested but they should be close.

[codeprada]

A join would go something like this...

SELECT * FROM table1 INNER JOIN table2 ON table1.userId1 = table2.userId2 WHERE table1.userId1 != $userId ORDER BY table1.userId1

However only use this if the user should exist in both tables which I assume this is the case since you used $userId in both tables.

[Sayid Ahmed]

JOIN worked perfectly. Although I can see trouble looming ahead when I want to do queries for 3 tables or more but I think I can use UNION for that. Thanks a lot everyone

[Valek]

You can do JOINs for more than two tables.

[codeprada]

The only thing with JOINS is that when you've got too much it increases the time taken to execute the query especially if your database is over the 2nd normalization level.

[eZACKe]

Sayid Ahmed, on 29 June 2011 - 07:13 PM, said:

JOIN worked perfectly. Although I can see trouble looming ahead when I want to do queries for 3 tables or more but I think I can use UNION for that. Thanks a lot everyone

Not a problem. Just keep in mind that a join performs a Cartesian Product of all the tables being joined, so if you have a lot of tables with a lot of rows, you could run in to some efficiency problems.

Something with 3 tables or more would still fit the basic format:

SELECT * FROM table1, table2, table3,...., tablen WHERE table1.id = table2.id AND table1.id = table3.id.....

Something like that. A lot of the time you'll be looking for what is called a NATURAL JOIN which basically just joins on the condition that columns with the same name are equal (like a userId column).

Another thing to note: Since joins can get pretty massive, it is best not to use * in your SELECT clause if at all possible. If you only really care about 1 column and there's 10 in the table, don't use *.

Joins may seem complicated at first, but really they all pretty much have the same form, you just have to know what condition you're joining on.

Hope this helps!

This post has been edited by eZACKe: 29 June 2011 - 07:12 PM

[satis]

And if you're trying to join multiple tables, but a certain ID may or may not be in one of those tables, use a left join. With a normal join, if an id is missing out of table 3 of 5, for instance, that whole row gets trimmed from the result set. If you left join, it's still there, just whatever fields didn't exist in table 3 are now null.

Union is different. If I'm not mistaken, UNION will take the results from different tables and stack them... so you could get 5 rows from table 1 and 5 more rows from table 2 for a total of 10 rows. Plus it requires some sort of equivalence of the columns. With joins it increases the number of columns, not the number of rows. Different uses in each case.

Also, personally, I prefer this syntax:

SELECT  one.column1, one.column2,
	two.column8, two.column11,
	three.column4, three.column7
FROM table1 one
	LEFT JOIN table2 two
		on one.userid = two.userid
	LEFT JOIN table3 three
		on one.userid = three.userid
WHERE two.something = 'somethingorother'
ORDER BY one.userid ASC

The same syntax works for regular joins. By doing it this way, it's really easy to see which tables you're joining and what their relationships are. I also alias the tables (unless the table names are really short) and apply the alias to all the fields, regardless of whether or not it's needed, to make figuring out which field is from which table much easier.

Finally, when filtering, I put that in the WHERE clause. You could put it in the join statements as well (an and after the join relationship) but by sticking the actual criteria in the WHERE, it makes those easier to find.

Not saying this is the perfect way to do it or anything, but after a bunch of years of building progressively more confusing SQL statements, I find that following the above makes going back and debugging stuff WAY easier.

This post has been edited by satis: 30 June 2011 - 06:31 AM