mysql PDO how to bind LIKE

Question 1

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?

Question 2

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);

Question 3

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

$objPdo->query("CREATE TEMPORARY TABLE r1
SELECT CONCAT(MONTH(Heure_deb),'/',DAY(Heure_deb)) as 'Date',
                      Heure_deb,
                      Operateur.Id_op ,
                      Nom_op ,
                      Prenom_op,
                      Nom_act ,
                      TIME(Heure_deb) as heure,
                      Commentaire,
                      Type
                      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 ,
                      Prenom_op,
                      Nom_act ,
                      Commentaire,
                      Type,
                      Time_Difference,
                      Decimal_duree
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";

while($row=$result->fetch()){
    echo"<tr>
          <td>".$row['Date']."</td>
          <td>".$row['heure']."</td>
          <td>".$row['Nom_op']."</td>
          <td>".$row['Prenom_op']."</td>
          <td>".$row['Type']."</td>
          <td>".$row['Time_Difference']."</td>
          <td>".$row['Decimal_duree']."</td>
          <td>".$row['Commentaire']."</td>
      </tr>";
}
echo"</table></div>";

Question 4

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

This should work:

$groupID = $dbo->lastInsertId();

Question 5

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

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

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

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

    $db->commit();

    return true;
}catch(Exception $e){
    $db->rollBack();
    throw $e;
}

Question 6

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

$property = 
    Property::select(
        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", "<", "?")
    ->orderBy("distance")
    ->take(20)
    ->setBindings([$lat, $lng, $lat,  $radius])
    ->get();

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

mysql PDO how to bind LIKE

Answers

php,mysql,pdo,bindparam