Database FAQ - HTML Forums

scoutt · 02-15-2005, 03:43 PM

users that use PHP find that running mysql is a lot easier to deal with then Access or SQL 2000.
Here is a basic install on a windows box.

mysql and php go real good with phpMyAdmin

Download Mysql here http://dev.mysql.com/downloads/mysql/4.1.html

Running Mysql on win2000 as a Service, should be very close to XP

1. Install Mysql to the default location.
2. run c:\mysql\bin\WinMysqlAdmin (c: is the drive letter you installed mysql to)
3. it should ask for a username and password, this is for root access.
4. looking at the ini tab make sure mysqld-nt is checked.
5. right click anywhere and click on "install as Service"
6. don't start the service just yet, you need to go into the services
panel, start->settings->control panel->Administative Tools->Services
find the mysql one and right click on it and goto properties.
7. On the second tab (log) make sure it is second radio button,
then click on browse to find the user that will run this service
everytime the computer starts. enter there password and then click
on apply and then ok.
8. then back in WinMysqlAdmin right click anywhere and start the service.

if you have question or want to comment on this please feel free to start a thread.

putts · 02-16-2005, 08:53 PM

Welcome to the wonderful world of joins!

Joins are SQL statements used to join two or more tables together to form one larger more complete tableset.

A sample join statement would look something like this

Code:

 
  SELECT A.ID, A.COLUMN, B.COLUMN FROM TABLE_1 A JOIN TABLE_2 B ON A.ID = B.ID

There are a few different types of joins ,and I may not even know all of them ,but he’s a list of the most widely used:
INNER JOIN (JOIN) – Forces integrity between the two fields in the “ON” part of the statement i.e. It will ONLY pull the records where these two values match perfectly
LEFT OUTER JOIN (OUTER JOIN) – Retrieves all the records from the table on the left side of the statement regardless of whether there’s a matching value on the other side
RIGHT OUTER JOIN – Exact opposite of a Left Outer – ALL from the right side regardless of values on the left.

Let’s look at some sample data

Code:

 
 
Table : EMPLOYEES 
| EMP_ID | FIRST_NAME | LAST_NAME | MANAGER_ID | DEPT_ID | 
| 1      | Bob        | Thomas    |   null     |     1     | 
| 2      | Tom        | Smith     |     1      |     1     | 
| 3      | John       | Johnson   |     1      |     1    | 
| 4      | Susan      | Contractor|    null    |   null   | 
 
Table : DEPARTMENTS 
| DEPT_ID | DEPT_NAME       | 
| 1       | Human Resources | 
| 2       | Marketing       |

Example 1:
You want a list of all the employees and the name of their department.

The following statements would work in slightly different ways….

Code:

 
 
1. SELECT emp.FIRST_NAME, emp.LAST_NAME, dpt.DEPT_NAME FROM 
EMPLOYEES 
emp INNER JOIN DEPARTMENTS dpt ON emp.DEPT_ID = dpt.DEPT_ID 
 
2. SELECT emp.FIRST_NAME, emp.LAST_NAME, dpt.DEPT_NAME FROM 
EMPLOYEES 
emp LEFT OUTER JOIN DEPARTMENTS dpt ON emp.DEPT_ID = dpt.DEPT_ID

Statement #1 would pull Bob, Tom and John but not Susan because it’s an INNER JOIN and that means that the statements is going to try to match up the values in DEPT_ID from the EMPLOYEES table with a coordinating value in the DEPARTMENTS table. Because no DEPT_ID in DEPARTMENTS would be found to match up with the null from the DEPT_ID for Susan’s record, it would ignore that record completely

Statement #2 would pull all the employees because it’s a LEFT OUTER which means that it doesn’t care whether or not a corresponding record exist in the DEPARTMENTS table it just wants to make sure that it pulls ALL the records from EMPLOYEES and any from DEPARTMENTS where the values happen to line up.

Example 2:
You want to pull all of the departments and the number of employees in each of them.

Here’s some possible solutions for this one….

Code:

 
 
1. SELECT COUNT(EMP_ID) AS EMP_TOTAL, DEPT_NAME, dpt.DEPT_ID FROM DEPARTMENTS
dpt JOIN EMPLOYEES emp ON dpt.DEPT_ID = emp.DEPT_ID 
GROUP BY DEPT_NAME,dpt.DEPT_ID 
 
2. SELECT COUNT(EMP_ID) AS EMP_TOTAL, DEPT_NAME, dpt.DEPT_ID FROM DEPARTMENTS 
dpt LEFT OUTER JOIN EMPLOYEES emp ON dpt.DEPT_ID = emp.DEPT_ID 
GROUP BY DEPT_NAME,dpt.DEPT_ID

Again, if you run the second one, you’d get an EMP_TOTAL of 3 for HR and then 0 for Marketing, but if you run the first you only get the 3 for HR and no record at all for Marketing.

Note: if you’re paying very close attention (which I’m sure you all are) you would have noticed in the first Example I used the Aliases for the Tables (emp,dpt) for all the fields but in the second I only use them on DEPT_ID. Technically, you only have to prefix your column names with the Alias if that particular field is in both tables (such as DEPT_ID) but it really isn’t that bad of an idea to always use them just in case your table structure happens to change down the way.

Example 3:
You want a list of all the employees and their manager’s last name…

Code:

 
 
SELECT emp.FIRST_NAME, emp.LAST_NAME, emp.EMP_ID, mgr.LAST_NAME FROM EMPLOYEES
emp LEFT OUTER JOIN EMPLOYEES mgr ON emp.MANAGER_ID = mgr.EMP_ID

So now we’re joining tables upon themselves just basing the join on different fields. Using this sort of concept you can actually pull a multiple level employee-manager relationship (such as if a employee has a boss and his boss has a boss and his boss has a boss, etc.) with one statement.

I’ll try to include an example of that more complex style of join later on.

You now have the knowledge, use it wisely or, in the American spirit, don’t use it all and then sue someone else for not reminding you to use it in vital situations.