• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 265
  • Last Modified:

MySQL/Database Help

I'm wondering if someone can explain this code a little for me the table is actualy jos_ezrealty I can understand where the defined variable for that is coming from however I'm a little confused where the a.* is reference and the letter in front of the code. thanks so much

   
$query = "SELECT a.*, u.mid AS mid, u.dealer_name AS dealer_name, u.dealer_company AS dealer_company, u.dealer_email AS dealer_email, 
	u.dealer_phone AS dealer_phone, u.dealer_mobile AS dealer_mobile, u.dealer_sms AS dealer_sms, u.show_sms AS show_sms, 
	u.dealer_image AS dealer_image, u.dealer_type AS dealer_type, u.logo_image AS logo_image, u.published AS dealerpublished, 
	p.mid AS amid, p.dealer_name AS adealer_name, p.dealer_company AS adealer_company, p.dealer_phone AS adealer_phone, 
	p.dealer_mobile AS adealer_mobile, p.dealer_sms AS adealer_sms, p.show_sms AS ashow_sms, p.dealer_image AS adealer_image, 
	p.dealer_type AS adealer_type, p.logo_image AS alogo_image, p.published AS adealerpublished, 
	q.username AS user1, r.username AS user2 FROM #__ezrealty as a"
	. "\n LEFT JOIN #__ezrealty_profile AS u ON u.mid = a.owner"
	. "\n LEFT JOIN #__ezrealty_profile AS p ON p.mid = a.assoc_agent"
	. "\n LEFT JOIN #__users AS q ON q.id = a.owner"
	. "\n LEFT JOIN #__users AS r ON r.id = a.assoc_agent"
    . "\n WHERE a.published = '1' AND a.id = $id";
    $database->setQuery( $query );
    $rows = $database->loadObjectList();
	$row = $rows[0];

Open in new window

0
adamrosshill
Asked:
adamrosshill
  • 2
1 Solution
 
gr8gonzoConsultantCommented:
The "a." is just a nickname, really.

SELECT myTable.myField FROM myTable;
SELECT t.myField FROM myTable AS t;

Those two queries do the same thing. When you declare a table name AS something, you're creating a short nickname for that table to make it easier and faster to write the query. In my example above, there's really not a lot of difference in the length of the query. But when you're joining lots of tables or referencing a lot of fields, using "t" instead of "myTable" makes the query considerably shorter and easier to read.

It also helps when you're joining the same table on itself:

SELECT t1.parentID, t2.ID FROM myTable as t1 LEFT JOIN myTable as t2 ON t1.parentID=t2.ID;

Hopefully that makes sense!
0
 
adamrosshillAuthor Commented:
Ya thanks so much! I did a little more goggling and found this article as well. I guess I always just joined long hand.
http://www.keithjbrown.co.uk/vworks/mysql/mysql_p8.php

You also explained it very well.. I wasn't sure if I would mess up the variables by adding in some more field in here but it worked well.. thanks!
0
 
adamrosshillAuthor Commented:
thanks
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now