PDO union select results nothing

Question

I need to select name, surname from users where username = column following inside network_follow table

the query:

"SELECT * FROM network_follow WHERE following = :f UNION SELECT name, surname FROM users where username = (following)";

php code:

 $f = $conexao_pdo->prepare('SELECT * FROM network_follow WHERE following = :f UNION SELECT name, surname FROM users where username = (following)');
    $f->bindParam(':f', $db_user);
    $f->execute();

    while($values = $f->fetch(PDO::FETCH_ASSOC)){
        $fvalue = $values['follower'];
        $fname = $values['name'];
        echo '<center> 
    <div><a href=""> <img class="rounded-circle"  width="45" src="'.get_user_picture($fvalue).'"></img>&nbsp;@'.$fvalue.'</a>
     <span style="padding-left: 15px;">'.$fname.'<span>
     <div></center>';

}

Resulting to me $fvalue just fine, but not $fname

for some reasons union select is not working, can somebody help?

Doing a union with `*` on one SELECT and `name, surname` on the other doesn't seem right. What do these tables look like? Have you attempted to run this query outside of PHP? — Jonnix, Aug 06 '19 at 20:37
I think what you actually want is a `JOIN` instead of `UNION`. — rickdenhaan, Aug 06 '19 at 20:40
table `network_follow` have column `following` , `follower` , table `users` have `name`, `surname`, `username` columns — , Aug 06 '19 at 20:40
@Developer so how exactly does `following` match up with `name` for example? Seems likely @rickdenhaan is right that you don't actually want a UNION at all. — Jonnix, Aug 06 '19 at 20:42
Something along the lines of `SELECT nf.*, u.name, u.surname FROM network_follow nf INNER JOIN users u ON u.username = nf.following WHERE nf.following = :f`? Though if `following` is really the string username, you might want to normalise your schema a bit more. — Jonnix, Aug 06 '19 at 20:47
I don't have name, surname inside `network_follow` table, only following/followers, the columns username, name, surname is inside `users` table — , Aug 06 '19 at 20:51
Then your comment doesn't make sense. My guess is you don't know about SQL aliases, but you should look those up yourself along with joins. — Jonnix, Aug 06 '19 at 20:55
should I also add users table inside my `PDO::FETCH_ASSOC` ? because when I echo `$fname = $values['name']´ it shows noting on name but shows `follower`just fine — , Aug 07 '19 at 02:23

score 1 · Accepted Answer · answered Aug 06 '19 at 20:55

1

A UNION is used to select similar data from multiple tables.

A JOIN is used to combine related data from multiple tables.

Examples:

SELECT `city`, `sights` FROM `places_i_visited`
UNION
SELECT `city`, `sights` FROM `places_i_want_to_visit`

+----------------+--------+
| city           | sights |
+================+========+
| Los Angeles    | 1537   |  -- from `places_i_visited`
| Rio de Janeiro | 829    |  -- from `places_i_visited`
| Moscow         | 1822   |  -- from `places_i_want_to_visit`
+----------------+--------+

SELECT `city`, `sights`, `visits`.`date_visited`, `visits`.`duration`, `visits`.`sights_seen` FROM `places_i_visited`
INNER JOIN `visits` ON `visits`.`city_id` = `places_i_visited`.`city_id`

+----------------+--------+--------------+----------+-------------+
| city           | sights | date_visited | duration | sights_seen |
+================+========+==============+==========+=============+
| Los Angeles    | 1537   | 2017-06-25   | 14       | 25          |
| Rio de Janeiro | 829    | 2018-11-04   | 7        | 12          |
+----------------+--------+--------------+----------+-------------+

In your case, you want to take information from your network_following table and combine it with the user's name from the users table. A JOIN is more appropriate here:

SELECT network_follow.*, users.name, users.surname FROM network_follow
INNER JOIN users ON users.username = network_follow.following
WHERE following = :f

I'm using an INNER JOIN here to make sure you only see entries that have results in both tables.

If you want to see if you have inconsistent data in your database, you could use a LEFT JOIN instead. That way, you'll see all entries from the network_follow table. If there is no such user in the users table, you will still see those entries but the name and surname columns will be NULL.

answered Aug 06 '19 at 20:55

rickdenhaan

10,857
28
37

I don't have `name`, `surname` inside `network_follow` table, only following/followers, the columns `username`, `name`, `surname` is inside users table , the where clause is username = following column, for some reasons your code retuns blank – Aug 06 '19 at 21:05
My code is explicitly selecting those columns from the `users` table. If you're getting a blank result, `var_dump($f); die();` immediately after `$f = $conexado_pdo->prepare(...);`. If that outputs `bool(false)` make sure [you can see any errors](https://stackoverflow.com/a/32648423/1941241) and tell us what's wrong. – rickdenhaan Aug 06 '19 at 21:11
does it's right? it works fine but whan I echo `$fname = $values['name'];` nothing show – Aug 07 '19 at 01:25
var dump shows `object(PDOStatement)#2 (1) { ["queryString"]=> string(155) "SELECT network_follow.*, users.name, users.surname FROM network_follow INNER JOIN users ON users.username = network_follow.following WHERE following = :f" }` – Aug 07 '19 at 01:28
Then the query is executing fine but your database may not contain what you think it does. Are there any matching rows at all (`var_dump($f->rowCount());`)? If not, you need to check the value of `$db_user` against what's actually in your database. If there are matching rows, `print_r($values);` to see what each row contains. – rickdenhaan Aug 07 '19 at 19:34
running `print_r($values);` I get this `( [following] => user1 [follower] => user2 [name] => [surname] => )` but both users are in db value, maybe something in the query is not selecting – Aug 09 '19 at 02:38
At this point, it's impossible to say without looking at your database. If you're using an `INNER JOIN`, this can only happen if `users` contains a user with `username = 'user1'` where `name` and `surname` are empty. If you're using a `LEFT JOIN`, this can mean the same thing or that `users` does not contain a user with `username = 'user1'` – rickdenhaan Aug 09 '19 at 14:36

score 0 · Answer 2 · answered Aug 06 '19 at 20:50

0

You can use the following query instead of use UNION statement:

SELECT t1.*
    ,t2.name
    ,t2.surname
FROM network_follow AS t1
INNER JOIN users AS t2 ON t1.following = t2.username
WHERE t2.following = ':f'

Although you need to think about any ID's columns, Primary and Foreign keys to normalize a bit your tables.

answered Aug 06 '19 at 20:50

Francisco Sevilla

94
1
7

when I echo `$fname = $values['name']´ it shows nothing but the query is executed, I changed to `SELECT t1.* ,t2.name ,t2.surname FROM network_follow AS t1 INNER JOIN users AS t2 ON t1.following = t2.username WHERE t1.following = :f'` – Aug 07 '19 at 01:38

PDO union select results nothing

2 Answers2