Asked  1 Year ago    Answers:  5   Viewed   10 times

OK so my question is I have a function but i want to parse it different WHERE clauses for when it executes a query. for example:

function query($where)
{
$query = $mysql->prepare("SELECT * FROM table WHERE ?");
$query->bind_param("s", $where);
$query->execute();

...

}

query("table.id=123 AND table.name='abc'");

I have learnt that this is incorrect So how do I perform something similar, i have many places where I need to use this function with different WHERE clauses, and make a function for each is impractical and so is not making a function and calling it directly.

 Answers

3

For a prepared statement with a WHERE clause you have to specify what values will be specified later, for instance:

SELECT * FROM table WHERE ID=?

if you want to make it more dynamic you can have specify the query in one function and then call the query function. For example, you have this:

function query($query, $param, $where)
{
$query = $mysql->prepare($query);
$query->bind_param($param, $where);
$query->execute();

...

} 

and in your other function you say:

$results=query("SELECT * FROM table WHERE Id=?","s","1");

you could make this even more sophisticated by making a query class that can contain an array of where clauses:

class query
{
  public $query;
  public $param;
  public $where;
}

$query=new query();
$query->query="SELECT * FROM Table WHERE group=? AND name like ?";
$query->param="ss";
$query->where = array();
$query->where[]="administrators";
$query->where[]="sam";

and change your query function to look like:

function SQLCall(query $query)
{
$db = $mysql->prepare($query->query);
call_user_func_array(array(&$db, 'bind_param'), $where)
$db->execute();

...

}
Thursday, April 1, 2021
 
qitch
 
1

I went ahead and ran a test where one query uses a prepared statement, and the other builds the entire query then executes that. I'm probably not making what I'm wanting to know easy to understand.

Here's my test code. I was thinking prepared statements sort of held back execution until a $stmt->close() was called to optimize it or something. That doesn't appear to be the case though as the test that builds the query using real_escape_string is at least 10 times faster.

<?php

$db = new mysqli('localhost', 'user', 'pass', 'test');

$start = microtime(true);
$a = 'a';
$b = 'b';

$sql = $db->prepare('INSERT INTO multi (a,b) VALUES(?, ?)');
$sql->bind_param('ss', $a, $b);
for($i = 0; $i < 10000; $i++)
{
    $a = chr($i % 1);
    $b = chr($i % 2);
    $sql->execute();
}
$sql->close();

echo microtime(true) - $start;

$db->close();

?>
Thursday, April 1, 2021
 
THEK
 
1

This actually depends on the Mysql server. The default max size for all data combined in the entire query is 1mb. See: http://dev.mysql.com/doc/refman/5.1/en/packet-too-large.html

If your data combined is under that "max_allowed_packet" threshold, just use "s" for the binding type for any text field. Infact, you can usually get away with using "s" for any field type at all (date, float, etc).

If your entire entry combined that you want to insert is over 1mb (or whatever you reset it to) in length, you'll want to use mysqli_stmt::send_long_data method and the "b" binding type to send this particular field in chunks.

Thursday, April 1, 2021
 
NewPHP
 
2


(source: scottgu.com)

You need something like this? Use the Linq Dynamic Query Library (download includes examples).

Check out ScottGu's blog for more examples.

Friday, June 4, 2021
 
TuomasR
 
1

Where awaits conditions in form of lambdas rather than strings, so you have to refactor your code a little bit (just an idea below):

IQueryable<CustCommPreference> query = _marketoEntities.CustCommPreferences.AsQueryable();
if (!string.IsNullOrEmpty(cusid)) 
    query = query.Where(x => x.MasterCustomerID == cusid);
if (!string.IsNullOrEmpty(mktid)) 
    query = query.Where(x => x.MarketID == mktid);

and later use it:

...
join b in query
...
Wednesday, August 18, 2021
 
Mirko
 
5

I wouldn't like using a StringBuilder to dynamically create a query each and every time, especially when the number of meaningful combinations is countable and finite.

I'd always prefer static Strings. Yes, you have to type them in, but you'll do that once. I'd rather do that than pay the price in complexity and at runtime.

Saturday, July 31, 2021
 
Jon
 
Jon
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :