Help with a DELETE query

[jmueller0823]

We're testing a simplified version of a query, below.

--The query does not error. Yet, it does not perform the deletion.
--We believe it is connecting properly.

Ideas anyone? Thanks.
Jim

<?php

$dbhost = 'xxxx';
$dbuser = 'xxxx';
$dbpass = 'xxxx';
$dbname = 'xxxx';

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die
                      ('Error connecting to mysql');

if (!@mysql_select_db($dbname)) {
  exit('Unable to locate the database');   

}

$result = @mysql_query('DELETE FROM arp3_cam WHERE id=82');

if (!$result) {
  exit('<p>Error performing query: ' . mysql_error() . '</p>');
}
?>

[djjjozsi]

Why are you use @ before that commands ?

leave and try.

jmueller0823;10889931 wrote:

We're testing a simplified version of a query, below.

--The query does not error. Yet, it does not perform the deletion.
--We believe it is connecting properly.

Ideas anyone? Thanks.
Jim

<?php

$dbhost = 'xxxx';
$dbuser = 'xxxx';
$dbpass = 'xxxx';
$dbname = 'xxxx';

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die
                      ('Error connecting to mysql');

if (!@mysql_select_db($dbname)) {
  exit('Unable to locate the database');   

}

$result = @mysql_query('DELETE FROM arp3_cam WHERE id=82');

if (!$result) {
  exit('<p>Error performing query: ' . mysql_error() . '</p>');
}
?>

[laserlight]

jmueller0823 wrote:

--The query does not error. Yet, it does not perform the deletion.

Are you sure that there is a row with id=82 in the database table before and after the attempted deletion?

djjjozsi wrote:

Why are you use @ before that commands ?

I agree, remove the error suppression operators from those function calls. Although it should make no difference here since you test the return value, during development it can help you in debugging. In production, display_errors should be turned off anyway.

[jmueller0823]

Response to JJ

Why are you use @ before that commands ?

Nope. I removed the ampersands and same results.

Are you sure that there is a row with id=82 in the database table before and after the attempted deletion?

Absolutely. I am browsing the records in PhpMyAdmin.

BTW I can copy-and-paste the query and run it in PhpMyAdmin -- and it does work.
Also, if I purposely mis-spell one of the db parameters, the error code does work...

[laserlight]

That's weird. Do other SQL statements (insert, update, etc) work?

I suggest testing with this:

<?php

$dbhost = 'xxxx';
$dbuser = 'xxxx';
$dbpass = 'xxxx';
$dbname = 'xxxx';

$conn = mysql_connect($dbhost, $dbuser, $dbpass)
    or die('Could not connect to database server: ' . mysql_error());

mysql_select_db($dbname)
    or die('Could not select database: ' . mysql_error());

$result = mysql_query('DELETE FROM arp3_cam WHERE id=82')
    or die('Could not execute SQL statement: ' . mysql_error());

echo 'Number of rows deleted: ' . mysql_affected_rows();

?>

If the deletion goes through, you should get the output:

Number of rows deleted: 1

If not, you should get the output:

Number of rows deleted: 0

What output do you get?

By the way, why not use the PDO extension or MySQLi extension instead of the legacy MySQL extension?

[djjjozsi]

Character coding is the another i would check.

The page's char code, and when you select/delete lets create them the same. And if you delete a value with a number, it should had the type of an integer. Use Cast operations.

In phpmyadmin, see the Delete command, and write that into your php command. MAybe that conaines Cast operations.

bye,
jjozsi

jmueller0823;10889941 wrote:

Response to JJ
Nope. I removed the ampersands and same results.

Absolutely. I am browsing the records in PhpMyAdmin.

BTW I can copy-and-paste the query and run it in PhpMyAdmin -- and it does work.
Also, if I purposely mis-spell one of the db parameters, the error code does work...

[jmueller0823]

What output do you get?

Number of rows deleted: 0

I replaced our code with your snippets.

y the way, why not use the PDO extension or MySQLi extension instead of the legacy MySQL extension?

I have no idea what that is 🙂

DJJ:

In phpmyadmin, see the Delete command, and write that into your php command

Yes, that's where I got the SQL from.

Character coding is the another i would check...The page's char code,

Ahhh.

Would that be (something like) this in the header?

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">

I do not have any of that on the page.
The page ONLY contains the script, as you see it below.

My script now looks like this

<?php

$dbhost = 'xxxx';
$dbuser = 'xxxx';
$dbpass = 'xxxx';
$dbname = 'xxxx';


$conn = mysql_connect($dbhost, $dbuser, $dbpass)
    or die('Could not connect to database server: ' . mysql_error());

mysql_select_db($dbname)
    or die('Could not select database: ' . mysql_error());

$result = mysql_query('DELETE FROM arp3_cam WHERE id=82')
    or die('Could not execute SQL statement: ' . mysql_error());


echo 'Number of rows deleted: ' . mysql_affected_rows();

?>

[laserlight]

jmueller0823 wrote:

Number of rows deleted: 0

hmm... what about INSERT and UPDATE statements? Can you even select anything from the database table?

At this point, everything points to a user error: you must be interpreting something incorrectly. If that is not the case, and if djjjozsi's hunch is not correct, then I am afraid that I have no idea what else to try, so you will have to wait for a database guru to come and take a look.

Incidentally, what version of PHP and MySQL are you using?

jmueller0823 wrote:

I have no idea what that is

Newer database extensions that provide more features than the legacy MySQL extension, especially prepared statements.

jmueller0823 wrote:

Would that be (something like) this in the header?

That is the document type definition and html start tag. The character encoding would be specified in a meta tag like the one on this page:

<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" />

More importantly in your case, it is affected by the actual encoding of the file itself. Some editors/IDEs allow you to easily check this, e.g., by presenting possible encodings in a menu and highlighting the current encoding selection.

[jmueller0823]

PHP Version 5.2.3

Is the MySQL version available in phpinfo? I don't see it...

At this point, everything points to a user error

Agree.

[laserlight]

It should be available under the mysql section in the "Client API version" field.

[jmueller0823]

Can you even select anything from the database table?

Yes. Using the query below, I can display the recs...

$result = mysql_query('SELECT * FROM arp3_cam')
    or die('Could not execute SQL statement: ' . mysql_error());

while ($row = mysql_fetch_array($result)) {
echo $row['email']; 
}

I just noticed that the EXACT code generated by phpMyAdmin is:

DELETE FROM `arp3_cam` WHERE `arp3_cam`.`id` = 82 LIMIT 1

A little different from what I'm using.
The quotes don't seem to work.
Notice the 'period' between cam and id?

MySQL Client API version 5.0.45

[laserlight]

jmueller0823 wrote:

A little different from what I'm using.
The quotes don't seem to work.

Those are backticks, and are used (by MySQL only - the standard is to use double quotes) to quote identifiers that are (or may be) reserved. In this case none of your identifiers (arp3_cam and id) are reserved, so there should be no difference.

jmueller0823 wrote:

Notice the 'period' between cam and id?

That just says that id is a column of arp3_cam, but since there is no other table specified in the SQL statement, there can be no ambiguity so that is unnecessary.

[jmueller0823]

What about the "LIMIT 1"
Is that significant?

[laserlight]

jmueller0823 wrote:

What about the "LIMIT 1"
Is that significant?

No, from what I understand it is an optimisation to prevent a scan of the rest of the table once a match is found, but here the problem is that a match is not found even when it should be.

[djjjozsi]

Let's send us an SQL dump,

the create SQL command of that table please.
You should see the privilages of your user,
if i'm not mistaken it could editable.

I've never heard like this you wrote.

[jmueller0823]

djjjozsi

Please elaborate.

What would that query look like?

I'm familiar with a DUMP, but certainly you don't want an entire dump.

[djjjozsi]

In phpmyadmin, you can found an export,

and send just like this, with no INSERT commands:

CREATE TABLE IF NOT EXISTS counter (
counter_id int(11) NOT NULL auto_increment,
sid varchar(255) character set latin2 collate latin2_hungarian_ci NOT NULL,
datum date NOT NULL,
ip varchar(20) NOT NULL,
PRIMARY KEY (counter_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=62 ;

jmueller0823;10889973 wrote:

djjjozsi

Please elaborate.

What would that query look like?

I'm familiar with a DUMP, but certainly you don't want an entire dump.

[jmueller0823]

Okay.

1. I created the table with your query.
2. Then, I did an Export

-- phpMyAdmin SQL Dump
-- version 2.11.9.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Oct 17, 2008 at 03:17 PM
-- Server version: 5.0.51
-- PHP Version: 5.2.6

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `growthtr_arp3`
--

-- --------------------------------------------------------

--
-- Table structure for table `counter`
--

CREATE TABLE IF NOT EXISTS `counter` (
  `counter_id` int(11) NOT NULL auto_increment,
  `sid` varchar(255) character set latin2 collate latin2_hungarian_ci NOT NULL,
  `datum` date NOT NULL,
  `ip` varchar(20) NOT NULL,
  PRIMARY KEY  (`counter_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=62 ;

--
-- Dumping data for table `counter`
--

Is that what you're looking for?

[djjjozsi]

You've misunderstand me a little, but never mind 🙂

OK,
now lets run this sql command in phpmyadmin:

INSERT INTO counter (counter_id, sid, datum, ip) VALUES
(1, 'roukqn6t8ji541cmt8j2pdk5d5', '2008-10-11', '78.92.161.78'),

run this in php,
$sql="DELETE FROM counter WHERE datum='2008-10-11' AND sid='roukqn6t8ji541cmt8j2pdk5d5' LIMIT 1";

This DELETE this row only.

If it's goes, then your structure is the failure.

And then insert it once, and if it DELETES the row, lets check with the id field..

good luck.

jmueller0823;10889984 wrote:

Okay.

1. I created the table with your query.
2. Then, I did an Export

-- phpMyAdmin SQL Dump
-- version 2.11.9.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Oct 17, 2008 at 03:17 PM
-- Server version: 5.0.51
-- PHP Version: 5.2.6

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `growthtr_arp3`
--

-- --------------------------------------------------------

--
-- Table structure for table `counter`
--

CREATE TABLE IF NOT EXISTS `counter` (
  `counter_id` int(11) NOT NULL auto_increment,
  `sid` varchar(255) character set latin2 collate latin2_hungarian_ci NOT NULL,
  `datum` date NOT NULL,
  `ip` varchar(20) NOT NULL,
  PRIMARY KEY  (`counter_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=62 ;

--
-- Dumping data for table `counter`
--

Is that what you're looking for?

[jmueller0823]

Okay.
I Inserted the record.

And ran this:

$result = mysql_query("DELETE FROM counter WHERE datum='2008-10-11' AND sid='roukqn6t8ji541cmt8j2pdk5d5' LIMIT 1") ;

Very Strange.

Running your query displays:
Could not execute SQL statement: Table 'growth_arp3.counter' doesn't exist

Yet, I can see the 'counter' table in phpmyadmin -- and browse it, etc.

The complete code looks like this:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" /> 
</head>
<body>


<?php

$dbhost = 'xxxx';
$dbuser = 'xxxx';
$dbpass = 'xxxx';
$dbname = 'xxxx';



$conn = mysql_connect($dbhost, $dbuser, $dbpass)
    or die('Could not connect to database server: ' . mysql_error());

mysql_select_db($dbname)
    or die('Could not select database: ' . mysql_error());


$result = mysql_query("DELETE FROM counter WHERE datum='2008-10-11' AND sid='roukqn6t8ji541cmt8j2pdk5d5' LIMIT 1") 
    or die('Could not execute SQL statement: ' . mysql_error());


echo 'Number of records processed ' . mysql_affected_rows();

?>

</body>
</html>