how to populate the foreign key

[zed420]

Hi All

Can some please enlighten me how to populate the foreign key in php. I have two tables;

CREATE TABLE `user` (
  `id` smallint(5) unsigned NOT NULL auto_increment,
  `fullname` varchar(60) NOT NULL,
  `username` char(15) NOT NULL,
  `pswd` char(32) NOT NULL,
  `address` varchar(70) NOT NULL,
    PRIMARY KEY  (`id`)
  ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `blockBook` (
  `user_id` smallint(5) unsigned NOT NULL,
  `job_id` smallint(5) unsigned NOT NULL auto_increment,
  `cust_name` varchar(40) NOT NULL,
  `cust_address` varchar(60) NOT NULL,
    PRIMARY KEY  (`job_id`),
  FOREIGN KEY (user_id) REFERENCES user(id) 
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

 

This is how I'm INSERTING into the table;

$user_id = $_POST['user_id'];
$job_id = $_POST['job_id'];
$cust_name = $_POST['cust_name'];
$cust_address = $_POST['cust_address'];

$query = "INSERT INTO blockbook  VALUES
('user_id','job_id','$cust_name','$cust_address')";

   $result = mysql_query($query)or die(mysql_error());
}

And this is my SELECT query;

 

$query = "SELECT  * FROM blockbook, user  
WHERE  user_id= id";
   $result = mysql_query($query)or die(mysql_error());

But it’s not working the foreign key value is always 0.

How the devil do I populate it? I've even tried manually it doesn't work.

Thanks

Zed

 

[xylex]

I don't think MyISAM supports fk constraints.  Try using InnoDB.

[zed420]

Thanks for reply I've just enabled InnoDB in phpMyAdmin and droped the database recreated db and tables but  It didn't work.

 

Thanks anyway

Zed

[Barand]

VALUES

('$user_id','$job_id','$cust_name','$cust_address')";

[zed420]

Thanks Barand its still the same. I need to know how to fetch the user tables primary key value (which is auto incre) to blockbook table's user_id (the foreign key) of this table.

 

thanks zed

[Barand]

After inserting the new user, call

$newid = mysql_insert_id();

 

Must be done before the current mysql connection is closed as this value is per-connection.

[zed420]

Sorry mate fairly new to this where do you want me to place thid code.

[Barand]

Well, as it needs to be called after you insert the new user record and you need the value to place in the inserted new blockbook record, I would suggest somewhere between the two inserts. The normal place is immediately after the first insert.