two tables:
Syntax: [ Download ] [ Hide ] [ Select ]
a_child
ch_id u_id name
1 2 David
a_subjects
sub_id ch_id sub_name
1 1 English
2 1 History
$result = mysql_query("SELECT c.ch_id, c.name, s.sub_name
FROM a_child c
INNER JOIN a_subjects s ON (c.ch_id = s.ch_id) WHERE c.u_id = 2") or die(mysql_error());
while ($child = mysql_fetch_array($result)) {
echo $child['name'].", ";
}
for some reason this keeps giving me "David, David," and i cannot figure out why
your query is correct and it is returning what is supposed to return... just change your echo to this to see:
Code: Select all
echo $child['name'] . ", " . $child['sub_name'] . "<br />";
An inner join essentially combines the records from two tables (A and B) based on a given join-predicate. The SQL-engine computes the Cartesian product of all records in the tables. Thus, processing combines each record in table A with every record in table B. Only those records in the joined table that satisfy the join predicate remain, and the final result set will be the composed of the fields that you choose from each table.
In your example the final result set will look like this
1 David English
1 David History
then is up to you to decide how you want to
display the results. Same concept apply with more tables