$sqls="SELECT ul.ul_uid,ul.ul_uname ,ul.ul_email,ul.ul_usertype,u.u_fname,u.u_lname FROM tbl_userlogin AS ul INNER JOIN tbl_user AS u ON u.u_id=ul.ul_uid WHERE ul.ul_uname=? OR ul.ul_email=?";
$stmt=mysqli_stmt_init($conn);
if(!mysqli_stmt_prepare($stmt,$sqls)){
header("location:/signup.php?err=sqlError001");
exit();
} else {
mysqli_stmt_bind_param($stmt,"ss",$uname,$email);
mysqli_stmt_execute($stmt);
mysqli_stmt_store_result($stmt); //returns a boolean
mysqli_stmt_get_result($stmt); //returns nothing
$count= mysqli_stmt_num_rows($stmt);
$res=mysqli_stmt_bind_result($stmt,$uid,$uname,$email,$usertype,$fname,$lname);//returns a boolean
}
if($count>0)
{
$result=mysqli_fetch_assoc($res());
if($result['ul_email']==$email)
{
header("location:../signup.php?err=Email Exist");
exit();
}
else if($result['ul_uname']==$uname)
{
header("location:../signup.php?err=username taken");
exit();
}
}
my previous code:
This worked well. But i was recommended to use prepared statement due to security reasons.
Is there any other function to fetch data in prepared statement instead of mysqli_fetch_assoc($query) ?
$sql="SELECT ul.ul_uid,ul.ul_uname ,ul.ul_email,ul.ul_usertype,u.u_fname,u.u_lname FROM tbl_userlogin AS ul INNER JOIN tbl_user AS u ON u.u_id=ul.ul_uid WHERE ul.ul_uname='$uname' OR ul.ul_email='$email'";
$squery=mysqli_query($conn,$sql);
$count=mysqli_num_rows($squery);
if($count>0)
{
$result=mysqli_fetch_assoc($squery);
if($result['ul_email']==$email)
{
header("location:../signup.php?err=Email Exist");
exit();
}
else if($result['ul_uname']==$uname)
{
header("location:../signup.php?err=username taken");
exit();
}
}
The way I would tackle the problem is to use the following debug function to check to ensure all the expected variables are correct and rectify when incorrect.
The function accepts all types of variables, arrays and objects. Try calling the function followed by die; to halt execution.
<?php
declare(strict_types=1);
error_reporting(-1); // maximum errors and warnings
ini_set('display_errors' , 'true'); // show on screen
function fred($val='nothing passed ???')
{
echo '<pre>'; // adds linefeeds
print_r($val);
echo '</pre>';
}
I don’t do mysqli myself, but is this line correct?
$result=mysqli_fetch_assoc($res());
I’m wondering about the extra set of brackets after $res.
If this is just to check whether you’ve already got a record with that username or password, is there really any need to actually retrieve the information in this specific case? Your query looks for anything where either the username or email supplied is found, so you only really need to know whether zero rows or more than that were found - if it’s more than zero, you’ve got a duplicate. It’s generally not good security to explain to whoever is signing up to your site which of the two bits of information is duplicate - the message would normally be quite vague.
That said, it’s a good thing to learn as it does add security and, sometimes, performance, and it’ll generally be suggested if you post code that doesn’t use the technique.
And, even if you do want to check the fields individually, you retrieve a lot of different columns in that query and then only seem to use two of them - it’s usually better to only retrieve the columns you actually need. Obviously your code might continue to do something we can’t see with the other information, although in theory your code will also only continue if the query returns no results.
As an aside, you’ll have to do something in this line and the other similar one
yeah, the extra brackets are not needed. I removed that and tried and that doesnt make any difference.
I can ignore this validation,no problem but i need data to be fetched in similar cases. that would be very useful.
I retrieved those columns because i need it later. And a URL can contain spaces and the inbuilt function will replace the space with “%20”.
Thank you for your consideration but please help me with fecthing data, maybe i dont need now (by removing duplicate information) but later i really need this(fetch data).
You don’t have to, it’s your choice. But I highly recommend it, you won’t regret it.
Even if you don’t know OOP yet, you can still learn PDO. I used PDO before I learnt OOP and liked it better than mysqli.
Thank you all who supported me . I found the mistake . mysqli_stmt_bind_param($stmt,"ss",$uname,$email); mysqli_stmt_execute($stmt); $result=mysqli_stmt_get_result($stmt); $count= mysqli_stmt_num_rows($stmt); while($row=mysqli_fetch_assoc ($result)) { echo "<br>".$row['u_fname'] ; }
we need to loop it up and then access the records. We just can’t directly use it as $result variable
Good that you’ve found the issue. It’s a little confusing for me because I just went straight from the old mysql calls to PDO, I’ve never done anything with mysqli and there seems to be additional steps to get to the result.
