Using a variable in mysql_fetch_assoc to output data from a column across multiple rows.?
if i have a request like this
$query = mysql_query("
SELECT * FROM tbl_school
INNER JOIN tbl_livingexp ON (tbl_school.schoolname=tbl_livingexp.schoolname)
INNER JOIN tbl_tuition ON (tbl_school.schoolname=tbl_tuition.schoolname)
INNER JOIN tbl_apprequirments ON
(tbl_school.schoolname=tbl_apprequirments.schoolname)
where tbl_school.schoolname = '$schoolname'");
and 3 tables contain a column named language.
when i start writing this code.
while($row = mysql_fetch_assoc($query)) {
//output.
how can I output the language of the column only from the tbl_school table if I were to type it like this?
$row['language']; ?
I tried $ row ['tbl_school.language']; but that gave me an error.
You must explicitly call them:
SELECT a.foo first_col, b.foo second_col, c.foo third_col
FROM a, b, c
WHERE [conditions]
If you are fetching data with
$row = mysql_fetch_assoc($query);
you can now access the columns using $ row ["first_col"], $ row ["second_col"] and $ row ["third_col"]. It has to do with how the mysql client library works. The table name will simply not be appended to the column name. This means that each column name must be unique in order to appear in the result.
a source to share
In production code, try to avoid it SELECT *
at all costs. You need to explicitly specify the column names in your query:
SELECT
le.schoolname,
le.language AS livingexp_language,
t.language AS tuition_language,
...
FROM tbl_school
INNER JOIN tbl_livingexp AS le ON (tbl_school.schoolname=tbl_livingexp.schoolname)
INNER JOIN tbl_tuition AS t ON (tbl_school.schoolname=tbl_tuition.schoolname)
...
Using:
$row["livingexp_language"];
a source to share
(For completeness) You can also achieve the same by using mysql_fetch_row rather than mysql_fetch_assoc, which puts your results into a numeric array that you can reference:
$row = mysql_fetch_row($query);
$id = $row[0];
$language = $row[1]; // for instance
In this case, it is even more important to avoid SELECT * as it is very easy to make mistakes and difficult to debug.
a source to share