Delete row MySQL DB with PHP - using Google Map API

irish_stevo815 · 12-03-2015 6:22pm #1

I have a website using Google Maps API and i have it saving marker with name, lot, lon, etc to a MySQL database using PHP. This works ok as i can see the database with phpMyAdmin. But now I want to ability to remove a specific marker when the DELETE button is clicked (This button appears when the marker on the map is clicked).

The JS that displays the marker when it is clicked is:

//add the marker to the event_markers array
event_markers = marker;

// Content for the Event Info window - includes a Remove button
var eventContent = $('<div class="event-ifno">' + '<h4 class="event-name">' + point.name + '</h4><hr>' +
'<p class="event-description">'+point.description+'</p>' +
'</span><button name="remove-event" class="remove-event btn btn-danger btn-sm" onclick="tidy_maps.remove_event()" title="Remove Event">Remove Event</button>'+
'</div>');

// Display Event details on marker click
google.maps.event.addListener(event_markers, "click", function () {
infowindow.setContent(eventContent[0]);
infowindow.open(map, event_markers);
});

//Find remove button in infoWindow
var removeEvent = eventContent.find('button.remove-event')[0];

//add click listner to remove marker button
google.maps.event.addDomListener(removeEvent, "click", function(event) {
marker.setMap(null);

});

I have a Delete marker function also:

// Remove marker and delete event
tidy.maps.remove_event = function()
{
var lat = $(this).data("lat");
var lon = $(this).data("lon");
$(this).prev("li").remove();
$(this).remove();
$.post(
"removedata.php",
{lat : $lat, lon : $lon}
);
}

The PHP i have for removing the data is:

<?php

// connect to the database
$con = mysqli_connect("localhost", "root", "password", "gmaps1");
if (!$con) {
die("Can not connect: " .mysql_error());
}

//Delete Marker & Event
$lat = $_REQUEST;
$lon = $_REQUEST;

$sql = "DELETE FROM tidy_maps_test WHERE lat=$lat && lon=$lon";
if(mysqli_query($con, $sql)) {
die("Error: ".mysql_error());
}

?>

When the Remove button is clicked, it removes the marker from the map, but not the database and it appears when the page is refreshed. Any help here would be great.

Graham · 12-03-2015 6:35pm

First thing I'd do is use the developer tools for your browser to check that removedata.php is actually being called by your Javascript.

Chrome developer tools has a tab for 'Network' which should show you what's being sent from your browser.

irish_stevo815 · 12-03-2015 6:58pm

Graham wrote: »

First thing I'd do is use the developer tools for your browser to check that removedata.php is actually being called by your Javascript.

Chrome developer tools has a tab for 'Network' which should show you what's being sent from your browser.

I have done this and can't see the PHP file anywhere. But I'm not really sure what I'm lookign at

Graham · 12-03-2015 7:55pm

This bit calls your php script in the background.

$.post(
"removedata.php",
{lat : $lat, lon : $lon}
);

On the network tab of chrome dev tools, you should see removedata.php under the name column when the javascript calls the above code.

If you're not seeing it, I'd check the Javascript again.

In dev tools, keep an eye in the top right corner for a red circle with a cross in it, this indicates error/s. Click on it to get more details.

irish_stevo815 · 25-03-2015 1:07pm

Still having a bit of trouble here. I've changed it up a bit. It now has a Confirm box when Delete is clicked and if OK is selected, it should delete the row and redirect to a new page. JS Below:

tidy_maps.delete = function() {
var confirm_remove = confirm("Do You Want to Remove This Event?")
if(confirm_remove) {
$.ajax({
type:'POST',
url:'removedata.php',
});
window.location = "http://www.google.com/";
}
else {
alert("ERROR!!!!");
}
}

The PHP has been slightly amended too:

// connect to the database
$con = mysqli_connect("localhost", "root", "password", "gmaps1");
if (!$con) {
die("Can not connect: " .mysql_error());
}

$sql = "DELETE FROM events WHERE id = $id";

$query = mysqli_query($con, $sql);

if(mysqli_affected_rows($con)) {
$del_msg = "Event Successfully Removed!."
echo "<script type='text/javascript'>alert('$del_msg');</script>";

}

mysqli_close($con);

It still does not quiet work. In the console, I can see that it sends to "removedata.php" but does not delete the row. Also, i have a window redirect to Google when it is confirmed to delete, and this works. But the "removedata.php" itself is still wrong.

In the line

$sql = "DELETE FROM events WHERE id = $id";

if i change $id to an actual ID Number, it deletes the row with the ID from the DB when i run removedata.php in the browser.

25-03-2015 1:22pm

You don't appear to be sending any data to removedata.php. And you don't seem to be setting $id anywhere in removedata.php

irish_stevo815 · 25-03-2015 1:26pm

28064212 wrote: »

You don't appear to be sending any data to removedata.php. And you don't seem to be setting $id anywhere in removedata.php

What am i missing to achieve this?

Giblet · 25-03-2015 1:36pm

Holy SQL Injection attack batman!

25-03-2015 1:39pm

You need to send the id of the row to delete to removedata.php, then (assuming you're POSTing it as rowid):
[PHP]$id = $_POST["rowid"];
$sql = "DELETE FROM events WHERE id = $id";[/PHP]

If you don't have access to the id in the JS, you need to send the lat and long to removedata.php. Your first attempt was more accurate, now you're just calling removedata.php without sending any data to it.

And yes, as Giblet says, learn about SQL Injection. If this is a live website, it would be trivial for someone to wipe your whole database

Delete row MySQL DB with PHP - using Google Map API

Comments