Execute an SQL query inside another SQL query

Question

Ok, I assume that the title is not the best title, but let me explain my problem: I'm creating a website that needs to show posts of people (anyway), and I have to show their gravatar's profile picture, so this what I did:

<?php 
            function get_gravatar( $email, $s = 80, $d = 'mm', $r = 'g', $img = false, $atts = array() ) {
                $url = 'https://www.gravatar.com/avatar/';
                $url .= md5( strtolower( trim( $email ) ) );
                $url .= "?s=$s&d=$d&r=$r";
                if ( $img ) {
                    $url = '<img src="' . $url . '"';
                    foreach ( $atts as $key => $val )
                        $url .= ' ' . $key . '="' . $val . '"';
                    $url .= ' />';
                }
                return $url;
            }
            require("db.php");
            $sql = "SELECT * FROM posts ORDER BY date DESC";
            foreach ($db->query($sql) as $row) {
                // var_dump($row);
                $user = $row['user_id'];
                $sql_user = "SELECT email FROM users WHERE id = $user";
                foreach ($db->$sql_user as $row_user) {
                    var_dump($row_user);
                    echo "<img src=\"".get_gravatar($row_user['email'])."\"/>";
                }
                echo "<h2>".$row['title']."</h2><br/>";
                echo "<p>".$row['content']."</p><br/>";
            }

But, it doesn't work (well, it works, but it doesn't shows me the profile picture of the user, only the post). So, I think the problem is that I can't call 2 times the variable $db at the same time, but I'm not sure, so that's m=why I'm asking if there is a way to fix my problem or to select 2 tables at the same time.

see your second foreach you miss to put query function

krishn Patel
– krishn Patel

2017-03-28 07:06:53 +00:00
Commented Mar 28, 2017 at 7:06 — krishn Patel
– krishn Patel, Commented Mar 28, 2017 at 7:06

Lloric Mayuga Garcia · Accepted Answer · 2017-03-28 07:35:50Z

2

you can short your logic

$sql="SELECT u.email "
                    . "FROM posts AS p "
                    . "LEFT JOIN users AS u "
                    . "ON u.id=p.user_id "
                    . "ORDER BY p.date DESC";

edited Mar 28, 2017 at 7:35

answered Mar 28, 2017 at 7:11

Lloric Mayuga Garcia

1563 silver badges4 bronze badges

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

4 Comments

dev.arguiot Over a year ago

Could you develop? I mean, could you explain how I can use it?

waltron Over a year ago

This is better, as you won't be running multiple queries in your inner for loop, which adds overhead. Also I think it should be "SELECT p.user_id,u.email " in the first part. u and p mixed up.

Lloric Mayuga Garcia Over a year ago

just call the column you want, in you case is, $row['email'] , this is already relate in users data.also you can get $row['user_id'] in single result set

Lloric Mayuga Garcia Over a year ago

@waltron aw, your right, :) its mistake sample, or maybe we can remove taht, because only needed is email

Jens · Accepted Answer · 2017-03-28 07:07:34Z

1

use join

SELECT * FROM users join posts  on users.id =Posts.user_id ORDER BY date DESC

answered Mar 28, 2017 at 7:07

Jens

69.6k15 gold badges107 silver badges133 bronze badges

Comments

ScaisEdge · Accepted Answer · 2017-03-28 07:08:10Z

1

you don't apply query to sql string in second foreach

  foreach ($db->query($sql_user) as $row_user) {

answered Mar 28, 2017 at 7:08

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

2 Comments

dev.arguiot Over a year ago

Your solution works, but I prefer the join one, thanks :)

ScaisEdge Over a year ago

you prefer a solution that is a suggestion but not the solution for you question .. ? .. normally the soution is acceppeted .. and the suggestion are marked as useful ..

Jens · Accepted Answer · 2017-03-28 07:14:53Z

1

see your second foreach you miss to put query function

foreach ($db->query($sql) as $row) {



foreach ($db->query($sql_user) as $row_user) {

edited Mar 28, 2017 at 7:14

Jens

69.6k15 gold badges107 silver badges133 bronze badges

answered Mar 28, 2017 at 7:08

krishn Patel

2,5991 gold badge22 silver badges31 bronze badges

1 Comment

dev.arguiot Over a year ago

Your solution works, but I prefer the join one, thanks :)

NID · Accepted Answer · 2017-03-28 07:25:21Z

1

You could just use JOIN

JOIN clause is used to combine rows from two or more tables, based on a related column between them.

edited Mar 28, 2017 at 7:25

answered Mar 28, 2017 at 7:07

NID

3,3241 gold badge21 silver badges29 bronze badges

7 Comments

Jens Over a year ago

join and Union are very different

NID Over a year ago

@Jens Yeah they are different... UNION is used to combine the result from multiple SELECT statements into a single result set. while JOIN clause is used to combine rows from two or more tables, based on a related column between them... he could use any of this to achieve his result...he wanted to select 2 tables at the same time..

Jens Over a year ago

No he can not. You can not solve a Problem with very different functions

NID Over a year ago

i was suggesting to use either one of this

Jens Over a year ago

Yes but if OP using Union, he get never the result he/she wants

|

Collectives™ on Stack Overflow

Execute an SQL query inside another SQL query

5 Answers 5

4 Comments

Comments

2 Comments

1 Comment

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

4 Comments

Comments

2 Comments

1 Comment

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related