Help retrieving data from multiple tables in a database

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
romeo
Forum Contributor
Posts: 138
Joined: Sun Apr 21, 2002 12:50 pm

Help retrieving data from multiple tables in a database

Post by romeo »

3 Tables, 1 answer...

MAIL Table has a the fields mail_name, dom_id and account_id
ACCOUNTS Table has the id and password
DOMAINS Table has id and name





What I want is to give it an email address which would be
mail.mail_name@domains.name and get the password (mail.account_id goes into accounts.id)



ALL Help is appreciated
Goowe
Forum Commoner
Posts: 94
Joined: Mon Mar 15, 2004 9:51 am
Location: Southeast Alaska

Post by Goowe »

If you have a column in each table that's equal to the information in eachother table's columns... (If, in each row, in the mail table you had row_id equal to two, in the accounts table you had row_id equal to two, and in the domains table you had row_id equal to two) you could try....

Code: Select all

<?php
$query = "SELECT * FROM mail, accounts, domains WHERE mail.row_id = accounts.row_id AND accounts.row_id = domains.row_id ORDER BY mail_name ASC";
$result = mysql_query($query) or die("<P>Query failed: ".mysql_error());
while($line = mysql_fetch_array($result, MYSQL_ASSOC))
{
   echo "E-Mail: ".$line['mail_name']." <br> Password: ".$line['password'];
}
?>
I might be way off :? Good luck!
romeo
Forum Contributor
Posts: 138
Joined: Sun Apr 21, 2002 12:50 pm

Almost maybe?

Post by romeo »

So here is what i got

Code: Select all

<?php
$link = mysql_connect($host, $user, $password)

$query = "SELECT * FROM mail, accounts, domains WHERE mail.mail_name = '$username' AND mail.dom_id = domains.id AND mail.account_id = accounts.id ORDER BY mail.mailname ASC";

$result = mysql_db_query($dbname, $query, $link) or die("<P>Query failed: ".mysql_error()); 

while($line = mysql_fetch_array($result)) 
{ 
   echo "<br><br>The Email Address : ". $line['mail.mail_name'] . $line['domains.name'] . " <br> The Password: .$line['password']; 
} 
?>


but I am getting an error
Parse error: parse error, unexpected T_VARIABLE in /home/httpd/vhosts/blaht.com/httpdocs/email.bak/part2.php on the $query line



Help please :)
m3rajk
DevNet Resident
Posts: 1191
Joined: Mon Jun 02, 2003 3:37 pm

Post by m3rajk »

have you looked at joins? they are nicer.
select table1.item1, table1.item2,...,table2.item1,...,table3.item3,... from table1 join table2.item1 on table1.item1 join table3.item1 on table1.item2 where .....
m3rajk
DevNet Resident
Posts: 1191
Joined: Mon Jun 02, 2003 3:37 pm

Post by m3rajk »

oh yeah, also easier since you just call the items by item name in the resturned rows....

$queryvariable['item1']
Goowe
Forum Commoner
Posts: 94
Joined: Mon Mar 15, 2004 9:51 am
Location: Southeast Alaska

Post by Goowe »

Code: Select all

<?php
$link = mysql_connect($host, $user, $password);

$query = "SELECT * FROM mail, accounts, domains WHERE mail.mail_name = '".$username."' AND mail.dom_id = domains.id AND mail.account_id = accounts.id ORDER BY mail.mail_name ASC";

$result = mysql_db_query($dbname, $query, $link) or die("<P>Query failed: ".mysql_error());

while($line = mysql_fetch_array($result))
{
   echo "<br><br>The Email Address : ".$line['mail_name'].$line['name']." <br> The Password: ".$line['password'];
}
?>
You had a parse error on line 2 (you forgot the closing semi colan), I went ahead and put some extra quotes around the variable in the query just incase. You also had a parse error on line 10 but it shuld be fixed now. Also, when you're echo'ing out array from the mysql_fetch_array I don't think $line['mail.mail_name'] will work. As long as there aren't two or more columns with the same name, you're fine.
romeo
Forum Contributor
Posts: 138
Joined: Sun Apr 21, 2002 12:50 pm

Post by romeo »

that worked wonderfully, thanks alot!
Post Reply