php - Better way of building dynamic PDO statement -

php - Better way of building dynamic PDO statement -

i'm trying build dynamic pdo sql statement. thought on how better?

i maintain getting array(3) { [0]=> string(5) "00000" [1]=> null [2]=> null } free when run this...

so mysql pdo statement doesn't seem working well!

any thought on how prepare this?

$park = $_post["park"]; $lecturestyle_id = $_post["lecturestyle"]; $group_size = $_post["groupsize"]; $roomstructure_id = $_post["roomstructure"]; $array = explode(",", $_post["facilities"]); // alter mon here echo '<td class="gridside"> mon '; // build facilities search for($i = 0; $i < count($array); $i++){ if ($array[$i]!=0) { $fac .= 'and facilities_id='.$array[$i].' '; } else $fac .= ''; } echo '</td>'; ($i = 1; $i <= 9; $i++) { // alter mon here echo '<td class="box" id="mon'.$i.'">'; // dynamically build sql query $sql = " select * ts_room rm left bring together ts_roomfacilities rf on rm.id = rf.room_id left bring together ts_facilities f on f.id = rf.facilities_id left bring together ts_building b on rm.building_id=b.id capacity>=".$group_size.' '; $sql .= $fac; if($park!="any") { $sql .= " , b.park_id=".$park; } if($lecturestyle_id!="any") { $sql .= " , lecturestyle_id=".$lecturestyle_id; } if($roomstructure_id!="any") { $sql .= " , roomstructure_id=".$roomstructure_id; } $sql .= " , rm.id not in (select count(*) ts_request rq left bring together ts_allocation on a.request_id = rq.id day_id=1 , period_id=".$i." or a.status not null , a.status in ('pending','declined','failed'))"; $stm = $pdo->prepare( $sql ); $rows = $stm->fetchcolumn(); echo $rows.'<br>free</td>'; echo '</td>'; }

a funny fact. mention sql injection , prepared statements while nobody wants write code :)

though understand it: pdo weak conditional queries, task going quite toilsome. other api, pdo basic tasks beginner's manual only, , offers no real help developer whatever real life issues.

so, i'll give safemysql illustration both safe , convenient. not compatible pdo, can utilize instead , benefits.

so, thought parse placeholders not in whole query, in arbitrary query part only. say, facilities part:

$fac_sql = ''; foreach($array $facility){ if ($facility) { $fac_sql .= $db->parse(' , facilities_id=?i',$facility); } }

now have syntactically right statement in $fac_sql variable. same goes other parts

$cond = ''; if($park!="any") { $cond .= $db->parse(' , b.park_id=?s',$park); } if($lecturestyle_id!="any") { $cond .= $db->parse(" , lecturestyle_id=?s",$lecturestyle_id); } if($roomstructure_id!="any") { $cond .= $db->parse(" , roomstructure_id=?s",$roomstructure_id); }

...as whole query:

$sql = " select * ts_room rm left bring together ts_roomfacilities rf on rm.id = rf.room_id left bring together ts_facilities f on f.id = rf.facilities_id left bring together ts_building b on rm.building_id=b.id capacity >= ?s; ?p ?p , rm.id not in (select count(*) ts_request rq left bring together ts_allocation on a.request_id = rq.id day_id=1 , period_id=?i or a.status not null , a.status in ('pending','declined','failed'))"; $rows = $db->getcol($sql,$group_size,$fac_sql,$cond,$i);

(code not tested, serve example!).

another safemysql benefit homecoming both mysql error message , whole query in case of error. you'll able either prepare error or seek query in console test.

so - how conditional queries have in general.

as particular problem query - it's irrelevant pdo or prepared statements or conditional query building. there flaw somewhere in query logic. have write query in plain text , seek console/phpadmin/sqlyog/whatever. may inquire assistance sql on stackoverflow well. query working, may start building dynamically.

also, have feeling it's way overcomplex, , needs simplified. may split several easier queries. run such monster in loop big flaw. improve rewrite in 1 call.

php mysql pdo