|
Posted by Greg Cullen on 01/19/05 06:41
Relatively new to PHP. Having an issue trying to nest sql statements.
Basically I am trying to pull a variable from SQL1, Pass it as a
Variable/Bind or Parm to SQL2 and then Go back to SQL1 and pull the next
value and pass to SQL2 again for processing.
It seems like the SQL2 is getting stuck on the first value passed by SQL1.
Like SQL2 does not rebuild with the new variable.
In my example I am testing reading all my tables and reporting their
definitions. I have other uses for this technique if PHP and MySQL support.
<?
require_once ('mysql_connect.php');
$result1 = mysql_query('show tables',$dbc);
if ($myrow1 = mysql_fetch_array($result1))
{
// display list if there are records to display
$tmptablename = sprintf("describe {$myrow1[0]}");
do {
$result2 = mysql_query($tmptablename,$dbc);
echo "Table: {$myrow1[0]}";
// Create page headers
echo "<table border=\"1\" cellspacing=\"1\" width=\"80%\"
id=\"{$myrow1[0]}\">";
echo "<tr>";
echo "<td width=\"20%\" bgcolor=\"#006600\"><b><font
color=\"#FFFFFF\">Field</td>";
echo "<td width=\"20%\" bgcolor=\"#006600\"><b><font
color=\"#FFFFFF\">Type</td>";
echo "<td width=\"5%\" bgcolor=\"#006600\"><b><font
color=\"#FFFFFF\">Null</td>";
echo "<td width=\"10%\" bgcolor=\"#006600\"><b><font
color=\"#FFFFFF\">Key</td>";
echo "<td width=\"20%\" bgcolor=\"#006600\"><b><font
color=\"#FFFFFF\">Default</td>";
echo "<td width=\"25%\" bgcolor=\"#006600\"><b><font
color=\"#FFFFFF\">Extra</td>";
echo "</tr>";
if ($myrow2 = mysql_fetch_array($result2))
{
// display list if there are records to display
do {
echo "<tr>";
echo "<td width=\"20%\">";
if ("{$myrow2['Field']}"==null)
{
echo " ";
}
else
{
echo "{$myrow2['Field']}";
}
echo "</td>\n";
echo "<td width=\"20%\">";
if ("{$myrow2['Type']}"==null)
{
echo " ";
}
else
{
echo "{$myrow2['Type']}";
}
echo "</td>\n";
echo "<td width=\"5%\">";
if ("{$myrow2['Null']}"==null)
{
echo " ";
}
else
{
echo "{$myrow2['Null']}";
}
echo "</td>\n";
echo "<td width=\"10%\">";
if ("{$myrow2['Key']}"==null)
{
echo " ";
}
else
{
echo "{$myrow2['Key']}";
}
echo "</td>\n";
echo "<td width=\"20%\">";
if ("{$myrow2['Default']}"==null)
{
echo " ";
}
else
{
echo "{$myrow2['Default']}";
}
echo "</td>\n";
echo "<td width=\"25%\">";
if ("{$myrow2['Extra']}"==null)
{
echo " ";
}
else
{
echo "{$myrow2['Extra']}";
}
echo "</td>\n";
echo "</td>\n";
echo "</tr>\n";
} while ($myrow2 = mysql_fetch_array($result2));
echo "</table>";
echo "<br>";
$myrow2 = Null;
mysql_free_result(result2);
} else {
// no records to display
echo "Sorry, no records were found!";}
} while ($myrow1 = mysql_fetch_array($result1));
}
else {
echo "No Tables";
}
mysql_close();
include ('footer.php');
?>
[Back to original message]
|