Delete Behaviour

I'm having trouble deleting a record. The database structure is unusual in as much as I have the ID and the field showing the same information.

so:
serviceID servicename
Morning Morning
Evening Evening
South Green South Green

Now this works fine as long as there is not a space in the serviceID column. So it will delete Evening and Morning but NOT South Green.

This is the PHP code

<?php 
// WA Application Builder Delete
if (isset($_POST["Delete_x"])) // Trigger
{
  $WA_connection = $localhost;
  $WA_table = "bbc_service";
  $WA_redirectURL = "bbc_service_Results.php";
  $WA_keepQueryString = false;
  $WA_fieldNamesStr = "serviceID";
  $WA_columnTypesStr = "',none,''";
  $WA_fieldValuesStr = "".((isset($_POST["WADADeleteRecordID"]))?$_POST["WADADeleteRecordID"]:"")  ."";
  $WA_comparisonStr = "=";
  $WA_fieldNames = explode("|", $WA_fieldNamesStr);
  $WA_fieldValues = explode("|", $WA_fieldValuesStr);
  $WA_columns = explode("|", $WA_columnTypesStr);
  $WA_comparisions = explode("|", $WA_comparisonStr);
  $WA_connectionDB = $database_localhost;
  mysql_select_db($WA_connectionDB, $WA_connection);
  if (!session_id()) session_start();
  $deleteParamsObj = WA_AB_generateWhereClause($WA_fieldNames, $WA_columns, $WA_fieldValues, $WA_comparisions);
  $WA_Sql = "DELETE FROM `" . $WA_table . "` WHERE " . $deleteParamsObj->sqlWhereClause;
  $MM_editCmd = mysql_query($WA_Sql, $WA_connection) or die(mysql_error());
  if ($WA_redirectURL != "")  {
    if ($WA_keepQueryString && $WA_redirectURL != "" && isset($_SERVER["QUERY_STRING"]) && $_SERVER["QUERY_STRING"] !== "" && sizeof($_POST) > 0) {
      $WA_redirectURL .= ((strpos($WA_redirectURL, '?') === false)?"?":"&").$_SERVER["QUERY_STRING"];
    }
    header("Location: ".$WA_redirectURL);
  }
}
?>

Is there anything I can add / alter to that code that will allow the deletion of a record that has a space in the ID?

I know that this was probably bad design when I did this (about a year ago now) but it seemed a good idea at the time. If I was to change the ID to a numeric value now it would involve changing an awful lot of pages else where in the site that uses the data. So If there is a simple solution I'd be grateful to know it. Like adding an underscore if there is a space for example. The code is beyond my level of expertise I'm afraid.

Many thanks.

Strange that it will work on items that do not have a space.

To trouble shoot the issue, lets write the SQL being executed to the screen,



change the following line of code:

$MM_editCmd = mysql_query($WA_Sql, $WA_connection) or die(mysql_error());

to:

die($WA_Sql);

$MM_editCmd = mysql_query($WA_Sql, $WA_connection) or die(mysql_error());

this should write SQL similar to:
DELETE FROM `bbc_service` WHERE serviceID = 'South Green'


to check the validity of the SQL, use a tool like PHPMyAdmin or SQLYog to access the database directly and run the query. Try changing it to a SELECT query instead:
SELECT * FROM `bbc_service` WHERE serviceID = 'South Green'


and try changing the where clause to lower case:
SELECT * FROM `bbc_service` WHERE serviceID = 'south green'

the key to a delete statement is in the where clause. Any records returned by the where clause will be deleted, so we need to determine why the where clause is not returning any results.

Hi Jason, thanks for helping out.

Firstly, using PhpMyAdmin all the queries worked perfectly.

Now adding the

die(WA_Sql);

resulted in this line of text being displayed on a blank browser page

DELETE FROM `bbc_service` WHERE `serviceID`='South%20Green'

It's adding %20 in the space which is, of course, not in the database.

So that makes me realise why it's not working but how can I overcome it?

Just as a thought, would having this declaration on the page be the culprit?
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

hmmm, not sure why it is adding the %20 for the space, I cannot get it to reproduce the problem, perhaps try changing:

$MM_editCmd = mysql_query($WA_Sql, $WA_connection) or die(mysql_error());

to:

$MM_editCmd = mysql_query(str_replace("%20", " ", $WA_Sql), $WA_connection) or die(mysql_error());

Thanks Jason, that works perfectly. Well done.

Nice, glad to hear it worked.