ShaunaBear wrote:(Running: MySQL and PHP)
I have a database with two tables. An "Items" table and a "Specifications" table. What I have so far is a search done based on the items table. On the results page I want to determine whether or not a result exists in the Specifications table. They both have a column named "productcode". Based on the comparision I then need to be able to click on the result and it will either go to an "update" page or an "insert" page (or both on the same page but only one will show based on the variable passed).
Thanks for your help!! I am new to PHP programming!
So what you are saying is that you have 2 related tables, with the key field being "productcode", and you need to show data from both tables if it exists, and allow updating, or if no matching record exists in the 2nd table, allow inserting a new record. If I have that correct, here are the considerations:
- "productcode" must be the primary key in the first table, that is, it must be unique in that table;
- "productcode" in the 2nd table can be unique or not, but the way you handle it will be different, depending on whether you want to allow multiple Specifications for the same Item;
- You need to diagram just what you want to occur for each of these possibilities:
- The requested productcode is not found in the 1st table;
- The requested productcode is found in the 1st table, but not in the 2nd (display data from 1st table and allow updating, display data entry form to insert new record in 2nd table);
- The requested productcode is found in both tables (display data from both tables, allow updating of any data).
Your query would be a simple Left Join something like this:
Code: Select all
$sql="SELECT * FROM `Items` i LEFT JOIN `Specifications` s ON i.productcode=s.productcode";
$result=mysql_query($sql) or die(mysql_error());
But here is where you have to decide several things. If the relationship is a 1-to-1 (i.e., there's only one Specification, at most, per Item), you will always have just one row returned, either with or without data from the Specifications table. But if you allow multiple Specifications per Item (a 1-to-many relationship), then you will have to enter a
while loop to fetch as many rows as the result contains, which then raises the issue of how to display them and how to update them.
Let's suppose that it's a 1-to-1 relationship: since you will always want the same display, whether there is or is not a matching record, that's not an issue. What is an issue is what your update/insert logic must look like in the 2 cases. In either case, you will need to deal with each table separately. Presumably you will have a "Submit Changes" button or something, on the form, which will use the POST method to make the form values available to the PHP script that handles it. So you will have to pick out the Items values and put them in an UPDATE query statement for the Items table (you could check to see if anything has changed first, but I doubt that would be worth doing--just overwrite what might be there), then check to see if the Specification table already has a row with the productcode; if it has, pick out the Specifications values and put them in an UPDATE query statement for the Specifications table, or it hasn't, pick out the Specifications values and put them in an INSERT query statement for the Specifications table. That's all pretty straightforward.
If you need to allow multiple Specifications per Item, it becomes considerably more complicated. Since I don't know if that's a requirement, I won't even start to discuss that.
Hope this helps.