note 2343 modified in function.mysql-db-query by irc-html
| From: | irc-html@php.net | Date: | Mon, 06 May 2002 22:57:51 +0000 |
| Subject: | note 2343 modified in function.mysql-db-query by irc-html | ||
| References: | 1 | Groups: | php.notes |
| Request: | Send a blank email to php-notes+get-30271@lists.php.net to get a copy of this message | ||
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
--was--
Just my lil grain of sand...
Figuring out how to make nested selects on a MySQL DB? Mysql doesn't support queryes like:
<PRE>
SELECT a
FROM tabla
WHERE b IN (SELECT b FROM tablb WHERE ...)
</PRE>
But does support:
<PRE>
SELECT a
FROM tabla
WHERE b IN ('10','20','30')
</PRE>
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:
<PRE>
# 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;
}
</PRE>
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:
<PRE>
$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");
</PRE>
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
http://www.php.net/manual/en/function.mysql-db-query.php