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!