note 80821 modified in mysqli.query by thiago
| From: | thiago@php.net | Date: | Thu, 15 Oct 2009 01:09:15 +0000 |
| Subject: | note 80821 modified in mysqli.query by thiago | ||
| References: | 1 | Groups: | php.notes |
| Request: | Send a blank email to php-notes+get-161744@lists.php.net to get a copy of this message | ||
This may or may not be obvious to people but perhaps it will help someone.
When running joins in SQL you may encounter a problem if you are trying to pull two columns with the
same name. mysqli returns the last in the query when called by name. So to get what you need you can
use an alias.
Below I am trying to join a user id with a user role. in the first table (tbl_usr), role is a number
and in the second is a text name (tbl_memrole is a lookup table). If I call them both as role I get
the text as it is the last "role" in the query. If I use an alias then I get both as
desired as shown below.
<?php
$sql = "SELECT a.uid, a.role AS roleid, b.role,
FROM tbl_usr a
INNER JOIN tbl_memrole b
ON a.role = b.id
";
if ($result = $mysqli->query($sql)) {
while($obj = $result->fetch_object()){
$line.=$obj->uid;
$line.=$obj->role;
$line.=$obj->roleid;
}
}
$result->close();
unset($obj);
unset($sql);
unset($query);
?>
In this situation I guess I could have just renamed the role column in the first table roleid and
that would have taken care of it, but it was a learning experience.
--was--
This may or may not be obvious to people but perhaps it will help someone.
When running joins in SQL you may encounter a problem if you are trying to pull two columns with the
same name. mysqli returns the last in the query when called by name. So to get what you need you can
use an alias.
Below I am trying to join a user id with a user role. in the first table (tbl_usr), role is a number
and in the second is a text name (tbl_memrole is a lookup table). If I call them both as role I get
the text as it is the last "role" in the query. If I use an alias then I get both as
desired as shown below.
$sql = "SELECT a.uid, a.role AS roleid, b.role,
FROM tbl_usr a
INNER JOIN tbl_memrole b
ON a.role = b.id
";
if ($result = $mysqli->query($sql)) {
while($obj = $result->fetch_object()){
$line.=$obj->uid;
$line.=$obj->role;
$line.=$obj->roleid;
}
}
$result->close();
unset($obj);
unset($sql);
unset($query);
}
In this situation I guess I could have just renamed the role column in the first table roleid and
that would have taken care of it, but it was a learning experience.
http://php.net/manual/en/mysqli.query.php