我有一个问题检查如果我输入的数据已经存在在数据库表和
中如果不是，那么将它插入到表中。否则必须更新数据。有什么想法?
$bill_year=$_POST['bill_year']; //Getting the year value from dropdown list
$bill_month=$_POST['bill_month'];//Getting the Month value from dropdown list
$bill_value=$_POST['bill_value'];//Getting the Bill Value
//Todays date & Time
$tDate=date("Y/m/d");
$today=date('Ymd', strtotime($tDate));
$curr_dt=date("Y-m-d h:i:s A");
$curr_year=date('Y');// Getting the current year
$curr_month=date('m');//Getting the Current month
if(isset($_POST['enter_bill_value']))
{
if($curr_year >= $bill_year)// checking wether bill_year <= current year
{
if(is_numeric($bill_value))// checking wether bill_value is numeric?
{
//Checking Month number values.....
if($bill_month=='January')
{
$month_value=1;
}else if($bill_month=='February')
{
$month_value=2;
}else if($bill_month=='March')
{
$month_value=3;
}else if($bill_month=='April')
{
$month_value=4;
}else if($bill_month=='May')
{
$month_value=5;
}else if($bill_month=='June')
{
$month_value=6;
}else if($bill_month=='July')
{
$month_value=7;
}else if($bill_month=='August')
{
$month_value=8;
}else if($bill_month=='September')
{
$month_value=9;
}else if($bill_month=='October')
{
$month_value=10;
}else if($bill_month=='November')
{
$month_value=11;
}else if($bill_month=='December')
{
$month_value=12;
}
echo "huugffyyti";
//Connecting the DB an Send te values to Bill table
$host="localhost"; //Host name
$username="root"; // Mysql username
$password=""; // Mysql password
$db_name="wcm"; // Database name
$tbl_name="bill"; // Table name
// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or
die("cannot select DB");
$result2 =mysql_query("SELECT * FROM $tbl_name
WHERE bill_year='$bill_year',bill_month='$bill_month'");
$affected_rows=mysql_num_rows($result2);
if($affected_rows==1)//Chacking wether already entered the value to the same month
{
mysql_query("UPDATE $tbl_name SET bill_value='$bill_value'
WHERE bill_year='$bill_year' AND bill_month='$bill_month'");
}
else if($affected_rows==0)
{
// Insert the Values to bill table
mysql_query("INSERT INTO $tbl_name(bill_year,bill_month,bill_value,month_value)
VALUES ('$bill_year','$bill_month','$bill_value','$month_value')");
}
}else
{ //if bill value is not numeric
echo("<SCRIPT LANGUAGE='JavaScript'>
window.alert('Please Enter a Valid Numeric Value!!!');
</SCRIPT>");
}
}
else
{
//if year and month are not correct
echo("<SCRIPT LANGUAGE='JavaScript'>
window.alert('Please select a valid Year and Month!!!');
</SCRIPT>");
}
}
?>
这通常由SQL UPSERT命令解决。由于MySQL没有UPSERT，您可以使用这样的解决方案:
insert into sakila.actor (actor_id, first_name, last_name, last_update)
values (50,'NATALIE','SMITH','2013-09-27 12:34:56')
on duplicate key update
first_name = values(first_name),
last_name = values(last_name),
last_update = values(last_update);
关于如何创建UPSERT的更多信息，这里解释了这个示例。