Selecting everything with WHERE

Question

I'm currently working on a PHP project where multiple access rights exists. The logic is simple:
- When your access level is 2, you can see a list of your own projects only
- When your access level is 3, you can see ALL projects

So in SQL:
First one:

SELECT project_name FROM projects

Second one:

SELECT project_name FROM projects WHERE user_id = user_id

The problem is that I'm using PDO with prepared statements and such queries are executed multiple times across the script. This is how it looks like:

if ($_SESSION['access_level'] == 3) {
    $sql = "SELECT project_name FROM projects";
  } else {
    $sql = "SELECT project_name FROM projects WHERE user_id = ?";
}

$res = $db->prepare($sql);

// Some more PHP 

if ($_SESSION['access_level'] == 3)
      $res->execute();
    else
      $res->execute(array($_SESSION['user_id']));

As I'm doing this in multiple parts of the script, it becomes a mess. Is there a better way to do this? Personally I was thinking of a WHERE-clause where every record is selected. That way this would be possible at the start of the script:

if ($_SESSION['access_level'] == 3)
      $id = *;
    else
      $id = $_SESSION['user_id'];

Now querying is much easier:

$res = $db->prepare("SELECT project_name FROM projects WHERE user_id = ?");
$res->execute(array($id));

(Now it will get all records when your access level is 3, but only your own when you're only level 2)

This looks like a pretty dump solution imo as I'm not really usng the WHERE clause how it should be used. Also, using * is just not possible.

What's the best option for this?

Thank you!

Answer 1

You are looking for the solution in completely wrong way.

Every time you face a situation where you doing somethin in multiple parts of the script, and it become a mess, You have to create a function.

In fact, you are still writing all this ugly repeated code with prepare, execute, fetch, prepare, execute, fetch, prepare, execute, fetch - for the every query on the page. Doesn't it looks like a mess for you?

So, you have to create two functions.

general purpose one, just to fetch some value out of query without repeating useless code, to use it like this:

$proj_names_arr = $db->getColumn("SELECT project_name FROM projects");

and one mentioned by Raisen, based on the first one, to be used like this

$proj_names_arr = getProjects();

It will be the only real improvement of your code

As for the function, it's not that hard
a rough example:

function getColumn() {
  $args  = func_get_args();
  $query = array_shift($args);
  $res = $db->prepare($query);
  $res->execute($args);
  $data = array();
  while ($row = $res->fetch(PDO::FETCH_NUM)) {
    $data[] = $row[0];
  }
  return $data;
}

so, it can be called

$proj_names = $db->getColumn("SELECT project_name FROM projects WHERE user_id = ?",
                             $_SESSION['user_id']);