Counting Then Deleting

[DSTR3]

I am having problems with the syntax on this. I am counting the number of LocationID's in the tblTemp and if they are less than a certain number I delete them. But my syntax is off.

 

$sql = "SELECT LocationID, count(LocationID) AS totalCount FROM tblTemp GROUP BY LocationID";
$res = mysql_query($sql) or die("FAIL: $sql <br>" . mysql_error());

 foreach($row = mysql_fetch_array($res)) {
  if($row['totalCount'] < (" . $nchecked . ")) {
 }
 $mysql_query2("DELETE FROM tblTemp WHERE LocationID = '". $row['LocationID'] ."'");
 }
 }

[requinix]

if($row['totalCount'] < (" . $nchecked . ")) {

I don't know what you're doing with $nchecked but I'm quite sure you should be using

if($row['totalCount'] < $nchecked) {

[DSTR3]

$nchecked is a variable that counts how many options I have selected. Then I am deleting against that value. I will try your suggestion. Thank you. Its still complaining about the foreach line.

 

$result = mysql_query("SELECT LocationID, count(LocationID) as totalCount
   from tblTemp Group BY LocationID")
  foreach($row = mysql_fetch_array($result)) {
   if($row['totalCount'] < $nchecked) {
  }
  $mysql_query2("DELETE FROM tblTemp WHERE LocationID = '". $row['LocationID'] ."'");
  }
  }

[requinix]

"Still"? Alright, the whole thing then.

 

1. Missing a semicolon on the mysql_query() line.

2. foreach is for arrays, not conditions. Use a while loop.

3. I doubt mysql_query2 is a function variable.

[DSTR3]

Bingo! The errors are gone! But..................of course it's not deleting!

 

$result = mysql_query("SELECT LocationID, count(LocationID) as totalCount
   from tblTemp Group BY LocationID");
  while($row = mysql_fetch_array($result)) {
   if($row['totalCount'] < $nchecked) {
  }
  $mysql_query("DELETE FROM tblTemp WHERE LocationID = '". $row['LocationID'] ."'");
  }
  }

[Jessica]

You need to check for errors.

[DSTR3]

I did this and the ERROR I received was "Fatal error: Function name must be a string" on the last line.

 

$result = mysql_query("SELECT LocationID, count(LocationID) as totalCount
   from tblTemp Group BY LocationID");
  $result = mysql_query($sql) or die("FAIL: $sql <br>" . mysql_error());
  while($row = mysql_fetch_array($result)) {
   if($row['totalCount'] < $nchecked) {
  }
  $mysql_query("DELETE FROM tblTemp WHERE LocationID = '". $row['LocationID'] ."'");
  }
  }

[Jessica]

Well? You have a variable where you should have a function.

You're also not checking for MYSQL errors.

[requinix]

  On 2/10/2013 at 10:20 PM, requinix said:

3. I doubt mysql_query2 is a function variable.

You probably mean to use mysql_query().

[Psycho]

Why are you running two queries: 1 to get the count and another to delete. And the delete query is in a loop no less. Never run queries in loops. All you need is ONE query

 

$query = "DELETE FROM tblTemp
	  WHERE LocationID IN (
	    SELECT LocationID
	    FROM (SELECT LocationID
			  FROM tblTemp
			  GROUP BY LocationID
			  HAVING count(*) < $nchecked
			 ) t
		 )";

[DSTR3]

Thank you. I see the light.

[Jessica]

  On 2/11/2013 at 12:03 AM, Psycho said:

Why are you running two queries: 1 to get the count and another to delete. And the delete query is in a loop no less. Never run queries in loops. All you need is ONE query

 

$query = "DELETE FROM tblTemp
	 WHERE LocationID IN (
	 SELECT LocationID
	 FROM (SELECT LocationID
			 FROM tblTemp
			 GROUP BY LocationID
			 HAVING count(*) < $nchecked
			 ) t
		 )";

 

I was going to point it out once we solved the basic debugging issues.

 

But do you even need the subselect?

[Psycho]

  On 2/11/2013 at 1:01 AM, Jessica said:

But do you even need the subselect?

 

I wouldn't think so, but I couldn't get it working otherwise. Perhaps Barand can provide the more efficient solution.