[RESOLVED] Defining Query by the Result of Another

bronzino

Is there a way to define a query by the result of another query?

I have an experimental table with various elements of my webpages in there, including some of my PHP/MySql queries. It looks something like this:

Table WebPages

ID INT
Content LONGTEXT

I was hoping to be able to put in my PHP code something like this:

$query_1="select Content from WebPages where ID='3'";
$query_2= mysql_result($query_1);

$query_1="select Content from WebPages where ID='3'";
$query_2= mysql_fetch_object($query_1);

But that certainly is not working; I am getting a message that query_2 is empty. Any suggestions for a good way to do this? I think the basic problem is that I am not getting a literal result from mysql_result($query_1).

bronzino

Sorry, should have mentioned, here is what the data for ID 3, "Content" looks like:

"select * from WebPages where Type='PHP' and Category='NavOption'"

bradgrafelman

First, treat the query you have stored in the DB as nothing more than some string data that you want to retrieve (after all, that's all it is). SELECT and fetch it as you would any other string data.

Then, once you've got that string in PHP, you can call [man]mysql_query/man to execute that string as a query (since, after all, that's all a SQL query is - just a string).

bronzino

Thanks for confirming that this is possible to do.

I think my issue is with syntax.

// this query locates the query content in the table
mysql_select_db($database_Collection, $Collection);
$query_Findq = "SELECT Content FROM WebPages WHERE WebPages.ID='3'";
$Findq = mysql_query($query_Findq, $CollectionLocal) or die(mysql_error());
$row_Findq = mysql_fetch_assoc($Findq);
$totalRows_Findq = mysql_num_rows($Findq);

// this query attempts to adopt that query result as its definition
$Useq = mysql_query($row_Findq['Content'], $Collection) or die(mysql_error());
$row_Useq = mysql_fetch_assoc($Useq);
$totalRows_Useq = mysql_num_rows($Useq);

The problem is the line starting with $Useq. I'm just not sure how to format that so that PHP reads the first query result as a literal string.

bradgrafelman

bronzino wrote:
I'm just not sure how to format that so that PHP reads the first query result as a literal string.

How else would it be "reading" it if not as a string? PHP doesn't know what a "query" is - a SQL query is nothing more than a string. It is no different than:

$string = 'Hello world!';

If the above code does work, are you getting any error messages?

bronzino

Thanks for continuing to try to help.

When I use my last example, and try to "echo" the Query result in the body of the page, just to see if it's working, the error message I get is "Query is empty."

I know the first query does return a result (a string that is a select statement). It's just the second one that yields that error message. For some reason, the PHP is not interpreting "Results of Query 1" (the $Useq line) as a proper query definition for Query 2 ($query_Useq).

bronzino

quote.[/QUOTE]

Please ignore that reference - a typo.

bradgrafelman

What variable/expression were you trying to echo (when you got the "Query is empty." message)?

Have you tried echo'ing $row_Findq['Content']? What does it output?

bronzino

That one outputs okay. Here's what you get:

"select * from WebPages where Category='NavOption'"

Perfect! That test won't work, by the way, if I leave that second query definition in the PHP.

I re-inserted the second query definition after concluding that test and echo'ed it as follows:

<?php do { ?>
<?php echo $row_Findq['Content']; ?>
<?php } while ($row_Findq = mysql_fetch_assoc($Findq)); ?>

And now I'm getting this error message:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"select * from WebPages where Category='NavOption'"' at line 1

bradgrafelman

That would seem to suggest that the double quotes are included in the actual data? If so, that's of course not going to be a valid SQL query.

bronzino

Ignore the above. My usual forum permits later edits and I'm rather lost without that feature.

That one outputs okay. Here's what you get:

"select * from WebPages where Category='NavOption'"

Perfect! That test won't work, by the way, if I leave that second query definition in the PHP.

I re-inserted the second query definition after concluding that test and echo'ed it as follows:

<?php do { ?>
<?php echo $row_Useq['Content']; ?>
<?php } while ($row_Useq = mysql_fetch_assoc($Useq)); ?>

And now I'm getting this error message:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"select * from WebPages where Category='NavOption'"' at line 1

bradgrafelman

I'm confused.. nothing in your most recent post differs from your previous post.

Thus, my response in this post doesn't differ any from my previous post either...

bradgrafelman;10982118 wrote:
That would seem to suggest that the double quotes are included in the actual data? If so, that's of course not going to be a valid SQL query.

bronzino

bradgrafelman;10982118 wrote:
That would seem to suggest that the double quotes are included in the actual data? If so, that's of course not going to be a valid SQL query.

Yes, you are correct. There are quotes in the actual data.

I've now axed them. I'm happy to say that I'm not getting error messages anymore, but I'm also not getting any data in response to:

<?php do { ?>
<?php echo $row_Useq['Content']; ?>
<?php } while ($row_Useq = mysql_fetch_assoc($Useq)); ?>

bradgrafelman

Can you show us a new copy of all of the code involving the second query?

Also, you previous defined a variable $totalRows_Useq that contained the number of rows returned. Have you viewed the contents of this variable to see if any rows were actually returned?

bronzino

That was indeed all of the code for the second query:

$Useq = mysql_query($row_Findq['Content'], $CollectionLocal) or die(mysql_error());
$row_Useq = mysql_fetch_assoc($Useq);
$totalRows_Useq = mysql_num_rows($Useq);

And I realize now that you solved my technical problem - the quotes.

The final problem (the lack of returned data) is one that I have to solve. I tried this instead; it collapses both queries into one, just to test:

mysql_select_db($database_CollectionLocal, $CollectionLocal);
$query_Dx = "SELECT * FROM WebPages WHERE Category='NavOption'";
$Dx = mysql_query($query_Dx, $CollectionLocal) or die(mysql_error());
$row_Dx = mysql_fetch_assoc($Dx);

And no data is returned, just as is the case with the second query. I do have a column in my WebPages table called "Category" and there are rows where "NavOption" is in that column. But this query returns no results, so there some as-yet undiscovered and unrelated issue involved. I think I can probably sort that out though.

Thank you very much for your help. The quotes in the data were the problem!