How to write MySQL select statement using HTTP POST request value

you can try this stuff:
change this statement   "SELECT * FROM tbl_data WHERE invoice_num=$_POST["x_invoice_num"]"

to
   "SELECT * FROM tbl_data WHERE invoice_num=" . $_POST['x_invoice_num' . "]"

Thanks, but neither of those work.

>>but neither of those work.

what kind of thing doesn't work?

try output it, and see what you get ?


<?php

//...

echo "SELECT * FROM tbl_data WHERE invoice_num=".$_POST["x_invoice_num"];

?>


what you get here? is the invoice num exists in your database?

ryancys, sorry for the delay.  I needed to do a lot of testing in order to provide a meaningful reply.

if I point to the page in my browser, echoing as you suggest does the following:

    echo "SELECT * FROM tbl_data WHERE invoice_num=".$_POST["x_invoice_num"];
prints to the page:
    SELECT * FROM tbl_data WHERE invoice_num=

Tell me if I'm wrong, but I don't think this helps us.

Referring to my original code, if I change the invoice_num value to one that is already in my db table, for example:

    $result = mysql_query("SELECT * FROM tbl_data WHERE invoice_num=123991238");

then I am sent an email containing the correct POST value in $_POST["x_invoice_num"] (see line 15 of my code) along with the correct value of $student_id from my db table (see lines 5 and 16 of my code).  This tells me (1) that my select statement contains no errors and (2) that the value in $_POST["x_invoice_num"] is correct.

If I change the code to what you suggested, I still receive an email, but the $student_id value is blank.  This may indicate that there is a problem with the db query.

I'm wondering if the problem is something else.

The HTTP post request is probably reaching my php page literally within a second of when the x_invoice_num value is being written to my db.  Is it possible that this is not enough time after the data is written before my new query?

so is your form got a form element called as "x_invoice_num" ? and make sure if it's exists, it's not appears more than once, and it got value before your submission?

seems like your form got element "x_invoice_num", but the value is empty or it's an element array?

also, check your form, make sure the "method" in form tag is "POST" ?


in short, make sure $_POST["x_invoice_num"] is not empty....

The method is POST, and I've proven for certain that $_POST["x_invoice_num"] is not empty.  As I said in my prior post, the value of $_POST["x_invoice_num"] ends up in the email that my script generates (see line 15 of my code), so it clearly is not empty.

Is the syntax of the select statement correct?

    mysql_query("SELECT * FROM tbl_data WHERE invoice_num=".$_POST["x_invoice_num"]);

?

>>Is the syntax of the select statement correct?

it's correct.


or can you post your form scripts here for debugging?

Thanks for offering to look at it, but the form is not actually the relevant thing.   My HTML form sends values both to my db and to the Authorize.Net payment gateway.   The gateway has something called a Silent Post function, which I've configured to send an array of values to the URL of my PHP page.   There are 42 Silent Post returned value pairs, one of which is 'x_invoice_num'.

I wrote this a couple of posts ago, but you didn't respond:

What do you think, is this a possibility?  Does the question need clarification?

Hello,

Use this:

 $post = '"'.$_POST["x_invoice_num"].'"';
$query1 = "SELECT * FROM tbl_data WHERE invoice_num=$post";

Thanks, shobinsun, but the result is no different.  The correct value for $_POST["x_invoice_num"] shows up in the body of my email, but the value of $student_id is still blank, which I think indicates a problem with the db query.  Or, as I suggested in my last post, perhaps there isn't enough time after writing the x_invoice_num value to my db before trying to read it.

I have to go to bed now, but I'll do more testing/debugging in the morning, and I'll post results if relevant.  Thanks for trying to help.

>>Is it possible that this is not enough time after the data is written before my new query?

Your php scripts are interpreted line by line, it's NOT possible to jump out of scripts here.


alternatively, use $_REQUEST["x_invoice_num"] instead of $_POST["x_invoice_num"] , and see what happens?

Line 5 is missing quotation marks.

Change it to:

$student_id = $row['student_id'];

If this doesn't work, I think the best idea would be to rewrite your code to isolate exactly where the problem lies.

Why don't you try this code:

<?php include_once('./db.inc.php');
 
$invoice_num = $_POST["x_invoice_num"];
echo "<p>invoice num: $invoice_num</p>";
 
$sql = "SELECT * FROM tbl_data WHERE invoice_num=$invoice_num";
echo "<p>sql: $sql</p>";
 
$result = mysql_query($sql);
 
if($result === false) {
   echo "<p>Mysql_query failed!</p>";
} else {
   $num_rows = mysql_num_rows($result);
   echo "<p>num_rows: $num_rows</p>";
 
   while ($row = mysql_fetch_array($result)) {
      echo "<pre>";
      print_r($row);
      echo "</pre>";
      $student_id = $row['student_id'];
      echo "<p>student_id: $student_id</p>";
   }
}
 
?>

Select allOpen in new window

ryancys, I was right that there was not enough time after the data is written before my new query. I've got it working now. I simply needed to add:

    sleep(10);

after the opening php tag.

I'm awarding you the points for correcting my select statement syntax and out of appreciation for all the time you put into trying to help me.

Below is the working code. Thanks to all who tried to help!

<?php
 
sleep(10);
 
include_once('./db.inc.php');
 
$result = mysql_query("SELECT * FROM tbl_data WHERE invoice_num=".$_POST["x_invoice_num"]);
while ($row = mysql_fetch_array($result)) {
        $student_id = $row[student_id];
}
 
$sendTo   = "me@domain.com";
$subject  = "Merchant Email Receipt";
$headers  = "From: " . "you@domain.com";
$message  = "========= GENERAL INFORMATION =========" . "\r\n\r\n";
$message .= "Merchant: MY INSURANCE COMPANY" . "\r\n";
$message .= "Date/Time: " . date("m/d/Y") . ", " . date("h:i A") . "\r\n\r\n";
$message .= "========= ORDER INFORMATION =========" . "\r\n\r\n";
$message .= "Invoice: " . $_POST["x_invoice_num"] . "\r\n";
$message .= "Student ID: " . $student_id;
 
mail($sendTo, $subject, $message, $headers);
 
?>

Select allOpen in new window

There are other explanations for this behaviour. You certainly don't need to wait any time whatsoever after an INSERT before running a SELECT. You can prove this to yourself by pasting the SELECT code into the script that does the INSERT, on the very next line. I think the most likely explanation is that there is a problem with your db.inc.php file or MySQL configuration. It should be reusing the previous connection to the database.

If you're happy with the code, then the problem is solved but that doesn't mean that your conclusion is correct. This (working) code proves that you don't need to wait:

<?php
 
$conn = mysql_connect("localhost", "username", "password");
 
if (!$conn) {
    echo "Unable to connect to DB: " . mysql_error();
    exit;
}
  
if (!mysql_select_db("mydbname")) {
    echo "Unable to select mydbname: " . mysql_error();
    exit;
}
 
$sql = "INSERT INTO table_name (col1, col2) VALUES ('blah', 'blah2')";
 
$result = mysql_query($sql);
 
$sql = "SELECT *
        FROM   table_name
        WHERE  col1 = 'blah'";
 
$result = mysql_query($sql);
 
if (!$result) {
    echo "Could not successfully run query ($sql) from DB: " . mysql_error();
    exit;
}
 
if (mysql_num_rows($result) == 0) {
    echo "No rows found, nothing to print so am exiting";
    exit;
}
 
while ($row = mysql_fetch_assoc($result)) {
    echo $row["col2"]; //  echoes blah2
}
 
mysql_free_result($result);
 
?>

Select allOpen in new window

dazweeja, thanks for taking the time to explain this. But I want to make sure you understand the circumstances, because what you're suggesting may not be applicable.

There are 2 entirely separate steps taking place. First, my Web form is submitted, and one php script sends its values to 2 places: (1) my database, and (2) the Authorize.Net payment gateway. So now some values are saved to the database.

Then the Authorize.Net payment gateway sends some other values to my 2nd php script, the one I've been working on and discussing here. This script now queries the database for the same values that the 1st script has written (or is still writing?) to the database, after which it sends out an email with posted values from both the gateway and the database.

I'm new to programming, so my assumptions may be way off. But here's my concern regarding the timing of all of this: I have no way of knowing that the 1st php script has written its values to the database before the 2nd php script queries it for the same values.

To put it in your terms, which are new to me but ones which I obviously need to learn, one script is doing the INSERT, while another is running the SELECT. Therefore, even though my 2nd script is 3rd in a sequence of URLS from which data is being relayed, I have no way of knowing that the INSERT has taken place before the SELECT is executed.

Now that I've explained this, do you still believe my conclusion is incorrect?

Thanks again.

That makes sense but I'm not clear on why the php page that handles the form submit doesn't write to the database and then call the Authorize.Net payment gateway. Are you saying it's the other way around?

Putting in an arbitrary value like 10 seconds seems like a fragile solution.

The php page that handles the form submit writes to the database and sends values to the payment gateway. The payment gateway then sends new values to a URL of my choosing, so I've chosen to send the new values to my 2nd page of php, the one that this question has been addressing.

Does that answer your question? Are you suggesting there may be a better way?

That sounds like a reasonable approach to me.

My point though is that generally speaking - from the developer's perspective - PHP is a single-threaded programming environment and one statement must be completed before the next begins. So if you call the mysql_query function, for example, your PHP script will wait for the completion of this function before it will execute the next statement in your code. If you use a mysql_query statement to perform a SQL INSERT, PHP will wait to receive either a TRUE or FALSE depending on whether the INSERT has been successful or not. This will happen regardless of whether you store the return value in a variable or not. This allows the programmer to take action in case the INSERT fails. If this wasn't the case code like that below (from php.net) would be useless because there would be no guarantee that the insert had been completed so there would be no way to be sure that you could get the insert ID:

mysql_query("INSERT INTO mytable (product) values ('kossu')");
printf("Last inserted record has id %d\n", mysql_insert_id());

This was why I wanted to clarify the execution of your code. As I understand it, the code we are looking at here can only run when the Authorize.Net payment gateway has itself run, and the values will only be sent to the Authorize.Net payment gateway after the code that performs the query that inserts the values in the database has been executed. So we can be confident that the INSERT query has completed because we know it has already completed by the very next line in the code following the query execution. But this doesn't have to be guesswork - it's so easily testable by temporarily running a SELECT query in the (1st?) PHP script directly after the INSERT query and before sending the values to the Authorize.Net payment gateway. That way you can verify for yourself that the INSERT statement must have completed before the values have been sent to the Authorize.Net payment gateway.

Does this make sense or have I misunderstood the sequence of execution in your code?

By the way, executing a MySQL INSERT on a local server query is extremely quick - a number of milliseconds - and far quicker than initiating a network connection with a remote server like a payment gateway.

Yes, that does make sense, and your understanding of the execution sequence in my code is correct.

And what you've written is extremely illuminating. Thanks so much for taking the time to explain! I'm grateful.

Glad to help. We still don't know why you need to call sleep for it to work correctly but it's working now so that's the main thing.