0

I have looked at the same code for hours trying to figure out why my queries are not working. The two I have listed below are the two that are not working.

$getRequestIdQuery = "SELECT request_id FROM request_table WHERE request_key = '$requestKey' AND sort_order = $so"; $getRequestId = $pdo->prepare($getRequestIdQuery); $getRequestId->execute(); foreach($getRequestId as $idRow) { $requestId = $idRow['request_id']; } // This will update the ready status of the request id returned above $updateReadyStatusQuery = "UPDATE request_table SET request_ready = 1 WHERE request_id = $requestId"; $updateReadyStatus = $pdo->prepare($updateReadyStatusQuery); $updateReadyStatus->execute();

The above runs whenever a file copy returns true. I am already sure this is running as there are omitted error logs from the above that show up during every test run. I am also certain the query in question works as I have successfully run the query (as it shows up in the error log) in phpmyadmin. The following is a snippet of code only a few lines above this that runs correctly:

$checkForComposedQuery = "SELECT * FROM composed_files WHERE file_source_id = '$fsi' AND file_number = '$fn'"; $checkForComposed = $pdo->prepare($checkForComposedQuery); $checkForComposed->execute();

Any hints as to what might be causing this to not work? Both of the above snippets occur within a foreach loop if that helps.

Much thanks in advance.

UPDATE:

The following incorporates code that includes suggestions added by Charles below:

$gotCopied = copy($sourceHymnFile, $destHymnFile); if ($gotCopied == true) { error_log("The file has been successfully copied."); $idRow; $getRequestIdQuery = "SELECT request_id FROM request_table WHERE request_key = ? AND sort_order = ?"; $getRequestId = $pdo->prepare($getRequestIdQuery); $getRequestId->execute(array($requestKey, $so)); error_log("this is the value of request key : ".$requestKey); // Displays correct $requestKey value error_log("This is the value of sort order : ".$so); // Displays correct $so value $idRow = $getRequestId->fetch(PDO::FETCH_ASSOC); $requestId = $idRow['request_id']; error_log("This is the value of the request id : ".$requestId); // No output in error log for $requestId above // This will update the ready status of the request id returned above $updateReadyStatusQuery = "UPDATE request_table SET ready = 1 WHERE request_id = ?"; error_log("This updates the status of the song request if the song is played : ".$updateReadyStatusQuery); $updateReadyStatus = $pdo->prepare($updateReadyStatusQuery); $updateReadyStatus->execute(array($requestId)); }

The following correctly runs correctly for constants as entered:

 if ($gotCopied == true) { error_log("The file has been successfully copied."); $idRow; $getRequestIdQuery = "SELECT request_id FROM request_table WHERE request_key = ? AND sort_order = ?"; $getRequestId = $pdo->prepare($getRequestIdQuery); $getRequestId->execute(array(5, 2)); error_log("this is the value of request key : ".$requestKey); error_log("This is the value of sort order : ".$so); $idRow = $getRequestId->fetch(PDO::FETCH_ASSOC); $requestId = $idRow['request_id']; error_log("This is the value of the request id : ".$requestId); // No output in error log for $requestId above // This will update the ready status of the request id returned above $updateReadyStatusQuery = "UPDATE request_table SET ready = 1 WHERE request_id = ?"; error_log("This updates the status of the song request if the song is played : ".$updateReadyStatusQuery); $updateReadyStatus = $pdo->prepare($updateReadyStatusQuery); // This execute works correctly if a value is set for $requestId $updateReadyStatus->execute(array($requestId)); }
Improve this question
5
  • Why haven't you looked at pretty samples in manual ru2.php.net/manual/en/pdo.query.php? Commented Mar 10, 2011 at 1:54
  • I've read through it a couple of times, though failed to see why the second snippet works and the first doesn't. Is there something I am missing? Commented Mar 10, 2011 at 2:01
  • 1
    @user652677: it is a simpler and better way to perform a query if you don't use variables binding: pdo::query(). Use it to solve your issue. In your particular case though I'd better bind $fsi and $fn variables. Commented Mar 10, 2011 at 2:03
  • What are the values in $so and $requestKey? 5 and 2? What's the value of $getRequestId-rowCount() after the execute? I'm willing to bet that it's zero. Commented Mar 10, 2011 at 23:58
  • The rowCount is zero only when it is $getRequestId->execute($requestKey, $so), though not so when it is $getRequestId->execute(5, 2). These are values for $requestKey and $so, respectively. rowCount = 1 in the second case. Commented Mar 11, 2011 at 2:40

1 Answer 1

Reset to default
2

You have two problems here.

First, placeholders and binding. Your code here is vulnerable to SQL injection. PDO contains a tool to help mitigate this threat. 

$getRequestIdQuery = "SELECT request_id FROM request_table WHERE request_key = ? -- new! AND sort_order = ?"; $getRequestId = $pdo->prepare($getRequestIdQuery); $getRequestId->execute(array($requestKey, $so));

The ?s in the query are placeholders. The array passed to execute provides a list of replacements for any placeholders. They are automatically escaped and quoted as necessary.

Second, you're retrieving results incorrectly. You need to call the fetch method (or fetchAll method) on the statement handle. For example:

$idRow = $getRequestId->fetch(PDO::FETCH_ASSOC); $requestId = $idRow['request_id'];

Note that there's no loop here. Your previous loop would have expected multiple results, but it overwrote the same variable in each loop. It looks like you're expecting only one result, so you only need to worry about one result.

We should also update your other query to use placeholders.

$updateReadyStatusQuery = "UPDATE request_table SET request_ready = 1 WHERE request_id = ?"; $updateReadyStatus = $pdo->prepare($updateReadyStatusQuery); $updateReadyStatus->execute(array($requestId));

... and your third ...

$checkForComposedQuery = "SELECT * FROM composed_files WHERE file_source_id = ? AND file_number = ?"; $checkForComposed = $pdo->prepare($checkForComposedQuery); $checkForComposed->execute(array($fsi, $fn));
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thank you for your response. I have tried your suggestions, but am still having the same issue. If I put in constants for the top query in your response, i.e. WHERE request_key = 5 AND sort_order = 2, the output is correct relative to the two constants entered. This is true with the second query in which I have problems. It is when variables are introduced that there are issues. I have checked the variable names time and again and even error log their values just below the query. Any thoughts?
The statement object has a queryString property, which should be the query that was run against the server. Try checking that property to see if it helps you debug the problem.
I put an error log to display the queryString of the prepared pdo statement, and it shows up as it is supposed to (provided the question marks should remain). It still remains a mystery. Any suggestions on a possible alternative?
Can you edit your original question with the specific code that is not working now, including the query, the prepare, the execute, the fetch, and the actual, real values of the variables being passed in the execute?

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.