CS479/579 - Web Programming II

Displaying exercises/e8/solution/inventory.php

#!/usr/bin/php
<?php
include "config.php";

// Drop any tables that exist, so we can re-create them:
$myconn->query("DROP TABLE IF EXISTS vendor");
$myconn->query("DROP TABLE IF EXISTS component");
$myconn->query("DROP TABLE IF EXISTS inventory");

// Creates the vendor table:
$myconn->query("
create table if not exists vendor (
  vid	integer auto_increment,
  name	varchar(64),
  primary key (vid)
)") or die("create vendor " . $myconn->error . "\n");

// Creates the component table:
$myconn->query("
create table if not exists component (
  cid	integer,
  name	varchar(64),
  primary key (cid)
)") or die("create component " . $myconn->error . "\n");

// Creates the inventory table:
$myconn->query("
create table if not exists inventory (
  iid		bigint,
  cid		integer,
  vid		integer,
  model		varchar(128),
  description	varchar(128),
  price		float,
  amount	integer,
  index cid (cid),
  index vid (vid),
  primary key (iid)
)") or die("create inventory " . $myconn->error . "\n");

// Processes vendors.txt:
$vendors = [];	// We'll remember the vendor id's as we save them in this array
$vd = explode("\n", file_get_contents("vendors.txt"));

$vendor = null;
$st = $myconn->prepare("INSERT INTO vendor (name) VALUES (?)") or
  die("prepare".$myconn->error."\n");
$st->bind_param("s", $vendor);

foreach($vd as $v) {
  if (strlen($v) < 1) break;  // Stop if it's a blank line

  // Set the $vendor input and execute the prepared statement
  $vendor = $v;
  $st->execute();

  // $myconn->insert_id has the value of the last auto_increment value used
  // when inserting a new element (where the field value uses the auto_increment
  // default).  We could use a select after we've inserted everything to get
  // this same information.
  $vendors[$v] = $myconn->insert_id;
}
$st->close();


// Processes cids.txt:
$cids = [];  // Used to remember the cid for each component name.
$cd = explode("\n", file_get_contents("cids.txt"));

$cid = null; $name = null;
$st = $myconn->prepare("INSERT INTO component (cid,name) VALUES (?,?)") or
  die("prepare".$myconn->error."\n");
$st->bind_param("is", $cid, $name);

foreach($cd as $v) {
  if (strlen($v) < 1) break;
  list($cid,$name) = explode(",",$v);
  $st->execute();
  $cids[$name] = $cid;
}
$st->close();

// Processes inventory.txt
$id = explode("\n", file_get_contents("inventory.txt"));

$iid = null; $cid = null; $vid = null; $model = null; $description = null;
$price = null; $amount = null;

$st = $myconn->prepare("INSERT INTO inventory
  (iid,cid,vid,model,description,price,amount) VALUES (?,?,?,?,?,?,?)") or
    die("prepare".$myconn->error."\n");
$st->bind_param("iiissdi", $iid, $cid, $vid, $model, $description, $price, $amount);

foreach($id as $v) {
  if (strlen($v) < 1) break;
  // $comp and $vend will be converted from their names into integers using
  // the $cids and $vendors maps we created earlier:
  list($iid,$comp,$vend,$model,$description,$price,$amount) = explode(",",$v);

  $iid = (int)$iid;

  // We use the remembered cid and vid from the first two tables we setup here
  // to setup the proper cid and vid values for this insertion:
  $cid = (int)$cids[$comp];
  $vid = (int)$vendors[$vend];

  $price = (float)$price;
  $amount = (int)$amount;
  $st->execute();
}
$st->close();

?>