|
|
Posted by Richard Lynch on 01/19/05 18:51
Greg Cullen wrote:
> 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.
They do support this, though it might be easier to use the built-in
functions for gathering this kind of meta-data...
> <?
> require_once ('mysql_connect.php');
> $result1 = mysql_query('show tables',$dbc);
Don't you want to loop through ALL the tables?
Why then are you doing an 'if' here to test *ONE* result, instead of:
while (list($tablename) = mysql_fetch_row($result1))
> if ($myrow1 = mysql_fetch_array($result1))
> {
> // display list if there are records to display
> $tmptablename = sprintf("describe {$myrow1[0]}");
The sprintf here is pretty bogus. You're not using anything except the
first string argument, so basically you're just confusing readers with the
sprintf() bit.
Of course, by tearing apart the result's row in my while loop above, this
also gets simplified:
$tmptablename = "describe $tablename";
> 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>";
Dude, just break out of PHP and back in again, and you can forget all
those silly \\\\s.
?>
<tr>
<td width="20%" bgcolor="#006600"><br><font
color="#FFFFFF">Field</font></b></td>
..
..
..
</tr>
Though now I know why the whole if/do-while mess...
It's much easier to either:
A) Use http://php.net/mysql_num_rows to decide to print table headers, or
B) Go ahead and print them, but if there are NO rows, use:
<TR><TD colspan="5">No Tables</TD></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));
The REAL problem you are having is that *WAY* up at the beginning of this
loop, you set $temptablename (or whatever it was) *OUTSIDE* this loop.
So the "describe ..." never changes, and you keep describing the first
table, even though you are looping through all the tables in the do-while.
> }
>
> else {
>
> echo "No Tables";
>
> }
> mysql_close();
>
> include ('footer.php');
> ?>
Synopsis of Tips:
1. Reserve do-while for the rare case when it's REALLY needed and cleaner.
2. Get out of PHP mode for large chunks of HTML
3. Use list() to tear apart your rows into well-named variables
--
Like Music?
http://l-i-e.com/artists.htm
Navigation:
[Reply to this message]
|