Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3

Thread: delete problem

  1. #1
    Regular Coder
    Join Date
    Feb 2006
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    delete problem

    hi, i ahve some query problem....below is my code.
    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???????

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.

  • #3
    Regular Coder
    Join Date
    Feb 2006
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts
    did u mean the query below is not correct???

    Code:
    $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???


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •