1

How do I add wildcards % to the "specifications.sn" ?

SELECT `products2`.`type`, 
       `products2`.`sn`, 
       `products2`.`lap`, 
       `specifications`.`year` 
FROM `products2`
INNER JOIN `specifications` 
      ON `products2`.`sn` LIKE `specifications`.`sn`
WHERE `products2`.`id` = ? LIMIT 1

EDIT:

Added current query

  • Removed backticks

  • Added your CONCAT 

    SELECT products2.type, 
products2.sn, 
products2.lap, 
specifications.year 
FROM products2
INNER JOIN specifications 
ON products2.sn LIKE CONCAT('%', specifications.sn, '%')
WHERE products2.id = ? LIMIT 1

Alternative query - Same error like code above

  • Removed backticks
  • Removed INNER JOIN

  • Added LIKE in WHERE statement

    SELECT products2.type, 
products2.sn, 
products2.lap, 
specifications.year
FROM products2, 
specifications
WHERE products2.id = ? AND products2.sn LIKE '%' + specifications.sn + '%' LIMIT 1

EDIT2

PHP code

if ($stmt = $mysqli->prepare('SELECT products2.type, 
products2.sn, 
products2.lap, 
specifications.year 
FROM products2
INNER JOIN specifications 
ON products2.sn LIKE CONCAT('%', specifications.sn, '%')
WHERE products2.id = ? LIMIT 1')) { 

$stmt->bind_param('i', $id);
$id = $_GET['id'];
$stmt->execute();
$stmt->bind_result($type, $sn, $lap, $year);
$stmt->fetch();

echo $type . '<br/>';
echo $sn . '<br/>';
echo $lap . '<br/>';
echo $year . '<br/>';

$stmt->close();

} else {
echo $mysqli->error;
}
Aldwoni
  • 1,168
  • 10
  • 24
user1273409
  • 39
  • 3
  • 9
  • I think it's a problem of how the query is called. If you paste it in phpmyadmin it will work – Sebas Oct 14 '12 at 19:55
  • You're right. How can I fix this problem? Is it because i'm using prepared statement? – user1273409 Oct 14 '12 at 20:08
  • Could you post the php part as well please? – Sebas Oct 14 '12 at 20:20
  • you have to escape the quotes inside the query: \' \' – Sebas Oct 14 '12 at 20:36
  • also, you are binding the 'i' parameter, while in the query you are using '?' is that normal? On a second note, please note that you are getting the value of $id AFTER binding it. I would retrieve it before hand. – Sebas Oct 14 '12 at 20:38
  • Like this: LIKE \'%\' + specifications.sn + \'%\' – user1273409 Oct 14 '12 at 20:50
  • Yes, the parameter with 'i' and ? is normal. http://php.net/manual/en/mysqli.prepare.php I will retrieve the $id before then. – user1273409 Oct 14 '12 at 20:52
  • yes, should be like this. If you're not sure of the query you can echo the compiled query to the browser – Sebas Oct 14 '12 at 21:45
  • Now I get 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 '+ specifications.sn + '%' LIMIT 1' at line 2 – user1273409 Oct 15 '12 at 06:57
  • It works! Added the \'%\' as you said. But I used the "current query" / Code #2 in my question above. Thanks – user1273409 Oct 15 '12 at 07:54

1 Answers1

3
SELECT `products2`.`type`,
       `products2`.`sn`,
        `products2`.`lap`, `specifications`.`year`
FROM `products2`
INNER JOIN `specifications`
      ON `products2`.`sn` LIKE 
         CONCAT(`specifications`.`sn`, '%')
WHERE `products2`.`id` = ? LIMIT 1

Please note the following:

  • this is going to be highly ineffective regarding to join performances
  • CONCAT(NULL, 'ADADSA') returns NULL, therefore you have to take care of this special use case if you think specifications.sn might be NULL.

Cheers.

Sebas
  • 21,192
  • 9
  • 55
  • 109