Announcement

Collapse
No announcement yet.

Database FAQ

Collapse
This topic is closed.
X
This is a sticky topic.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Database FAQ

    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.
    Have a Script or Snippet you want to share?

    WWW Standards: HTML 4.01,
    HTML 5, CSS2.1, CSS3, XHTML 1.0
    PHP Standards: PHP Standards

  • #2
    Join FAQ

    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.
    Signature Image Rotator will return when I have time to fix it

    Running Scoreboard
    Anthony : 3 points

    Comment

    Working...
    X