jtorral Posted December 27, 2010 Share Posted December 27, 2010 I have a dilemma. Maybe it's an easy fix but i just cant see the solution. My code already works without preparing the sql. The problem is preparing the sql and building the dynamic query. In a nut shell, I have a form with 10 different fields. And depending on which field gets data, I build my query so that it only works on the fields the user typed something into. let me summarize .... I have a basic query that starts off like this $updatestring = ""; $query = "UPDATE table a SET "; if field1 set then $updatestring = $updatestring . " field1 = field1val " if field2 set then $updatestring = $updatestring . " field2 = field1val " if field3 set then $updatestring = $updatestring . " field3 = field1val " if field4 set then $updatestring = $updatestring . " field4 = field1val " and so on .... Finally $query = $query . $updatestring which can end up looking like: UPDATE table a SET field1 = field1val, field3 = field1val .... depending on which fields were set. I take care of commas and so on. Like I said this works. However, to prepare the statement really complicates things because if it looks like this: UPDATE table a SET field1 = ? , field3 = ? how do I know which variable to bind to the param out of the 10 possible fields ? How do I build the string types and place them in the right order? Do you see my dilemma? mysqli_stmt_bind_param($stmt, 'ii', $field1val, $field3val); Any help would be appreciated. I will also post this in the mysql section. Thanks, JT then Quote Link to comment Share on other sites More sharing options...
requinix Posted December 27, 2010 Share Posted December 27, 2010 Unless you want to make a function that executes a prepared query using a variable number of parameters (I've done it - bit tricky though) you're better off using mysqli_real_escape_string() and building the query manually. I will also post this in the mysql section. This is a PHP question, not a MySQL question. Don't. Quote Link to comment Share on other sites More sharing options...
snowman15 Posted December 27, 2010 Share Posted December 27, 2010 You could loop through all your input fields, checking for input, then if input fields !empty(), you could set $array[field] = value. then when your building your query, you can foreach ($array as $key =>$value){ sql .= $key .' = '. $value; } I didn't take a ton of time to read and think about what you were saying, but im pretty sure a simple array and foreach loop could take care of your problem, cheers Quote Link to comment Share on other sites More sharing options...
jtorral Posted December 28, 2010 Author Share Posted December 28, 2010 Figured it out. Was hoping it would be an easy thing but I guess you have to code sometimes Here is what I did. As I check every field, if it is one that needs updating I create the following array $binddata[col1]["type"] = "s"; $binddata[col1]["varname"] = "varval1"; . and so on for all my fields .... . $binddata[col9]["type"] = "i"; $binddata[col9]["varname"] = "varval9"; I have also created a small function which takes the array and builds my bind command. Here is the actual function that you pass the array to. I added 2 more parameters to the function incase you need to add one extra parameter to build with. function buildbind($binddata, $addthistype, $addthisvar) { $bindstring = "mysqli_stmt_bind_param(\$stmt, '"; foreach ($binddata as $key) { $bindstring = $bindstring . $key[type]; } $bindstring = $bindstring . "$addthistype' "; foreach ($binddata as $key) { $bindstring = $bindstring . ",\${$key[varname]} "; } $bindstring = $bindstring . " $addthisvar );"; return $bindstring; } So, whenever I have one of these dynamic queries, I build up my array and call the function buildbind($binddata, "", "") It returns a string like so. mysqli_stmt_bind_param($stmt, 'ssi', $ipaddr, $username, $lastactivity); 1. define $thequery with a bunch of ?'s in it. 2. prepare the statement with the query: $stmt = mysqli_prepare($link, $thequery); 3. create the bindcmd string with: $bindcmd = buildbind($binddata, "", "" ); 4. turn the string into an actual php line of code: eval($bindcmd); do some logic and execute the command: if( ! mysqli_stmt_execute($stmt) ) {error_message("Failed to execute statement"); } And it works !!! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.