CODEWITHSUNDEEP
phpmysql
Eae
Eae

Reputation: 4321

MySQL delete troubleshooting

I restarted the MySQL service and I attempted to use my PHP programs delete function to delete an existing row but I'm finding although the delete queries were counted the row was not deleted. I tried applying on delete cascade to the foreign key of the child table but that did not seem to have an effect. I'm wondering why the delete would be doing nothing.

CREATE TABLE `customers` (
  `idcustomers` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(45) DEFAULT NULL,
  `lastname` varchar(45) DEFAULT NULL,
  `address1` varchar(45) DEFAULT NULL,
  `address2` varchar(45) DEFAULT NULL,
  `city` varchar(45) DEFAULT NULL,
  `state` varchar(45) DEFAULT NULL,
  `zip` varchar(45) DEFAULT NULL,
  `phone` varchar(45) DEFAULT NULL,
  `email` varchar(45) DEFAULT NULL,
  `cell` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`idcustomers`),
  UNIQUE KEY `idcustomers_UNIQUE` (`idcustomers`)
) ENGINE=InnoDB AUTO_INCREMENT=54 DEFAULT CHARSET=latin1


CREATE TABLE `events` (
  `idevents` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(250) DEFAULT NULL,
  `start` datetime DEFAULT NULL,
  `end` datetime DEFAULT NULL,
  `allday` varchar(50) DEFAULT NULL,
  `url` varchar(1000) DEFAULT NULL,
  `customerid` int(11) NOT NULL,
  `memo` longtext,
  `dispatchstatus` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`idevents`),
  KEY `FK_events` (`customerid`),
  CONSTRAINT `FK_events` FOREIGN KEY (`customerid`) REFERENCES `customers` (`idcustomers`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=latin1

Com_delete 2

The PHP looks like this:

<?php
session_start();

date_default_timezone_set("America/Los_Angeles");

if($_SESSION['loggedin'] != TRUE)
{
header("Location: index.php");
}

require_once('../php.securelogin/include.securelogin.php');
$mysqli = new mysqli($ad_host, $ad_user, $ad_password, "samedaycrm");

if ($mysqli->connect_errno) {
printf("Connect failed: %s\n", $mysqli->connect_error);
exit();
}


$customerid = $_SESSION['customer_id'];

$tSQL = "delete from events where customerid = \"$customerid\"";
$result = $mysqli->query($tSQL);

$tSQL = "delete from customers where idcustomers = \"$customerid\"";
$result = $mysqli->query($tSQL);



echo $mysqli->error;
?>

Upvotes: 1

Views: 90

Answers (1)

NappingRabbit
NappingRabbit

Reputation: 1918

Assuming that the customerid and idcustomers columns are both numeric it should be fine. You should not need to quote the variables in those queries btw, then you wouldnt need to escape them. You may try:

$tSQL = "delete from events where customerid = $customerid";

but it should not be any different than what you used already. Of course if you are not sure of the type of the column you can use:

$tSQL = "delete from events where customerid = '".$customerid."'";

or you can get away with: 

$tSQL = "delete from events where customerid = '$customerid'";

but I have always hated that for some reason.

if all of that fails troubleshoot by spitting out the $customerid (or even the whole $tSQL) variable and then trying the query manually in phpmyadmin or toad or whatever db client you use, and see what it tells you. If it just says 0 rows affected, then run it like a select instead. Tailor to fit.

Upvotes: 1

Related Questions