PHP Mysqli Delete from DB

Question

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";
}

"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)." Your code says you want to delete from your gps_routes where the user's email and route_id equal specific values. Have you tried updating your query to use $lat and $long instead? — user8034901
– user8034901, Commented Oct 1, 2018 at 7:12
@kerbholz if you look at the code I am checking to see if the column is empty and if so deleting it for that specific user. — user2101081
– user2101081, Commented Oct 1, 2018 at 7:13
Ok, any reason why you select all gps_routes and loop through them instead of just getting all gps_routes WHERE route_lat IS NULL AND route_long IS NULL? Or better yet, just DELETE FROM gps_routes WHERE route_lat IS NULL AND route_long IS NULL? — user8034901
– user8034901, Commented Oct 1, 2018 at 7:19
You need to add some debug information my friend. Right below where you define the $user $id etc variables, maybe issue some var_dump()s, e.g. var_dump($user, $lat, $lng); and see what the actual values of the fields are. — minitauros
– minitauros, Commented Oct 1, 2018 at 7:20
What? Do you update the count of routes for a user every time a user adds/removes a route? You could just "count" all gps_routes for a certain user in sql. — user8034901
– user8034901, Commented Oct 1, 2018 at 7:26

Joseph_J · Accepted Answer · 2018-10-01 08:01:31Z

3

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;

edited Oct 1, 2018 at 8:01

answered Oct 1, 2018 at 7:21

Joseph_J

3,6692 gold badges15 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Bobby Axe Over a year ago

I don't even understand why contributor had to loop his delete there by wasting so much resource deleting each row one at a time +1

Joseph_J Over a year ago

@Bobby Axe It's learning.. I'm sure I still make mistakes that someone better than me would look at and say something similar.

Bobby Axe Over a year ago

Well that's true. Am not perfect either looking back at some of my old code i wonder who wrote that stuff.

Collectives™ on Stack Overflow

PHP Mysqli Delete from DB

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related