CODEWITHSUNDEEP
phpmysql
mckeegan375
mckeegan375

Reputation: 255

How to contruct most efficient MySQL query

I was wondering if someone could help me find the most efficient way of pulling data from two tables which have a common ID.

A simple example is that I have two tables; customers and messages which have the following fields:

Customers - id, firstname, surname

Messages - customer_id, message

The relationship is that the customer table would only have one entry with a unique id but the messages table could have multiple messages for each customer.

On a PHP page, I want to show the customers name at the top of the page and then all of the messages for that customer.

At the moment, I am using two queries as follows:

$q1 = mysql_query("SELECT * FROM customers WHERE id='123'");
while($row1= mysql_fetch_assoc($q1)){
    echo "$row1[firstname] $row1[surname]";
}

$q2 = mysql_query("SELECT * FROM messages WHERE customer_id='123'");
while($row2= mysql_fetch_assoc($q2)){
    echo "$row2[message]";
}

This is a simplified version of what I am trying to achieve but I was wondering if there is a way I can do this with one query. I have seen JOIN and UNION functions when searching on here (but have never used them) but cannot see how these could allow me to display the customers name only once with all the messages. If these were coming from one query and there were 10 messages then the customers name would be repeated 10 times?

Not sure that I've made full sense there but any help would be appreciated :)

Upvotes: 0

Views: 44

Answers (1)

Mark Baker
Mark Baker

Reputation: 212402

Make sure you have appropriate indexes on both tables

SELECT customers.name,
       messages.*
  FROM customers
  LEFT JOIN messages
         ON messages.customer_id = customers.id
 WHERE customers.id = 123

Upvotes: 1

Related Questions