How to pass table id to another table in PHP

Question

There are 02 tables called item and customer.

item(item_id, item_name) customer(cus_id, iid, cus_name)

I just tried to store item_id from item to the iid in the customer. but it always showing null values. My database is item_sales.

Here is my PHP code

<html>
<title></title>
<head></head>
<body>

<?php 
$hostname = "localhost";
$database = "item_sales";
$username = "root";
$password = "";

$con = mysql_pconnect($hostname, $username, $password);

error_reporting(0);

?>

<form action="index.php" method="post" enctype="multipart/form-data">

<p>Customer Name : <input type="text" name="cus_name" /><br/><br/>  </p>
<p>Select an Item: 
<select name="iid">
        <?php
            $sql = mysql_query("SELECT * FROM item");
            mysql_select_db($database,$con);
            while($sqlv = mysql_fetch_array($sql)) 
            { ?>
                <option id="<?php echo $sqlv['item_id']; ?>"><?php echo $sqlv['item_name']; ?></option>
            <?php } ?>
    </select>
    </p>

 <?php

 if(isset($_POST['submit']))
 {
        $sql2 = "SELECT * FROM item WHERE iid='%item_id%'";
        mysql_select_db($database,$con);
        $mydata = mysql_query($sql2);

        $cus_name = $_POST['cus_name'];


        $sql3 = "INSERT INTO customer (cus_id, iid, cus_name) VALUES ('', '$_POST[iid]', '$cus_name')";
        mysql_query($sql3);

 }
 ?>



 <input type="submit" name="submit" value="Add Sale" />
</form>


</body>
</html>

The is very dangerous code. You're allowing POST variables to be directly inserted into the database with absolutely no validation. This leaves you application open to sql injection. — Chris, Aug 08 '15 at 06:18
Despite that, not sure what the problem is, but I'm not sure where the value for %item_id% is being set for the second SQL. This is probably your problem. — Chris, Aug 08 '15 at 06:19
I have no idea to correct this code. but i just want to store `item_id` from `item` table to the `iid` in the `customer` table when I select an option from the drop down list. That `iid` should link to the `item_id` in the `item` table. can you please correct this code ? — Random.dmx, Aug 08 '15 at 07:02
And what does this code do? $sql2 = "SELECT * FROM item WHERE iid='%item_id%'"; mysql_select_db($database,$con); $mydata = mysql_query($sql2); — Chris, Aug 08 '15 at 07:08
`iid` field has always `null` value. `null` means `0` value. This is the process. I have `02` tables. `item` and `customer` `item` table has `02` fields. `item_id` & `item_name`. `item_name` has values `TV` , `Hi-Fi`. This values display in drop down list. ... `TV` id is `1` & 'Hi-Fi' id is `2`. And there is a text field `cus_name`. When I add a customer, every time I select an `item` from dropdown list. These values should save in `customer` table with selected `item` id in the `iid` in the `customer` table. — Random.dmx, Aug 08 '15 at 07:24
`cus_id` = `1`, `iid` = `1` (That is should TV) `cus_name` = `Random` This is the process what I want. But i couldn't store that dropdown list `item_id` to `iid` in the `customer` table. — Random.dmx, Aug 08 '15 at 07:27

score 1 · Answer 1 · edited May 23 '17 at 12:14

The reason it is not working is that you are attempting to save the iid select into the iid field, and I'm guessing the iid field in customer is a numeric type field, like INT - using the POST variable like this, you are going to be saving the text of the SELECT rather than the val.

What you need to do to fix this particular problem is set a "value" on each of the select options. You've set an ID but thats no real help here.

<select name="iid">
    <?php
    $sql = mysql_query("SELECT * FROM item");
    mysql_select_db($database,$con);
    while($sqlv = mysql_fetch_array($sql)) 
    { ?>
        <option value="<?php echo $sqlv['item_id']; ?>"><?php echo $sqlv['item_name']; ?></option>
    <?php } ?>
</select>

This is besides the point your code is very dangerous. I would recommend you do not use the original mysql functions as, 1) they don't offer any real protection from malicious users, and 2) they will be removed from PHP support very soon.

See this SO article on how to replace the mysql functionality from your PHP code : How can I prevent SQL injection in PHP?

That article also might help you understand the dangers your code offers.

I don't understand what you are saying. I didn't get a solution. Any way Thank you for replying Mr. Chris. — Random.dmx, Aug 08 '15 at 07:35
YESSSSSS !!!!!!! IT WORKED :) :) :) THANK YOU SOOOOOOOOOO MUCH Mr Chris...... I changed somthing in my code :) but you are the man :) Thank you :) — Random.dmx, Aug 08 '15 at 09:01
Glad its solved, but please read into SQL injection and make your code less susceptible to attack/abuse — Chris, Aug 08 '15 at 09:13

score 0 · Accepted Answer · answered Aug 08 '15 at 09:03

The correct code is following :

<html>
<title></title>
<head></head>
<body>

<?php 
$hostname = "localhost";
$database = "item_sales";
$username = "root";
$password = "";

$con = mysql_pconnect($hostname, $username, $password);

error_reporting(0);

?>

<form action="index.php" method="post" enctype="multipart/form-data">

<p>Customer Name : <input type="text" name="cus_name" /><br/><br/>  </p>
<p>Select an Item: 
<select name="iid">
        <?php
            $sql = mysql_query("SELECT * FROM item");
            mysql_select_db($database,$con);
            while($sqlv = mysql_fetch_array($sql)) 
            { ?>
                <option value="<?php echo $sqlv['item_id']; ?>"><?php echo $sqlv['item_name']; ?></option>
            <?php } ?>
    </select>
    </p>

  <?php

  if(isset($_POST['submit']))
  {
        $sql2 = "SELECT * FROM item";
        mysql_select_db($database,$con);
        $mydata = mysql_query($sql2);

        $cus_name = $_POST['cus_name'];
        $iid = $_GET['item_id'];


        $sql3 = "INSERT INTO customer (cus_id, iid, cus_name) VALUES ('', '$_POST[iid]', '$cus_name')";
        mysql_query($sql3);

    }
    ?>



    <input type="submit" name="submit" value="Add Sale" />
 </form>


 </body>
 </html>

How to pass table id to another table in PHP

2 Answers2