>我有以下基本查询,它从表中选择一个 PIN,将其绑定到变量,然后将其从表中删除。
$sth = $this->db->query("SELECT available_pins FROM pin_list ORDER BY RAND() LIMIT 0,1 ;");
$pinarray = $sth->fetch();
$this->user_pin = $pinarray->available_pins;
$sth = $this->db->prepare("DELETE FROM pin_list WHERE available_pins = ? LIMIT 0,1");
$sth->execute(array($this->user_pin));
我的问题:PIN被选中并且回显正常,但它不会从表中删除。我做错了什么?
另外,在这两种情况下,我如何最好地添加一个 if 语句来捕获错误?
您的DELETE
语法中存在语法错误。 LIMIT
没有 DELETE
的偏移量参数。
选择元组时,将其展开以包含 PK。将属性绑定到变量后,可以通过使用 PK 限定 where 子句来删除元组。
下面是一个示例。唉,这个例子是用过程式 PHP 编写的,我已经有一段时间没有编写 OO-php 了。对此感到抱歉。尽管如此,我认为传达了主要思想。否则请告诉我。
鉴于数据库如下所示:
CREATE TABLE `pin_list` (
`id` INT( 11 ) NOT NULL AUTO_INCREMENT ,
`available_pins` CHAR( 8 ) NOT NULL ,
`aux` VARCHAR( 14 ) NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = MYISAM ;
mysql> select * from pin_list;
+----+----------------+-----+
| id | available_pins | aux |
+----+----------------+-----+
| 1 | 43236543 | f |
| 2 | 43236523 | f |
| 3 | 43266523 | f |
| 4 | 48266523 | f |
| 5 | 48264823 | f |
+----+----------------+-----+
5 rows in set (0.00 sec)
PHP 脚本可以这样写:
mysql_connect('mysql_server','user-id','pwd');
mysql_select_db('database-name');
//Select the available pin and its PK.
$query = "SELECT id, available_pins FROM pin_list ORDER BY RAND() LIMIT 0,1";
$rs = mysql_query( $query );
//Select id and available_ping into an array
$pinarray = mysql_fetch_array( $rs );
echo "The pin: " . $pinarray[1]; //Do something with it
//Save the primary key (PK, here: the id-attribute) for use in delete statement
$pk = $pinarray[0];
//Now: delete the pin that you have fetched from the database
echo "DELETE FROM pin_list WHERE id = " . $pk; //echo to debug sql-statements in php.
//Uncomment to delete
//$del_qry = "DELETE FROM pin_list WHERE id = " . $pk;
//mysql_query( $del_qry )