note 2343 deleted from function.mysql-db-query by didou
| From: | didou@php.net | Date: | Sat, 16 Aug 2003 09:30:18 +0000 |
| Subject: | note 2343 deleted from function.mysql-db-query by didou | ||
| References: | 1 | Groups: | php.notes |
| Request: | Send a blank email to php-notes+get-54307@lists.php.net to get a copy of this message | ||
Note Submitter: arahn@altavista.net
----
Just my lil grain of sand...
Figuring out how to make nested selects on a MySQL DB? Mysql doesn't support queryes like:
SELECT a
FROM tabla
WHERE b IN (SELECT b FROM tablb WHERE ...)
But does support:
SELECT a
FROM tabla
WHERE b IN ('10','20','30')
So, they suggest using temporary tables and left join to solve that kind of problems, but for small
queryes, and supposing you're not using heap tables (Mysql 3.23>), I think that practice is
rather clumsy...
So I made I lil workaround for that problema... Do the Inner select first, and put the result in the
form ('a', 'b', 'c') as a string, and then use the form of IN () above
described.
Here it is a lil function that does it for several specific cases:
# Converts result to the form 'var IN(###,###,###...)'
function HacerCondicion ($result,$field,$cond)
{
if (mysql_num_rows($result)==0) {
return "1=1";
} else {
$condicion = "$field $cond (";
while ($reg = mysql_fetch_array($result)) {
$condicion .= "$reg[$field],";
}
$condicion = substr($condicion,0,-1);
$condicion .= ")";
return $condicion;
}
It can be improved, putting all the results into an array and then using Implode() to join with
commas...
An example of use might be:
$ucadas = mysql_db_query("foo",
"SELECT asignatura AS codigo
FROM prelaciones
WHERE uca != 0
AND $uca >= uca");
$conduca = HacerCondicion($ucadas,'codigo','NOT IN');
$result = mysql_db_query("bar",
"SELECT ping
FROM materias
WHERE $conduca");
I hope THREE things:
- That you understand my weird examples.
- That the above will be of any help to any of you
- That you make money with it!
Cheers! Alfredo