1

I am having a few difficulties with mysql and PDO.

I wish to insert a product into the database, however the product table contains foreign keys. Naturally, I will not know the Foreign key ID when inserting. Am I doing this right??? Is there a better way of tackling this problem?

TABLE Products

  • Id PK AI int
  • Name Varchar(20)
  • CategoryId Int FK
  • TypeId Int FK

TABLE Categories

  • Id Int PK
  • Cat varchar(20)

TABLE Types

  • Id Int PK

  • Type varchar(20)

    $type = 'Gloves';
$category = 'Clothing';
$sql = 'INSERT INTO Products
    SET Name = :name, CategoryId = :catId, TypeId = :typeId
    WHERE 
        CategoryId IN (SELECT Id FROM Categories WHERE Cat = :category) AND
        TypeId IN (SELECT Id FROM Types WHERE Type = :type)'

$stmt = $db->prepare($sql); 
$stmt->execute(array(':name' => 'Pink childrens gloves', ':category' => $category, ':type' => $type));

As mentioned in a comment below: Normally, I would be getting the ID from a select box. I cannot do this because it will be a script executing the query, not a user.

Gravy
  • 12,264
  • 26
  • 124
  • 193
  • 1
    You would normally select category and type from a drop down list or some other widget that seeks valid options and returns an ID. Do you rely in a barebone input box? – Álvaro González Dec 18 '12 at 16:32
  • I know this, because from a select box I can grab the id... I am trying to automate things... it will be a script, not a user which will be inserting the products. – Gravy Dec 18 '12 at 16:35
  • Honestly, I can't see how being a script prevents you from handling IDs, but I've provided an answer anyway. – Álvaro González Dec 18 '12 at 16:44

4 Answers4

1

are you sure that this is what you want?

$sql = 'INSERT INTO Products
    SET Name = :name
    WHERE 
        CategoryId IN (SELECT Id FROM Categories WHERE Cat = :category) AND
        TypeId IN (SELECT Id FROM Types WHERE Type = :type)'

I think you are trying to use UPDATE

$sql = 'UPDATE Products
    SET Name = :name
    WHERE 
        CategoryId IN (SELECT Id FROM Categories WHERE Cat = :category) AND
        TypeId IN (SELECT Id FROM Types WHERE Type = :type)'
jcho360
  • 3,724
  • 1
  • 15
  • 24
  • I want to insert a new row into the table. I wish for the query to detect the correct id for the foreign keys. e.g. assuming type gloves has id of 1, and category clothing has id of 5... Then I want to insert into the products table (name: Childrens pink glove, typeid: 1, categoryid: 5) – Gravy Dec 18 '12 at 16:29
  • 1
    Mysql doesn't support `WHERE` in insert statement, please check this link: http://stackoverflow.com/questions/485039/mysql-insert-where-query – jcho360 Dec 18 '12 at 16:32
1

MySQL allows a combination of SELECT + INSERT in a single query:

INSERT INTO tbl_temp2 (fld_id)
  SELECT tbl_temp1.fld_order_id
  FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;

... but I wouldn't care about it. You cannot do proper error checking if you do three different things in a single query.

My advice is that you first validate that there're a category and a type that match the given names. In that step, you can easily get the corresponding IDs, which will let you perform a simple INSERT. Additionally, if you need to insert many products, you can validate first and once.

Álvaro González
  • 142,137
  • 41
  • 261
  • 360
0

In addition to @Álvaro G. Vicario's answer, you can also do something like (works in normal sql, I have not tried it with bound variables):

$sql = 'INSERT INTO Products
        SET Name = :name,
            CategoryId = (SELECT Id FROM Categories WHERE Cat = :category),
            TypeId = (SELECT Id FROM Types WHERE Type = :type)';

But I would always check for existing categories and types first, insert where necessary and get the required id's as this will lead to unexpected results if there are no matches in the inner selects.

jeroen
  • 91,079
  • 21
  • 114
  • 132
  • 1
    This is exactly what I wanted. But I see what yo umean about checking for existing categories & types first. – Gravy Dec 18 '12 at 16:54
0

First of all you need to figure out which is the table that have foreign key data.

Then you need to get all possible values from foreign key table.

Finally you need to build and drop-down list or similar to give ability of select acceptable foreign key.

$q=$db->prepare('SELECT ke.referenced_table_name assoc_table,
ke.referenced_column_name assoc_col FROM
information_schema.KEY_COLUMN_USAGE ke WHERE ke.referenced_table_name IS NOT NULL             
AND ke.table_schema=:database AND ke.table_name=:tablename AND ke.column_name=:col');
$q->bindValue(':database','mydatabasename'); //Set your database name here
$q->bindValue(':tablename','Departments');   //Set your table name here
$q->bindValue(':col','City');                //Set the column which foreign key values you want to have here
if($q->execute()) {
 $foreingtable=$q->fetch(PDO::FETCH_ASSOC);
 $q=$db->prepare('SELECT '.$foreingtable['assoc_col'].' FROM '.$foreingtable['assoc_table']);
  if($q->execute())
   echo json_encode($q->fetchAll(PDO::FETCH_COLUMN));
 }
else {
   header('http/1.1 500 Internal Server Error');
   print_r($q->errorInfo());
   exit;
 }

More about this: Get list of possible foreign key values in MySql using PDO

Mujaheed
  • 1