Prepared statement with variable fields and values.

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.
Gabriel7Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Olaf DoschkeSoftware DeveloperCommented:
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.
Gabriel7Author 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 DeveloperCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.