Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Results 1 to 7 of 7
  1. #1
    Senior Member james's Avatar
    Join Date
    May 2003
    Location
    Melbourne, AUSTRALIA
    Posts
    364
    Member #
    1352
    I've finally got MySQL working on my local server, and I use phpMyAdmin 2.5.1 to make DB's.
    I've created a small set of relational tables in a test database (excuse my terminology if it's wrong), and a test PHP page to list the tables. Here's the way it's set up...

    table: students
    | index_num (INT, key, auto_increment) | name (VARCHAR) | teacher_num (INT) |

    table: teachers
    | index_num (INT, key, auto_increment) | name (VARCHAR) |

    Then in php I want to write out a table of students and their teachers...
    PHP Code:
    mysql_connect("localhost");
    mysql_select_db("first_db");
    $sql "SELECT students.name AS stname, teachers.name AS tchname FROM students, teachers"
    $sql .= " WHERE teachers.index_num=students.teacher_num"
    $results mysql_query($sql);
    /*... process with mysql_fetch_array($results) */
    /*... display as table */ 
    This works, but I'm wondering if I'm proceeding in the most standard, and simple way.

    • Are my names for the index fields good, standard?
    • I used phpMyAdmin to create the fields, but apparently you can do some SQL like:

      CREATEFIELD_KEYWORD `related_number` IN `this_table` REFERENCES(`related_table.index_num`)

      Which phpMyAdmin apparently doesn't allow. It doesn't stop me from cross referencing

      this_table.related_number to related_table.index_num, but then again, I could accidentally

      cross reference two fields, not produce an error, but get silly output. This looks like a first

      step in stopping this annoying bug from happening, so how do I implement it fully?
    • Can I use features like these to simplify my lookups? Something like

      SELECT students.name, students.teacher_num REFS name FROM students

      ... That query doesn't mention the teachers table, but MySQL should be able to work out that

      the 'name' field is in that table, because students.teacher_num references that table.


    Phew.
    James H
    Home Page · Mars Page · www.fihsf1.net (formerly www·fihs·net)

  2.  

  3. #2
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    SQL is not an intuitive language. It will not allow you to select from a table unless you specify it in your FROM list.

    Additionally, your database is not relational. Relational implies an explicit relationship, which you don't have. Try doing something like this:
    Code:
    CREATE TABLE students (
    	id int NOT NULL AUTO_INCREMENT,
    	name varchar (255),
    	PRIMARY KEY (id)
    );
    
    CREATE TABLE teachers (
    	id int NOT NULL AUTO_INCREMENT,
    	name varchar (255),
    	PRIMARY KEY (id)
    );
    
    CREATE TABLE classes (
    	id int NOT NULL AUTO_INCREMENT,
    	teacher_id int NOT NULL REFERENCES teachers(id),
    	name varchar (255),
    	PRIMARY KEY (id)
    );
    
    CREATE TABLE students_classes (
    	student_id int NOT NULL REFERENCES students(id),
    	class_id int NOT NULL REFERENCES classes(id),
    	PRIMARY KEY (id)
    );
    Now what you've got is a complex relational structure, involving teachers who can have many classes each, but only one class per teacher, and many students who can have many classes each, but there can also be many students in a class.

    Here are some queries you can use:
    Code:
    -- Get a list of students and their classes
    SELECT
        sc.student_id,
        sc.class_id
        s.name AS student
        c.name AS class
    FROM
        students AS s
        INNER JOIN students_classes AS sc ON s.id = sc.student_id
        INNER JOIN classes AS c ON sc.class_id = c.id
    ORDER BY s.name, c.name
    
    -- Get a list of teachers and their classes
    SELECT
        t.id AS teacher_id,
        c.id AS class_id
        t.name AS teacher
        c.name AS class
    FROM
        teachers AS t
        INNER JOIN classes AS c ON c.teacher_id = t.id
    ORDER BY t.name, c.name
    
    -- Get a list of teachers, their classes, 
    -- and the students in each
    SELECT
        c.teacher_id,
        sc.student_id,
        sc.class_id
        t.name AS teacher
        c.name AS class
        s.name AS student
    FROM
        teachers AS t
        INNER JOIN classes AS c ON c.teacher_id = t.id
    ORDER BY t.name, c.name
        INNER JOIN students_classes AS sc ON sc.class_id = c.id
        INNER JOIN students AS s ON s.id = sc.student_id
    ORDER BY t.name, c.name, s.name
    Hope that helps some


  4. #3
    Senior Member james's Avatar
    Join Date
    May 2003
    Location
    Melbourne, AUSTRALIA
    Posts
    364
    Member #
    1352
    OK, INNER JOIN seems the way to go. Does it prevent you from inner joining where neither field references the other, eg
    Code:
    ...
        INNER JOIN students_classes AS sc ON sc.class_id = c.id
        INNER JOIN students AS s ON classes.id = sc.student_id
    ...
    This is a mistake, because student_classes.student_id references student.id, not classes.id. Does the REFERENCES keyword prevent this INNER JOIN taking place?

    Also, I see that the standard name for a table key is 'id', and a field that references the key from 'tablex' is tablex_id.
    James H
    Home Page · Mars Page · www.fihsf1.net (formerly www·fihs·net)

  5. #4
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    Well that's the standard that I've always used... it is generally accepted as the way to go.

    Also, regarding:

    INNER JOIN students AS s ON classes.id = sc.student_id

    I meant to say:

    INNER JOIN students AS s ON s.id = sc.student_id

  6. #5
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    Also, if you want to return a record from one table even if there are no records in the related table, consider using LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.

    LEFT returns records from the first table in the join of the two even if there are no related records in the second.

    RIGHT returns records from the second even if there are no related records in the first

    FULL OUTER returns records from either whether or not there are related records from the other.

    Note: these will only join records that are joined. If there is a record with no related records in the other table, the values in the cursor will be NULL for the records from the other table.

  7. #6
    Senior Member james's Avatar
    Join Date
    May 2003
    Location
    Melbourne, AUSTRALIA
    Posts
    364
    Member #
    1352
    Originally posted by transio
    Also, regarding:
    INNER JOIN students AS s ON classes.id = sc.student_id
    I meant to say:
    INNER JOIN students AS s ON s.id = sc.student_id
    Sorry, I purposely made that mistake--I deliberately wrote faulty code, that would give you rubbish when executed. (Your original code is correct!)
    I was wondering if SQL would allow it to go through, because you're comparing INTs to INTs, or if it would stop it (i.e., have an error) because neither of those fields explicitly references the other.

    Which one is it?

    Having an error would be a good thing to happen, because then you could make sure errors don't happen at design time, rather than in your queries. It's kinda analogous to dangling pointers in C++.

    I'll have a look for stuff on the different types of JOINs. I think it might be time to pull out a pen and a bit of paper.

    Thankyou Mr Transio
    James H
    Home Page · Mars Page · www.fihsf1.net (formerly www·fihs·net)

  8. #7
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    SQL doesn't care about relationships on SELECTs. Only on INSERTs. It's called referential integrity. A student_class can't reference a student_id that doesn't exist, so it won't let you insert a student_id unless the matching id exists in the student table.

    However, If I want to SELECT a student_class where the class_id equals the student_id, there's no limitation. That would limit the potential functionality of SQL.

    An example of when you would need to equate unrelated fields is "row inequality comparisons", where you compare autonumber id's to row COUNTs or MAX values in groups. This allows for some complex sorting. Another place you need it is in "time pairing denormalization", where you assign paired values to different time sequences to create denormalized event pairs.

    You'll probably never use these, but the language on the whole won't limit you, because it would then limit itself, ya know?


Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 11:13 PM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com