Comparing fields of one table to other fields of another table

Question

Hello! I have a function called add_item() which actually inserts values in a field item_name of temporary table called temp having fields temp_id and item_name. I have another table calleed item which consists of fields item_id, item_name, price. I have another table called quotation which consists of fields q_id, item_id,item_name,price. Now I cant figure out how do i compare the item_name from temp to the field item_name from item. And then, insert the values of item in quotation table. Can anyone guide me please? For example i have the following records in temp
temp_id item_name
1                kit
2               drill
3             tester

and i have the following records in item:
id  item_name       price
1       drill A             100
2       drill B             354
3      drill C              743
4      tester              643
5      cccc                643

What I want to do is compare the item_name from temp table with that of table item. after comparing i should insert these values in table quotation
quotation_id       searched_name item_name price
1                         kit                       not found     null
2                        drill                         drill A        100
3                        drill                         drill B        354
4                       drill                         drill C         743
5                      tester                       tester        643
Can someone please guide me where to start?Thanks

Answer 1

If you mean how to refer to fields in multiple tables/databases in a single query, you use the '.' notation:

SELECT table1.id, table2.value, table3.discount etc...

with the appropriate tables specified in your FROM and JOIN clauses. This can be extended to refer to fields in other databases entirely:

SELECT table1.id, table2.value
FROM database1.table1, database2.table2

update:

INSERT INTO quotations
   SELECT item.item_name AS item_name, price
   FROM records
   LEFT JOIN temp ON item.item_name = temp.item_name;

should do the trick, assuming your quotations table has item_name and price fields. This will pull out any records where the item_Name is the same in both tables, and insert the item_name and price into quotations.

Answer 2

//select query for getting data from both tables

$get_data=mysql_query("SELECT *,temp.item_name AS searched, item.item_name AS response FROM temp JOIN item ON temp.item_name = item.item_name");

while($datas=mysql_fetch_array($get_data)) {

          //inserting into quotation table
          mysql_query("INSERT INTO quotation SET 
                       searched_name='".$datas['searched']."',
                       item_name='".$datas['response']."',
                       price='".$datas['price']."' ");

}