I am trying to follow this tutorial on how to do a left join and I have the following codes… I can get it to work only using these codes…I am trying to understand what he is doing at 7:19 but I guess that he is using pdo and not mysqli? I am trying to learn about mysqli… thanks!
I don’t think so. It looks like he is using object notation instead of array notation.
Even if you never write OOP code it is a good idea to learn the syntax so that you can use others code where you have no choice but to use object notation. Anyway, don’t let this distract you from following the tutorial, it’s focus is on JOINs. So even though the syntax will look a bit different you should still be able to learn from it.
I can get my left join to work by doing these codes but the problem now is that how would I grab the username from the login? At the moment, it will only work if I match to a particular name in my database…
<?php
include_once 'includes/dbh.php';
$sql = "SELECT users.user_first, user_access.access_type as accesslevel FROM users
LEFT JOIN user_access ON users.user_access = user_access.id;
";
$result = mysqli_query($conn, $sql);
$resultCheck = mysqli_num_rows($result);
if($resultCheck > 0) {
while($row = mysqli_fetch_assoc($result)) {
if ($row['accesslevel'] == 'primer level' && $row['user_first'] == 'Mervin') {
echo 'Welcome to primer level contents';
} else {
echo 'You do not have permissions';
}
}
}else {
echo 'No result';
}
I am only trying to grant access to the user if they are on primer level only… based on their user id…
I can’t see anything in that code that is checking permission for a specific user, it’s just getting the information for all users and then checking each one. That last piece of code retrieves both user rows, then runs through each of them in turn, but in no specific order because you don’t have an ORDER BY clause.
In this case it appears that it gets the two users in the order they are displayed in the image, sees that the first one has a value of ‘2’ in the user_access column, this doesn’t equate to primer level in your other table, so it does a redirect and never gets to the second record.
I thought that if I do this… it will see if ($row[‘accesslevel’] == ‘primer level’) and that should work? Only users with the primer level can access this page?
Well, you just need to specify which user you want to check the permission levels for. At the moment, you’re checking all of them, and it just happens that the first one it recovers isn’t a “primer level” user, so it redirects.
If it’s part of a login system, then you should only retrieve the information you need for the user that is trying to log in, with a WHERE clause in your query.
I am using my phone now so can’t copy and paste but initially i did add where user_uid=‘$uid’; but i got something about undefined $uid. I am doing a login system so i am not sure how to pass the information that the user has used while logging in to this page called primer.php.
Should I have used inner join instead? Because an user always need some kind of permission?
It’s nothing to do with the kind of join you use. You said earlier that the first bit of code to just display the user details works OK, so the query must be alright. The issue is that you’re just looking at permissions for all users, not just the one who is trying to log in.
Where were you getting the value of $uid from?
Is that not part of the form? So it would pass the form variables through to this page in either $_GET or $_POST arrays, depending on how your form is configured?
In your database table user_access you don’t have id of user stored in that table so i don’t see a reason to join tables.
You can simple check like this
for multiple users
// for multiple users if you don't have ID
$query = mysqli_query($conn, "SELECT * FROM users");
$resultCheck = mysqli_num_rows($query);
// if there is row returned
if ($resultCheck > 0) {
// loop to get user access
while ($row = mysqli_fetch_assoc($query)) {
if ($row['user_access'] != 2 && $row['user_activate'] != 1) {
header("Location: index.php?signup=accessdenied");
exit();
} else {
echo ' Welcome to the content';
}
} // while
}
and for a single user, you must provide user_id
// for single user if you got ID
$query = mysqli_query($conn, "SELECT * FROM users WHERE id = '".$id."'");
$resultCheck = mysqli_num_rows($query);
// if there is row returned
if ($resultCheck > 0) {
// loop to get user access
while ($row = mysqli_fetch_assoc($query)) {
if ($row['user_access'] != 2 && $row['user_activate'] != 1) {
header("Location: index.php?signup=accessdenied");
exit();
} else {
echo ' Welcome to the content';
}
} // while
}
Maybe I misunderstood what the joined table is trying to do… perhaps i only need the joined table for my own information but i guess i should just select user_uid and match it with user_access=1…
Yes, the join is just to allow you to display a name against each of the permission levels. Handy if you insist on having a value of “2” meaning “level 1”, for example, but not required for actually performing the check.
