concat php value with entire quotes in sql query

Question

How to concat this php value with entire quotes in sql query, so that it saves as properly in phpmyadmin database?

 $db = new mysqli('localhost','root','','geo'); 

// Each value is a placeholder

$sql = "UPDATE file_contents SET Origin_URL = CONCAT('https://www.google.com/maps/dir/', ?, ',' ?, '/', ?, ',', ?) WHERE Sl = 1 LIMIT 6";

$stmt = $db ->prepare($sql);

// First parameter should correspond to number and types of your arguments
// You have 5, first four are strings, fifth is a number, so "ssssd"

$stmt->bind_param($OriginLatId,$OriginLongId,$DestinationLatId,$DestinationLongId);

$stmt->execute();

Please help me get the correct sql query to insert this url in my database successfully, this is the table, and I have made the Origin_URL column into a varchar column. The data goes into this column.

This is table, and I have made the Origin_URL column into a varchar column. The data goes into this column.

Use single quotes for the SQL string encapsulation. Also this is `sql-server` or `mysql`? Look at the highlighting of `CONCAT("` and you should see the issue. Additionally `+` is used for math in PHP, to concatenate use `.`. — chris85, Dec 10 '16 at 16:08
I am using phpmyadmin database here, to store this value in Origin_URL column — Unnati, Dec 10 '16 at 16:10
the output received after echoing $val1 in php, is required in sql query form.Kindly help. — Unnati, Dec 10 '16 at 16:13
yes, using dot(.) does solve my problem in case of php, but I want to store this $val1 in a table in phpmyadmin — Unnati, Dec 10 '16 at 16:16
You have a `mysqli` answer below give it a try, or show us your code usage, and what db you actually are using. — chris85, Dec 10 '16 at 16:22
Kindly check, I have edited the code and added the screenshot of phpmyadmin. I m now getting error as Fatal error: Uncaught Error: Call to a member function bind_param() on boolean in C:\xampp\htdocs\GeoProj\execute.php:264 Stack trace: #0 {main} thrown in C:\xampp\htdocs\GeoProj\execute.php on line 264 — Unnati, Dec 10 '16 at 16:42
I am confused as what has to be used, I basically want the formed url to go in the column Origin_URL. Yes because the fifth is included in query itself . i.e. Sl = 1 was the fifth — Unnati, Dec 10 '16 at 16:57

score 0 · Answer 1 · answered Dec 10 '16 at 16:12

Never concat SQL queries in PHP or any other language that supports bind statements (which is almost any modern language there is).

To use bind statements you need first to prepare it:

// Each value is a placeholder
$sql = "UPDATE file_contents SET Origin_URL = CONCAT('https://www.google.com/maps/dir/', ?, ',' ?, '/', ?, ',', ?) WHERE Sl = ?";

$stmt = $db->prepare($sql);

// First parameter should correspond to number and types of your arguments
// You have 5, first four are strings, fifth is a number, so "ssssd"
$stmt->bind_param('ssssd', $OriginLatId, $OriginLongId, $DestinationLatId, $DestinationLongId, $id);
$stmt->execute();

Please check I have edited and executed as per your code, I am getting error as Fatal error: Uncaught Error: Call to a member function bind_param() on boolean in C:\xampp\htdocs\GeoProj\execute.php:264 Stack trace: #0 {main} thrown in C:\xampp\htdocs\GeoProj\execute.php on line 264 — Unnati, Dec 10 '16 at 16:36

score 0 · Answer 2 · answered Dec 10 '16 at 16:42

0

I would suggest you to use PDO

    <?php
$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
?>

After getting connection you can prepare you statement

    <?php
$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':value', $value);

// insert one row
$name = 'one';
$value = 1;
$stmt->execute();

// insert another row with different values
$name = 'two';
$value = 2;
$stmt->execute();
?>

Expecting this will help you

answered Dec 10 '16 at 16:42

Humza Malik

343
3
14

This is just a PDO example, why not just post a link to the manual as a comment? – chris85 Dec 10 '16 at 16:55
http://php.net/manual/en/pdo.prepared-statements.php here is link of PDO support php – Humza Malik Dec 10 '16 at 16:56

chris85 · Accepted Answer · 2016-12-10T17:15:00.647

There are a number of issues with your string concatenation, + is for addition, variables in single quotes are strings, not variables, and you seem to be adding quotes in too many instances.

You should be able to build your string with the complex curly braces in double quotes:

$val1 = "https://www.google.com/maps/dir/{$OriginLatId},{$OriginLongId}/{$DestinationLatId},{$DestinationLongId}";

You can read more about this here, http://php.net/manual/en/language.types.string.php.

or by standard concatenation:

$val1 = 'https://www.google.com/maps/dir/' .  $OriginLatId .',' . $OriginLongId . '/' . $DestinationLatId . ',' . $DestinationLongId;

You can read more about this here, http://php.net/manual/en/language.operators.string.php.

Then just write that to the DB. There's no need for the mysql concat function.

$sql = 'UPDATE file_contents 
SET Origin_URL = ? 
WHERE Sl = 1 LIMIT 6';
$stmt = $db->prepare($sql);
$stmt->bind_param('s', $val1);
$stmt->execute();

concat php value with entire quotes in sql query

3 Answers3