dbo

[franches]

$QueryString="SELECT StaffTable.PIN AS Pin, StaffTable.PinProfile AS PinProfile, StaffTable.Name AS Name, datalog.dbo.Employees.Superior AS Superior, Employees1.Payroll_id AS Payroll_id, Employees1.FName AS Fname, Employees1.MName AS MName, Employees1.GName AS GName, datalog.dbo.Employees.Payroll_id AS Payroll_id2 FROM StaffTable INNER JOIN datalog.dbo.Employees ON StaffTable.PinProfile = datalog.dbo.Employees.Payroll_id INNER JOIN datalog.dbo.Employees Employees1 ON datalog.dbo.Employees.Superior = Employees1.Payroll_id WHERE (StaffTable.PinProfile = '".$user_session."')";

I am trying to understand this code. I do understand when it says StaffTable.PIN (just correct me if I'm wrong) it will select PIN field from StaffTable which is the table name and name it as PIN and also same with StaffTable.PinProfile and StaffTable.Name. However, datalog.dbo.Employees.Superior AS Superior, Employees1.Payroll_id AS Payroll_id, Employees1.FName AS Fname, Employees1.MName AS MName, Employees1.GName AS GName, datalog.dbo.Employees.Payroll_id AS Payroll_id2. In my database I have checked that there is a database name datalog, table Employees and field Superior but I have not found the "dbo" and also table Employees1 do not exist. I really don't understand why is there "dbo" and Employees1. Is there someone who could help me with this?

thank you in advance.

[Salsa]

Hi Franches.

If the query actually works, also look for a database named `datalog.dbo` or a table in datalog named `dbo.Employees`, etc. It's not conventional to include dots in database, table or column names, but it is possible to do so if the names are enclosed in backticks (`) when created. The writer of your example query may have used such a scheme to obfuscate the code.

I hope this helps.

[Slade]

databasename.dbo.tablename is standard convention for referencing a table in another database. datalog.dbo.employees in this case, refers to the employees table owned by dbo(ignore this part for now), in the database named datalog.

INNER JOIN datalog.dbo.Employees Employees1

This line does something special. It again points to the employees table in database datalog, but also creates an alias for referring to this specific join.

There are two reasons for creating an alias. The first is to cut down on repitition of databasename.dbo.tablename.fieldname every time you reference a field from the table. You only have to call the fields by aliasname.fieldname (end of sentence). The second reason is so you can talk about more than one specific reference to a table, as is being done in this case.

This lets you build a table that has say categories in it. If a category has the property of having a parent(ie, it's a subcategory), then you can link again against this table to find out which category is the parent. (I hope that wasn't too jumbled.)

[Salsa]

Hmm. So it is; dbo - database objects.

Cool. Thanks for the correction Slade.

[franches]

databasename.dbo.tablename is standard convention for referencing a table in another database. datalog.dbo.employees in this case, refers to the employees table owned by dbo(ignore this part for now), in the database named datalog.

can you further explain this one? I really am confused with this.

datalog.dbo.employees is also same as datalog.employees?

[Slade]

Ok, let's change the names a little so that we can be sure we're not overlapping meanings. Take the following for example:

select id, name, attributes from otherdatabase.dbo.table_of_somedata sd
where sd.id = 1

I've done two things here:

First, I'm referencing a table named table_of_somedata in the database named otherdatabase. otherdatabase.dbo.table_of_somedata is not the same as otherdatabase.table_of_somedata, either two dots(eg: "..") or ".dbo." are required between the database name and the table name.

Second, I'm giving the the fully qualified* table name otherdatabase.dbo.table_of_somedata a very easy to type alias of sd. This does nothing more than make it so that every time further in the query when you go to join against, where condition, group by, or even select from the table, you can just type the alias sd instead of typing out otherdatabase.dbo.table_of_somedata.

dbo: dbo literally translates to database owner. It has to do with who owns the object you're talking about. In this case, we're saying that the database owner owns the table table_of_somedata. I regret to say that I have not seen an implementation of a structure where you would need to use anything other than dbo.

*Yes, I know, there is one more step above databasename, but we're trying to keep it simple, here...