Compare 2 tables based on search results
Moderator: General Moderators
-
ShaunaBear
- Forum Newbie
- Posts: 6
- Joined: Mon Jun 30, 2008 1:30 pm
Compare 2 tables based on search results
(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!
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!
Re: Compare 2 tables based on search results
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: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!
- "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).
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());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.
-
ShaunaBear
- Forum Newbie
- Posts: 6
- Joined: Mon Jun 30, 2008 1:30 pm
Re: Compare 2 tables based on search results
Thank you so much for your reply! It gives me a great jumping off point 
-
ShaunaBear
- Forum Newbie
- Posts: 6
- Joined: Mon Jun 30, 2008 1:30 pm
Re: Compare 2 tables based on search results
How do you create relationships in PHP? I am using phpMyAdmin. I was in the Items table and click on relationship views, then selected "specifications>productcode" beside "productcode" for items. I got this error "#1452 - Cannot add or update a child row: a foreign key constraint fails (`tlx/#sql-6b8_b1`, CONSTRAINT `#sql-6b8_b1_ibfk_1` FOREIGN KEY (`productcode`) REFERENCES `specifications` (`productcode`) ON DELETE NO ACTION ON UPDATE NO ACTION)"
WHat is the proper way to create a relationship? I do want a 1-to-1 relational database.
WHat is the proper way to create a relationship? I do want a 1-to-1 relational database.
Re: Compare 2 tables based on search results
Fundamentally, MySQL doesn't provide a separate "relationship" mechanism, although I believe some tools may do so. Relationships are the result of the schema design and each query that contains a JOIN. If you are using InnoDB type tables (MySQL allows you to choose among several table types, or "database engines"), you can declare CONSTRAINTS, about which I know very little. That's probably what the error message you received is referring to. It seems to be saying there are Constraints on the foreign key `productcode` in your Specifications table. At least, that's how I read it.ShaunaBear wrote:How do you create relationships in PHP? I am using phpMyAdmin. I was in the Items table and click on relationship views, then selected "specifications>productcode" beside "productcode" for items. I got this error "#1452 - Cannot add or update a child row: a foreign key constraint fails (`tlx/#sql-6b8_b1`, CONSTRAINT `#sql-6b8_b1_ibfk_1` FOREIGN KEY (`productcode`) REFERENCES `specifications` (`productcode`) ON DELETE NO ACTION ON UPDATE NO ACTION)"
WHat is the proper way to create a relationship? I do want a 1-to-1 relational database.
What does your SQL statement look like? Also, can you get to a MySQL command line? If you can, run the mysql command describe Items and describe Specifications and show us the results.
When posting code in the forum, please surround your code with [ code] and [ /code] tags, for readability. (Without the space which I added to prevent them from being interpreted.)
-
ShaunaBear
- Forum Newbie
- Posts: 6
- Joined: Mon Jun 30, 2008 1:30 pm
Re: Compare 2 tables based on search results
Here is my code so far, it is not working properly and I don't know why:
It is not displaying "productcode". It only displays "discription". What am I doing wrong?
Code: Select all
$srch = "-1";
if (isset($_GET['fldproduct_code'])) {
$srch = $_GET['fldproduct_code'];
$srch="%".$srch."%";
}
mysql_select_db($database_shauna, $shauna);
$query_rsSpecs = "SELECT * FROM `Items` i LEFT JOIN `Specifications` s ON i.productcode = s.productcode WHERE ((i.productcode LIKE '$srch' OR i.description LIKE '$srch') AND (i.website = 'ON')) ORDER BY i.productcode";
$rsSpecs = mysql_query($query_rsSpecs, $shauna) or die(mysql_error());
$row_rsSpecs = mysql_fetch_assoc($rsSpecs);
$totalRows_rsSpecs = mysql_num_rows($rsSpecs);
<BODY>
<p class="style1">Total Records Found: <?php echo $totalRows_rsSpecs ?> </p>
<?php if ($totalRows_rsSpecs > 0) { // Show if recordset not empty ?>
<table width="98%" border="0" cellspacing="0" cellpadding="2">
<?php do { ?>
<tr>
<td><?php echo '<a href="update_specs.php?recordID='.$row_rsSpecs['productcode'].'">'.$row_rsSpecs['description'].'</a>'; ?></td>
</tr>
<tr>
<td class="style1"> <?php echo $row_rsSpecs['productcode']; ?></td>
</tr>
<tr>
<td><hr size="1" color="#999999" noshade="noshade" /></td>
</tr>
<?php
} while ($row_rsSpecs = mysql_fetch_assoc($rsSpecs)); ?>
</table>
<?php } // Show if recordset not empty ?>
<?php if ($totalRows_rsSpecs == 0) { // Show if recordset empty ?>
<p>Go back and search again...</p>
<?php } // Show if recordset empty ?>
</BODY>Re: Compare 2 tables based on search results
(Note that I have edited your previous post (I'm a moderator, I have awesome powers!
) to enclose your code in [ php] and [ /php] tags, for readability. It will help if you will do that on your own postings.)
OK, you have several serious PHP syntax errors that must be corrected. Using the line numbers shown in the post above, at line 15, you begin some php code with the proper tag, but on the very next line you have html code, which the parser will throw away because it's not valid php code. Either end your php block before you continue with html or you can echo the html code, which tells the parser to send it on through to the browser. You have the same issue at line 32. You did it correctly at line 17, although it's more common to use the form:
Then you don't need to check whether any rows were returned, because if not, the while fails on the first try and the code block is never executed. Also, the do { ... } while ... form means that it will always execute the first time, which you really don't want here, I think.
Try fixing those errors and see what happens.
OK, you have several serious PHP syntax errors that must be corrected. Using the line numbers shown in the post above, at line 15, you begin some php code with the proper tag, but on the very next line you have html code, which the parser will throw away because it's not valid php code. Either end your php block before you continue with html or you can echo the html code, which tells the parser to send it on through to the browser. You have the same issue at line 32. You did it correctly at line 17, although it's more common to use the form:
Code: Select all
while ($row_rsSpecs = mysql_fetch_assoc($rsSpecs)); {
// do your row processing
}Try fixing those errors and see what happens.
-
ShaunaBear
- Forum Newbie
- Posts: 6
- Joined: Mon Jun 30, 2008 1:30 pm
Re: Compare 2 tables based on search results
That didn't really fix my problem, I still get the exact same result. The problem I am having is that it is only showing "productcode" for the items that have the same productcode in both tables. I need "productcode" from the items table to show no matter what. If "productcode" from table "items" does not exisit in table "specifications" then the "productcode" is passed to the INSERT specifications form. "If productcode" from table "items" does match "productcode" from specifications then the "productcode" is passed to the UPDATE specifications form.
Specifications table only has one entry for testing purposes right now. Items table has a few thousand entries.
Bottom line, I need the productcode to show up whether it exists in the specs table or not!
Here is a screenshot of the results. Notice the bottom left corner, it is not displaying the recordID (productcode) and under each item description it is not displaying the productcode. The only one that does is because it is in my specifications table. How do I get all the productcodes to display? How am I able to determain if it does or does not exisit in the specs table?
Specifications table only has one entry for testing purposes right now. Items table has a few thousand entries.
Bottom line, I need the productcode to show up whether it exists in the specs table or not!
Code: Select all
<?php
$srch = "-1";
if (isset($_GET['fldproduct_code'])) {
$srch = $_GET['fldproduct_code'];
$srch="%".$srch."%";
}
mysql_select_db($database_shauna, $shauna);
$query_rsSpecs = "SELECT * FROM `Items` i LEFT JOIN `Specifications` s ON i.productcode = s.productcode WHERE ((i.productcode LIKE '$srch' OR i.description LIKE '$srch') AND (i.website = 'ON')) ORDER BY i.productcode";
$rsSpecs = mysql_query($query_rsSpecs, $shauna) or die(mysql_error());
$row_rsSpecs = mysql_fetch_assoc($rsSpecs);
$totalRows_rsSpecs = mysql_num_rows($rsSpecs);
?>
<BODY>
<p class="style1">Total Records Found: <?php echo $totalRows_rsSpecs ?> </p>
<!-- BEGIN Show if recordset not empty -->
<?php if ($totalRows_rsSpecs > 0) { ?>
<table width="98%" border="0" cellspacing="0" cellpadding="2">
<!-- BEGIN Show search results -->
<?php while ($row_rsSpecs = mysql_fetch_assoc($rsSpecs)) { ?>
<tr>
<td><?php echo '<a href="update_specs.php?recordID='.$row_rsSpecs['productcode'].'">'.$row_rsSpecs['description'].'</a>'; ?></td>
</tr>
<tr>
<td class="style1"> <?php echo $row_rsSpecs['productcode']; ?></td>
</tr>
<tr>
<td><hr size="1" color="#999999" noshade="noshade" /></td>
</tr>
<?php } ?>
<!-- END Show search results -->
</table>
<?php } ?>
<!-- END Show if recordset not empty -->
<!-- BEGIN Show if recordset empty -->
<?php if ($totalRows_rsSpecs == 0) { ?>
<p>Go back and search again...</p>
<?php } ?>
<!-- END Show if recordset empty -->
</BODY>
<?php
mysql_free_result($rsSpecs);
?>
- Attachments
-
- Screenshot of Results page
- PHP.jpg (97.59 KiB) Viewed 313 times
Re: Compare 2 tables based on search results
If your tables are set up in the usual relational database style, the description would only exist in the Specs table, so there would be no information in the database to display! A proper database design would not duplicate such data in both tables, it would use the ID key field to extract that information from the Specs table. Also, you would simply not be able to even have a record in the Items table for which there was no matching record in the Specs table. Without seeing a complete field list of both tables, I have to guess what you have to work with.ShaunaBear wrote:The problem I am having is that it is only showing "productcode" for the items that have the same productcode in both tables. I need "productcode" from the items table to show no matter what.
...
Specifications table only has one entry for testing purposes right now. Items table has a few thousand entries.
Bottom line, I need the productcode to show up whether it exists in the specs table or not!
What the URL in the lower left corner shows is that the $_GET parameter being passed is empty (?recordid=), and sure enough, in your code, you have:Here is a screenshot of the results. Notice the bottom left corner, it is not displaying the recordID (productcode) and under each item description it is not displaying the productcode. The only one that does is because it is in my specifications table. How do I get all the productcodes to display? How am I able to determain if it does or does not exisit in the specs table?
Code: Select all
<a href="update_specs.php?recordID='.$row_rsSpecs['productcode'].'">'.$row_rsSpecs['description'].'</a>';
It's pretty hard for me to figure out what your script is intended to do. You will have to somehow resolve these issues before it will produce what you want. At the heart of it is that it appears that this was a script that was designed to use different variables than you are using and that you haven't sorted all that out. It is further complicated by the fact that you are trying to get results with test data that isn't complete enough for it to work in the first place. I'd recommend that you use test tables with similar names, but with "test_" at the beginning, and populate the tables with at least several Specs and several Items that validly reference your test specs. Then at least you will be able to try to run your script and expect to get results. You'll still have to contend with the missing Specs when you try to connect to the real tables.