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
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"
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?
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.
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).
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.
@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)
What I am doing wrong here?
Any reason you are preferring LIKE over strict comparison operator?