Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New Coder
    Join Date
    Mar 2009
    Posts
    16
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Set Foreign Key using PHP coding

    Hi,
    Really thanks for the guidelines ytd and it was really helpful because as a beginner I really hardly to recognise small tiny error but anywhere I have learnt sth new and I will try my best to learn more. Now, I have another question to ask, How to actually set foreign key using PHP? Below shows the coding that I have done and I ady set movie_id as the PRIMARY KEY and I hope I can get some helps to set category_id as the FOREIGN_KEY using PHP code (if possible using PHP coding, not set directly through phpmyadmin....I wondering if that is a way to do so although I oso dono how to set FOREIGN KEY through phpmyadmin)

    <?php
    //Connect to the MySQL server
    $db_host = "localhost";
    $db_username = "root";
    $db_password = "*****";
    $server = mysql_connect($db_host,$db_username,$db_password)or die("Could not connect!/n");

    //Create a database called "cinema"
    $db_name = "cinema";
    mysql_query("CREATE DATABASE $db_name",$server)or die(mysql_error());

    //Select a database that has created
    mysql_select_db($db_name)or die("Could not select the database $db_name!\n");

    //Create multiple tables
    $table = "CREATE TABLE movie(
    movie_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    movie_name VARCHAR(50) NOT NULL,
    duration VARCHAR(20),
    description TEXT,
    category_id VARCHAR(3) NOT NULL
    )";

    mysql_query($table)or die(mysql_error());

    mysql_close($server);
    ?>
    Last edited by Fou-Lu; 03-23-2009 at 03:56 AM. Reason: Removed password

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    This isn't a PHP question, I'll move it to the mysql forum.
    Constraint for foreign key is here: http://dev.mysql.com/doc/refman/5.1/...nstraints.html
    You can add it with an ALTER command, or as a part of you're CREATE TABLE command.
    The create table syntax is here: http://dev.mysql.com/doc/refman/5.1/...ate-table.html
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • #3
    New Coder
    Join Date
    Mar 2009
    Posts
    16
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Sorry, I am just the beginner and I wanna ask is that meant to set a FOREIGN KEY, it only can use sql script to do so but there is no syntax in PHP coding?Thanks....

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    PHP has no ability to interact with SQL. MySQL (and MySQLi) are extensions created to allow connections to MySQL. PHPMyAdmin is PHP based software, but all the database handling is still written in SQL. So, from PHP any SQL commands can be executed, but this is why you need to run them through a mysql_query command. The link I gave you just shows the format you'll need to add a foreign key to you're table(s).
    Oh, you can set up foreign keys in PHPMyAdmin as well. To do so, click on you're table of choice to bring up the main schema page. Below the definition of the fields (or possibly below the add a new field section), you'll see a link for relationship. This will only exist if you're tables are in INNODB format.

    Does that answer you're question?
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • Users who have thanked Fou-Lu for this post:

    pelehelp (03-23-2009)

  • #5
    New Coder
    Join Date
    Mar 2009
    Posts
    16
    Thanks
    9
    Thanked 0 Times in 0 Posts
    I saw this from a website but I only know that It using function to do so. It include some syntax such as 'onDelete' => 'CASCADE' which I not quite sure about it. Can someone guide me that this is the right way to set the FOREIGN KEY using PHP code?

    public function up()
    {
    $definition = array(
    'name' => 'email_foreign_key'
    'local' => 'email_id',
    'foreign' => 'id',
    'foreignTable' => 'email',
    'onDelete' => 'CASCADE'
    );

    $this->createForeignKey('table_name', $definition);
    }

  • #6
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    That isn't a function, its a custom object method which has the purpose of creating a DAO. The method contained in the createForeignKey method will be using an 'ALTER' command to change the table structure. For documentation on this, you'll need to contact the original creator of the class object in question.
    As I said, PHP is not capable of handling foreign keys, any DDS or DMS statements. These are merely text in PHP which is passed to a mysql extension function to handle the querying. The language itself is still SQL.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • Users who have thanked Fou-Lu for this post:

    pelehelp (03-23-2009)


  •  

    Posting Permissions

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