When using phpMyAdmin to work on mySQL databases, you may have noticed that database and table names, as well as fields, are surrounded by backticks. If you do a query, the result may look something like this:

SELECT *  FROM `product` WHERE `name` LIKE 'Articulation'

But you can take them out and it still works.

SELECT *  FROM product WHERE name LIKE 'Articulation'

There is a discussion of this at StackOverflow and the consensus is that it is a non-standard practice that could cause problems if you migrate to another DBMS, but it has advantages if you are sticking with mySQL.

If you use backticks in your code, you can use reserved keywords for table names. You can also use table names with spaces.

“It’s a lot easier to search your code-base for something in backticks. Say you have a table named event. grep -r “event” * might return hundreds of results. grep -r “\`event\`” * will return anything probably referencing your database.” (EllisGL)

If you use them consistently, it does make the code a bit easier to read, but harder to write. Note that if you need more than the table name, you put backticks around the database as well as the table. e.g. `myDatabase`.`myTable` NOT `myDatabase.myTable`

If you are doing a bunch of things in a query it can get a bit messy. Here’s an example with and without.

SELECT `product`.`name`, `category`.`name` 
FROM `product`
INNER JOIN product_category AS category
ON `product`.`category_id` = `category`.`id`

But you can take them out and it still works.

SELECT product.name, category.name 
FROM product
INNER JOIN product_category AS category
ON product.category_id = category.id

You can even take out just some of them and it works fine, however it is probably not good coding practice.

SELECT product.name, `category`.name
FROM `product`
INNER JOIN product_category AS category
ON `product`.`category_id` = `category`.`id`

Note that you still need to use quotes for search terms (like ‘Articulation’ above) and when inserting into non-numeric fields.

I haven’t given this topic any thought before now, but looking at my PHP code, I noticed that I never use backticks in my queries.

I have two MySQL databases on my server for my customers. One has customers who work for universities and one has customers who purchased from our website or made inquiries by phone or email. There is some overlap between the two and for the latest product announcement, I wanted to avoid sending duplicate emails. I usually use a php script to do my queries. If I am sending to just one group, the query is fairly straightforward.

require_once('marketing_database_my.inc');

$fname = "./MarketingExports/email.txt";

    $sql  = "(SELECT name, email FROM Universities ";
    $sql .= "WHERE email IS NOT NULL AND email != '' ";
    $sql .= "AND duplicate_email = 0 ";
    $sql .= "AND remove_email != 1) ";
    
    $result = $dbMKT->query($sql);
    $numRecords = mysqli_num_rows($result);

    echo "There are $numRecords records.";
    // Open the file to save the words
        
    $fp = fopen($fname, 'w');
    if (!$fp) {
        echo "Couldn’t open file.";

    } else {

        echo "Save to file $fname.<br />";
        for ($i = 0; $i < $numRecords; $i++) {
            $row = $result->fetch_array();
            $id = $row["id"];
            $company = trim($row["company"]);
            $name = trim($row["name"]);
            $email = trim($row["email"]);
         
            // Add to the email mail list
            $info = "\"$name\" <$email>";
            fwrite ($fp, "$info" . "\r");
        }
    }
    fclose($fp);

However, if I want to combine the University list and the other customers list, I need to adjust the query a bit by using a UNION

  $sql  = "(SELECT name, email, company FROM Customers ";
  $sql .= "WHERE email IS NOT NULL AND email != '' ";
  $sql .= "AND duplicate_email = 0 ";
  $sql .= "AND remove_email != 1) ";
  $sql .= "UNION ";
  $sql .= "(SELECT Universities.name, Universities.email, Universities.title FROM Universities, Customers ";
  $sql .= "WHERE Universities.email IS NOT NULL AND Universities.email != '' ";
  $sql .= "AND Universities.duplicate_email = 0 ";
  $sql .= "AND Universities.remove_email != 1 ";
  $sql .= "AND Universities.email != Customers.email) ";
  $sql .= "ORDER BY email ";

Two things to note. Name and email are the same in both tables. However, some of my customer records have the customer name as the company and a blank name field. The UNION won’t work if there are a different number of fields in the SELECT statement, so I include the title field for Universities just to make MySQL happy.

I can order by email because it is in both tables. Notice also that I had to tell the second part of the query which table to work on for the WHERE statement.