PHP, PDO, SQLite INNER JOIN Statement And Variable

Question

I need to do a PHP PDO call to my db with an INNER JOIN and WHERE clause.

In navicat GUI this statement is running fine and i can see the results. The problem come out lather in php environment about string concatenation.

I would like to format this request so that it can be digested by php:

SELECT * FROM tsourcetb as T INNER JOIN users as U ON U.username = T.username WHERE U.username = $username AND T.username = $username;

what I tried to do

$sth = $db->prepare("SELECT * FROM tsourcetb as T INNER JOIN users as U ON U.username = T.username WHERE U.username = $username AND T.username = $username");

the return is an error indicating that there is no table with the variable name. Basically it takes the variable as the name of the table the return is an error indicating that there is no table with the variable name. Basically it takes the variable as the table name and not the table name as it should like (SELECT * FROM $username) jumping out the first part of statement).

The intent is to have all the records of table A where the username field is = to the username field of table B with value passed from a variable. Thank for any suggestion to achieve my goal.

UPDATE

php is magic need to try and retray. At the end tish one help me to goal:

$username = ($_POST['username']);
$password = ($_POST['password']);

$statement = $db->prepare('SELECT p.* FROM `tsourcetb` as p LEFT JOIN    `users`as s ON p.username = s.username WHERE s.username = :username;');
$statement->bindParam(':username', $username, PDO::PARAM_STR);
$statement->execute();

/* look here -> $statement->fetchall(PDO::FETCH_ASSOC) */

$array_select = $statement->fetchall(PDO::FETCH_ASSOC);
echo json_encode($array_select, JSON_PRETTY_PRINT);

Masivuye Cokile · Accepted Answer · 2018-09-26 12:10:27Z

0

<?php


$sth = $db->prepare("SELECT * FROM `tsourcetb` as T INNER JOIN users as U ON U.username = T.username WHERE U.username = ?  AND T.username = ? ");
$sth->execute([$username,$username]);
$results = $sth->fetchall();


?>

wrapper your table name with backticks and also use placeholders

answered Sep 26, 2018 at 12:10

Masivuye Cokile

4,7733 gold badges22 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

sundsx Over a year ago

hi and thanks for time. I have 2 table and in you example only one is backticksed. Anyway nothing happens no results was valorized

E3Im · Accepted Answer · 2018-09-26 12:21:12Z

0

Try this:

$stmt = $db->prepare("SELECT * FROM tsourcetb as T INNER JOIN users as U ON U.username = T.username WHERE U.username = :username AND T.username = :username");
$stmt->bindValue(':username', $username, PDO::PARAM_STR);
$stmt->execute();

You need to bind a value with prepared statement:

Source: Docs

edited Sep 26, 2018 at 12:21

answered Sep 26, 2018 at 12:06

E3Im

3302 silver badges18 bronze badges

2 Comments

sundsx Over a year ago

i get an error: Call to undefined method PDO::bindValue()

E3Im Over a year ago

@sundsx You are right, I made a mistake changed it

WKoppel · Accepted Answer · 2018-09-26 12:24:48Z

0

You have to bind parameters when you are making an dynamic query with PDO. Change this in your query.

$username -> :username

And before you make the call

$yourQueryObj->bindValue(':username', $username, PDO::PARAM_STR);

That's why prepared statments are safer than regular variables as you assign it's type before it's sent for query.

You can read about it here http://php.net/manual/en/pdostatement.bindvalue.php

You should be able also execute with array of parameters after preparing like that :

$sth = execute(array(':username'=> $username));

edited Sep 26, 2018 at 12:24

answered Sep 26, 2018 at 12:06

WKoppel

5043 silver badges15 bronze badges

1 Comment

sundsx Over a year ago

Call to undefined method PDO::bindParam()

Collectives™ on Stack Overflow

PHP, PDO, SQLite INNER JOIN Statement And Variable

3 Answers 3

1 Comment

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related