I can't query all my data from the database when using DISTINCT

July 25, 2022

I have an SQL query here wherein it returns
Notice: Undefined index: sbvip

because i’m using SELECT DISTINCT, I can’t query the other columns
I need to avoid duplicate records thats why I used DISTINCT.

meanwhile, when I use SELECT * , there are no errors but duplicate records are showing..

below is my sql query

function setvip_sportsbook_model(){
 //connect to the server
$conn= mysqli_connect("localhost","root","","people");
//check the connection
if(mysqli_connect_errno($conn)){
echo "Error";
}
else{
//echo "connect Ok";    
}


 //prepare the sql

$sql= "SELECT DISTINCT merchant_name FROM merchants2";
//execute query
$result= mysqli_query($conn,$sql);
//process result
if($myrow = mysqli_fetch_array($result)){
    $record = array();
do{
    
    $info['merchant_name']=$myrow['merchant_name'];
    $info['sbvip']=$myrow['sbvip'];

    
    $record[] = $info;
    
}while($myrow = mysqli_fetch_array($result));
 }  
   return $record;
 }

can help me what query i need to do for the sbvip? thank you a lot

>Solution :

change sql to

$sql= "SELECT DISTINCT merchant_name, sbvip FROM merchants2";

this will select only records that have a distinct merchant_name and sbvip value.

however, it sounds worrisome that you have duplicate records in your database at all. what if one gets updated but not the other? then you have old version and new versions of the same record in the same database giving inconsistent results.

another tip, your record retrieving loop can be rewritten as this:

$record = array();
while($myrow = mysqli_fetch_array($result)) {
    
    $info['merchant_name']=$myrow['merchant_name'];
    $info['sbvip']=$myrow['sbvip'];

    
    $record[] = $info;
    
}

you can remove the first if($myrow = mysqli_fetch_array($result)){. this will also help in the case that no records are found, preventing the return value from being null.