我正试图从名为address_contact的表中获取最后一个ID#,我使用的代码是:
$result = mysql_query("SELECT id FROM address_contact ORDER BY lastUpdate DESC LIMIT 1")
or die(mysql_error());
$row = mysql_fetch_array( $result );
$id = .$row['id'];
现在我想关闭那个连接,打开一个新的连接,然后使用我们刚刚得到的ID#从以下3个表中获取所有数据:
Table 1: address_address
Table 2: address_contact
Table 3: address_email
所以它看起来像这样???
$result = mysql_query("SELECT address_contact.id,address_contact.lastname,address_contact.firstname,address_contact.primaryAddType,address_address.id,address_address.phone1,address_address.phone2,address_address.line2,address_email.id,address_email.email
FROM address_address
LEFT JOIN address_contact ON address_address.id=address_contact.id
LEFT JOIN address_email ON address_address.id=address_email.id
WHERE address_contact.id = ".$id)
但必须有一种更简单/更快的方法来做到这一点?
如果这个ID是为您刚刚插入的全新记录创建的,那么您应该使用msyql_last_insert_id()
函数,它可以确保您获得this特定脚本/数据库句柄所做的最后一次插入。您的方法受竞赛条件的约束——如果某个OTHER脚本在该脚本背后插入,您将获得该脚本的ID,而不是您的ID。
话虽如此,你最好还是做
从您的表中选择最大(id)
而不是按订单/限制版本。这样做更有效率。
基本代码序列为:
INSERT INTO yourtable ....
SELECT @id := last_insert_id();
SELECT ... FROM yourtable WHERE id = @id;
您可以在单个SQL语句中使用子查询来查找ID。
SELECT
address_contact.id,
address_contact.lastname,
address_contact.firstname,
address_contact.primaryAddType,
address_address.id,
address_address.phone1,
address_address.phone2,
address_address.line2,
address_email.id,
address_email.email
FROM
address_address
LEFT JOIN address_contact ON address_address.id = address_contact.id
LEFT JOIN address_email ON address_address.id = address_email.id
WHERE address_contact.id = (
SELECT id FROM address_contact ORDER BY lastUpdate DESC LIMIT 1
)
为什么不使用单一查询
SELECT address_contact.id,address_contact.lastname,address_contact.firstname,address_contact.primaryAddType,address_address.id,address_address.phone1,address_address.phone2,address_address.line2,address_email.id,address_email.email
FROM address_address
LEFT JOIN address_contact ON address_address.id=address_contact.id
LEFT JOIN address_email ON address_address.id=address_email.id
ORDER BY address_contact.lastUpdate DESC
LIMIT 1