Jump to content

Proper Formatting for this query


Recommended Posts

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"];
  }
 

Link to post
Share on other sites

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

?>

 

  • Great Answer 1
Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.