SQL PHP Insert issue

Question

Cheers,

So, I have a "parent table" where I need to insert some stuff. The "parent table" - FILES contains

id_file
id_cust - foreign key for idcust from table Customers
register_date
id_object - foreign key for idcust from table Objects

Table Customers

idcust
cust_name
address
phone

Table Objects

idobject
name_object

I have created an HTML form to insert a new FILE which contains 2 combo boxes and register date

<form method="post" action="">
<tr>
<td>Customer name: </td> <td> 
<select name="cust_name">
<?php 
$sql = mysqli_query($conn, "SELECT * FROM customers");
while ($row = $sql->fetch_assoc()){ 
echo "<option value=\"cust_name1\">" . $row['cust_name'] . "</option>";
}?>
</select></td></tr>

<tr>
<td>Register date</td>
<td> <input  type="date" name="register_date"/></td>
</tr>

<tr>
<td>Object: </td> <td> 
<select name="object">
<?php 
$sql = mysqli_query($conn, "SELECT * FROM objects");
while ($row = $sql->fetch_assoc()){ 
echo "<option value=\"name_object1\">" . $row['name_object'] . "</option>";
}?>
</select></td></tr>
<tr><td colspan=2> <input name ="submit" type="submit" value="Add a new file"></td>
</tr></form>

What I have tried:

Since yesterday, I have tried a looooot of queries, this is the last one:

<?php

 $conn = mysqli_connect("localhost", "root", "", "testdb");
 if ($conn -> connect_error){
   die("Connection failed:". $conn-> connect_error);
 }
 if(!empty($_POST['submit'])){

   $cust_name = $_POST['cust_name'];
       $register_date  = date('Y-m-d',strtotime($_POST['register_date']));
       $name_object =$_POST['name_object'];

   $sql = "INSERT INTO files VALUES (
   (SELECT * FROM customers c WHERE c.cust_name = $cust_name),
   $register_date,
   (SELECT * FROM objects o  WHERE o.name_object = $name_object)
   )";

   $conn->query($sql);
   if($conn->error){
     echo $conn->error;
   } else
   {
     $message= "We have added the file no. " .$conn->insert_id;
   }
 }
 ?>

I have tried with LEFT JOIN, without any success... Can you help me with this query? Thank you.

You don't have a form field called "name_object", so your `$name_object` variable will have no contents. — droopsnoot, Jan 21 '20 at 13:04
When you populate your drop-downs in the HTML form, you should assign the id from each table to the `value`, not as you are doing here. What if you have two customers with the same name? You seem to assign the same value to each option. Use the ids, then you can do as @Jens said above, and the values will already be correct. — droopsnoot, Jan 21 '20 at 13:05
In any case, if your method would work (and it might, if you sorted out the values issue), you would need to `select id`, not `select *` - what do you think it will do with all the columns that are returned, when you only need one value? You should only `select` the columns you need, everywhere. — droopsnoot, Jan 21 '20 at 13:09
I'll put the issues in an answer where I can show quoted code more easily. — droopsnoot, Jan 21 '20 at 13:19
Does this answer your question? [How to include a PHP variable inside a MySQL statement](https://stackoverflow.com/questions/7537377/how-to-include-a-php-variable-inside-a-mysql-statement) — Dharman, Jan 21 '20 at 16:48

score -1 · Answer 1 · answered Jan 21 '20 at 13:24

There are several problems with the code as you have it. First of all, in this line, you want the value of a form-field called name_object.

 $name_object =$_POST['name_object'];

but there is no such form name. So either change that code to use the correct field name, or change the form to use this name. Spot the difference:

<select name="object">

In here:

echo "<option value=\"cust_name1\">" . $row['cust_name'] . "</option>";

and here:

echo "<option value=\"name_object1\">" . $row['name_object'] . "</option>";

you're setting the value for every drop-down to be the same value, both of them text strings, which are almost certainly not in your other tables. Just do a "view source" on the form when it's displayed to see what I mean.

Change the above to use the unique-id for each table, which will pass them through to your form processing code. Once in there, all you need to do is

 $sql = "INSERT INTO files (id_cust, register_date, id_object) 
         VALUES ($cust_name, $register_date, $name_object)";

after you've done some validation of the various values and fixed the field name issue.

Of course, once it's working, you'll want to change it to use prepared statements rather than just concatenating strings into the query.

Sorry about object, I have fixed it, but it still doesn't work - Notice: Undefined index: cust_name .I guess it doesn't associate the cust_name (and idcust) from table customers with id_cust from the table files. — avT432, Jan 21 '20 at 13:50
Where is that error message appearing? When you display the form and options, or when you try to submit it? If it's the latter, what do you see when you `var_dump($_POST)` ? What does your code look like now? — droopsnoot, Jan 22 '20 at 13:42

SQL PHP Insert issue

1 Answers1