Php – theSQL bind_param with IN(?)

mysqliPHP

Using bind_param on all my queries, I now want to use an IN(?) where the number of elements in the list can vary.

The SQLout function I'm using here basically does a $sql_db->prepare, ->bind_param, ->execute(), ->store_result(), ->bind_result

// the code below does not work as the query only matches on element 'a':
$locations = ('a','b','c','d','e');

SQLout ("SELECT Name FROM Users WHERE Locations IN (?)",
    array('s', $locations), array(&$usrName));


// the code below does work as a brute-force method,
// but is not a viable solution as I can't anticipate the number of elements in $locations going forward:

SQLout ("SELECT Name FROM Users WHERE Locations IN (?,?,?,?,?)",
    array('sssss', $locations[0],$locations[1],$locations[2],$locations[3],$locations[4]), array(&$usrName));

Has anyone come up with a more elegant solution to this?

Best Answer

This is one place placeholders fall on their faces. Minus the auto-escaping, they're almost literally just a string replacement operation internally, meaning that if you have WHERE Locations IN (?), and pass in 1,2,3,4, you'll get the equivalent of

WHERE Locations IN ('1,2,3,4')  // note, it's a string, not individual comma-separated integers

logically equivalent to

WHERE Locations = '1,2,3,4' // again, just a string

instead of the intended

WHERE Locations = 1 OR Locations = 2 OR Locations = 3 OR Locations = 4

The only practical solution is to build your own list of comma-separated placeholders (?), e.g:

$placeholders = implode(',', array_fill(0, count($values), '?'));
$sql = "SELECT Name FROM Users WHERE Locations IN ($placeholders)";

and then bind your parameters are usual.

Related Solutions

Php – How to do ‘insert if not exists’ in MySQL

Use INSERT IGNORE INTO table.

There's also INSERT … ON DUPLICATE KEY UPDATE syntax, and you can find explanations in 13.2.6.2 INSERT ... ON DUPLICATE KEY UPDATE Statement.

Post from bogdan.org.ua according to Google's webcache:

18th October 2007

To start: as of the latest MySQL, syntax presented in the title is not possible. But there are several very easy ways to accomplish what is expected using existing functionality.

There are 3 possible solutions: using INSERT IGNORE, REPLACE, or INSERT … ON DUPLICATE KEY UPDATE.

Imagine we have a table:
CREATE TABLE `transcripts` (
`ensembl_transcript_id` varchar(20) NOT NULL,
`transcript_chrom_start` int(10) unsigned NOT NULL,
`transcript_chrom_end` int(10) unsigned NOT NULL,
PRIMARY KEY (`ensembl_transcript_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Now imagine that we have an automatic pipeline importing transcripts meta-data from Ensembl, and that due to various reasons the pipeline might be broken at any step of execution. Thus, we need to ensure two things:

repeated executions of the pipeline will not destroy our > database

repeated executions will not die due to ‘duplicate > primary key’ errors.

Method 1: using REPLACE

It’s very simple:
REPLACE INTO `transcripts`
SET `ensembl_transcript_id` = 'ENSORGT00000000001',
`transcript_chrom_start` = 12345,
`transcript_chrom_end` = 12678;
If the record exists, it will be overwritten; if it does not yet exist, it will be created. However, using this method isn’t efficient for our case: we do not need to overwrite existing records, it’s fine just to skip them.

Method 2: using INSERT IGNORE Also very simple:
INSERT IGNORE INTO `transcripts`
SET `ensembl_transcript_id` = 'ENSORGT00000000001',
`transcript_chrom_start` = 12345,
`transcript_chrom_end` = 12678;
Here, if the ‘ensembl_transcript_id’ is already present in the database, it will be silently skipped (ignored). (To be more precise, here’s a quote from MySQL reference manual: “If you use the IGNORE keyword, errors that occur while executing the INSERT statement are treated as warnings instead. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted.”.) If the record doesn’t yet exist, it will be created.

This second method has several potential weaknesses, including non-abortion of the query in case any other problem occurs (see the manual). Thus it should be used if previously tested without the IGNORE keyword.

Method 3: using INSERT … ON DUPLICATE KEY UPDATE:

Third option is to use INSERT … ON DUPLICATE KEY UPDATE syntax, and in the UPDATE part just do nothing do some meaningless (empty) operation, like calculating 0+0 (Geoffray suggests doing the id=id assignment for the MySQL optimization engine to ignore this operation). Advantage of this method is that it only ignores duplicate key events, and still aborts on other errors.

As a final notice: this post was inspired by Xaprb. I’d also advise to consult his other post on writing flexible SQL queries.

Php – Reference — What does this symbol mean in PHP

Incrementing / Decrementing Operators

++ increment operator

-- decrement operator

Example    Name              Effect
---------------------------------------------------------------------
++$a       Pre-increment     Increments $a by one, then returns $a.
$a++       Post-increment    Returns $a, then increments $a by one.
--$a       Pre-decrement     Decrements $a by one, then returns $a.
$a--       Post-decrement    Returns $a, then decrements $a by one.

These can go before or after the variable.

If put before the variable, the increment/decrement operation is done to the variable first then the result is returned. If put after the variable, the variable is first returned, then the increment/decrement operation is done.

For example:

$apples = 10;
for ($i = 0; $i < 10; ++$i) {
    echo 'I have ' . $apples-- . " apples. I just ate one.\n";
}

Live example

In the case above ++$i is used, since it is faster. $i++ would have the same results.

Pre-increment is a little bit faster because it really increments the variable and after that 'returns' the result. Post-increment creates a special variable, copies there the value of the first variable and only after the first variable is used, replaces its value with second's.

However, you must use $apples--, since first, you want to display the current number of apples, and then you want to subtract one from it.

You can also increment letters in PHP:

$i = "a";
while ($i < "c") {
    echo $i++;
}

Once z is reached aa is next, and so on.

Note that character variables can be incremented but not decremented and even so only plain ASCII characters (a-z and A-Z) are supported.

Stack Overflow Posts:

Understanding Incrementing

Best Answer

Related Solutions

Php – How to do ‘insert if not exists’ in MySQL

Php – Reference — What does this symbol mean in PHP

Related Topic