CODEWITHSUNDEEP
phpmysqlpdo
Carl Barrett
Carl Barrett

Reputation: 229

php pdo selecting from multiple tables with more than one where clauses

I am new to PHP PDO and converting some regular MySQL queries to work with PDO.

The query below when tested in phpMyAdmin works great when the assigned values replaced the current placeholders in the SQL statement. But when I configure it to work as it is now with PDO, it does not produce any results or errors. Can someone please tell me or show me what is it that I am doing wrong?

Someone told me that I cannot pass parameters as references in the array.

And if correct, what is the best way for creating a solution and by using only the user ID passed through to the variable $uid. Thanks.

<p>// For testing</p>
<pre>$uid = 1;</pre>
<p>&nbsp;</p>   
<pre>$array = array(
    ':uId' => ''.$uid.'',
    ':aId' => 'u.user_id',
    ':gID' => 'a.group_id',
    ':eID' => 'a.entry_id',
    ':pID' => 'a.permit_id'
    );</pre>

 

// create the sql for qd_user_usam table

$sql = "SELECT u.user_id, a.acl_id, g.group_name, e.entry_level, p.permit_level
FROM qd_users as u, qd_users_acl as a, qd_users_group as g, qd_users_entry as e, qd_users_permission as p 
WHERE u.user_id = :uID
            AND a.acl_id    = :aID 
            AND g.group_id  = :gID
            AND e.entry_id  = :eID
            AND p.permit_id = :pID";

<p>try
{</p>
   <p>// Build the database PDOStatement</p>
   <pre>$_stmt = $this->_dbConn->prepare($sql);</pre>
   <pre>$_stmt->execute($array);</pre>
<pre>}
catch(PDOException $e)
{</pre>
    <pre>$this->_errorMessage .= 'Error processing user login access. <br /> Line #'.__LINE__ .' '.$e ;</pre>  
    <pre>die($this->_errorMessage);
}</pre>

<pre>$results = $_stmt->fetchAll(PDO::FETCH_ASSOC);</pre>
<pre>return $results;</pre>

<pre>$results = null;</pre>
<pre>$this->_dbConn = null;</pre>

Upvotes: 2

Views: 7296

Answers (2)

G-Nugget
G-Nugget

Reputation: 8836

The problem is that you're trying to write your JOINs implicitly by binding the joined columns as parameters, which would not work. The parameters can not reference another column; they are seen as strings in this case. If you rewrite the query like this it should fix the JOIN problem:

SELECT u.user_id, a.acl_id, g.group_name, e.entry_level, p.permit_level 
    FROM qd_users AS u
        JOIN qd_users_acl AS a ON (u.user_id = a.acl_id)
        JOIN qd_users_group AS g ON (g.group_id = a.group_id)
        JOIN qd_users_entry AS e ON (e.entry_id = a.entry_id)
        JOIN qd_users_permission AS p ON (p.permit_id = a.permit_id)
    WHERE u.user_id = :uID

Upvotes: 1

Your Common Sense
Your Common Sense

Reputation: 157870

You take prepared statements wrong.
Thy have to be used not to represent whatever value in the query, but dynamically added data only.

While a.group_id is a column name and have to be written as is, without prepared statements. 

// For testing
$uid = 1;

// create the sql for qd_user_usam table
$sql = "SELECT u.user_id, a.acl_id, g.group_name, e.entry_level, p.permit_level 
        FROM qd_users as u, qd_users_acl as a, qd_users_group as 
             g, qd_users_entry as e, qd_users_permission as p 
        WHERE u.user_id = ?
        AND a.acl_id    = u.user_id
        AND g.group_id  = a.group_id
        AND e.entry_id  = a.entry_id
        AND p.permit_id = a.permit_id";
$_stmt = $this->_dbConn->prepare($sql);
$_stmt->execute(array($uid));

Upvotes: 1

Related Questions