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 represent the id from the myMembers 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> &nbsp; &nbsp; &nbsp; <a href='inventory_edit.php?pid=$id'>edit</a> &bull; <a href='inventory_list.php?deleteid=$id'>delete</a><br />";
    }
} else {
	$product_list = "You have no products yet";
}
?>

[chrishea]

Your Insert statement on line 44 doesn't look to be correct. The column names and the values need to be in sync. The second column specified was agent_id but the second value is $price. Yoy are also missing a comma after agent_id.

For an insert you can use the alternate format
set a='xxxxx', b='yyyyy',...
This makes it easier to be sure that everything is specified properly.

[bbizzl]

Thanks for the response.
I fixed the agent_id. I still am a little lost on the insert. Are you able to expand upon. 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.
Thanks Again!

[chrishea]

Your Insert needs to have an agent_id in the Values list to insert into the table. The question is: where is that agent_id coming from? Is it entered and passed to this module as a $_POST variable or is it coming from somewhere else? You need to answer that then use the variable in the Values list. If you know the member name and need to get the id, you'll have to Select from the MyMembers table by the name in order to get the ID.

There is no magic to this. For the tables to be linked by agent id you must provide the appropriate agent_id value to insert into the row in the products table.