PHP: How to grab data from multiple db tables PDO

PHP
1

Hey Everyone. I’d appreciate some feedback here.

What is the best way to grab data from multiple db tables simultaneously, given that you need to grab more than one column from some tables AND also grab data for this particular User only.

I have a database with 4 tables in it:

  • Tool [Primary key: Tool-id] (grab Tool-id, Name, Section columns)
  • Schedule [Tool-id - secondary] (grab Date if Tool-id matches)
  • Goals [ Tool-id - secondary] (grab Date if Tool-id matches)
  • User - Only grab data for the logged-in user (User-id comes from $_SESSION)

What is wrong with this code snippet?

$stmt3 = $pdo->prepare("SELECT `Tool`.`Tool-id`,`Tool`.`Name`, `Tool`.`Section`
FROM `Tool` 
    INNER JOIN `Schedule` 
        ON `Tool`.`Tool-id`=`Schedule`.`Tool-id`
    INNER JOIN `Goals` 
       ON `Tool`.`Tool-id`=`Goals`.`Tool-id`
    WHERE `Goals`.`User-id` = '$User_id'
    AND `Tool`.`Section` = '$B'
");

I’ve tried different combinations and almost killed Google googling the hell out of it. Still lost because I am new to this and can’t interpret some of the answers I see on StackOverflow.

Any suggestions?

Also tried this:

$stmt2 = $pdo->prepare("SELECT * FROM `Tool` 
 INNER JOIN `Goals` 
    ON `Tool`.`Tool-id`=`Goals`.`Tool-id`
    WHERE `Goals`.`User-id` = '$User_id'");

And then adding stuff into my page with a PHP while loop. But my While Loop generates extra <li> space that is not necessary:


<? while($row = $stmt2->fetch()) { ?>
<ul>
    <li>
     
            <? 
                $B = "B";
                if ($row["Section"] == $B) {
                            echo($row["Tool-id"]); 
                            echo($row["Section"]); 
                            echo($row["Name"]); } 
            ?> 
     
    </li>   
</ul>
<? 
} 
?>
2

Can you give a bit more detail on what it’s doing that it should not, or what it is not doing that it should? It’s hard to picture the data relationships, figure out the query results based on imaginary data, and then decide what’s “wrong”. Perhaps if you could show some sample data, and a sample of what you want the output to give, that would help.

Incidentally, as you’re using prepared statements, you should be using parameters rather than appending strings into the query. That won’t (probably) alter whether it works the way you want it to, but there are other advantages and as you’ve done the “difficult” bit of adding the prepare() you might as well make the most of it.

I take it that second bit of code is a cut-down version of what you are actually using? That is, if you only want to display data where the section column is “B”, add that condition into the query. Is there an issue in the display when using column names that appear in multiple tables, or does that not matter in this case because the value is the same?

3

I don’t see anything obviously wrong with the query itself, but problems with how you are using prepared statements and the while loop.
The while should be inside the <ul> so you get a single <ul> containing multiple <li>s.

4

That’s right, I only want section B data from one of the tables. But because I’m also trying to select other columns from multiple tables all at the same time, I am not seeing the right output from my db.

I’ll put my while loop inside inside the

    and see what happens.
    And what problems do you see in the prepared statement?

5

Really just what @droopsnoot already mentioned, putting variables directly in the query. It should still work but it’s bad practice and defeats the object (one of) of using prepared statements.
Doing that you may as well run a direct query and suffer the consequence of sql injection attacks without the overhead prepared statements. :shifty:

Though to get the results you want from the query, take it a step at a time, try getting it to work directly in sql to begin with. Once that works, intergate it with pdo and php.

1 Like
6

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