A mixture of PHP PDO and fabricating a query replacing SELECT SQL CALC FOUND ROWS
-
I will try my best to describe this, it's the same PHP project from my post below, my next biggest hurdle to jump here. I'm trying to solve this through SQL, without having to change much of the the PHP Code I have this line of code, in which it takes a query from the calling class which passes it to this class that contains the line below. Sort of crazy or genius.
$lc_query = '(SELECT SQL_CALC_FOUND_ROWS ' . substr($pc_query, 6) . ') LIMIT ' . (($ln_page - 1) * $ln_pagesize) . ', ' . $ln_pagesize; // This wipes out the parameters!
So I changed the code from using the old mysql handle to the new PDO. I could not find a way to pass an array of parameter tokens to this class, because the token count could be any number of them. The classic trying to bind parameters in a loop failure. Original way: Class 1, generate the query, and passes it to class 2 Class 2, calculate page count, then uses the line above to wrap the passed in query and transmit it. So I did this: class 1: generates the dynamic query, makes a PDO instance, bind parameters, pass PDO to class 2 class 2: receives the PDO handle, and executes. So far so good, it works good, stable, but I need the calculations in the Class 2 for page indexing. I can't figure out how to reintegrate the above statement in class 2 where the page numbers are generated. I was hoping I would be able to extract the query out of the PDO object, and reconstruct it. Plus I don't think the line above works in PDO. If you have any thoughts, comments or past experience with this, I'd appreciate it! Class 1
//PDOClass: Make a Default Connection
$cmd_sp = $PDOClass->QUERY($lc_query);
if ($b_mfgName) $cmd_sp->bindParam(':mfgName', $lc_mfg, PDO::PARAM_STR);
if ($b_deptID) $cmd_sp->bindParam(':deptID', $pn_dept_id, PDO::PARAM_INT);
if ($b_partnerID) $cmd_sp->bindParam(':partnerID', $go_sitedict['partner_id'], PDO::PARAM_INT);$SPClass = new SPClass(
$cmd_sp,
$cmd_sp_mfg,
FALSE,
'',
$ll_debug
);Class 2:
// So how to we wrap this around the query that we already sent to PDO?
$lc_query = '(SELECT SQL_CALC_FOUND_ROWS ' . substr($pc_query, 6) . ') LIMIT ' . (($ln_page - 1) * $ln_pagesize) . ', ' . $ln_pagesize; // This wipes out the parameters!try {
// PDOClass: Execute the PDO that was passed in
$cmd_showProduct->execute();}