Mysqli UNION returning only primary key

[nmeri17]

I figured array_pop in that while loop will return the same element each time so here is the modified version of that function

	
	$row = array('username', 'email', 'password', 'profile_pic', 'bio');
	$new_arr = array();
	$sess = $_SESSION['username'];
		
	$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);
	$ret_vals = [$username, $email, $password, $profile_pic, $bio];
	
	for ($j = array_pop($ret_vals); $j < count($ret_vals); $j++) {
	while ($prep->fetch()) {
		$new_arr[] = $j;
	}
	}

	$new_arr = array_reverse($new_arr);
	$row = array_combine($row, $new_arr);
	return $row;
}

This however does not work because for some reason, new_arr turns up empty with two elements, each having value NULL

[droopsnoot]

I’m struggling with the array_pop part of things, perhaps I’m not understanding what you are actually trying to achieve. At the point in the code above where you create $ret_vals, surely that will be empty because those variables have no content - you’ve assigned them as the destination for your query results, but you haven’t called fetch().

Can you just display what comes back from the query loop, then describe the structure of the array you want it in? Maybe using an array to retrieve the results with fetch_assoc() would make it easier to combine it into an array.

[nmeri17]

I’ve resolved everything now. Thanks guys. Here’s what I changed

	while ($prep->fetch()) {
	$ret_vals = [$bio, $profile_pic, $password, $email, $username];

	for ($j = 0; $j < count($row); $j++) {
		$new_arr[$row[$j]] = array_pop($ret_vals);
	}
	}

	return $new_arr;

@droopsnoot I am trying to query two tables and make an associative array out of the returned result and the above write-up achieves that. thanks for trying to help.

[DaveMaxwell]

@nmeri17

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’"

Is there a reason you’re not just using a join? It seems like a much cleaner (and correct) approach.

SELECT u.username
     , ui.profile_pic
     , ui.bio
     , u.email
     , u.password
  FROM users u
 INNER JOIN user_info ui ON u.username = ui.username

[droopsnoot]

OP is using a JOIN now.

Glad you have it sorted nmeri.

[colshrapnel]

He is probably using some outdated manual, where such a code madness is described as the only way to get the data from mysqli. While there are simpler ways. A simplest one (as he is not using prepared statements anyway) would be

$sess = $conn->real_escape_string($_SESSION['username']);
$res = $conn->query("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'");
return $res->fetch_assoc();

[nmeri17]

Killjoy! You were here the entire 15+ hours this question was active and provided no meaningful input nor as you said “sane code”. You sat by the wayside and watched on. At some point, you even tried derailing my thread. Now I’ve managed to resolve it using clues from one or two people here, you’re stringing your result along. Are you trying to help or just show what a wicked person you are??

[nmeri17]

@colshrapnel

->mysqli_fetch_assoc()

and seriously, this?? wherever you got this method from? smh

[nmeri17]

@Mittineague

INNER JOIN user_info
ON users.username LIKE user_info.username
WHERE users.username LIKE “$sess”

I understand ON is a synonym for WHERE; is there a reason you did’t use WHERE and used LIKE instead of just the equality sign?

[DaveMaxwell]

@nmeri17

I understand ON is a synonym for WHERE; is there a reason you did’t use WHERE and used LIKE instead of just the equality sign?

The ON specifies the criteria by which the two tables are matched up. It’s meant to tell the DBMS how the tables compare and isn’t meant as a manner to filter out all the results. Basically a first sort.

The LIKE should only be used if you are doing a generic match, so there is typically a % at the front or end of the criteria. In this case, since you’re doing an exact match, the equality sign would be more appropriate.

[nmeri17]

I changed the string to this and it still parsed. I think the situation doesn’t call for LIKE
"SELECT users.username, users.email, users.password, user_info.profile_pic, user_info.bio FROM users INNER JOIN user_info ON users.username=user_info.username WHERE users.username='$sess'"

[Mittineague]

@nmeri17

used LIKE instead of just the equality sign?

Personal preference.
I use equal for numeric comparisons and LIKE for string comparisons.

True, in the majority of cases “somestring” will equal “somestring”

When using = MySQL compares the numeric value of the string characters.
And as long as character encoding and collation are the same, no problem.

When LIKE is used it allows MySQL to recognize characters that may look the same to us but have different codepoints (different numeric values).

As long as you are always working with the same character encoding and collation you should be fine using = for string comparisons.

[colshrapnel]

@Mittineague

When using = MySQL compares the numeric value of the string characters.

I am afraid you are wrong here.
I suppose you are talking of collations , and I can assure you that = operator is taking collations into account as well, using not character codes but collation tables for comparison.

And as long as character encoding and collation are the same, no problem.

Well, first of all, such a case just should never be. A column’s charset should mark the actual data encoding. Period. Otherwise it’s an error, and nothing will be working good.
And second, in such a case LIKE won’t be of any help either. as it’s comparing strings exactly the same way as =, save for parsing wildcars.

So I suppose that you are using LIKE due to some confusion.
Taking into account the apparent danger of such a misuse, I strongly advise you to quit that practice and start using LIKE only on purpose - for the wildcard search.

[Mittineague]

So you are saying the MySQL documentation is incorrect or that I am misinterpreting it?
https://dev.mysql.com/doc/refman/5.7/en/string-comparison-functions.html

Per the SQL standard, LIKE performs matching on a per-character basis, thus it can produce results different from the = comparison operator:
…
In particular, trailing spaces are significant, which is not true for CHAR or VARCHAR comparisons performed with the = operator:

[Lemon_Juice]

@colshrapnel, @Mittineague, you are both half wrong and half right

Of course, the = operator does not compare strings numerically so it can be used just fine - and I think it should - in theory…

@colshrapnel

And second, in such a case LIKE won’t be of any help either. as it’s comparing strings exactly the same way as =, save for parsing wildcars.

Not true, there are differences, one of which is the trailing spaces. Another difference - in some collations certain accented characters compare differently to other (similar) characters (or character pairs) depending on whether = or LIKE is used.

It is rare that these differences matter in real usage but sometimes they may be significant.

[quote=“colshrapnel, post:34, topic:222213”]
I strongly advise you to quit that practice and start using LIKE only on purpose - for the wildcard search.[/quote]

My logic also tells me to use LIKE only for wildcard search and = for normal comparisons because I assume such is their intended purpose. However, due to the weird rules that exist in mysql I don’t think people make a mistake if they use them differently. In my opinion LIKE should really be the same as = except for the wildcard capability and any comparison dialects should have their own separate keywords (just like the COLLATE keyword). And the trailing spaces thing - it’s a little insanity on mysql part - who invented that and what for? And why trailing and not leading spaces? We need to adapt to the mess of mysql environment.

BTW, this is going off topic, I hope the OP doesn’t mind!