Proper Formatting for this query

[snowymasta]

Hi guys, 

I'm starting to get back into coding for a hobby and wondering is there a better way of doing these php and mysql calls? they all work but not sure if it was the efficent way or is there others that everyone else uses?

 

//connection for user details lookup
$sql0 = "SELECT uid, fullname, emailaddress, created_at FROM MK_users WHERE uid={$_SESSION['id']}";
$result0 = $conn->query($sql0);

//connection for user against role acceess
$sql2 = "SELECT role_uid, role_bid, role_access, access_created_at FROM MK_role_access WHERE role_uid={$_SESSION['id']}";
$result2 = $conn->query($sql2);

 while($row2 = $result2->fetch_assoc()) {
    $_SESSION['role_bid'] = $row2["role_bid"];
  }

//connection for role access against business lookup
$sql3 = "SELECT businessname, account_type, website, main_address, logo FROM MK_baccounts WHERE bid={$_SESSION['role_bid']}";
$result3 = $conn->query($sql3);

while($row3 = $result3->fetch_assoc()) {
  
    $_SESSION['businessname'] = $row3["businessname"];
  }
 

[Barand]

Use JOINs with a single query to get the information you want instead of running multiple queries

If you are only retrieving a single record, don't use a while loop - a single fetch will suffice

Use prepared statements - don't put variables directly into the SQL string

<?php

/*
    MK_users                   MK_role_access             MK_baccounts
    +-----+---------------+    +----------+----------+    +-----+------------------------+                    
    | uid | fullname      |    | role_uid | role_bid |    | bid | businessname           |                    
    +-----+---------------+    +----------+----------+    +-----+------------------------+                    
    | 1   | Laura Norder  |    | 1        | 10       |    | 10  | Amazon                 |                    
    | 2   | Peter Dowt    |    | 2        | 20       |    | 20  | Apple                  |                    
    | 3   | Tom Di Canari |    | 3        | 30       |    | 30  | Paula's Poodle Parlour |                    
    +-----+---------------+    +----------+----------+    +-----+------------------------+                    
*/

$_SESSION['id'] = 2;   // provide a value

$res = $conn->prepare("SELECT  ba.bid
                             , ba.businessname
                        FROM MK_role_access ra 
                             JOIN 
                             MK_baccounts ba ON ra.role_bid = ba.bid
                        WHERE ra.role_uid = ?
                        ");
                        
$res->bind_param('i', $_SESSION['id']);
$res->execute();
$res->bind_result($bid, $business);
$res->fetch();

echo "$bid - $business <br>";           //==>  20 - Apple

?>