2 Database Tables For Users - PHP Construction

PHP
1

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> &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";
}
?>
2

Where is the value for agent_id meant to come from? Possibly a user id from a session?

3

Exactly, the session start comes from the user id from the myMembers table.

4

Hey Space Phoenix,

Do you know how I can get that agent_id filled with the id of the myMembers table. Thanks!

5 
INSERT INTO
    products (
          product_name
        , agent_id
        , price
        , details
        , category
        , subcategory
        , date_added
    )
    VALUES(
          '$product_name'
        [COLOR=#ff0000], '$agent_name'[/COLOR]
        , '$price'
        , '$details'
        , '$category'
        , '$subcategory'
        , now()
    )

Add the bit that I’ve highlighted in red

6

I made changes. The id from the myMembers table still won’t go into the agent_id. The agent_id from the products table won’t come from a field; It needs to match the id from my_members. The session starts from a different script in the same page. The session is identified by the id in myMembers.

I am slightly newer to php, thanks for the help!

7

Is it true that each product can only ever have one agent?

How does a user differ from an agent?

8

Correct the user id from the myMembers needs to be the same as the agent_id. Each product needs an agent_id. Users can have multiple products. There just needs to be a agent_id with it. This way only the user that created the product can edit or change it.

9

You need to add just before the INSERT query which needs the agent id

$agent_id=$_SESSION['id];