What to do ??? Prepared sql stament and dynamic query becoming impossible.

[jtorral]

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

[requinix]

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.

[snowman15]

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

[jtorral]

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 !!!