PHP/MySQL: Displaying data from different tables based on a users ID

Question

I have two tables "personal_trainer" and "training_plan". PersonalTrainerID is a foreign key in training_plan. I want to display that when a trainer logs in with their email and password, only the training plans that apply to that ID appears.

However, I am having trouble understanding the logic. I have coded it so that all the information from the training_plan table appears, I cannot created it such that only the rows that apply to the ID are visible to the user. I have made this by the simple sql statement "SELECT * from training_plan". There is a filter textbox to search the table that doesn't effect the code if you're wondering.

I have commented the code to try make it easier to understand. Any help would be greatly appreciated!

           <?php
       if (isset($_POST['search'])) /*This code allows the filter textbox to search the db */
       {
           $valueToSearch = $_POST['ValueToSearch'];
           $query = "select * from training_plan WHERE concat('trainingPlanID', `personalTrainerID`, `clientID`, `trainingType`, `exercise1`, `exercise2`, `exercise3`, `exercise4`, `exercise5`, `exercise6`, 'reps', 'sets', 'description')like'%".$valueToSearch."%'";
           $search_result = filterTable($query);

       }
       else {
           $query = "SELECT * from training_plan WHERE PersonalTrainerID= (SELECT personalTrainerID FROM personal_trainer WHERE email=$_SESSION['user'])"; /*The error that is displayed is 'syntax error, unexpected string after ['*/
           $search_result = filterTable($query);
       }

       function filterTable($query)
       {
           $connect = mysqli_connect("localhost:3308","root","","fypdatabase");
           $filter_Result = mysqli_query($connect, $query);
           return $filter_Result;
       }
       ?>



       <?php /*This displays the data in a table but so far outputs all of the table data */
       while($row = mysqli_fetch_array($search_result))
    {
        ?>
                <tr>
                <td><?php echo $row["trainingPlanID"]; ?></td>
                <td><?php echo $row["personalTrainerID"]; ?></td>
                <td><?php echo $row["clientID"]; ?></td>
                <td><?php echo $row["trainingType"]; ?></td>
                <td><?php echo $row["exercise1"]; ?></td>
                <td><?php echo $row["exercise2"]; ?></td>
                <td><?php echo $row["exercise3"]; ?></td>
                <td><?php echo $row["exercise4"]; ?></td>
                <td><?php echo $row["exercise5"]; ?></td>
                <td><?php echo $row["exercise6"]; ?></td>
                <td><?php echo $row["reps"]; ?></td>
                <td><?php echo $row["sets"]; ?></td>
                <td><?php echo $row["description"]; ?></td>
                <td>
                    <a href="?Delete=<?php echo $row["trainingPlanID"]; ?>" onclick="return confirm('Are you sure?');">Delete</a>
                </td>
                <td>
                    <a href="updateTplan.php?Edit=<?php echo $row["trainingPlanID"]; ?>" onclick="return confirm('Are you sure?');">Update</a>
                </td>
              </tr>
              <?php

You need something like: SELECT * from training_plan WHERE PersonalTrainerID=user_id — Muhammad Bilal
– Muhammad Bilal, Commented Jan 2, 2019 at 10:43
Hi Muhammad, i was thinking the same. However, since the session ID is an email address not in the table, it doesnt find the ID in the training plan table. — Darán
– Darán, Commented Jan 2, 2019 at 10:52
Oh, then you can use subquery like: SELECT * from training_plan WHERE PersonalTrainerID= (SELECT id FROM personal_trainer WHERE email=sessionID) — Muhammad Bilal
– Muhammad Bilal, Commented Jan 2, 2019 at 10:56
Im just getting a small error on the syntax $query = "SELECT * from training_plan WHERE PersonalTrainerID= (SELECT personalTrainerID FROM personal_trainer WHERE email=$_SESSION['user'])"; This is for the session user, any ideas? — Darán
– Darán, Commented Jan 2, 2019 at 11:12
Incidentally, this looks (in part) like a problem of poor design. Any time you find yourself with enumerated columns (above, say, 2) alarm bells should start ringing. — Strawberry
– Strawberry, Commented Jan 2, 2019 at 11:12

Muhammad Bilal · Accepted Answer · 2019-01-02 11:22:14Z

2

Change your query to:

$query = "SELECT * from training_plan WHERE PersonalTrainerID = (SELECT personalTrainerID FROM personal_trainer WHERE email='".$_SESSION['user']."')";

answered Jan 2, 2019 at 11:22

Muhammad Bilal

2,1341 gold badge15 silver badges24 bronze badges

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

1 Comment

Darán Over a year ago

Unbelievable works perfectly! Thanks so much for your help man :)

Collectives™ on Stack Overflow

PHP/MySQL: Displaying data from different tables based on a users ID

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related