CODEWITHSUNDEEP
phpmysqlsqlrow
Random.dmx
Random.dmx

Reputation: 33

How to pass table id to another table in PHP

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>

Upvotes: 1

Views: 2809

Answers (2)

Random.dmx
Random.dmx

Reputation: 33

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>

Upvotes: 0

Chris
Chris

Reputation: 570

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.

Upvotes: 1

Related Questions