Build MySQL-queries automatically

10 Dec 2008 - php, mysql, databaser, tutorial

Läs den här texten på Svenska

When I'm building PHP applications, I often find myself constructing long SQL queries that are hard to get any grip on, subqueries and joins that make the structure of the query hard to see. Plus, if you're going to make four seperate requests that are very simliar, you end up copying and pasting a lot of SQL code. Usually I solve this by indentation and heredoc

But I wanted a more elegant solution and built a PHP function that transform an array to a MySQL query. It's far from complete but handles most of the common MySQL syntaxes. And the main benefit is that you can alter it easily depending on in-data.

Base

$query= array(
    "select" => "id, headline",
    "from" => "articles",
    "where" => array(
        "id" => 12
    )
);

= select id, headline from articles where id = 12

This is how you build a query

Variable in-data

if($_GET["member"]){
    $query["where"]["member"] = $_GET["member"];
}

select id, headline from articles where id = 12 and member = 34

Ok, so the first example wasn't very complicated, we could have done that manually, so to speak. But what if we want to alter the query based on some specific in-data? Well, that's very easy. Note that you should never ever use $_GET and $_POST as-is, you should always validate them to another variable. This was just an example, I will use my validated "$in" variable in the rest of the examples.

Multi-table

$query= array(
  "select" => array("a.id", "a.headline"),
  "from" => array("articles as a"),
  "where" => array(
    "a.category" => "Weblog"
  )
);

select a.id, a.headline from articles as a where a.category = "Weblog"

if($in["member"]){
  $query["select"][] = "m.name";
  $query["from"][] = "member as m";
  $query["where"]["a.member"] = "m.id";
  $query["where"]["m.name"] = $in["member"];
}

select a.id, a.headline, m.name from articles as a, member as m where a.category = "Weblog" and a.member = m.id and m.name = "Sandman"

Another great thing about this is that you can change the query after the fact based on in-data and the new query requires a multi-table select. This is where the strength of this functions comes into play. Note that I've changed the "from" and "select" parts of the array to arrays themselves.

JOIN

if ($in["member"]){
    $query["select"][] = "m.name";
    $query["join"]["left"] = array(
      "db" => "member as m",
      "on" => array("m.id" => "a.member")
    );
    $query["where"]["m.name"] = $in["member"];
  }

select a.id, a.headline, m.name from articles as a left join (member as m) on (m.id = a.member) where a.category = "Weblog" and m.name = "Sandman"

If you prefer join queries, the function handles them as well

GROUP, ORDER and LIMIT

$query = array(
    "select" => "count(id), id, headline",
    "from" => "articles",
    "where" => array(
      "category" => "Weblog"
    ),
    "order" => "date desc",
    "limit" => 30,
    "group" => "member"
  );

select count(id), id, headline from articles where category = "Weblog" group by member order by date desc limit 30

And obviously basic functions like group, limit and order

WHERE

$query = array(
    "select" => "count(id), id, headline",
    "from" => "articles",
    "where" => array(
      "category" => "Weblog",
      "date:>" => "2008-10-01"
    ),
    "order" => "date desc",
    "limit" => 30,
    "group" => "member"
  );