www.webdeveloper.com
Results 1 to 6 of 6

Thread: MySQL Relational Database

  1. #1
    Join Date
    Nov 2007
    Posts
    362

    MySQL Relational Database

    Hi,

    Using PHP & MySQL, how does one create a relational database?

  2. #2
    Join Date
    Jun 2003
    Location
    here
    Posts
    4,551
    http://dev.mysql.com/doc/refman/5.0/...eign-keys.html


    look to the first comment for an example.

  3. #3
    Join Date
    Nov 2007
    Posts
    362
    How do I convert the following database into a relational database? The database contains peoples favorite food and drinks.

    Code:
    if (mysql_query("CREATE DATABASE db",$con))
    {
      echo "Database Created!";
    }
    else
    {
      echo mysql_error();
    }
    
    mysql_select_db("db", $con);
    $sql = "CREATE TABLE persons 
    (
      name text,
    )";
    
    mysql_query($sql,$con);
    
    $sql = "CREATE TABLE items 
    (
      favouritedrink text,
      favouritefood text,  
    )";
    
    mysql_query($sql,$con);
    mysql_close($con);

  4. #4
    Join Date
    Jun 2003
    Location
    here
    Posts
    4,551
    you need a field joining the two, so either a unique ID or some such:

    PHP Code:
    mysql_query(
      
    "CREATE TABLE persons(
          ID INT NOT NULL AUTO_INCREMENT, 
          name VARCHAR(64),
          PRIMARY KEY (ID)
      )"
    );
    mysql_query(
      
    "CREATE TABLE items(
         person INT,
         favouritedrink text,
         favouritefood text,
         FOREIGN KEY (person) REFERENCES persons(ID) on delete cascade
      )"
    ); 
    Last edited by scragar; 03-16-2008 at 11:36 AM.

  5. #5
    Join Date
    Nov 2007
    Posts
    362
    How do I retrieve each user from the database, along with their favorite food and drink?

  6. #6
    Join Date
    Jun 2003
    Location
    here
    Posts
    4,551
    PHP Code:
    $rs mysql_query("SELECT * FROM persons
      LEFT JOIN(items)
      ON (persons.ID=items.person)"
    );
    while(
    $row mysql_fetch_assoc($rs)){
      echo 
    '<p>';
      
    var_dump($row);
      echo 
    '</p>';
    }; 

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles