open two tables at once??

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
hward
Forum Contributor
Posts: 107
Joined: Mon Apr 19, 2004 6:19 pm

open two tables at once??

Post by hward »

how can you pull info out of two tables at the same time??
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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.
hward
Forum Contributor
Posts: 107
Joined: Mon Apr 19, 2004 6:19 pm

Post by hward »

ok i am way to much of a newbie to understand all of that any other help???
lostboy
Forum Contributor
Posts: 329
Joined: Mon Dec 30, 2002 8:12 pm
Location: toronto,canada

Post 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)
hward
Forum Contributor
Posts: 107
Joined: Mon Apr 19, 2004 6:19 pm

Post 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
User avatar
dull1554
Forum Regular
Posts: 680
Joined: Sat Nov 22, 2003 11:26 am
Location: 42:21:35.359N, 76:02:20.688W

Post 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
hward
Forum Contributor
Posts: 107
Joined: Mon Apr 19, 2004 6:19 pm

Post by hward »

have tried doing two queries but can never get it to execute them
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
hward
Forum Contributor
Posts: 107
Joined: Mon Apr 19, 2004 6:19 pm

Post 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
";
????????????????????????
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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 ";

?>
hward
Forum Contributor
Posts: 107
Joined: Mon Apr 19, 2004 6:19 pm

Post by hward »

still can't execute query
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
hward
Forum Contributor
Posts: 107
Joined: Mon Apr 19, 2004 6:19 pm

Post by hward »

nice!!!!!! thanks
hward
Forum Contributor
Posts: 107
Joined: Mon Apr 19, 2004 6:19 pm

Post by hward »

works fine thanks again
Post Reply