Asked  7 Months ago    Answers:  5   Viewed   34 times

In this query

select wrd from tablename WHERE wrd LIKE '$partial%'

I'm trying to bind the variable '$partial%' with PDO. Not sure how this works with the % at the end.

Would it be

select wrd from tablename WHERE wrd LIKE ':partial%'

where :partial is bound to $partial="somet"

or would it be

select wrd from tablename WHERE wrd LIKE ':partial'

where :partial is bound to $partial="somet%"

or would it be something entirely different?

 Answers

53

You could also say:

SELECT wrd FROM tablename WHERE wrd LIKE CONCAT(:partial, '%')

to do the string joining at the MySQL end, not that there's any particular reason to in this case.

Things get a bit more tricky if the partial wrd you are looking for can itself contain a percent or underscore character (since those have special meaning for the LIKE operator) or a backslash (which MySQL uses as another layer of escaping in the LIKE operator?—?incorrectly, according to the ANSI SQL standard).

Hopefully that doesn't affect you, but if you do need to get that case right, here's the messy solution:

$stmt= $db->prepare("SELECT wrd FROM tablename WHERE wrd LIKE :term ESCAPE '+'");
$escaped= str_replace(array('+', '%', '_'), array('++', '+%', '+_'), $var);
$stmt->bindParam(':term', $escaped);
Wednesday, March 31, 2021
 
qitch
answered 7 Months ago
77

A bit offtopic but I find it important enough.

A very recent user comment in the manual page for mysql_stmt_bind_param contains the exact answer to this very question.

You see, this site, although encourage laziness, not always answer your question better than good old google and manual can.

Saturday, May 29, 2021
 
ramdemon
answered 5 Months ago
33

This is only executing the insert with the last value as it's the last value that is bound to the statement. Call execute in each iteration of the loop.

foreach($this->array as $k => &$v) {
    $sql->bindValue(":Name" , $v , PDO::PARAM_STR);
    $sql->execute();
}
Saturday, May 29, 2021
 
ericstumper
answered 5 Months ago
90

No. A query parameter substitutes only for a single constant value. For example, a numeric constant or literal string or date.

Anything else -- column names, table names, SQL keywords, functions, expressions -- must be in the SQL string at parse time.


Re your comment:

You should understand that parameters are not just a convenience to interpolate extra strings into your SQL. PREPARE is analogous to a compile phase for Java or C#, whereas EXECUTE is analogous to running the compiled code.

Prepare time is when the RDBMS does syntax checking, and also validation of references. It must give an error if you name a table that doesn't exist, or invoke a function that doesn't exist.

You can't pass table names or function calls as parameters because then the RDBMS wouldn't be able to validate those references at prepare time. You shouldn't be able to use a query parameter to change the syntax of the statement, or introduce invalid tables or functions.

So the parameter placeholder must be an irreducible syntactic element that is never an invalid reference, i.e. a single literal value -- a number or a string.

Saturday, May 29, 2021
 
TheLovelySausage
answered 5 Months ago
12

found the answer for mysqli:

public function get_result($sql,$types = null,$params = null)
    {
        # create a prepared statement
        $stmt = $this->mysqli->prepare($sql);

        # bind parameters for markers
        # but this is not dynamic enough...
        //$stmt->bind_param("s", $parameter);

        if($types&&$params)
        {
            $bind_names[] = $types;
            for ($i=0; $i<count($params);$i++) 
            {
                $bind_name = 'bind' . $i;
                $$bind_name = $params[$i];
                $bind_names[] = &$$bind_name;
            }
            $return = call_user_func_array(array($stmt,'bind_param'),$bind_names);
        }

        # execute query 
        $stmt->execute();

        # these lines of code below return one dimentional array, similar to mysqli::fetch_assoc()
        $meta = $stmt->result_metadata(); 

        while ($field = $meta->fetch_field()) { 
            $var = $field->name; 
            $$var = null; 
            $parameters[$field->name] = &$$var; 
        }

        call_user_func_array(array($stmt, 'bind_result'), $parameters); 

        while($stmt->fetch()) 
        { 
            return $parameters;
            //print_r($parameters);      
        }


        # the commented lines below will return values but not arrays
        # bind result variables
        //$stmt->bind_result($id); 

        # fetch value
        //$stmt->fetch(); 

        # return the value
        //return $id; 

        # close statement
        $stmt->close();
    }

then:

$mysqli = new database(DB_HOST,DB_USER,DB_PASS,DB_NAME);
$output = new search($mysqli);

$sql = "
SELECT *
FROM root_contacts_cfm
ORDER BY cnt_id DESC
";
print_r($output->get_result($sql));

$sql = "
SELECT *
FROM root_contacts_cfm
WHERE root_contacts_cfm.cnt_id = ?
ORDER BY cnt_id DESC
";

print_r($output->get_result($sql,'s',array('1')));

$sql = "
SELECT *
FROM root_contacts_cfm
WHERE root_contacts_cfm.cnt_id = ?
AND root_contacts_cfm.cnt_firstname = ?
ORDER BY cnt_id DESC
";

print_r($output->get_result($sql, 'ss',array('1','Tk')));

mysqli is so lame when comes to this. I think I should be migrating to PDO!

Friday, June 4, 2021
 
PeanutsMcgee
answered 5 Months ago
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 :