View Full Version : Set Foreign Key using PHP coding

03-23-2009, 03:52 AM
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)

//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_name VARCHAR(50) NOT NULL,
duration VARCHAR(20),
description TEXT,
category_id VARCHAR(3) NOT NULL

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


03-23-2009, 03:56 AM
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/en/innodb-foreign-key-constraints.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/en/create-table.html

03-23-2009, 04:15 AM
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....

03-23-2009, 04:20 AM
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?

03-23-2009, 04:27 AM
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);

03-23-2009, 04:40 AM
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.