I have a large database, I grabbed client id's and im trying to use those id's for the next query. I am trying to only query 1 time, instead of trying to query over 1,000 times.
Here is my query:
$query_inv_packs_for_date_range = "SELECT invid, packid, clientid,
date_range_start, date_range_end, value FROM inv_packs WHERE clientid
IN(implode(',', $in))";
Then I try to bind the parameters by unpacking the array inside the function parameter like so.
if($stmt = mysqli_prepare($connection, $query_inv_packs_for_date_range)){
mysqli_stmt_bind_param($stmt, $types, ...$uniqueIds);
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $invoice_id, $pack_id, $client_id_from_inv_pack_table, $date_range_start, $date_range_end, $value);
while(mysqli_stmt_fetch($stmt)){
$invoice_packs_table_data[$inv_pack_count]['invoiceID'] = $invoice_id;
$invoice_packs_table_data[$inv_pack_count]['packID'] = $pack_id;
$invoice_packs_table_data[$inv_pack_count]['clientID'] = $client_id_from_inv_pack_table;
$invoice_packs_table_data[$inv_pack_count]['date_range_start'] = $date_range_start;
$invoice_packs_table_data[$inv_pack_count]['date_range_end'] = $date_range_end;
$invoice_packs_table_data[$inv_pack_count]['value'] = $value;
$inv_pack_count++;
// get date ranges, and create variables that are going to be outputted immediately at the end of all calculations
// maybe not... just put them all in the array
}
$stmt->close();
}
How can I get this query to work.
$in
$in = array of question marks, which works fine, and $uniqueids has a little over 1,000 unique numbers representing the ID's.
... "which works fine" ... Are we sure that it works fine?
what does $query_packs_for_date_range look like after this statement is evaluated?
$query_inv_packs_for_date_range = "SELECT invid, packid, clientid,
date_range_start, date_range_end, value FROM inv_packs WHERE clientid
IN(implode(',', $in))";
For debugging, I would echo or var_dump() the contents of $query_inv_packs_for_date_range before I passed it to prepare.
I'm thinking $in is getting evaluated, but the implode function is not.
Personally, I'd write it like this:
$query_inv_packs_for_date_range = 'SELECT invid, packid, clientid,
date_range_start, date_range_end, value FROM inv_packs WHERE clientid
IN (' . implode(',', $in) . ')';
// ^^^ ^^^
--
The number of question marks (bind placeholders) will need to match the number of bind values.