php/mysql question

@prophitDec 15.2006

Im trying to add in a function that will check for duplicates.
Basicly when a person trys to add something to the database it will querry the database and make sure that the info is not already there.
What would be the mysql querry command?

[code] $pp = $_POST[‘property’];

SELECT * FROM networkdown WHERE property = ‘$pp’ [/code]

to post a comment

PHP

4 Comments(s) _↴

@chazzyDec 15.2006 — #you have mysql_query, that issues a query.

@NogDogDec 15.2006 — #My recommendation is to make sure the column of interest is defined with a unique index in the table definition. Then just check for a duplicate entry error when doing the insert:

[code=php]
 $query = "INSERT INTO networkdown (...field list...) VALUES (...value list...)";
 $result = mysql_query($query);
 if(!$result)
 {
 if(mysql_errno() == 1062)
 {
 // it's a duplicate, so handle that error here
 }
 else
 {
 // it's some other error:
 die("Query failed: $query - ".mysql_error();
 }
 }
 else
 {
 // insert was successful
 }
 [/code]

@prophitauthorDec 15.2006 — #The table actually has a property number attached to it. I want my form to check that property table and see if that property already is listed in the database.

here is my current code

<i>
 </i>&lt;LINK REL=stylesheet HREF="../inc/work.css" TYPE="text/css"&gt;
 
 &lt;center&gt;
 &lt;table border="0"  cellspacing="3" cellpadding="3" width="400"&gt;
 &lt;tr&gt;
 &lt;td&gt;
 &lt;pre&gt;
 
 &lt;form action="&lt;?php $_SERVER['PHP_SELF']; ?&gt;" method="post"&gt;
 &lt;b&gt;Property #:&lt;/b&gt; &lt;input type="text" name="property"&gt;&lt;br&gt;
 &lt;b&gt;Ticket #  :&lt;/b&gt; &lt;input type="text" name="ticket"&gt;&lt;br&gt;
 &lt;b&gt;Comments:&lt;/b&gt;
 &lt;textarea name="status" rows="4" cols="40"&gt;&lt;/textarea&gt;
 &lt;center&gt;&lt;input type="submit" name="submit" value="Add"&gt;&lt;/center&gt;
 &lt;/form&gt;
 &lt;/pre&gt;
 &lt;/td&gt; 
 &lt;/tr&gt;
 &lt;/table&gt;
 &lt;br&gt;
 
 &lt;?php
 
 if(isset($_POST["submit"])) {
 $error_msg='';
 if(trim($_POST["property"])=='') {
 $error_msg.="Please enter a Property Number&lt;br&gt;";
 } else {
 if(!ereg("[0-9]", $_POST["property"])) $error_msg.="Please enter a valid Property Number&lt;br&gt;";
 }
 if(trim($_POST["ticket"])=='' || strlen(trim($_POST["ticket"])) &lt; 6 || strlen(trim($_POST["ticket"])) &gt; 6) {
 $error_msg.="Please enter a 6 digit Ticket Number&lt;br&gt;";
 } else {
 if(!ereg("[0-9]", $_POST["ticket"])) $error_msg.="Please enter a valid Ticket Number&lt;br&gt;";
 }
 
 <i>	</i>// display error message if any, if not, proceed to other processing
 <i>	</i>if($error_msg==''){
 
 require_once ("../inc/conn.php");
 
 $pp	= $_POST['property'];
 $tt	= $_POST['ticket'];
 $cm	= $_POST['status'];
 
 $query = "INSERT INTO networkdown VALUES ('0','$pp','$tt', NOW(), '$cm' )";
 mysql_query($query);
 mysql_close();
 
 
 $headers= 'MIME-Version: 1.0'."rn";
 $headers.= 'Content-type: text/html; charset=iso-8859-1'."rn";
 $headers.= 'From: [email protected]'."rn";
 
 $to		 = "[email protected]";
 $subject = "Network Outagen Propertyn $pp";
 $message = "
 &lt;html&gt;
 &lt;body&gt;
 Property &lt;b&gt;$pp&lt;/b&gt; is experiencing network issue.n&lt;br&gt;
 Please refer to ticket &lt;b&gt;$tt&lt;/b&gt; for details.n&lt;br&gt;
 $cm
 &lt;/body&gt;
 &lt;/html&gt;
 ";
 
 mail ($to, $subject, $message, $headers);
 
 echo '&lt;center&gt;Your request has been added and an email has been sent out.&lt;br&gt;'.
 'Taking you back now...&lt;/center&gt;';
 echo '&lt;META HTTP-EQUIV=Refresh CONTENT=1;URL=form.php&gt;';
 
 
 } else {
 echo "&lt;font color=red&gt;$error_msg&lt;/font&gt;";
 }
 }
 
 ?&gt;

@NightShift58Dec 16.2006 — #

[code=php]
 <LINK REL=stylesheet HREF="../inc/work.css" TYPE="text/css">
 
 <center>
 <table border="0"  cellspacing="3" cellpadding="3" width="400">
 <tr>
 <td>
 <pre>
 
 <form action="<?php $_SERVER['PHP_SELF']; ?>" method="post">
 <b>Property #:</b> <input type="text" name="property"><br>
 <b>Ticket #  :</b> <input type="text" name="ticket"><br>
 <b>Comments:</b>
 <textarea name="status" rows="4" cols="40"></textarea>
 <center><input type="submit" name="submit" value="Add"></center>
 </form>
 </pre>
 </td> 
 </tr>
 </table>
 <br>
 
 <?php
 
 if (isset($_POST["submit"])) {
 
  $error_msg='';
 
  if(trim($_POST["property"])=='') {
   $error_msg .= "Please enter a Property Number<br>";
  } elseif (!ereg("[0-9]", $_POST["property"])) {
   $error_msg .= "Please enter a valid Property Number<br>";
  }
 
  if(trim($_POST["ticket"]) =='' || strlen(trim($_POST["ticket"])) < 6 || strlen(trim($_POST["ticket"])) > 6) {
   $error_msg.="Please enter a 6 digit Ticket Number<br>";
  } elseif(!ereg("[0-9]", $_POST["ticket"])) {
   $error_msg.="Please enter a valid Ticket Number<br>";
  }
 
  // display error message if any, if not, proceed to other processing
  if ($error_msg == '') {
 
   require_once ("../inc/conn.php");
 
   $pp	= $_POST['property'];
   $tt	= $_POST['ticket'];
   $cm	= $_POST['status'];
 
   $query  = "SELECT property from networkdown where property = '$pp' limit 1";
   $result = mysql_query($query);
 
   IF (!$result) :
   $error_msg .= "There an unexpected problem executing the database query.";
   ELSEIF (mysql_num_rows($result) == 1) :
   $error_msg .= "This property number is already present in the database.";
   ENDIF;
 
   IF ($error_msg == "") :
   $query = "INSERT INTO networkdown VALUES ('0','$pp','$tt', NOW(), '$cm' )";
   mysql_query($query);
   mysql_close();
 
   $headers  = 'MIME-Version: 1.0'."rn";
   $headers .= 'Content-type: text/html; charset=iso-8859-1'."rn";
   $headers .= 'From: [email][email protected][/email]'."rn";
 
   $to		    = "[email protected]";
   $subject  = "Network Outagen Propertyn $pp";
   $message  = "
   <html>
   <body>
   Property <b>$pp</b> is experiencing network issue.n<br>
   Please refer to ticket <b>$tt</b> for details.n<br>
   $cm
   </body>
   </html>
   ";
 
   mail ($to, $subject, $message, $headers);
 
   echo '<center>Your request has been added and an email has been sent out.<br>'.
   'Taking you back now...</center>';
   echo '<META HTTP-EQUIV=Refresh CONTENT=1;URL=form.php>';
   ENDIF;
  }
 IF ($error_msg <> "") : 
 echo "<font color=red>$error_msg</font>";
 ENDIF;
 }
 
 ?>
 [/code]

The above is a modification of your code which will allow you to check for duplicate prior to inserting another record. This eliminates any requirements to have your table indexed in a particular manner - though as NogDog suggests, it may be something that you should consider. However, there are times when duplicates are not desirable under certain conditions but may be just as desirable under other conditions, such as support tickets, depending on program flow and context.

I have used the alternative PHP coding method (IF:ELSE:ENDIF) to visually separate your code from the one I added.

Also in #PHP _↴

[RESOLVED] Disagreing PHP :confused:Keeping MySQL Improved Errors From Displaying Without @Noob form problems

Success!

Help @prophit spread the word by sharing this article on Twitter...

Tweet This

php/mysql question

4 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

4 Comments(s) _↴

Also in #PHP _↴