0

I have a query with bound parameters for the values for inserting data into a table. I am using PDO. The idea is that I have an invoice with an order number, date, time, etc. The invoice has a list of products with their quantities, however the # of unique products varies from one invoice to another. As a result that means the # of columns the quantities will be inserted into will vary from invoice to invoice. Invoice A with 4 products will be inserting data into 4 columns vs invoice B with 5 products which will be dealing with 5 products. 

$sth = conn->prepare("INSERT INTO test1 (col_1, col_2, col_3) VALUES (:order, :date, :time)");

$sth->bindParam(':order', $orderID);
$sth->bindParam(':date', $date);
$sth->bindParam(':time', $time);

$sth->execute();

This query works and inserts whatever values I have for the variables into the appropriate columns. How can I have the columns function similar to a bound parameter too?

When I tried something like below it did not work.

 $sth = conn->prepare("INSERT INTO test1 (:col1, :col2) VALUES (:order, :date, :time)");

  $sth->bindParam(':order', $orderID);
  $sth->bindParam(':date', $date);
  $sth->bindParam(':time', $time);
  $sth->bindParam(':col1', $col1);
  $sth->bindParam(':col2', $col2);

I made the values for the variables $col1 and $col2 column names names in the test1 table. I came across this post in my search while I tried to solve this issue which helped somewhat.

Can PHP PDO Statements accept the table or column name as parameter?

Part of the issue though is that I don't know how many column names I will be inserting into in advance and the above linked idea seems to only work if you know the # of columns in advance. Any advice on this?

Community
  • 1
  • 1
Csw
  • 105
  • 1
  • 8
  • 1
    you can't simple as that. –  Mar 01 '16 at 02:16
  • What would be an alternative? – Csw Mar 01 '16 at 02:20
  • 3
    you could use regular old php, but i worry if some one says " I don't know how many column names I will be inserting into in advance" sounds like the db schema is going to be wrong –  Mar 01 '16 at 02:21
  • The way I have the db setup currently is the following: TABLE invoices (order_id, date, total_cost). TABLE upcs (upc_id, product_name) TABLE inventory (order_id,product1, product2, product3, product4) etc. I am new to trying to setup a DB by myself so if the issue is the db is all wrong I'm happy to fix it. In my head I figured it would work like insert into inventory (order_id ) VALUES ($order_id, ); something like that. – Csw Mar 01 '16 at 02:27
  • 1
    TABLE inventory should be (order_id, product) with 1 row per product –  Mar 01 '16 at 02:32
  • How would I show that each order_id has more than 1 product then though? Wouldn't that end up being like row1: (orderid: 29498498 product: dogfood)? – Csw Mar 01 '16 at 02:42
  • 1
    (orderid: 29498498 product: dogfood), (orderid: 29498498 product: catfood) etc.. exactly how it is done. –  Mar 01 '16 at 02:44
  • 1
    this is going to get to broad for S.O i would suggest most tutorials would show you this approach. –  Mar 01 '16 at 02:45
  • ahh I think I understand now. Then I would have a 3rd separate column for quantity and store the quantity for the item there. So it would look like (orderid: 2949 product: dogfood quantity:5) (orderid 2949 product: catfood quantity:10). Everything is still associated with the order number so I would be able to easily run a select to find the information if I wanted to display all items for order 2949. Thank you. – Csw Mar 01 '16 at 02:51
  • 1
    look at joins, you can get all the products,invoice and upcs details in one query –  Mar 01 '16 at 03:01

0 Answers0