How to insert a PHP variable into an SQL query

Question

I have an SQL query

qry1 = 
"SELECT DISTINCT (forename + ' ' + surname) AS fullname
FROM users
ORDER BY fullname ASC";

This gets forename and surname from a table called users and concatenates them together, putting a space in the middle, and puts in ascending order.

I then put this into an array and loop through it to use in a select drop-down list.

This works, however, what I now want to do is compare the fullname with a column called username in another table called users.

I'm struggling with how to write the query though. So far I have...

$qry2 
"SELECT username 
FROM users 
WHERE (forename + ' ' + surname) AS fullname 
=" . $_POST['Visiting'];

Any advice on to what I am doing wrong?

You want [Concat](https://www.w3schools.com/sql/func_mysql_concat.asp) to join two or more strings in mysql — Ramesh Kithsiri HettiArachchi, Oct 01 '18 at 11:07
Since `(forename + ' ' + surname) AS fullname` is a `string`, you'd need quotes: `='" . $_POST['Visiting'] . "'";`. — brombeer, Oct 01 '18 at 11:08
You should try to sort out your problems: does the query work when you put some static data in there? Does your MySQL connection show some error message (which you should add to your question by editing it)? — Nico Haase, Oct 01 '18 at 11:08
share both tables data and what you want as a output after comparing — Zaynul Abadin Tuhin, Oct 01 '18 at 11:08
Read about SQL Injection and [how to prevent it](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Carlos Campderrós, Oct 01 '18 at 11:13
Are any of the users in your table perchance called 'Bobby Tables'? — Darren H, Oct 01 '18 at 16:59

score 1 · Answer 1 · answered Oct 01 '18 at 11:34

Rather CONCAT the two columns together. Also remember to escape any variables before adding them to your query.

$qry2 =
"SELECT username AS fullname
FROM users 
WHERE CONCAT(forename, ' ', surname)
='" . mysqli_real_escape_string($connection, $_POST['Visiting']) . "'";

Where $connection is your current db connection

Kart Av1k · Answer 2 · 2018-10-01T11:17:26.153

I'm not sure that the use of the declared word 'AS' after 'WHERE' is correct in principle.

if you use MySQL, query should look like this:

SELECT [columns]
FROM [tables] [AS declareTableName]
WHERE [condition]
GROUP BY [declares|columns]
SORT BY [declares|columns]

But, i think your problem not in the query. Concatenating names in the query is incorrect. You must separate string with names in Back-end and than use it in query:

$names = explode(' ', $_POST['Visiting']);

Nico Haase · Answer 3 · 2018-10-01T13:04:05.400

0

This might work, assuming you use PDO:

$qry2 = "SELECT username FROM users
      WHERE CONCAT(forename, ' ', surname) = '" . $conn->quote($_POST['Visiting']) . "'";

...but you should have a look at the possible vulnerabilities through SQL injections.

Without knowing which library you use for connecting to the MySQL database, it's impossible to give proper advise about which method you should use for escaping the user's input. quote is the PDO method for escaping, real-escape-string is the equivalent for MySQLi

edited Oct 01 '18 at 13:04

answered Oct 01 '18 at 11:11

Nico Haase

11,420
35
43
69

2

This got my downvote because of the danger of SQL-injection. It will be copied, and used, and the drama will continue. – KIKO Software Oct 01 '18 at 11:11
I understand, it's a pity ginomay89 doesn't react, so you could correct your answer. – KIKO Software Oct 01 '18 at 12:17
@KIKOSoftware better that way? – Nico Haase Oct 01 '18 at 12:48
1

Yes, better, but now ginomay89 probably doesn't know what to do with that `$conn->quote()` method. – KIKO Software Oct 01 '18 at 13:00

score 0 · Answer 4 · answered Oct 25 '18 at 11:33

You should really refer to using PDO. When using PDO you can bind parameters to specified parts of your query. PDO also has built-in SQL-injection prevention, which is a great security measure that you won't have to deal with yourself. I hope this answers your question. See my example below.

Example:

// Create a new PDO object holding the connection details
$pdo = new PDO('mysql:host=localhost;dbname=test', $user, $pass);

// Create a SQL query
$query = "SELECT username FROM users WHERE (forename + ' ' + surname) AS fullname = :visiting;";

// Prepare a PDO Statement with the query
$sth = $pdo->prepare($query);

// Create parameters to pass to the statement
$params = [
    ':visiting' => $_POST['Visiting']
]

// Execute the statement and pass the parameters
$sth->execute($params);

// Return all results
$results = $sth->fetchAll(PDO::FETCH_ASSOC);

If you have any other questions about PDO, please refer to the following:

Official PDO documentation:
http://php.net/manual/en/book.pdo.php

Documentation on how to bind variables:
http://php.net/manual/en/pdostatement.bindparam.php

score -2 · Answer 5 · answered Oct 01 '18 at 11:16

-2

You can use this construction (without "AS fullname" and with apostrophes around variable):

$qry2 "SELECT username FROM users WHERE (forename + ' ' + surname) = '" . $_POST['Visiting'] . "'";

But for better security (SQL injection) You should use the escaping of variable. For example this construction, if You use MySQL database:

$qry2 "SELECT username FROM users WHERE (forename + ' ' + surname) = '" . mysql_real_escape_string($_POST['Visiting']) . "'";

answered Oct 01 '18 at 11:16

Jan Čejka

7
3

One should no longer use deprecated `mysql_*` methods – Nico Haase Oct 01 '18 at 11:36
yes, it is only example. sql injecting protection is important. functions/classes depends on used sql engine (mysql, postgresql, ...). – Jan Čejka Oct 02 '18 at 12:05

How to insert a PHP variable into an SQL query

5 Answers5