Checking For Existing Field Value??

[twilitegxa]

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?

[twilitegxa]

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?

[twilitegxa]

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";

}


?>