PHP mysql select query where (wildcard)

Question

I'm trying to use the following MySQL query, but there is obviously something wrong. I'm trying to make page.php or page.php?q= return the following query:

if (!isset($_GET['q'])) { $where = "WHERE column1 LIKE %"; }
else { $where = "WHERE column1 LIKE ".$_GET['q']; }

$query = "SELECT column1, column2 FROM table '$where' GROUP BY column1";

So if there is no GET, then there is no WHERE in the MySQL query. I the GET is set to something, then there is a WHERE with that GET value.

Currently I'm getting the following error:

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 ''WHERE column1LIKE ' GROUP BY column1' at line 1

Your code is wide-open to SQL Injection attacks. Please use parameterised queries. — Dai
– Dai, Commented Dec 8, 2012 at 1:04
Thanks Dai. At the moment this is just a temporary page. I´t won´t be up for long and is only on an intranet. — Skuli Axelson
– Skuli Axelson, Commented Dec 8, 2012 at 1:11

Wing Lian · Accepted Answer · 2012-12-08 01:06:11Z

2

You need to use some sort of escaping, but that's an exercise for another day. If you simply want to get it working, remove the single quotes around the where variable.

$query = "SELECT column1, column2 FROM table $where GROUP BY column1";

answered Dec 8, 2012 at 1:06

Wing Lian

2,41818 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

webaware · Accepted Answer · 2012-12-08 02:06:16Z

1

For a general solution using PDO, try this snippet (where $db is a PDO connection object).

$params = array();

$sql = 'SELECT column1, column2 FROM table where 1 ';

if (!empty($_GET['q'])) {
    $sql .= " column1 like ?";
    $params[] = '%' . $_GET['q'] . '%';
}

if (!empty($_GET['r'])) {
    $sql .= " column2 like ?";
    $params[] = '%' . $_GET['r'] . '%';
}

$sql .= ' GROUP BY column1 ORDER BY column1';

$query = $db->prepare($sql);
$i = 1;
foreach ($params as $param) {
    $query->bindValue($i, $param);
    $i++;
}
$query->execute();

answered Dec 8, 2012 at 2:06

webaware

2,8193 gold badges34 silver badges39 bronze badges

1 Comment

Skuli Axelson Over a year ago

Thanks, I updaed my script for PDO and the above helped with my issue.

MC Emperor · Accepted Answer · 2012-12-08 01:23:10Z

1

You need to put the search string in the WHERE clause between single quotes, like this:

$where = "";
// if there the get q is set we add the were clause
if (!isset($_GET['q'])) {
    $where = "WHERE column1 LIKE %";
    // ^ This WHERE clause is useless, since it matches all strings.
    // Omitting this WHERE clause has the same effect.
}
else { $where = "WHERE column1 LIKE ".$_GET['q']; }

$query = "SELECT column1, column2 FROM table ".$where." GROUP BY column1";

Notice that your script is highly vulnerable agains attacks. Read about SQL-injections.

edited Dec 8, 2012 at 1:23

answered Dec 8, 2012 at 1:10

MC Emperor

23.3k16 gold badges90 silver badges139 bronze badges

Comments

Karim Magdy Mansour · Accepted Answer · 2012-12-08 01:08:09Z

0

I think you could simply do that: btw.. you do not need the other part "" the like % "" you can simply omit the where clause all together and it will do the same effect... here is a replica of what you just posted:

$where = "";
//if there the get q is set we add the where clause
if(isset($_GET['q'])) { 
   $where = "WHERE column1 LIKE '".$_GET['q']."'"; 
}

$query = "SELECT column1, column2 FROM table ".$where." GROUP BY column1";

answered Dec 8, 2012 at 1:08

Karim Magdy Mansour

3162 silver badges8 bronze badges

Collectives™ on Stack Overflow

PHP mysql select query where (wildcard)

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related