twilitegxa Posted March 10, 2010 Share Posted March 10, 2010 How can I check my table for existing field values and update a field if the value exists or add a new record if the value does not? I have the following page: <?php //connect to database $conn = mysql_connect("localhost", "root", "") or die(mysql_error()); mysql_select_db("smrpg",$conn) or die(mysql_error()); //get identity $get_identity = "select * from scouts where username = '".$_SESSION['userName']."' and active = '1'"; $get_identity_res = mysql_query($get_identity, $conn) or die(mysql_error()); while ($user_identity = mysql_fetch_array($get_identity_res)) { $identity = $user_identity['identity']; } //check items in inventory $get_items = "select * from inventory where identity = '$identity'"; $get_items_res = mysql_query($get_items, $conn) or die(mysql_error()); while ($items = mysql_fetch_array($get_items_res)) { $item = $items['item']; //check for existing items } ?> Right now I have it checking for all the items in the character's inventory, but now I want to check that if the $_POST['sel_item'] value appears in the table inventory in field item, then I want to update the quantity field by the $_POST['sel_item_qty'], but if not, I need to add a new record, inserting the item and quantity into the table. Can anyone tell me how to check for the existing field value and then update or insert, according the result? Quote Link to comment Share on other sites More sharing options...
twilitegxa Posted March 10, 2010 Author Share Posted March 10, 2010 So far I have this: //check for existing items if ($item == $_POST['sel_item']){ $new_quantity = ($quantity + $_POST['sel_item_qty']); //update item $update = "UPDATE inventory set quantity = $new_quantity where item = '$_POST[sel_item]'"; $update_res = mysql_query($update, $conn) or die(mysql_error()); //get price of selected item $get_price = "select * from store_items where item_title = '$_POST[sel_item]'"; $get_price_res = mysql_query($get_price, $conn) or die(mysql_error()); while ($item_price = mysql_fetch_array($get_price_res)){ $price = $item_price['item_price']; } //gather gold $get_gold = "select * from gold where identity = '$identity'"; $get_gold_res = mysql_query($get_gold, $conn) or die(mysql_error()); while ($show_gold = mysql_fetch_array($get_gold_res)) { $gold = $show_gold['amount']; } $new_gold = ($gold - $price); //set gold $update_gold = "update gold set amount = $new_gold where identity = '$identity'"; $update_gold_res = mysql_query($update_gold, $conn) or die(mysql_error()); } else { //insert item } } But how can I insert the item if the $_POST[sel_item] didn't exist in the table? I tried a regular insert statement, but it is inserting the sel_item for each other item that is already in the inventory table that isn't the sel_item. Can anyone help? //insert item $insert_item = "insert into inventory values ('', '$identity', '$_POST[sel_item]', '$_POST[sel_item_qty]', '1')"; $insert_item_res = mysql_query($insert_item, $conn) or die(mysql_error()); echo "inserted"; Ho can I just insert the record once? Quote Link to comment Share on other sites More sharing options...
twilitegxa Posted March 11, 2010 Author Share Posted March 11, 2010 I guess I found a way to do it: <?php //connect to database $conn = mysql_connect("localhost", "root", "") or die(mysql_error()); mysql_select_db("smrpg",$conn) or die(mysql_error()); //get identity $get_identity = "select * from scouts where username = '".$_SESSION['userName']."' and active = '1'"; $get_identity_res = mysql_query($get_identity, $conn) or die(mysql_error()); while ($user_identity = mysql_fetch_array($get_identity_res)) { $identity = $user_identity['identity']; } //check items in inventory $get_items = "select * from inventory where identity = '$identity'"; $get_items_res = mysql_query($get_items, $conn) or die(mysql_error()); while ($items = mysql_fetch_array($get_items_res)) { $item = $items['item']; $quantity = $items['quantity']; } //check for selected item $result = mysql_query("select * from inventory where item = '$_POST[sel_item]'"); $num_rows = mysql_num_rows($result); if ($num_rows == 1){ $new_quantity = ($quantity + $_POST['sel_item_qty']); //update item $update = "UPDATE inventory set quantity = $new_quantity where item = '$_POST[sel_item]'"; $update_res = mysql_query($update, $conn) or die(mysql_error()); //get price of selected item $get_price = "select * from store_items where item_title = '$_POST[sel_item]'"; $get_price_res = mysql_query($get_price, $conn) or die(mysql_error()); while ($item_price = mysql_fetch_array($get_price_res)){ $price = $item_price['item_price']; } //gather gold $get_gold = "select * from gold where identity = '$identity'"; $get_gold_res = mysql_query($get_gold, $conn) or die(mysql_error()); while ($show_gold = mysql_fetch_array($get_gold_res)) { $gold = $show_gold['amount']; } $new_gold = ($gold - $price); //set gold $update_gold = "update gold set amount = $new_gold where identity = '$identity'"; $update_gold_res = mysql_query($update_gold, $conn) or die(mysql_error()); } if ($num_rows == 0){ $insert_item = "insert into inventory values ('', '$identity', '$_POST[sel_item]', '$_POST[sel_item_qty]', '1')"; $insert_item_res = mysql_query($insert_item, $conn) or die(mysql_error()); echo "inserted"; } ?> Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.