Delete query

jamesloi

Hi,

I am trying to delete a record from my database using

$query = "DELETE FROM CD_data WHERE Item_code = '$itemcode'";

where item code is an input value. This does not work? but when i put in numbers instead of '$itemcode' it works. I have also checked that '$itemcode' does have a value.

i really appreciate anyones help.

chrisleon

If I could see the rest of the code, I can try to help

ahundiak

What is the data type of item_code? If it's numeric then don't use 's in your sql statement.

Echoing out any error message returned when you executed the delete statement often helps.

jamesloi

$itemcode is a numerical value, does this matter? do u know how to make it work.this is some of the php code i am using

if ( $referer[0] == URLLIST2 && $yesButton == 'Yes' ) {
   if ( !($link=mysql_connect($host, $user, $passwd)) ) {
      printError(sprintf("Error connecting to database from %s, by user %s", $host, $user));
   } else {
      mysql_select_db($dbName);
      $query = "DELETE FROM CD_data WHERE Item_code = '$itemcode'";

  if ( !mysql_query($query,$link) ) {
     printError(sprintf("Error %s : %s", mysql_errno(), mysql_error()));
  } else {
     echo ("<h4>You have removed the CD<br /><br />\n" .
           "Go back to" .
           "<a href=\"http://\"> Admin page<h4/>");
  }
  mysql_close($link);

ahundiak

If the database field called "item_code" is indeed of type integer then use:

$query = "Delete from cd_data where item_code=$itemcode";

(No quotes around $itemcode).

Note: I am talking about the actual database field i.e.
create table cd_data
(
item_code integer
);

And not the php variable. It's possible that item_code is text data and just happens to have a numerical value. In which case, your delete statement looks ok.

I am assuming that the query is actually being executed i.e. it's getting through all your if statements?

You might try something like
mysql_query($query,$link);
if (mysql_errno()) {
// Do your error handling
}

jamesloi

hi ahundiak,

the database field called "Item_code" is an integer auto_increment primary key. I tried using your suggestion of taking the quotes out from $itemcode

$query = "Delete from cd_data where item_code=$itemcode";

but his returns an error 1054: unknown column $itemcode.
Do i have to change the type of Item_code to text, can it still be a primary key?

I really appreciate your help

ahundiak

Check your syntax very carefully or paste it into this thread. The query string got messed up when it was edited.

No need to change it to a text column. In fact, using integers is best.

jamesloi

ahundiak,

this is the code for my sql table,

CREATE TABLE CD_data
(
Item_code int unsigned not null auto_increment primary key,
Artist varchar(50),
Title varchar(100),
Record_label varchar(30),

)

and i am using this query string

$query = "DELETE from CD_data WHERE Item_code='$itemcode'";

if i take the quotes off $itemcode it givs me that error. I have checked everything else and there are definately no mistakes. when i use a number instead of $itemcode it deletes the record. Do u know how i can fix it? thanks

ahundiak

I'll have to let someone else solve this one.
The only way I can see that you would get a
unknown column $itemcode
is if you had something like

'delete from cd_key where $itemcode=whatever'

Very strange.

cgraz

I briefly skimmed over this post, so I hope I'm not repeating anything. I'd try the following


echo "Register Globals on: " . $itemcode . "<br>\n";
echo "Register Globals off: " . $_POST['itemcode'] . "<br>\n";
echo "Older Version: " . $HTTP_POST_VARS['itemcode'] . "<br>\n";

$query = mysql_query("DELETE FROM CD_data WHERE Item_code = '$itemcode'") or die(mysql_error()); // printing mysql error is always useful

Now I know this is going to sound stupid, but I did this the other day. Have you connected to your mysql server and selected a database? The other day I connected, but forgot to select database, and had no idea until I used mysql_error()

Cgraz

jamesloi

It still doesn't work, can anyone help?

cgraz

Can you be more specific? What did the above query print? Saying it didn't work doesn't tell us anything.

Cgraz

jamesloi

there are no errors but the record does not get deleted.

It is really fustrating me.:mad:

there is another post about this that i made called 'SQL query'
it has a lot of information and discussion about the problem.

i really appreciate any help dude.

cgraz

Change your code to this

$itemcode = $HTTP_POST_VARS['itemcode'];
$query = "DELETE FROM CD_data WHERE Item_code = '$itemcode'";
echo $query;

And copy and paste the output in this forum.

Cgraz

jamesloi

the reply is

DELETE FROM CD_data WHERE Item_code = '54'

54 is the value i input.

cgraz

ok, your query is good and the value of the variable is fine. Now do this

$itemcode = $HTTP_POST_VARS['itemcode'];
$query = mysql_query("DELETE FROM CD_data WHERE Item_code = '$itemcode'") or die(mysql_error());

And tell me if that outputs anything. Also, make sure you have made a connection to your mysql server and selected a database.

Also, are you processing this at the top of the page (and not inside of any html).

You also didn't have mysql_query in your first post, but I'm assuming you ran a mysql_query on that variable. Anyways, use the above code, tell me what it outputs. If it doesn't output anything, check to see if it worked or not.

Cgraz

jamesloi

It didnt print out anything, and it didnt delete the record

cgraz

Well your query is fine, the value for your variable is fine, and assuming your database is called CD_data and your field is called Itemcode (and caps are correct on both), then it should be working.

Last night I saw someone who was having a problem because they had their php within html. For example:


<table width="300" height="400">

<tr>

<td>

<?

$query = mysql_query("INSERT INTO. . .");

?>


this is the td

</td>

</tr>

</table>

Make sure you have your query outside of any HTML, especially tables, as this seems to cause odd problems.

Cgraz

jamesloi

thanks for your help. il have to work it out somehow