Click here to Skip to main content
15,888,610 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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


<pre><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.
Posted
Updated 21-Jan-20 1:27am
Comments
Richard Deeming 21-Jan-20 7:25am    
Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

PHP: SQL Injection - Manual[^]

1 solution

Let me start by giving you a pointer in how to set up the drop-list <option> elements in the form:

You populate your drop lists from your child tables.
For the value of each option, use the actual value you plan to insert into your table - i.e., the acceptable list of foreign keys - and thus only acceptable values.
(You can put whatever you like for the user to see in the select list - the text part of your child tables is good idea)

Now you only have valid options for the foreign keys as possible entries.

Before you begin this - make sure your input query is actually going to work by building one by hand (i.e., hard code it) and send it in through your application. If a "guaranteed" correct one won't work you have a problem to solve right there in what the parent table wants and why you think it wants!

Finally - and this is a "big one": if you get any error messages, what are they? If the wrong data is going into the table, what do you send and what do you get? That makes diagnosing the actual problem possible.
 
Share this answer
 
Comments
Member 14721629 21-Jan-20 7:56am    
I am really, really stuck here. Can you help me with this? Or suggest me something? I really need a little help..
W Balboos, GHB 21-Jan-20 10:01am    
Addendum to original answer: set up a screen dump of the actual SQL you're sending to the server. Is it what you think? Are the quotation marks properly matched. If you build the option lists as I noted, the values sent to the form target are ready for insertion "as is".

For example, is
 $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)
   )";

really giving you what you want for input?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900