2 Database Tables For Users - PHP Construction

[bbizzl]

Hey All,

 

Been banging my head into the wall on this one. I have 2 tables one for users 'myMembers' and one for products 'products'. Each table has a auto increment id. The myMembers id is the user id and the products table id is for the product id. I have a row in the products table for agent_id. I would like the agent_id to be filled with the id from the myMembers table.  I took a look at the manual; still do not understand how to take the id from the myMembers table then place that id into the agent_id; so the products(id) can be listed under the specific members id(agent_id) in the products table.

 

So far my script for the products table inserts items correctly, but does not file under the specific agent_id. Here is the script for entering items to the products table.

 

Thanks for the guidance!

 

 

<?php

// Script Error Reporting

error_reporting(E_ALL);

ini_set('display_errors', '1');

?>

<?php

// Delete Item Question to Admin, and Delete Product if they choose

if (isset($_GET['deleteid'])) {

echo 'Do you really want to delete product with ID of ' . $_GET['deleteid'] . '? <a href="inventory_list.php?yesdelete=' . $_GET['deleteid'] . '">Yes</a> | <a href="inventory_list.php">No</a>';

exit();

}

if (isset($_GET['yesdelete'])) {

// remove item from system and delete its picture

// delete from database

$id_to_delete = $_GET['yesdelete'];

$sql = mysql_query("DELETE FROM products WHERE id='$id_to_delete' LIMIT 1") or die (mysql_error());

// unlink the image from server

// Remove The Pic -------------------------------------------

    $pictodelete = ("../inventory_images/$id_to_delete.jpg");

    if (file_exists($pictodelete)) {

          unlink($pictodelete);

    }

header("location: inventory_list.php");

    exit();

}

?>

<?php

// Parse the form data and add inventory item to the system

if (isset($_POST['product_name'])) {

 

    $product_name = mysql_real_escape_string($_POST['product_name']);

$price = mysql_real_escape_string($_POST['price']);

$category = mysql_real_escape_string($_POST['category']);

$subcategory = mysql_real_escape_string($_POST['subcategory']);

$details = mysql_real_escape_string($_POST['details']);

// See if that product name is an identical match to another product in the system

$sql = mysql_query("SELECT id FROM products WHERE product_name='$product_name' LIMIT 1");

$productMatch = mysql_num_rows($sql); // count the output amount

    if ($productMatch > 0) {

echo 'Sorry you tried to place a duplicate "Product Name" into the system, <a href="inventory_list.php">click here</a>';

exit();

}

// Add this product into the database now

$sql = mysql_query("INSERT INTO products (product_name, agent_id price, details, category, subcategory, date_added)

        VALUES('$product_name','$price','$details','$category','$subcategory',now())") or die (mysql_error());

    $pid = mysql_insert_id();

// Place image in the folder

$newname = "$pid.jpg";

move_uploaded_file( $_FILES['fileField']['tmp_name'], "../inventory_images/$newname");

header("location: inventory_list.php");

    exit();

}

?>

<?php

// This block grabs the whole list for viewing

$product_list = "";

$sql = mysql_query("SELECT * FROM products ORDER BY date_added DESC");

$productCount = mysql_num_rows($sql); // count the output amount

if ($productCount > 0) {

while($row = mysql_fetch_array($sql)){

            $id = $row["id"];

$product_name = $row["product_name"];

$price = $row["price"];

$date_added = strftime("%b %d, %Y", strtotime($row["date_added"]));

$product_list .= "Product ID: $id - <strong>$product_name</strong> - $$price - <em>Added $date_added</em>       <a href='inventory_edit.php?pid=$id'>edit</a> • <a href='inventory_list.php?deleteid=$id'>delete</a><br />";

    }

} else {

$product_list = "You have no products yet";

}

?>

[Drummin]

In my humble opinion, I would think you would have a user table, a product table (i.e. store items) and an orders table with orders that have been placed.  While a "user" is logged in, their id goes with them, either by logging in at the last minute when the order is placed or "held" in SESSION while they browse.  Orders selected are the item id from the product table and the quantity they want.  So we're saving customer ID (from user table), item id (from product table) and any other order details like date in the orders table.