SELECT * FROM multiple tables

anthill

HI, I previously posted this in the Coding forum, but on reflection I think this one would be more appropriate. Plus nobody seemed to be able to answer my query.

I have two tables in a database called demo

table1:

+------+------+
| row1 | row2 |
+------+------+
| one1 | two2 |
+------+------+

table2:

+------+------+
| row1 | row2 |
+------+------+
| three | four4 |
+------+------+

and using this code i wanna select the value one1 from table1:

$db = mysql_connect("localhost","root");
mysql_select_db("demo",$db);

$test = "one1";

$query = mysql_query("SELECT a.*, b.* FROM table1 a, table2 b WHERE a.row1='$item' AND b.row1='$item'",$db);
$get = mysql_fetch_array($query);

but i'm not getting any results at all when echoing, any ideas?

Thanks

Ant

mogster

Why the last statement in this sentence?

$query = mysql_query("SELECT a.*, b.* FROM table1 a, table2 b WHERE a.row1='$item' AND b.row1='$item'",$db);

Shouldn't it be like this?

$query = mysql_query("SELECT a.*, b.* FROM table1 a, table2 b WHERE a.row1='$item' AND b.row1='$item'");

And then do the connect before the query?

knutm

anthill

makes no difference

marian

hi,

what you want to do is something like this:

$memID=mysql_query("select something from table1 WHERE primarykeyVariable='$variable");

if(mysql_num_rows($memID))
{
while($row = mysql_fetch_row($memID))
{
$value=$row[0];
}
}

this should do the trick! You must do a loop and you will be able to get the value at row1/table1. I have done this on coutless basis, if you need more help, let me know.

anthill

thanks marian, i know what your saying and yeah it works fine, but it doesn't solve my problem 🙁 I'm trying to query multiple tables in the one database, i.e table 1 and 2.

using your code:

$db = mysql_connect("localhost","root");
mysql_select_db("demo",$db);

$test = "three";
$query = mysql_query("SELECT t1.*, t2.* FROM table1 t1, table2 t2 WHERE t1.row1='$test' AND t2.row1='$test'");

if(mysql_num_rows($query)) 
{ 
while($row = mysql_fetch_row($query)) 
{ 
echo $value=$row[0]; 
} 
}

still no results

marian

You must make sure that both tables have the same common id. i don't think initializing the variable test to "three" will solve it. i think that might be also the error. not too sure though.

Cos, once it goes in to these two tables, it looks for an id that is common in table 1 and table 2, and as you know "three" is not in none of the tables aside from you initializing it. i may be wrong, but that is what i think.

marian

oh ok, i think that will work, ignore previous post

anthill

Thanks for all your help, but still no joy 🙁

Does anyone know or have some working code that selects values from two tables with the same column names?

Anyone?

Ant

djBuilder

I would try something like:

mysql_query("SELECT a., b.
FROM table1 a
INNER JOIN table2 b ON
a.col1 = b.col1
WHERE a.col1 = '$test' ");

anthill

Thanks all, its ok I got it sorted in the end. What i was doing was in fact fine, just of course there wasn't two lines that produced the $test variable, only the one. So there were in fact no results to display.

Cheers

Ant