PDA

Click to See Complete Forum and Search --> : php/mysql question

prophit
12-15-2006, 01:44 PM
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?


$pp = $_POST['property'];

SELECT * FROM networkdown WHERE property = '$pp'
chazzy
12-15-2006, 04:33 PM
you have mysql_query, that issues a query.
NogDog
12-15-2006, 04:34 PM
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:

$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
}
prophit
12-15-2006, 05:47 PM
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

<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>";
} else {
if(!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>";
} else {
if(!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 = "INSERT INTO networkdown VALUES ('0','$pp','$tt', NOW(), '$cm' )";
mysql_query($query);
mysql_close();


$headers= 'MIME-Version: 1.0'."\r\n";
$headers.= 'Content-type: text/html; charset=iso-8859-1'."\r\n";
$headers.= 'From: hsia@bestwestern.com'."\r\n";

$to = "hsiagroup@bestwestern.com";
$subject = "Network Outage\n Property\n $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>';


} else {
echo "<font color=red>$error_msg</font>";
}
}

?>
NightShift58
12-16-2006, 03:27 AM
<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'."\r\n";
$headers .= 'Content-type: text/html; charset=iso-8859-1'."\r\n";
$headers .= 'From: hsia@bestwestern.com'."\r\n";

$to = "hsiagroup@bestwestern.com";
$subject = "Network Outage\n Property\n $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;
}

?>


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.