SELECT * FROM multiple tables

[anthill]

Hi,

I've looked through a few posts similar to this and still can't find my answer. I have two databases and i wanna select information from each, only the rows i want to select from have the same name, i think that is what's causing the poblem, heres an example:

table1:

| row1 | row 2

| this is row 1 of table 1 | this is row 2 of table 1 |

table2:

| row1 | row 2 |
| this is row 1 of table 2 | this is row 2 of table 2 |

$test = "row";

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

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

while($get=mysql_fetch_array($query))
  {
  echo $get['row1'];
  // prints this is row one of table two

  echo $get['row2'];
  // prints this is row two of table two
  }

I'm trying to get all values in the $get array for both tables. This obviously isn't right as its only selecting the info from table 2. Anyone able to help me?

Thanks

Ant

[kilbey1]

I'd try this. Instead of

$query = mysql_query("SELECT a., b. FROM table1 AS a, table2 AS b WHERE a.row1 LIKE '%$test%' AND b.row1 LIKE '%$test%'",$db);

try

$query = mysql_query("SELECT a., b. FROM table1 AS a, table2 AS b WHERE a.row1 AS ROW_1 LIKE '%$test%' AND b.row1 AS ROW_2 LIKE '%$test%'",$db);

So that you can then do your code like so:

while($get=mysql_fetch_array($query))
  {
  echo $get['ROW_1'];
  // prints this is row one of table two

  echo $get['ROW_2'];
  // prints this is row two of table two
  }

[RedDragon]

selecting multiple tables only works fine if you have a difference between those tables as would be an id, simular fields you then would have to use WHERE a.id=b.id

hope i could help you

[anthill]

Hi, thanks for your help, but I had it right all the time, i just wasn't pulling the right values from the array (i think). Well ok this is what I used amd this is what I get:

$test = "row";

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

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


while($get=mysql_fetch_array($query))
  {
  echo $get['0']."<br>";
  //prints: this is row 1 of table 1


  echo $get['1']."<br>";
  //prints: this is row 2 of table 1


  echo $get['2']."<br>";
  //prints: this is row 1 of table 2


  echo $get['3']."<br>";
  //prints: this is row 2 of table 2

  }

Bingo!

Ant

[anthill]

I was kidding myself all along 🙁

why wont this return any values?

$test = "this is row 1 of table 1";

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

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


while($get=mysql_fetch_array($query))
  {
  echo $get['0']."<br>";
  echo $get['1']."<br>";
  echo $get['2']."<br>";
  echo $get['3']."<br>";
  }

[kilbey1]

Try it this way. Makes it easier to debug. It also looks like you're not calling the result.

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

$test = "this is row 1 of table 1";

$query = "SELECT a.*, b.*
          FROM table1 AS a, table2 AS b
          WHERE a.row1='$test'
          AND b.row1='$test'";
$result = mysql_query ($query, $db_connection) or die (mysql_error());

while($get=mysql_fetch_array($result))
  {
  echo $get['0']."<br>";
  echo $get['1']."<br>";
  echo $get['2']."<br>";
  echo $get['3']."<br>";
  }

[juschillinnow]

wait a minute.....I thought you said you were selecting from two different databases, not two different tables.

if from two different databases, you will have to make two different connects to the database with two different strings.

for example:

$db

and

$db1 and us is as a pointer in your mysql_query

like

$result = mysql_query($query, $db)
$result = mysql_query($query, $db1)

[anthill]

did I say databases? I thought I said tables...Anyway, these are the two tables as mentioned earlier:

table1:

| row1 | row2
| this is row 1 of table 1 | this is row 2 of table 1 |

table2:

| row1 | row2 |
| this is row 1 of table 2 | this is row 2 of table 2 |

in a database called demo.

Tried Kibey1's code but it isn't returning any results or errors, when obviously $test is on the firstline of row1

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

$test = "this is row 1 of table 1";

$query = "SELECT a.*, b.*
          FROM table1 AS a, table2 AS b
          WHERE a.row1='$test'
          AND b.row1='$test'";
$result = mysql_query ($query, $db) or die (mysql_error());

while($get=mysql_fetch_array($result))
  {
  echo $get['0']."<br>";
  echo $get['1']."<br>";
  echo $get['2']."<br>";
  echo $get['3']."<br>";
  }

anyone? 🙁

[jonsof]

Try this

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

$test = "this is row 1 of table 1";

$query = "SELECT a.*, b.*
          FROM table1 a, table2 b
          WHERE a.row1='$test'
          AND b.row1='$test'";
$result = mysql_query ($query, $db) or die (mysql_error());

while($get=mysql_fetch_array($result))
  {
  echo $get['0']."<br>";
  echo $get['1']."<br>";
  echo $get['2']."<br>";
  echo $get['3']."<br>";
  }

AS command is about giving alias for fields not tables, so if you want to give an alias for a table use "Select....from table1 t1, table2 t2....."

[anthill]

no difference 🙁 I am doing things right, right? Coz surely it has to work for someone??

Ant