Prepared statement with variable fields and values.

Gabriel7
Gabriel7 used Ask the Experts™
on
I have a prepare statement (php 7) I need to understand how to make.

Using this example:
            if($getData = $mysqli->prepare("SELECT val1, val2, val3, val4 from db.table where field1 like ? and field2 like ? and field3 like ?"))
            {
                  $getData->bind_param('sss', $item1, $item2, $item3);                  
                  $getData->execute();                        
                  $getData->bind_result($val1, $val2, $val3, $val4);      
                  $getData->store_result();
                  $getData->fetch();      
               }

Except I won't know field1, field2, or field3....or if there just field1 and field3.  And I need to deal with the "like" scenario...just as I would this query...(this one works with one variable and one field)...

$param = "%{$_REQUEST['ans']}%";
if($getShipToInfo = $mysqli->prepare("select stid, vendor_id, name_of, address_1_of, address_2_of, city_of, state_of, zip_of, country_of from db_table where owner_of=? and " . $_REQUEST['fld'] . " like ? order by ?"))
{
      $getShipToInfo->bind_param('sss', $_SESSION['s_owner'],  $param, $_REQUEST['fld']);                  
      $getShipToInfo->execute();
      $getShipToInfo->bind_result($stid, $vendor_id, $name_of, $address_1_of, $address_2_of, $city_of, $state_of, $zip_of, $country_of);      
      $getShipToInfo->execute();
      $getShipToInfo->store_result();
}

So, to recap...I need to be able to pass my fields AND my values to build a query.  Whether I'm passing address_2_of and city_of or any number of items.  I started by looking into call_user_func_array...but I'm still not getting it.  

Can anyone help?

Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Olaf DoschkeSoftware Developer

Commented:
You can, you can only input values by parameters, not fields. But you can, of course, build up whatever SQL command as a string, so that's not needing query parameterization. It's also not unsafe because you don't compose strings from user input.

Query parameters are only meant to be used for values, not for names of tables, columns or other things, even not for expressions.

Bye, Olaf.

Author

Commented:
So...to understand...if I have a person who puts data in field...say name_of...I can easily search that field...but if they also add more to address_of....and I want to say find this if name_of like ?% AND address_of like ?%

Example.
   name_of = "com";
  address_of = "par";

If it was just name_of...they could find all entries with "com" in it.  But if its also address_of...they can find everything with name_of like "com%" AND address_of like "par%" ...to filter down to companies who have com and maybe on parkway...

And lets say they want to filter to the city...city_of could be "spr"...might filter to springfield or something like that.
That make sense?
Olaf DoschkeSoftware Developer

Commented:
That would need LIKE ?+'%'

? is a string value, and you need to concatenate '%' to it this way. You can use ? in an expression, but if you store an expression in a variable you pass as ? it won't be executed in MySQL, that's what I'm saying.

What doesn't work out at all is WHERE ? LIKE ? and passing in a field name, you can't do that Or SELECT FROM ?.

What you can of course offer is let the user pick a column, eg Name, Address, and then compose the where clause from selected column and value entered.

Bye, Olaf.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial