Page 1 of 1

Help retrieving data from multiple tables in a database

Posted: Sat Mar 27, 2004 8:20 am
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

Posted: Sat Mar 27, 2004 1:25 pm
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!

Almost maybe?

Posted: Sat Mar 27, 2004 2:37 pm
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 :)

Posted: Sat Mar 27, 2004 2:51 pm
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 .....

Posted: Sat Mar 27, 2004 2:52 pm
by m3rajk
oh yeah, also easier since you just call the items by item name in the resturned rows....

$queryvariable['item1']

Posted: Sat Mar 27, 2004 2:53 pm
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.

Posted: Sat Mar 27, 2004 11:12 pm
by romeo
that worked wonderfully, thanks alot!