Problem adding user entered data to multiple tables

Question

gymangel812 0 Newbie Poster

13 Years Ago

I need to add a new sandwich name, description, origin, price and picture to a database. These are to be added to multiple tables. Ignore the drop down menu, it doesn't have anything to do with the query. The problem I'm having is getting the new product_id which is auto incremented then using that id to add the other data to the other tables.

<html>
<body>

<h3>Homework 2</h3>

<form enctype="multipart/form-data" action="process_menuhw2.php" method="post">
<p><table border=0>
<TR>
<TD>Sandwich Name: </TD>
<TD>

<?php
  require_once ('mysqli_connect.php');

  $sql = "Select products.productid, product_name, product_description, origin, image_name, price
from products, origins, images, prod_origins
Where products.productid = prod_origins.productid
And products.productid = images.productid
And PROD_ORIGINS.ORIGINID = ORIGINS.ORIGINID ORDER BY product_name ASC";

  $result = mysqli_query($dbc, $sql) or die( "Could not execute query: $query" );

  $str = "<SELECT NAME=product_name><BR>\n";
  while ($row = mysqli_fetch_array($result)) {
        $str .= '<OPTION VALUE=' .  $row['product_id'] . '>' . $row['product_name'] . '<BR>' . "\n";
  }
  $str .= '</SELECT>';

  echo $str
?>

</TD>
</TR>

<input type="hidden" name="MAX_FILE_SIZE" value="1000000">
<TR>
<TD>Enter a Sandwich Name:</TD>
<TD><input type="text" name="product_name" size="41"></TD>
</TR>

<TR>
<TR>
<TD>Sandwich Description:</TD>
<TD><input type="text" name="product_description" size="41"></TD>
</TR>
<TR>
<TD>Sandwich Origin:</TD>
<TD><input type="text" name="origin" size="41"></TD>
</TR>
<TR>
<TD>Sandwich Price:</TD>
<TD><input type="text" name="price" size="41"></TD>
</TR>
<TR>
<TR>
<TR>
<TD>Upload sandwich image: </TD>
<TD><input type="file" name="filename" size="41"></TD>
</TR>

</table>
<BR><BR>
<input type="submit" value="Submit">
</form>

</body>
</html>

<?php

echo '<html>
<head>
<title>Uploading...</title>
</head>
<body>
<h3>Uploading file...</h3>';

if ($_FILES['filename']['error'] > 0)
{
	echo 'Problem: ';
    	switch ($_FILES['filename']['error'])
    	{
      		case 1:  echo 'File exceeded upload_max_filesize';  break;
      		case 2:  echo 'File exceeded max_file_size';  break;
      		case 3:  echo 'File only partially uploaded';  break;
      		case 4:  echo 'No file uploaded';  break;
    	}
    	exit;
}

// put the file where we'd like it
$upfile = '../images/'.$_FILES['filename']['product_name'];

if (is_uploaded_file($_FILES['filename']['tmp_name']))
{
     	if (!move_uploaded_file($_FILES['filename']['tmp_name'], $upfile))
     	{
        	echo 'Problem: Could not move file to destination directory';
        	exit;
     	}
}
else
{
    	echo 'Problem: Possible file upload attack. Filename: ';
    	echo $_FILES['filename']['prduct_name'];
    	exit;
}

echo '<p>File uploaded successfully<br><br>';

if (isset($_POST['product_description'])) {
	$description = $_POST['description'];
} else {
        $description = $_FILES['filename']['product_name'];
}

// show what was uploaded
echo '<p>Preview of uploaded file:<br><hr>';
echo "<a href='$upfile'>$product_description</a>";
echo '<br><hr>';
echo '</body></html>';

// Set up the database connection.
require_once ('mysqli_connect.php');

    $product_name = $_POST['product_name'];
    $product_description = $_POST['product_description'];
    $price = $_POST['price'];

    $query = "INSERT INTO products (product_name, product_description, price)
                           VALUES ('$product_name', '$product_description', '$price')";

    $result = mysqli_query ($dbc, $query);

    $result = mysqli_query ($dbc, $query);

    $origin = $_POST['origin'];

    $query = "INSERT INTO origins (origin) VALUES ('$origin')";

    $result = mysqli_query ($dbc, $query);

    $imagename = $_POST['image_name'];

    $query = "INSERT INTO images (image_name) VALUES ('$imagename')";

    $result = mysqli_query ($dbc, $query);


//$product_name     = $_POST['product_name'];
//$imagename = $description;

echo 'Productid: ' . $productid . ' ' . 'Product Description: ' . $product_description . ' ' . 'Image Name:' .
$imagename . 'Origin: ' . $origin . 'Price: ' . $price . '<br>';

// Set up the query
 //   $query = "INSERT INTO products (productid, product_name, product_description)
                           VALUES ($productid, '$product_name', '$product_description')";

// Run the query and retrieve the result
$result = mysqli_query ($dbc, $query);

if ($result) { // If it ran OK.

		echo '<p>Data has been entered successfully.</p>';

	} else { // If it did not run OK.

		echo '<p>Data has not been processed due to a system error.</p>';

        }

?>

Here is the DB info:

/*
SQLyog - Free MySQL GUI v5.19
Host - 5.0.24a-community-nt : Database - sandwichesdb
*********************************************************************
Server version : 5.0.24a-community-nt
*/


SET NAMES utf8;

SET SQL_MODE='';

create database if not exists `sandwichesdb`;

USE `sandwichesdb`;

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO';

/*Table structure for table `images` */

DROP TABLE IF EXISTS `images`;

CREATE TABLE `images` (
  `imageid` int(3) unsigned NOT NULL auto_increment,
  `productid` int(3) unsigned NOT NULL,
  `image_name` varchar(30) default NULL,
  PRIMARY KEY  (`imageid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `images` */

insert into `images` (`imageid`,`productid`,`image_name`) values (1,1,'andouille_sausage.jpg'),(2,2,'banh_mi.jpg'),(3,3,'miso_marinated_jidori_chicken.'),(4,4,'tunisian_merguez.jpg'),(5,5,'indian_pav_bhaji.jpg'),(6,6,'indian_pav_vada.jpg'),(7,7,'croatian_pljeskavica.jpg'),(8,8,'yucatan_poc_chuc.jpg'),(9,9,'chile_relleno_torta.jpg'),(10,10,'chinese_roujiamo.jpg'),(11,11,'japanese_tartare.jpg'),(12,12,'israeli_tostee.jpg'),(13,13,'israeli_tostee_2.jpg'),(14,14,'salvadoran_turkey.jpg');

/*Table structure for table `origins` */

DROP TABLE IF EXISTS `origins`;

CREATE TABLE `origins` (
  `originid` int(3) unsigned NOT NULL auto_increment,
  `origin` varchar(30) default NULL,
  PRIMARY KEY  (`originid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `origins` */

insert into `origins` (`originid`,`origin`) values (1,'French\r'),(2,'Vietnamese\r'),(3,'Japanese\r'),(4,'Tunisian\r'),(5,'Indian\r'),(6,'Croatian\r'),(7,'Mexican\r'),(8,'Chilean\r'),(9,'Chinese\r'),(10,'Israeli\r'),(11,'Salvadoran\r');

/*Table structure for table `prod_origins` */

DROP TABLE IF EXISTS `prod_origins`;

CREATE TABLE `prod_origins` (
  `poid` int(3) unsigned NOT NULL auto_increment,
  `productid` int(3) unsigned NOT NULL,
  `originid` int(3) unsigned NOT NULL,
  PRIMARY KEY  (`poid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `prod_origins` */

insert into `prod_origins` (`poid`,`productid`,`originid`) values (1,1,1),(2,1,4),(3,2,1),(4,2,2),(5,3,3),(6,4,1),(7,4,4),(8,5,5),(9,6,5),(10,7,6),(11,8,1),(12,8,7),(13,9,8),(14,10,9),(15,11,3),(16,12,10),(17,13,10),(18,14,11);

/*Table structure for table `products` */

DROP TABLE IF EXISTS `products`;

CREATE TABLE `products` (
  `productid` int(3) unsigned NOT NULL auto_increment,
  `product_name` varchar(30) default NULL,
  `product_description` varchar(110) default NULL,
  `price` decimal(5,2) default NULL,
  PRIMARY KEY  (`productid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `products` */

insert into `products` (`productid`,`product_name`,`product_description`,`price`) values (1,'Andouille','\"Layered over two lean links, seasoned with no less than three pepper varieties, is a tart-sweet caponata of m','5.95'),(2,'Banh Mi','\"Sizzling shards of tender marinated beef are stacked into a French-style torpedo roll so fresh from the shop\'','4.95'),(3,'Jidori','\"Miso-marinated Jidori chicken baguette with salty, yeasty miso never dominating the delicate meat, and a subt','6.95'),(4,'Merguez','\"Perfumed with cinnamon and redolent of fennel, it comes anointed with a peppery harissa sauce and substantial','4.95'),(5,'Pav Bhaji','\"Rich vegetable curry, mounded onto slider-style buns\"','5.25'),(6,'Pav Vada','\"Tongue-scalding potato-bean patty, laced with fresh herbs on pav rolls and smeared with spicy chutney, puts c','5.25'),(7,'Pljeskavica','\"Baked somun, a descendant of pita, drinks up savory juices from the cevapcici filled with a sausage-like beef','6.50'),(8,'Poc Chuc','\"Served on French baguettes, preferring their firmer texture to sop up the sour-orange-and-garlic-instilled me','5.25'),(9,'Relleno Torta','\"Atop a grilled roll loaded with avocado slices, a translucent smear of beans and crema, sits a beautifully ro','5.50'),(10,'Roujiamo','\"A crisp-topped baked bun, dense and layered, with rich carnitas-like roasted pork cubes piled inside topped w','5.75'),(11,'Tartare','\"Spicy tuna tartare, rimmed with avocado and wasabi on a raft of sourdough for Sushi lovers.\"','6.95'),(12,'Tostee','\"Pita-like bagel stuffed with feta, olives and house sauce or or mozzarella sluiced with marinara.\"','4.95'),(13,'Tostee','\"Pita-like bagel stuffed with feta, olives and house sauce or or mozzarella sluiced with marinara.\"','4.95'),(14,'Pan Con Pavo','Native wild turkey seasoned with local chiles and served on a European-style torpedo roll.','5.50');

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

php

2 Contributors
1 Reply
232 Views
11 Hours Discussion Span
Latest Post 13 Years Ago Latest Post by LethargicCoder

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

LethargicCoder 0 Junior Poster in Training · Answer 1 · 2010-05-02T03:38:31+00:00

Insert Product and then retrieve the id by $productid = mysql_insert_id(); Insert Origin and then retrieve the id by $originid = mysql_insert_id(); Insert Image using above $productid Insert Product_Origin using above $productid and $originid See PHP Manual for more information on mysql_insert_id() http://php.net/manual/en/function.mysql-insert-id.php

$query = "INSERT INTO products (product_name, product_description, price)
                           VALUES ('$product_name', '$product_description', '$price')";

//    $result = mysqli_query ($dbc, $query); // Duplicate for some reason.

    $result = mysqli_query ($dbc, $query);
    $productid = mysql_insert_id(); // Get newly created product ID


    $origin = $_POST['origin'];

    $query = "INSERT INTO origins (origin) VALUES ('$origin')";

    $result = mysqli_query ($dbc, $query);
    $originid = mysql_insert_id(); // Get newly created origin ID

    $imagename = $_POST['image_name'];

    $query = "INSERT INTO images (productid, image_name) VALUES ($productid, '$imagename')"; // Use product ID from above

    $result = mysqli_query ($dbc, $query);

// Add your prod_origins item using values from above
    $query = "insert into prod_origins (productid, originid) values ($productid, $originid)";

    $result = mysqli_query ($dbc, $query);