Parameterised IN Clause in prepared statement using MySql,PHP and ADODB

Question

I am writing some SQL and using AdoDb to connect to my database and run the queries and so on. I am using parametrized queries and have run into a snag.

Is their a way to pass an array of values to an in_clause in AdoDb/MySql for parametrization.

My problem is that if I pass a prepared string as the parameter i.e. 'test','test2','test3' it does not work as the library or database auto escapes it and adds external quotes at the start and end so all the internal quotes are then auto escaped thus the query returns nothing as it looks for '\'test\',\'test2\',\'test3\'' as opposed to what I fed it.

UPDATED WITH ANOTHER POSSIBLE METHOD TO ACCOMPLISH THIS

<?php
$in_clause = implode(",", $first_names);

$query = "
SELECT    
    mytable_id_pk
FROM 
    mytable
WHERE
FIND_IN_SET(mytable_fname," . $DB->Param('first_names') . ")"

$stmt = $DB->Prepare($query);

$result = $DB->Execute($stmt,array($in_clause));
?>

shadyyx · Accepted Answer · 2012-05-22 09:04:40Z

7

I would do it this way (as I was googling for a while and google came up with nothing useful):

$count = count($first_names);
$in_params = trim(str_repeat('?, ', $count), ', ');

$query = "
SELECT    
    mytable_id_pk
FROM 
    mytable
WHERE
    mytable_fname IN ({$in_params});";

$stmt = $DB->Prepare($query);
$result = $DB->Execute($stmt, $first_names);

This should do it...

answered May 22, 2012 at 9:04

shadyyx

16.1k7 gold badges62 silver badges98 bronze badges

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

1 Comment

jiraiya Over a year ago

Thanks for this, I had been thinking of something like this myself. I also came upon another solution that worked nice I will update the question above with it. Thanks again I appreciate it.

András Váczi · Accepted Answer · 2012-05-22 09:04:45Z

-1

First a few tips:

Please read carefully the AdoDB documentation on prepared statements.
Never include ; in SQL query strings.

You can try something like this:

$question_marks = substr(str_repeat('?,', count($first_names)), 0, -1);

$query = "SELECT mytable_id_pk FROM mytable WHERE mytable_fname IN (" . $question_marks . ")";
$stmt = $DB->Prepare($query);
$result = $DB->Execute($stmt,$first_names);

WARNING: I haven't tested this (not having a mySQL installation here).

answered May 22, 2012 at 9:04

András Váczi

3,02225 silver badges30 bronze badges

3 Comments

shadyyx Over a year ago

Why not to include semicolon? I'm curious about the reason as I am using semicolons at the end of each query to mysql for a years... I understand that when moving to other DBMS I would have to repair every query, but is there any other wise reason?

András Váczi Over a year ago

@shadyyx Well, with mysqli_multi_query you certainly can include semicolons. On the other hand, mysql_query doc says explicitly: 'The query string should not end with a semicolon.' The exact reason of this prohibition is unclear (not only for me, as I have seen from recent googleing). However, it is completely useless and affects readability negatively (OK, it's quite subjective...)

jiraiya Over a year ago

thanks for the input. I had indeed read the adodb documentation, I had actually scoured it for info on this point and there was none so I was asking the question to see if there were any other innovative ways.

Collectives™ on Stack Overflow

Parameterised IN Clause in prepared statement using MySql,PHP and ADODB

2 Answers 2

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related