使用相同的id#从3个表中选择一行的快速方法。

I'm trying to get the last ID# from the table called address_contact and the code I use is:

我试图从名为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'];

Now I would like to close that connection and open a new one and then get all of the data from the following 3 tables using that ID# that we just got:

现在我想关闭这个连接并打开一个新的，然后从下面的3个表中获取所有的数据使用我们刚刚得到的ID#

Table 1: address_address
Table 2: address_contact
Table 3: address_email

so it would look something like this ???

它看起来像这样???

$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)

But there has to be a easier/faster way of doing this?

但是有一个更简单更快的方法吗?

3 个解决方案

#1

You can do this in a single SQL statement using a sub query to find the 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
  )

#2

If this ID is for a brand new record you'd just inserted, you should be using the msyql_last_insert_id() function, which guarantees that you get the last insert THIS particular script/database handle did. Your method is subject to race conditiosn - if some OTHER script does an insert behind this script's back, you'll get that script's ID, not yours.

如果这个ID是您刚刚插入的一个全新的记录，那么您应该使用msyql_last_insert_id()函数，它可以保证您获得了最后一个插入这个特定脚本/数据库句柄。您的方法取决于race conditiosn——如果其他脚本在这个脚本后面执行插入操作，那么您将获得该脚本的ID，而不是您的。

That being said, you would be better off doing

那就是说，你最好还是去做。

SELECT max(id) FROM yourtable

从yourtable选择马克斯(id)

instead of the order by/limit version. It's more efficient to it this way.

而不是order by/limit版本。这样更有效。

The basic code sequence would be:

基本的代码顺序是:

INSERT INTO yourtable ....
SELECT @id := last_insert_id();
SELECT ... FROM yourtable WHERE id = @id;

#3

Why not use single 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                  
ORDER BY address_contact.lastUpdate DESC 
LIMIT 1

#1