...

View Full Version : delete problem



tanhaha_how
02-24-2007, 03:29 AM
hi, i ahve some query problem....below is my code.


<?php
include 'connStr.php';
include 'datediff.php';

$query = "SELECT * FROM tblpatient";
$result = mysql_query($query);
$row = mysql_fetch_assoc($result);
while ($row = mysql_fetch_array($result)){


$query2 = "Select * from tbloverdue";
$result2 = mysql_query($query2);
$row2 = mysql_fetch_assoc($result2);

$TD = $row["Treatment_Date"];
$patientID = $row["Patient_ID"];
$overdueID = $row2["Patient_ID"];

$diff = datediff("$TD","now","m");

if (($row["Email"]) == ""){
$row["Email"] = "NO";}

if ($diff > 5 && $patientID != $overdueID){

$query1 = "Insert into tbloverdue(Patient_ID, Name, Treatment_Date, Email)values('".$row["Patient_ID"]."', '".$row["Name"]."', '".$row["Treatment_Date"]."', '".$row["Email"]."')";
$result1 = mysql_query($query1);

}
}

$query3 = "DELETE o.* FROM tbloverdue as o, (SELECT MAX(Overdue_ID) AS dupid, COUNT(Patient_ID) AS dupcnt FROM tbloverdue GROUP BY Patient_ID, HAVING dupcnt>1) AS dups WHERE o.Patient_ID = dups.dupcnt";
$result3 = mysql_query($query3);

mysql_query("COMMIT");

echo "<meta http-equiv=\"Content-Type\" content=\"text/html; charset=iso-8859-1\">";
echo "<meta http-equiv=\"REFRESH\" content=\"0; URL=overdue.php\">";

?>


the INSERT query works properly...but the DELETE query cannot work at all...actually the DELETE query is to delete the duplicate records.but dunno y it cant works at all...
can somebody help me pls???????

guelphdad
02-24-2007, 03:46 AM
you can't do a select on a table you plan to delete rows from.

note you should look into using mysql_error() in php as it would print the actual error message out to you and you would have seen that info.

tanhaha_how
02-24-2007, 05:27 AM
did u mean the query below is not correct???



$query3 = "DELETE o.* FROM tbloverdue as o, (SELECT MAX(Overdue_ID) AS dupid, COUNT(Patient_ID) AS dupcnt FROM tbloverdue GROUP BY Patient_ID, HAVING dupcnt>1) AS dups WHERE o.Patient_ID = dups.dupcnt";


but how to write a query to delete duplicate data???



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum