USE LIKE with php variable

Question

I have a problem using LIKE with PHP variables. I would like to select, based on a username, what matches the username in the DB. Here is my code:

$dbhost = "localhost";
$dbuser = "root";
$dbpass = "1234";
$dbname = "coffeecorner";
$connection = mysqli_connect($dbhost, $dbuser, $dbpass, $dbname);

$user = $_SESSION['username'];  

$sql  = "select username ";
$sql .= "from add_reservation";
$sql .= "where username like" . $user;
$result = mysqli_query($connection, $sql);

if(!$result)
{
   die("database query fail!" . mysqli_error($connection));
}

Error

database query fail! You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'likeipin' at line 1

Any help would be appreciated!

https://dev.mysql.com/doc/refman/5.7/en/string-comparison-functions.html#operator_like — shmosel, Mar 02 '17 at 01:05
The argument to `LIKE` has to be a string, so it needs quotes around it. But you should learn to use prepared statements with `mysqli_stmt_bind_param` instead of substituting variables into the SQL. — Barmar, Mar 02 '17 at 01:07
Why do you need to use `LIKE`? If you're looking for a specific username, it should be `=`, not `LIKE`. You use `LIKE` when you're trying to match a pattern with wildcards in it. — Barmar, Mar 02 '17 at 01:08
Your actual `LIKE` clause should be similar to this: `where username like '%Mond%'` ... currently, you are missing a space. And please use prepared statements here. — Tim Biegeleisen, Mar 02 '17 at 01:08
Your script is at risk of [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Have a look at what happened to [Little Bobby Tables](http://bobby-tables.com/) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) — RiggsFolly, Mar 02 '17 at 01:10
@Barmar I have change my code to '$sql .= "where username = " . $user;' and it throw an error ** database query fail!You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= ipin' at line 1** — Mohd Ariffin, Mar 02 '17 at 01:19
You're still missing the quotes: `$sql .= "where username = '" . $user "';` — Barmar, Mar 02 '17 at 02:30

score 1 · Accepted Answer · answered Mar 02 '17 at 02:34

You need quotes around the username. Also, if you're using LIKE to match a pattern, you should have wildcards in it.

$sql .= "where username likem '%$user%'";

But it's better to use a parametrized query.

$sql = 'SELECT username
        FROM add_reservation
        WHERE username like ?';
$user_pattern = "%$user%";
$stmt = mysqli_prepare($connection, $sql);
mysqli_stmt_bind_param($stmt, "s", $user_pattern);
$result = mysqli_stmt_execute($stmt);
if (!$result) {
    die("database query fail!" . mysqli_error($connection));
}

kourouma_coder · Answer 2 · 2017-03-02T01:44:50.660

You neeed to add a little a space after like :

$dbhost = "localhost";
$dbuser = "root";
$dbpass = "1234";
$dbname = "coffeecorner";
$connection = mysqli_connect($dbhost, $dbuser, $dbpass, $dbname);

$user = $_SESSION['username'];  

$sql  = "select username ";
$sql .= "from add_reservation";
$sql .= "where username like " . $user;
$result = mysqli_query($connection, $sql);

if(!$result)
{
  die("database query fail!" . mysqli_error($connection));
}

check the error message : database query fail!You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'likeipin' at line 1

the word like is stuck with the username forming a single string likeipin ; it should be like ipin meaning $sql .= "where username like " . $user;

Be carefull on session, session_start should be used before accessing session variable.

You can use this query string : $sql = "SELECT username FROM add_reservation WHERE username LIKE '%". mysql_real_escape_string($user) ."%'" or this one :
$sql = "SELECT username FROM add_reservation WHERE username LIKE '%".$user."%'"
Hope it help.

I have add a space ' $sql .= "where username like " . $user; ' , is throw an error ==> 'database query fail!You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'like ipin' at line 1' — Mohd Ariffin, Mar 02 '17 at 01:29
session_start() has been declared in the beginning of the code — Mohd Ariffin, Mar 02 '17 at 01:33

score 0 · Answer 3 · answered Mar 02 '17 at 02:50

0

after a few hours thinking and trying i have found the solution. this a the new code. We need to input a braces () on it;

if(session_id()=='' || isset($_SESSION['username'])){

$dbhost = "localhost";
$dbuser = "root";
$dbpass = "1234";
$dbname = "coffeecorner";
$connection = mysqli_connect($dbhost, $dbuser, $dbpass, $dbname);

$user = $_SESSION['username'];  

$sql  = "(SELECT * FROM add_reservation WHERE username like '$user')";
$result = mysqli_query($connection, $sql);

if(!$result)
{
   die("database query fail!" . mysqli_error($connection) . mysqli_errno($connection));
}

Hope it helped !

answered Mar 02 '17 at 02:50

Mohd Ariffin

13
4

The parentheses are totally unnecessary. – Barmar Mar 02 '17 at 15:28
I didn't say they're wrong, they're just unnecessary. You only need parentheses around a query when you're using it as a subquery. If it's the main query, they're redundant, and it should work just as well without them. – Barmar Mar 02 '17 at 19:17

USE LIKE with php variable

3 Answers3