Mysqli UNION returning only primary key

PHP
1

DISCLAIMER: I’ve been reassured by the gracious @mittineague that in spite of flawlessly winning the Sitepoint’s most despised user of the month, I am still free to ask questions on here without fear of being brutalized and coming under heavy fire by the indefatigable in-house task force being leashed by a negligible strand of thread.

My question has to do with fetching data from two tables using mysqli unions. The problem is that data is only fetched from the primary key/query and the second statement is ignored. If I switch the places of both statements, the new primary query returns results and the 2nd one returns null.
I want to select five columns; one common column and two different rows from both tables then scoop them into an array like

static function fetch_me() {
		$conn = new mysqli("st", "root",  "\57", "_db");
		$sess = $_SESSION['username'];
		$sql = "SELECT username, NULL AS profile_pic, NULL AS bio, email, password FROM users WHERE username='$sess' UNION ALL SELECT username, profile_pic, bio, NULL AS email, NULL AS password FROM user_info WHERE username='$sess'";
	if ($x = $conn->query($sql)) {
		return $x->fetch_assoc();
	}
	}

I’ve tried using it without the ALL clause plus a few other experimentations but unfortunately unable to strike gold yet. All the examples I’m seeing online are mysql related, none shows an explicit php example. The above query runs fine on phpmyadmin and when I request the PHP code from that query, it returns a sql that only returns the primary query as well

2

I think you want to use a slightly different query
UNION queries don’t like NULL like in your example code
Try this

SELECT users.username
  , users.email
  , users.password
  , user_info.profile_pic
  , user_info.bio
FROM users
INNER JOIN user_info
ON users.username LIKE user_info.username
WHERE users.username LIKE "$sess"
3

It returns null. Remember I’m using it with PHP’s fetch_assoc and not a standalone SQL string

4

SQL NULL and PHP null are two completely different things - just like ‘to’ and ‘two’

5

Yes! I just noticed that. The null returned must be a SQL null since it’s is all caps and works out PHP. I ran the exact same string on phpMyAdmin and it worked seamlessly returning the exact way I intend it to. In the phpMyAdmin interface, I had to first strip the quotation marks I put before passing it into PHP’s query function. How do I run it as php?

6

I think Binding it should work OK. Just turn the query into a prepared Statement.

7

First, they are pretty much the same. Second, I think that the OP is referring to the empty query result, not values in the returned row.

Either way I would like to see a clarification for this allegedly returned null - which particular variable contains it.

8

I tried this

$prep = $conn->prepare("SELECT users.username, users.email, users.password, user_info.profile_pic, user_info.bio FROM users INNER JOIN user_info ON users.username LIKE user_info.username WHERE users.username LIKE $sess");
		$prep->execute();
		$prep->bind_result($username, $email, $password, $profile_pic, $bio);
		return $prep->fetch();

Returns a boolean. Please what am I missing?

9

unknown value (NULL in SQL) and no value (null in PHP) are almost never the same thing. No value is just one of an infinite number of possible values that NULL represents.

10

Althoigh I respect your theoretical musings, but I have a feeling that they are of rather little use in a practical code.

For example, for a mysql null all you can get in php is a php null, despite so drastic theoretical difference.

1 Like
11

not if you assign a different value to be used as the output from the SQL for a NULL value - my preference would be to return the text string ‘unknown’ so as to avoid confusing it with the similarly named but completely different value in PHP.

The IFNULL() function in mySQL (and equivalents in other SQL variants) is provided for that specific purpose (to assign whatever value you intend to pass back for NULL).

12

@felgall @colshrapnel Pls sirs my question is still hanging

1 Like
13

Waxing Offf-Topic

It’s more than theoretical, it’s fact. Try

<?php
$var = NULL;
if ($var == NULL) { echo "yes we have no bananas"; }
?>

Now try a query with this in it

.... WHERE field = NULL
14

If $sess isn’t a number it needs to be enclosed in quotes.

1 Like
15

Well, first of all, let’s resolve the silly confusion for the OP, who is selecting two rows, but fetching only one.

Now to the exciting topic of nulls. Honestly, I don’t really understand your logic, @felgall

Instead of using a really unique null value, you are assigning a string, which indeed could be confused.

16

that’s invalid SQL as nothing is ever equal to NULL - not even NULL - you need to use IFNULL() to compare against NULL in mySQL and then the second parameter is the value to substitute for NULL - other variants of SQL use ISNULL() or COALESCE() or other functions instead - none of them use = NULL as nothing is ever equal to NULL whereas two PHP fields can both be null and therefore equal.

17

@Mittineague, mind if I ask you a couple questions?

mysql> select name, null as foo from users where name='user' 
union select null as name, name as foo from users where name='user';
+------+------+
| name | foo  |
+------+------+
| user | NULL |
| NULL | user |
+------+------+
2 rows in set (0.00 sec)
  1. What I am doing wrong here?
  2. Any reason you are preferring LIKE over strict comparison operator?
18

yep! That was an oversight on my part. Valid LIKE should be wrapped in single quotes.

oooooooooooooo…This does not work


	$row = array(username, email, password, profile_pic, bio);
	$new_arr = array();
		$sess = $_SESSION['username'];
		$sql = 'SELECT users.username, users.email, users.password, user_info.profile_pic, user_info.bio FROM users INNER JOIN user_info ON users.username LIKE user_info.username WHERE users.username LIKE "$sess"';
		
		$prep = $conn->prepare("SELECT users.username, users.email, users.password, user_info.profile_pic, user_info.bio FROM users INNER JOIN user_info ON users.username LIKE user_info.username WHERE users.username LIKE '$sess'");
		$prep->execute();
		$prep->bind_result($username, $email, $password, $profile_pic, $bio);
while ($b = $prep->fetch()) {
$new_arr[] = array_pop([$username, $email, $password, $profile_pic, $bio]);
}
array_reverse($new_arr);
		for ($i = 0; $i < count($new_arr); $i++) {
		foreach ($row as $key=> $value) {
		$value = $new_arr[$i];
		}
		}
		return $row;

How do I grab the values and return something similar to an associative array?

19

I agree that this statement, although inapplicable to the question asked, is valid by itself.

20

The use of array_pop() in this code doesn’t make sense to me:

$new_arr[] = array_pop([$username, $email, $password, $profile_pic, $bio]);

Might array_push() be more suitable?