CODEWITHSUNDEEP
phpmysqlsql
msvuze
msvuze

Reputation: 1397

Faster way of Selecting a row from 3 tables using same id#

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

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

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?

Upvotes: 0

Views: 106

Answers (3)

VitaliyG
VitaliyG

Reputation: 1857

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

Upvotes: 0

Geoffrey
Geoffrey

Reputation: 11354

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

Upvotes: 0

Marc B
Marc B

Reputation: 360702

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.

That being said, you would be better off doing

SELECT max(id) FROM yourtable

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

The basic code sequence would be:

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

Upvotes: 2

Related Questions