Execute a query, use value as a variable and run another query and then print it?

Question

I've two tables, users and coach_to_trainee. User can have multiple coaches, and the data is stored in coach_to_trainee columns coach_id and trainee_id.

I'm using coach_to_trainee table to print out data for the user, so he/she can simply see who is his/her coach.

<?php
              $user = $_SESSION['login']['id'];
              $q = "SELECT * FROM coach_to_trainee WHERE trainee_id='$user'";
              $coachid = $db->prepare($q);
              $coachid->execute();

              while($row = $coach->fetchObject()){
                $coachid = $row->coach_id;
                echo '<li><a href="?coach&id='.$coachid.'">'.INSERT_COACH_NAME.'</a></li>';
              }


              ?>

However, this will only return the ID of the coach, and I need to return the name also, from table users.

How I'm supposed to do another query inside while() and use $coachid to find the correct user?

why don't you actually prepare your query? Why don't use prepared statements? — Your Common Sense, Jan 24 '13 at 13:14
@YourCommonSense I tought that I'm using them? I started using pdo about 2 weeks ago so I've no idea if I'm doing something wrong — , Jan 24 '13 at 13:16
@YourCommonSense How does `$coachid = $db->prepare($q);` differ from `$db->prepare("SELECT * FROM users where id=?"); then?` What if someone would do drop table? — , Jan 24 '13 at 14:34

shadyyx · Accepted Answer · 2013-01-25T11:16:38.910

0

You have to use JOIN mysql clause, like this one:

SELECT u.name, u.id 
FROM users u
    LEFT JOIN coach_to_trainee ctt
        ON u.id = ctt.coach_id 
WHERE ctt.trainee_id = {$user}

You should be done with this one ;-)

Then in Your PHP use the selected values:

while($row = $coach->fetchObject()){
    echo '<li><a href="?coach&id='.$row->id.'">'.$row->name.'</a></li>';
}

edited Jan 25 '13 at 11:16

answered Jan 24 '13 at 13:11

shadyyx

15,825
6
60
95

Next time I'll open my mysql manual instead of thinking difficult in php. Thanks! – Jan 24 '13 at 13:12
I just noticed that $row->coach_id doesn't print anything, any ideas? – Jan 24 '13 at 17:19
@ChristianNikkanen It is `$row->coach_id` no more, since You edited the query it should be **`$row->id`** then!!! – shadyyx Jan 25 '13 at 10:03
Ah, I feel so stupid now! The whole join clause is pretty complicated to me. – Jan 25 '13 at 11:15
@ChristianNikkanen I have edited the answer (PHP part) to match the query. In this case You could do `SELECT u.name, u.id`, then call it like `$row->name` and `$row->id` or You could do `SELECT u.name, ctt.coach_id` and then it would be as before - `$row->coach_id` and `$row->name`. – shadyyx Jan 25 '13 at 11:18
Note: To secure your site against XSS, you should use `htmlspecialchars()` before you output a variable which can be influenced by the user. Or if an value has to be an integer you should cast it. In your example: `echo '
'. htmlspecialchars($row->name) . '

MarcDefiant

Jan 25 '13 at 11:22

@Mogria is it even possible as I'm using pdo? And the statement is prepared? – Jan 25 '13 at 11:34

@ChristianNikkanen Yes, this has nothing to do with SQL. Lets say an user enters the username ``. Without using `htmlspecialchars()` the script would be executed. For more info: http://stackoverflow.com/questions/1996122/how-to-prevent-xss-with-html-php – MarcDefiant Jan 25 '13 at 11:43

yeah, but doesn't pdo pretty much block complicated scripts by quoting the userinput? – Jan 25 '13 at 11:49

Execute a query, use value as a variable and run another query and then print it?

1 Answers1