Trying to understand some PHP/MySQL code

Databases
1

Hi, I received a long bit of code from a PHP programmer that I am trying to get my head around. I’d like to understand what, for example, SELECT m.url, sc.name, sc.title FROM menu m means. Why is it not just SELECT url, name, title FROM menu. What is .sc and m?

SELECT m.url, sc.name, sc.title
FROM menu m
INNER JOIN static_content sc
ON m.name = sc.menu_name
WHERE m.name = "' . $pageid . '"
ORDER BY order_no ASC;

Many, many thanks,

Leao

2

Thanks for the great replies!!!

order_no is in column 4.

Thanks again

3

i prefer table aliases to full table names, for the obvious reason that there is less “noise” and more “signal”

also, one of my rules for good sql is to qualify ~every~ column used in a query that has more than one table

here’s a quick question: which table is the order_no column in?

:wink:

4

I see there is an inner join in the sql statement. So m or sc are just the aliases of respective tables to denote the table name. Instead of using those aliases you can use the table name itself too.


SELECT menu.url, static_content.name, static_content.title
FROM menu
INNER JOIN static_content
ON menu.name = static_content.menu_name
WHERE menu.name = "' . $pageid . '"
ORDER BY order_no ASC;
5

those are table aliases

the reason you use them is twofold, and both apply when the query contains more than one table

  1. if the tables share a column name, an alias is required when you use the column name, otherwise the database won’t know which one you want and you’ll get a syntax error

  2. identifying the table that a column belongs to even if the column name is unique among all the tables in the query is best practice because it (self-)documents the query

6

huh?

my point was, from looking at the query, you cannot tell whether order_no is in the menu table or the static_content table