Passing Array to SQL query

I have an array like this:

$a[0]=10;
$a[1] = 30;
$a[2]=50;

and another array is:

$b[0]=20;
$b[1] = 40;
$b[2]=60;

I want sql query like this

select * from table where col_name between 10 and 20;
select * from table where col_name between 30 and 40;
select * from table where col_name between 50 and 60;

how can I pass array to this sql query?

you can’t. MySQL can’t work with input arrays. if you want a single query with those conditions, you need to create the appropriate where clause for it. query builders (like DBAL) make that considerably easier than doing it manually.

I’m not clear. Do you want to run the 3 different separate queries, of are you wanting to combine the 3 queries into one single query?

Something like:

$a = [10,30,50];
$b = [20,40,60];
$params = [];
$sql = 'SELECT * FROM table WHERE ';
for($i = 0; $i < count($a); $i++) {
    $params[] = $a[$i];
    $params[] = $b[$i];
    if ($i !== 0) $sql .= 'OR ';
    $sql .= '(colname BETWEEN ? AND ?) ';
}
echo $sql . "\n";
SELECT * FROM table WHERE (colname BETWEEN ? AND ?) OR (colname BETWEEN ? AND ?) OR (colname BETWEEN ? AND ?)

// Prepare then execute
//$stmt = $pdo->prepare($sql);
//$stmt->execute($params);
//$rows = $stmt->fetchAll();


1 Like

Thanx ! After making some changes this is what I was looking for :slight_smile:

$a=[10,30,50];
$b = [20,40,60];
//$params = [];
$sql = 'SELECT * FROM product WHERE ';
for($i = 0; $i < count($a); $i++) {
 $params = $a[$i];
$params2 = $b[$i];
 if ($i !== 0) $sql .= ' OR ';
$sql .= 'product_id BETWEEN '."$params" .' AND '." $params2";
}
echo $sql . "\n";
$executequery = mysqli_query($con,$sql) or die(mysqli_error($con));
if($executequery)
{
echo "Sucess";
}

why are you using this security hole when you have already been shown how to do it properly using prepare?

1 Like

I don’t know .But if i use mysql_query then it throws warning mysql deprecated.That’s why using mysqli.

I don’t think he meant that using mysqli was a security hole, just that not using prepared statements is less secure than using them. Although if all the incoming numbers are hard-coded as in your example, it’s hard to see where a security issue would occur. The point is that if there’s any chance your query will use parameters that can be supplied externally (for example via $_GET or $_POST) then prepared statements help to secure things that just appending text does not.

But going backwards to old-style mysql_ calls would be even worse.

2 Likes

It wouldn’t in that particular instance - I was just wondering why when @ahundiak had already given the solution using prepare that @neha_k went to all the trouble of converting it back to use query.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.