How to insert a mixed string using mysqli_bind_param prepared statements

Question

I've been searching for an answer for a while now, but can't seem to find anything.

I'm looking for a way to use mysqli_bind_param to insert a row into a table, where the param (?) is part of a larger string. This is my code:

$query = "INSERT INTO CDBusers_activity (order_ID, activity_text_desc) VALUES (?, 'Price edited from ? to ?')";
$stmt = mysqli_prepare($DB_conn, $query);
mysqli_bind_param($stmt, "sss", $orderID, $product_editPrice_was, $product_editPrice_now);
mysqli_stmt_execute($stmt);

I'm looking for a way to add the $product_editPrice_was and $product_editPrice_now into the row.

I could create a string:

$text = "Price edited from $product_editPrice_was to $product_editPrice_now"

and then bind that, but I am interested if there is a simpler way? For example:

$query = "INSERT INTO CDBusers_activity (order_ID, activity_text_desc) VALUES (?, 'Price edited from ' ? ' to ' ?)";

Create two fields `price_before`, `price_after` and insert into them. — u_mulder, Sep 14 '16 at 10:07
Thanks @u_mulder but the field activity_text_desc is used for other activity (for example "User deleted xyz", which is taken care of in other code). Im looking for an overall solution to binding more than one variable into a field at once. — SW_Cali, Sep 14 '16 at 10:19
Then the answer is __NO__, you can't use palceholders as a part of inserted data. — u_mulder, Sep 14 '16 at 10:19
I am not sure what could be simpler than just writing your values in a string like you did. — Your Common Sense, Sep 14 '16 at 10:35

score 1 · Answer 1 · answered Sep 14 '16 at 10:23

Asking your primary question - no, you can't replace part of inserted value with a placeholder.

The solution is:

$query = "INSERT INTO CDBusers_activity (order_ID, activity_text_desc) VALUES (?, ?)";
$stmt = mysqli_prepare($DB_conn, $query);
$text = "Price edited from $product_editPrice_was to $product_editPrice_now";
mysqli_bind_param($stmt, "ss", $orderID, $text);
mysqli_stmt_execute($stmt);

Another solution is to create to different fields like price_before, price_after.

But if you can't do it, you can try using mysql CONCAT() and placeholders for example, but i'm not sure if it works:

INSERT INTO CDBusers_activity (order_ID, activity_text_desc) VALUES (?, CONCAT('Price edited from ', ?, ' to ', ?)

score 1 · Answer 2 · answered Sep 14 '16 at 10:40

but I am interested if there is a simpler way?

This is an interesting question. In a way.

$text = "Price edited from $product_editPrice_was to $product_editPrice_now";

is apparently the easiest way to create a text string. While this text string can be bound to a query the usual way with a placeholder. Besides, you will have your SQL and data separated from each other, which will help you to organize your code better.

So I think that there are things you should not make simpler. Because such an attempt will likely make things more complex.

How to insert a mixed string using mysqli_bind_param prepared statements

2 Answers2