asked on

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.

Olaf Doschke

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.

Gabriel7

ASKER

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 Doschke

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.

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.