insert - but only if ...

[ejwf]

I have a user input form which creates any number of records to insert into a database - it could be 1 record it could be 23 it could be 45 etc.etc.

I have got this working OK - but on user trials I am finding the user is inputing the same records more than once in error , but sometimes one of the fields might contain slightly different information and I only want to take the first input information as the record to go in the database.

So I want to add a condition/clause so that if the records already exists (if record with the same primary information in fields Date Ref, MRef, SRef, CRef, CoRef is already in the database) don't add it again with the new information for B and C and User. - Any Ideas?

At the moment I am using

$query = "REPLACE INTO tblResults (DateRef,MRef,SRef,CRef,CoRef,B,C,LoadedBy)
		VALUES ('$DateRef','$MId','$SRef','$CRef','$CoRef','$B','$C','$UserId')";
$result = mysql_query($query)
or die(mysql_error());

[bodzan]

...i.e. You say you don't want some values to repeat in database, right? So just make an SELECT statement where you ask for those values for certain fileds and if you have those you just don't INSERT:

$query1 = "SELECT * FROM table_name WHERE field1 = 'value1' AND field2 = 'value2'...";
$result1 = mysql_query($query1);

if(mysql_num_rows($result1)==0){//the row with these values doesn't exist
     //do your INSERT
}
else{
     //do nothing or whatever else you want
}

[ejwf]

thanks for this - but can you help me on the else statement - this is the bit I don't know how to do

What I want it to say is

if(mysql_num_rows($result1)==0)
{//the row with these values doesn't exist 
     //do the INSERT 
} 
else{ 
     //if there is a row as a result of the query, 
    //then don't add this record 
    //but carry on and add all the others.......... (?)
}

[bodzan]

...since the logic should be something like:

while(each_insert){
      //do the if statement
}

i.e. You should be working in a way to check each statement through some while or other kind of loop statements and insert data where you meet the criteria...

[ejwf]

my code to create the form is

echo' <form action="process.php" method="POST" > 
<table align="centre" cellspacing="0" cellpadding="0"> 
<tr valign="top"> 
<td width="5"></td> 
<td width="5"></td> 
<td align="right" width="50"><b>Serial</b></td> 
<td align="left" width="200"><b> &nbsp;&nbsp/ Known As</b></td> 
<td align="right" width="20"><b>Code</b></td> 
<td align="right" width="20"></td> 
<td align="right" width="200">Previous Months B&nbsp;&nbsp</td> 
<td align="left" width="200"><b>This Months B</b></td> 
<td align="right" width="200">Previous Months C &nbsp;&nbsp</td> 
<td align="left" width="200"><b>This Months C</b></td> 
</tr >'; 

while ($row2 = mysql_fetch_array($result2,MYSQL_NUM))  

{  

$query= "SELECT (MRef) AS MRef, Max(B) As LastResultB, Max(C) As LastResultC 
FROM tblResults 
WHERE MRef = '".$row2[2]."' 
AND CRef = $CRef  

GROUP BY MRef, CRef"; 
$result3 = mysql_query($query) or die (mysql_error()); 
$row3 = mysql_fetch_array($result3); 

echo "<td width=\"5\" align=\"left\"><input type=\"hidden\" name=\"List\" value=\"{$row2[3]}\"></td> 
<td width=\"5\" align=\"left\"><input type=\"hidden\" name=\"SRef\" value=\"$SRef\"></td>";  

echo "<td width=\"50\" align=\"left\"> {$row2[0]}</td> 
<td width=\"200\" align=\"left\">  &nbsp;&nbsp/ {$row2[1]}</td> 
<td width=\"20\" align=\"right\"> {$row2[2]}</td> 
<td width=\"20\" align=\"left\"> {$row2[4]} <input type=\"text\" name=\"MId[]\" size=\"3\"></td> 
<td width=\"200\" align=\"right\"> $row3[1] &nbsp;&nbsp</td> 
<td width=\"200\" align=\"left\"> {$row2[5]} <input type=\"text\" name=\"B[]\"> </td> 
<td width=\"200\" align=\"right\"> $row3[2] &nbsp;&nbsp</td> 
<td width=\"200\" align=\"left\"> {$row2[6]} <input type=\"text\" name=\"C[]\"></td>"; 

}  // close while loop 

} //close main if 

echo '</table>'; 
echo "<br><p align=\"center\"><input type=\"submit\" name=\"submit\" value=\"Submit\"> ";  

echo '</form>'; 

code to process the form

if (is_array($_POST['B'])  && count($_POST['B'])>0) 
{ 
  $arr_size = count($_POST['BWClicks']);
  for ($i=0; $i< $arr_size; $i++)  

  {  

// if the user has put the info in but not the code   
if (!empty($_POST['B'][$i]) && empty($_POST['MId'][$i])) 
{   $count_check = sizeof($i); 

		if ($count_check > 0) 
		{
		echo "<br><br><br><br>" . 
             "<font face=\"Arial, Helvetica, sans-serif\">" . 
             "Sorry, it appears that a <b>Code</b> for your information " . 
             "is missing.<br>Please kindly return to the " . 
             "input form, and re-enter this code.  " . 
             "<br>Thank you" . 
             "</font>"; 
		}	 
} 


   // insert the record 
    else     

    { 
	$SRef = $_POST['SRef']; 

// sets date into correct format to match database
$today = date('YmdHis');

// establish variable concesref for use in next query
$query = "SELECT `CoRef` FROM `tblUser` WHERE `CLogIn` = '$username' AND`SRef` = '$SectionRef'";
$result = mysql_query($query) 
   or die(mysql_error());
$row = mysql_fetch_array($result); 
$CoRef = $row["CoRef"]; 

// sets datelogref variable
$query = "SELECT `DateId` FROM `tblDate` WHERE `CoRef` = '$CoRef' AND `OpenEarly` <= '$today' 
ORDER BY `EndDate` DESC LIMIT 1";
$result1 = mysql_query($query)
   or die(mysql_error());
$row1 = mysql_fetch_array($result1); 
$DateRef = $row1["DateId"]; 

// sets userid
$query = "SELECT WebUserId FROM tblUser WHERE CLogIn ='$username'";
$result5 = mysql_query($query)
	or die(mysql_error());
$row5 = mysql_fetch_array($result5);
$UserId = $row5["WebUserId"];

//set client ref
$query = "SELECT `CRef` FROM `tblS` WHERE `SId` = '$SRef'"; 
$result4 = mysql_query($query) 
   or die(mysql_error());
$row4 = mysql_fetch_array($result4); 
$CRef = $row4["CRef"]; 

$B= strip_tags(trim($_POST['B'][$i])); 
$C = strip_tags(trim($_POST['C'][$i])); 
$MId = ($_POST['MId'][$i]); 

$query = "REPLACE INTO tblResults (DateRef,MRef,SRef,CRef,CoRef,B,C,LoadedBy) 
        VALUES  ('$DateRef','$MId','$SRef','$CRef','$CoRef','$B','
$C','$UserId')"; 
$result = mysql_query($query) 
or die(mysql_error());
    }  

  }  // close for i < $arr_size 
} //close if count > 0

[bodzan]

...use the if / else statement somewhere?

Just place the if statement before the replace and like we already said if the record matching you criteria doesn't exist do the replace...

The code you made will go through every record, I think...