Using placeholders with INNER JOIN in PDO

Hello. I just have a query if I am going about JOINS in PDO correctly.

For my insert queries I follow the format

[code] $sql = “INSERT INTO sportsch_sport.satellitemulti (channelid_multi , satname , frequency , polarisation , symbrate , fec , encorfta, channelid_m )VALUES (
:channelid,
:satname,
:frequency,
:polarisation,
:symbrate,
:fec,
:encorfta,
:channelid_m)”;

  $stmt = $DB->prepare($sql);

  // bind the values
  $stmt->bindValue(":channelid", $channelid);
  $stmt->bindValue(":satname", $satname);
  $stmt->bindValue(":frequency", $frequency);
  $stmt->bindValue(":polarisation", $polarisation);
  $stmt->bindValue(":symbrate", $symbrate);
  $stmt->bindValue(":fec", $fec);
  $stmt->bindValue(":encorfta", $encorfta);
  $stmt->bindValue(":channelid_m", $channelid_m);
  

  
  // execute Query
  $stmt->execute();

[/code]

But i have a query with a LEFT Join, as below

[code] $keyword = trim($_GET[“keyword”]);
if ($keyword <> “” ) {
$sql = "SELECT f.hometeam, f.versus, f.awayteam, f.sport, f.competition, f.date, f.time,
Group_concat(s.name SEPARATOR ‘,’) name,
Group_concat(x.channelid_fc SEPARATOR ‘,’) channelid_fc
FROM footballfixtures f
LEFT JOIN fixturechannels x
ON x.matchid_fc=f.matchid
LEFT JOIN satellite s
ON x.channelid_fc=s.channelid
WHERE 1 AND " . " (hometeam LIKE :keyword)
OR awayteam LIKE :keyword
OR competition LIKE :keyword
GROUP BY f.hometeam, f.versus, f.awayteam, f.sport, f.competition, f.date, f.time
ORDER BY f.date, f.time ";
$stmt = $DB->prepare($sql);

$stmt->bindValue(":keyword", $keyword."%");

[/code]

What I am not sure about is do I need to use placeholders for all of the values such as f.home team, f.away team and so on? Or is the way I have approached this left join appropriate?

The join does work successfully, but I just wasn’t sure if this approach would help prevent SQL injection?

Many Thanks to anyone who reads this and/or provides their insight

Sorry I stupidly posted this in php. Could a moderator or admin move this thread to the Database section. Thanks

The only time you HAVE to have aliases (or what you call placeholders) is when you have the same field name on multiple tables. However, it is often better to have them simply for the human factor, i.e. when someone else, who may not be as familiar with your code and/or database structure, as to maintain or debug your code, they will be able to quickly glance at your code and see where fields occur within your database.

The 1 AND is unneeded, though. The only time you really want something like that is when you are building a query dynamically and you may or may not have other query parameters to go with it.

3 Likes

Many thanks for this info, much appreciated :slight_smile:

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.