CODEWITHSUNDEEP
phpmysqlisql-delete
user2101081
user2101081

Reputation: 451

PHP Mysqli Delete from DB

So I thought this would be a simple query to just delete rows that didn't have any data stored under certain columns, but for some reason my query is returning that zero rows have been deleted, I checked the table and they are still there.

What I want to do is delete from my gps_routes table where the route_lat and route_long do not contain a location (empty).

I have checked my to make sure I have delete permissions enabled as well.

$sql = "SELECT * FROM gps_routes";
$result = $link->query($sql);
$rowCount = $result->num_rows; $rows_deleted = 0; $delete_row = false;

if ($rowCount > 0)
{

 while($row = $result->fetch_assoc())
 {
      $user = $row['user_email'];
      $id = $row['route_id'];
      $lat = $row['route_lat'];
      $lng = $row['route_long'];

      if (empty($lat) || empty($lng)){
        $delete_row = true;
      }

      if (ctype_space($lat) || strlen(trim($lat)) == 0){
        $delete_row = true;
      }

      if ($lat == '' || $lat == ""){
        $delete_row = true;
      }

      if ($delete_row){
        $rows_deleted++;
        mysqli_query($link, "DELETE FROM gps_routes WHERE user_email = '$user' AND route_id = '$id'");
     }
  }
     echo "Routes deleted: $rows_deleted";
}

Upvotes: 2

Views: 634

Answers (1)

Joseph_J
Joseph_J

Reputation: 3669

From your code is suggest that you just want to go through your DB and check to see if the lat and long are empty. If they are then delete them.

Sounds like you can just use this query to get the job done.

mysqli_query($link, "DELETE FROM gps_routes WHERE (route_lat = '' OR route_lat IS NULL) OR (route_long = '' OR  route_long IS NULL)");

This is how I would do it based off the code you have provided:

$query = "DELETE FROM gps_routes WHERE (route_lat = '' OR route_lat IS NULL) OR (route_long = '' OR  route_long IS NULL)";
$result = $link->query($query);

echo 'Routes deleted: ' . $result->num_rows;

Upvotes: 3

Related Questions