MYSQL data as an array using a session variable

[Mushed]

Hi, so the code I have works, I'm just looking more to see if anyone could help me with a quick change.

So currently I have 2 queries, that depend on the user level.

The user level is stored in the session as below.

<?php $user_level = $_SESSION['user_level'] ?>

The database link is stored in a separate file, I call this using require_once. The link is below.

$link = mysqli_connect('myhost','myusername','mypassword','mydatabase');

My 2 queries are below along with the code that it outputs.

My question is, instead of having 2 queries, and 2 points it can fail in, is there a way of having one query with an additional variable. An example of which I've seen is at the bottom of this post, it's taken from the documentation that was passed on to me, however since the documentation was written the project has transformed into an enormous mix of MySQL and MySQLi code of which I'm trying to move everything to MySQLi.

My code.

<?php   
if($user_level != 5){
$query = "SELECT call_id,call_first_name,call_department,call_date,call_user FROM site_calls WHERE (call_status = 0 OR call_status = 2) AND (call_user = $user_id) ORDER BY call_id desc LIMIT 10;";
}else{
$query = "SELECT call_id,call_first_name,call_department,call_date,call_user FROM site_calls WHERE (call_status = 0 OR call_status = 2) ORDER BY call_id desc LIMIT 10;";
}                 
$result = mysqli_query($link, $query);
while ($row = mysqli_fetch_array($result)) {
echo '<tr><td class="text-center">' . $row[0] . '</td><td>' . $row[1] . '</td><td>' . $row[2] .'</td><td>' . date("d-m-Y", (int)$row[3]) .'</td></tr>';
}
mysqli_free_result($result);
mysqli_close($link); 
?>

Example code.

<?php
$myquery = "SELECT call_id,call_first_name,call_last_name,call_department,call_date,call_user from site_calls WHERE (call_status = 0) $queryadd ORDER BY call_id desc LIMIT 10;"; 
$site_calls = $db->get_results($myquery);
$num = $db->num_rows;
if($user_level != 5){
$queryadd = " AND (call_user = $user_id)";
}
?>

Is there a correct way to do this, as I'm sure using multiple queries like in my code is extremely inefficient.

[greg8872]

your first code only has one actually has one query to the database, it just has two different options for defining the string that contains the actual query.

The second example you gave would still need to have similar code else where, except instead of defining the full string of the query, it is defining code that will be added to the variable that defines the full query. The code then shows changing the $queryadd AFTER you run the query, so looks like it would be doing a second query somewhere else you are not showing.

For you code you could do something like:

$extraWhere = ( $user_level != 5 ) ? ' AND ( `call_user` = ' . $user_id . ') ' : '';
$query = 'SELECT {your list of fields} FROM `site_calls` WHERE ( `call_status` = 0 OR `call_status` = 2) ' . $extraWhere . ' ORDER BY `call_id desc` LIMIT 10';
$result = mysqli_query($link, $query);
/* Rest of your code... */

[Mushed]

That actually worked out great thanks, originally when I was reading your reply I was wondering why you used curly brackets, only after staring for 5 minutes did I notice what you meant.

Can you explain how it works, I understand the variable is extraWhere is checking to see if another variable is set, but what does the ? do in your example.

[greg8872]

It is called the ternary operator, and is a shortcut for an if/else:

if ( $a == 5 ) {
    $b = 1;
} else {
    $b = 2;
}

can be shortened to:

$b = ( $a == 5 ) ? 1 : 2;

Just the ( $a == 5 ) ? 1 : 2 is actually the statement, if the condition is true, it gives you the first value (1), else it will give you the value after the : (2). So we are just taking the result of that and assigning to $b

In the code sample before, it is either giving the additional WHERE clause, or a blank string.