Mysql SELECT WHERE $VAR IN $VAR,$VAR,$VAR

Question

Can anybody see how i can use a String instead of Numeral for this Statement,

Im trying to pull all rows with $delivery when $delivery="ALL" but i can only pull 1 single Variable, if i want to pull an array, i cannot , because i get Array conversion num to string,

$delivery_con = [];
if ($delivery==="collection") { $delivery_con[]="no";}
if ($delivery==="delivery") {$delivery_con[]="yes";}
if ($delivery==="local") {$delivery_con[]="yes local";}

if ($delivery==="either") {$delivery_con=["no","yes","yes local"];}


$query="SELECT * 
        FROM testdata 
        WHERE title LIKE ? 
        AND location LIKE ? 
        AND postcode LIKE ? 
        AND price >=? 
        AND price <=? 
        AND cond=? 
        AND catagory LIKE ? 
        AND delivery IN ? 
        ORDER BY $order $dir";

$stat=$db->prepare($query);

$stat->execute(array("%$searchfor%",
                    "%$location%",
                    "%$postcode%",
                    "$pricefrom",
                    "$priceto",
                    "$cond",
                    "%$catagory%",
                    "$delivery_con"));

So my question is, how can a get around this for the select function to work with $variables,

Im really stuck. If anybody can help

Thank you.

Rather than using the placeholder `?` have you tried putting the variable directly into the statement like so: `{$delivery_con}` ? Also why does `$delivery_con` need to be an array? — Liam G, Jun 05 '18 at 11:10
You have to write a small piece of code that transforms your array into a string with proper format for the `IN` operator in your select. Look at that syntax requirements, then build the string from a loop on the array. Like @MCMXCII pointed out below. — Nic3500, Jun 05 '18 at 11:15
This might be of assistance: https://stackoverflow.com/questions/1586587/pdo-binding-values-for-mysql-in-statement. — MCMXCII, Jun 05 '18 at 11:15
Needs to be an array because in the database, i have YES, NO , YES LOCAL, and i need all rows with either of them to be listed if the user should choose to view all of them... But the statment will only pull the chosen variable and not all 3. i have to make a seperate query to get it working is $delivery="either" / query2 - But iv got other forms with the same sort of issue to sort too, so i need a statment that will pull all rows if either selected — MelissaHicking, Jun 05 '18 at 11:22
`"$delivery_con"` the quotes makes the array a string, remove those — Masivuye Cokile, Jun 05 '18 at 11:25
Adobe Cold Fusion is the only app framework I've heard of that offers native binding of arrays to `IN()` clauses. — O. Jones, Jun 05 '18 at 11:46

score 0 · Answer 1 · answered Jun 05 '18 at 11:44

0

When working with IN() SQL operator, you need create a set of ? manually and put them into the query:

<?php

$delivery_con = [];

if ($delivery === "collection") {
    $delivery_con[] = "no";
}
if ($delivery === "delivery") {
    $delivery_con[] = "yes";
}
if ($delivery === "local") {
    $delivery_con[] = "yes local";
}

if ($delivery==="either") {$delivery_con=["no","yes","yes local"];}


$in = str_repeat('?,', count($delivery_con) - 1) . '?';


$searchfor = "%$searchfor%";
$location  = "%$location%";
$postcode  = "%$postcode%";
$catagory  = "%$catagory%";

$array1 = array($searchfor,$location,$postcode,$pricefrom,$priceto,$cond,$catagory);

$params = array_merge($array1, $delivery_con);

$query = "SELECT * 
        FROM testdata 
        WHERE title LIKE ? 
        AND location LIKE ? 
        AND postcode LIKE ? 
        AND price >=? 
        AND price <=? 
        AND cond=? 
        AND catagory LIKE ? 
        AND delivery IN ($in) 
        ORDER BY $order $dir";

$stat = $db->prepare($query);

$stat->execute([$params]);

answered Jun 05 '18 at 11:44

Masivuye Cokile

4,754
3
19
34

if ($delivery==="either") {$delivery_con="no,yes,yes local";} $query="SELECT * FROM testdata WHERE title LIKE ? AND location LIKE ? AND postcode LIKE ? AND price >=? AND price <=? AND cond=? AND catagory LIKE ? AND delivery IN (?) ORDER BY $order $dir"; $stat=$db->prepare($query); $stat->execute(array("%$searchfor%","%$location%","%$postcode%","$pricefrom","$priceto","$cond","%$catagory%",$delivery_con)); – MelissaHicking Jun 05 '18 at 11:53
But it wont display all rows for ["no,yes,yes local"], just the others.... – MelissaHicking Jun 05 '18 at 11:54
Have you tried to run the query without the placeholders? – Masivuye Cokile Jun 05 '18 at 12:02
no, its a prepared statment , im not sure if i can, il try it. – MelissaHicking Jun 05 '18 at 12:13
@MelissaHicking ofcoz you can for testing puporses – Masivuye Cokile Jun 05 '18 at 12:23
im not sure how i would write it using {$var} instead of (?). – MelissaHicking Jun 05 '18 at 12:24
Simple but a variable instead of ? – Masivuye Cokile Jun 05 '18 at 12:26
sorry, im trying your code now.. – MelissaHicking Jun 05 '18 at 12:28
Notice: Array to string conversion in D:\xampp\htdocs\Vbay\html\Vbaymain.php on line 809 Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in D:\xampp\htdocs\Vbay\html\Vbaymain.php on line 809.. I DID ADD the $priceto & $pricefrom to the list of vars, but still getting the same error – MelissaHicking Jun 05 '18 at 12:32

Mysql SELECT WHERE $VAR IN $VAR,$VAR,$VAR

1 Answers1