Please house i don’t know if the caption above describes my intent very well, but this is exactly what I want, to get a column from another table where value is found and then add it to the returned query.
E.g
Table comment
id | postid | commentername | commenterid| hiscomment | date | commentstatus
Table login
id | userid | session | password | time
Now here is my sql
$postid = 3;
$search = $conn->prepare("SELECT * FROM comment
RIGHT OUTER JOIN login
WHERE comment.postid = ?
AND comment.commentstatus = 'published'");
$search->bindParam(1, $postid, PDO::PARAM_INT);
$search->execute();
$result = $search->fetchAll(PDO::FETCH_ASSOC);
print_r($result);
but this is not giving me what i wanted,
is returning all the Columns from login when i only want it to return only the userid column from the login table and add it or merge it to all the Columns from comment table where the postid is 3.
i want it to add the column only on comment where the commentid matches the userid, example is commentid is 15 in comment table and userid id is also 15 in login table.
other rows where the commentid was not found in the login table should be left blank
So all commenters id whose id is found in the login table should have another column called userid added to their own array while those commenters whose userid is not found in the login table should not have the userid column added to their own array.
There are a couple issues I see right off the bat.
You can’t get the result set you’re looking for. You will always get a consistent set of values.
A SELECT * will return ALL of the columns for every joined table. If you want a specific set of fields, those must be specified in the query
If you’re looking to return all comments regardless of whether the user is logged in or not, then your JOIN is backwards. A RIGHT OUTER JOIN will use that table as the basis and only return those rows that have a value in that table. You want a LEFT OUTER JOIN
So your query will need to be more like this:
SELECT postid
, commentername
, commenterid
, comment
, date
, userid
FROM comment c
LEFT OUTER JOIN login l ON c.commenterid = l.userid
WHERE comment.postid = ?
AND comment.comment.status = 'published'
What this will return is something like this. The NULL tells you that you do not have a match.
Depending on what you need the userid for, you could use an IF in the select to return a more meaningful result…
SELECT postid
, commentername
, commenterid
, comment
, date
, IF(userid IS NULL, 'Not logged in', userid)
FROM comment c
LEFT OUTER JOIN login l ON c.commenterid = l.userid
WHERE comment.postid = ?
AND comment.comment.status = 'published'
This approach is more complicated, o rather use the above simpler method and avoid calling the if state in the query since it may result to skipping rows where userid is not logged in the table.
These is perfect, i can then only use a function in php to do stuff like
$i = 0;
foreach ($result as $re) {
$ison = (!empty($result[$i]['userid']) ? ' commenter is online' : 'commenter is offline';
$i++;
}
OR I can use it like this
foreach ($result as $key => $re) {
$ison = (!empty($result[$key]['userid']) ? ' commenter is online' : 'commenter is offline';
}
Thats exactly what I need, thanks alot @DaveMaxwell
yes it looks simple running that stuff in the SQL and passing the values there but you forgot that if the userid is not NULL then it will return digits which is the userid, and for me to make it say commenter is online that will still bring me back to looping and making the Statement of offline or online in the php
Unless i can expressly use
ternary operator in the sql like this
, IF(userid IS NULL ? 'commenter is offline' : 'commenter is online') AS userStatus
FROM comment c
I know the code above is wrong but something that works like an if and else statement in the sql would have been a perfect thing so I don’t end up outputting user id as number in the front end.
So i can just echo $result[$key][userStatus]
knowing that it can only output commenter is online or commenter is offline.
but your above code will output userid id when found, or maybe you made a mistake by repeating commenter is offline twice.
IF(userid IS NULL, 'commenter is offline', 'commenter is offline') AS userStatus
was is supposed to be like this
IF(userid IS NULL, 'commenter is offline', 'commenter is online') AS userStatus
And if not so why was commenter is offline repeated twice and seperated with commas?
Yes it was a mistake (now fixed), and the IF is essentially a tertiary operator, but it uses commas instead of the question mark and the colon. It works out to:
IF(condition, return this if true, return this if false)
So your output would be
0 = {
'postid' => 3,
'commentername' => 'jerry',
'commenterid' => 15,
'comment' => 'i have seen you',
'date' => '2021-08-16',
'userStatus' => 'commenter is online'
}
1 = {
'postid' => 3,
'commentername' => 'mike',
'commenterid' => 2,
'comment' => 'i know am not logged in',
'date' => '2021-08-16',
'userStatus' => 'commenter is offline'
}
then that methods is perfect for my use case, thanks alot.
I still need to ask about this ternary operator that uses commas, does it only work in SQL or can be used anywhere?
and if am to use the question mark ternary operator in a SQL will it work same as the commas own?
That specific syntax is the syntax for an IF statement in MySQL. There may be other languages that use the same syntax, and as you have seen, other languages that do the same with a different syntax. MS SQL server for example appears to use a different syntax for an IF statement.
I’m not sure this is really a “ternary operator”, though I guess the effect is the same.
What do you get if you try that?
Keep in mind that none of this is PHP-related, it’s all MySQL.
It’s a ternary function, which reduces effectively a two line CASE WHEN THEN ELSE to a single line. Still helpful and reductive, but it’s not an operator, no.
The unfurled form of Dave’s IF would be
CASE
WHEN userid IS NULL THEN "commenter is offline"
ELSE "commenter is online"
END AS userStatus
there is a problem on this LEFT OUTER JOIN method, it worked exactly as i wanted it but then it is adding extra row or data to the returned array list, just like a duplicate.
E.g
I have two rows of comments in the comments table with commenterid 15 in row one and commenterid 121 in row two
Then in login table i have only two rows in the login table and they are
row one with userid 15
row two with userid 19
Now the returned array supposed to be 2 key arrays like 0, 1
But am have three arrays 0,1,2
and when I looked closely it seems the query is also counting anything that was in the login and then make a duplicate of same row from comment where the userid match and then return it. so i end up having.
0 = {
'postid' => 3,
'commentername' => 'jerry',
'commenterid' => 15,
'comment' => 'i have seen you',
'date' => '2021-08-16',
'userStatus' => 'commenter is online'
}
1 = {
'postid' => 3,
'commentername' => 'jerry',
'commenterid' => 15,
'comment' => 'i have seen you',
'date' => '2021-08-16',
'userStatus' => 'commenter is online'
}
2 = {
'postid' => 3,
'commentername' => 'mike',
'commenterid' => 121,
'comment' => 'i know am not logged in',
'date' => '2021-08-16',
'userStatus' => 'commenter is offline'
}
Please what could be the cause? as all i wanted was just the two rows in the comments merged with values from not a duplicate row.
Yes you are right but that is how the workflow was designed, the comments list may have more than 20 comments from jerry, because the table is created to store only comments whereby you can see 20 of Jerry’s comment for post id 3 and also 8 comments by Abel for post if 3 and also all the comments are stored in a row showing commenterid, postid which is the post they commented on, and the comments they made and the time at which each comment was made.
Then we only use the login table to user other users if the person who posted this comment is currently logged in.
So we call the login table.
My odd way of getting it done is
Query the comments table and pull all comments made for post 3
loop through it to use commenterid to search login table and if found then user is login. but such method is really odd and unprofessional.
thats why i thought of joining tables.
See my odd way of doing it but am just pitying my server to have it loop through the login table for each comment, i think is not scalable that way especially if comments are 1000 plus.
//test things
function commenter_is_login($commenterid){
global $con;
$search = $con->prepare("SELECT userid FROM login_table WHERE userid = ?");
$search->bindParam(1, $commenterid, PDO::PARAM_INT);
$search->execute();
$result = $search->fetch(PDO::FETCH_ASSOC);
$find = (!empty($result)) ? 'commenter is online' : 'commenter is offline';
return $find;
}
$postid = 3;
$search = $con->prepare("SELECT * FROM comment_table WHERE postid = ?");
$search->bindParam(1, $postid, PDO::PARAM_INT);
$search->execute();
$result = $search->fetchAll(PDO::FETCH_ASSOC);
foreach($result as $key => $value){
$commenterid = $result[$key]['commenterid'];
$comment = $result[$key]['comment'];
$commenterstatus = commenter_is_login($commenterid);
echo '
Commenter is: '.$commenterid.'<br>
Comment made : '.$comment.'<br>
status : '.$commenterstatus;
}
Perhaps I didn’t make myself clear. Unless there are two records on the login table for userid 15, then there must be two comments on the comments table with the same text on it. That’s the only way you’d get duplicate results.
Ohhhh…you know what, I’m betting it’s the login table. That’s a session table…that’s going to return multiples depending on however many sessions there are for the user on the table.
You can get around that by adding the word distinct right after the word select
Sounds like it but the session can never be two in my login table, only the userid maybe be two or may, am working on deleting user userid if any login ocurred but thats for a reason that made me leave it like that, possiblity of having more same userid is there but can never have same session details in a row.
This worked, adding distinct worked like charm.
i need to refactor my code and see where I should be using more of JOINS bcs is really making codes simpler and effective.