Viewed   73 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?



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, November 16, 2022

There are three queries in this request and therefore you have to run them in three calls, not one:

SELECT CONCAT(MONTH(Heure_deb),'/',DAY(Heure_deb)) as 'Date',
                      Operateur.Id_op ,
                      Nom_op ,
                      Nom_act ,
                      TIME(Heure_deb) as heure,
                      FROM Operateur, Pointage, Activite
                      WHERE Operateur.Id_op = Pointage.Id_op
                      AND Activite.Id_act = Pointage.Id_act
                      ORDER BY date, Id_op, heure

$objPdo->query("Create temporary table r2
SELECT a.Id_op, a.Heure_deb, MIN(b.heure_deb) as fin, TIMEDIFF(b.Heure_deb, a.Heure_deb) as Time_Difference, ROUND(HOUR(TIMEDIFF(b.Heure_deb, a.Heure_deb)) + MINUTE(TIMEDIFF(b.Heure_deb, a.Heure_deb))/60,2) as Decimal_duree
FROM Pointage a
LEFT JOIN Pointage b ON a.Id_op = b.Id_op
WHERE a.heure_deb < b.heure_deb
Group by a.Id_op, a.Heure_deb

$result = $objPdo->query("select CONCAT(MONTH(r1.Heure_deb),'/',DAY(r1.Heure_deb)) as 'Date',
                      TIME(r1.Heure_deb) as heure,
                      r1.Id_op ,
                      Nom_op ,
                      Nom_act ,
from r1
LEFT JOIN r2 ON r1.Id_op = r2.Id_op and r1.heure_deb = r2.heure_deb
Order by Id_op, Date , heure";

Thursday, December 8, 2022

lastInsertId() is a method of the PDO class, not the PDOStatement class.

This should work:

$groupID = $dbo->lastInsertId();
Friday, November 18, 2022

If the transaction fails for whatever reason, the code does stop at the very line where error occurred end then the execution jumps directly to the catch block. So it is sufficient the way you have it written in Code Block 2.

Note that you should always re-throw the Exception after rollback. Otherwise you will never have an idea what was a problem. So it should be

    $stmt = $db->prepare(... 1 ...);

    $stmt = $db->prepare(... 2 ...);

    $stmt = $db->prepare(... 3 ...);


    return true;
}catch(Exception $e){
    throw $e;
Wednesday, September 14, 2022

OK, after some experimenting, here's the solution that I came up with:

$property = 
        DB::raw("title, lat, lng, ( 
            3959 * acos( 
                cos( radians(  ?  ) ) *
                cos( radians( lat ) ) * 
                cos( radians( lng ) - radians(?) ) + 
                sin( radians(  ?  ) ) *
                sin( radians( lat ) ) 
       ) AS distance")
    ->having("distance", "<", "?")
    ->setBindings([$lat, $lng, $lat,  $radius])

Basically, setBindings has to be called on the query. Wish this was documented!

Saturday, December 24, 2022
Only authorized users can answer the search term. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :