Page 1 of 1

open two tables at once??

Posted: Tue May 18, 2004 12:56 pm
by hward
how can you pull info out of two tables at the same time??

Posted: Tue May 18, 2004 1:01 pm
by Weirdan
using some sort of join: http://dev.mysql.com/doc/mysql/en/JOIN.html
This information is for MySQl, but should be pretty similar for any database supporting SQL language.

Posted: Tue May 18, 2004 3:32 pm
by hward
ok i am way to much of a newbie to understand all of that any other help???

Posted: Tue May 18, 2004 3:37 pm
by lostboy
tableA
idA
nameA
fieldA

tableB
idB
idA
somefieldB

select tableA.name, tableB.somefieldB from
tableA, tableB where
tableA.idA = tableB.idA

the above will join the two tables on the common field idA that exists in both tables (note: the names do NOT need to be the same)

Posted: Tue May 18, 2004 4:01 pm
by hward

Code: Select all

<?

$db_name = "database";
$table_name = "members";

$db_name = "database";
$table2_name = "adverts";

$connection = @mysql_connect("localhost", "user", "password") 
	or die("Couldn't connect.");

$db = @mysql_select_db($db_name, $connection)
	or die("Couldn't select database.");

$sql = "SELECT mem_userid, mem_username, mem_email,  adv_picture, adv_username
	FROM $table_name, table2_name
	 WHERE mem_userid > '30001'
	 ORDER BY mem_userid
	
	 ";

$result = @mysql_query($sql,$connection)
	or die("Couldn't execute query.");
the adv_picture, adv_username would be from table2_name

i need the mem_userid to equal the adv_username between the two tables

where am i going wrong

Posted: Tue May 18, 2004 4:10 pm
by dull1554
just do 2 different queries, it does not matter if you gather the info with the same query or with many the end result of comparing 2 values is the same

Posted: Tue May 18, 2004 4:13 pm
by hward
have tried doing two queries but can never get it to execute them

Posted: Tue May 18, 2004 4:55 pm
by pickle
A simple way to pull data out of two table at the same time, is to list both of them in the "FROM" clause. For example, say I have 2 tables, one called "computer_info" and another called "personal_info", and I want to get the operating system and hair colour of a person with a particular username. The query I could use for that is:

Code: Select all

SELECT
  os,
  hair_colour
FROM
  computer_info,
  personal_info
WHERE
  computer_info.username = 'joe_user' AND
  personal_info.username = 'joe_user'
If I just wanted to get all os's and all hair colours, I could just remove the WHERE clause.

Posted: Tue May 18, 2004 5:32 pm
by hward

Code: Select all

$sql = "SELECT mem_userid, mem_username, mem_email, mem_joindate, mem_sex, mem_country, adv_username, adv_picture
	FROM $table_name,
	FROM $table2_name
		 WHERE table_name.mem_userid > '30001' AND
		 WHERE table2_name.adv_username = 'mem_username'
	 ORDER BY mem_userid
";
????????????????????????

Posted: Tue May 18, 2004 5:36 pm
by John Cartwright

Code: Select all

<?php

$sql = "SELECT mem_userid, mem_username, mem_email, mem_joindate, mem_sex, mem_country, adv_username, adv_picture 
   FROM $table_name, 
            $table2_name 
       WHERE table_name.mem_userid > '30001' AND 
                  table2_name.adv_username = 'mem_username' 
    ORDER BY mem_userid ";

?>

Posted: Tue May 18, 2004 5:37 pm
by hward
still can't execute query

Posted: Tue May 18, 2004 5:50 pm
by feyd

Code: Select all

<?php

$sql = "SELECT t1.mem_userid, t1.mem_username, t1.mem_email, t1.mem_joindate, t1.mem_sex, t1.mem_country, t2.adv_username, t2.adv_picture 
   FROM $table_name t1, 
            $table2_name t2
       WHERE t1.mem_userid > '30001' AND 
                  t2.adv_username = t1.mem_username 
    ORDER BY t1.mem_userid ";

?>
try that.

Posted: Tue May 18, 2004 5:57 pm
by hward
nice!!!!!! thanks

Posted: Tue May 18, 2004 5:57 pm
by hward
works fine thanks again