MySQL/Database Help

Posted on 2009-12-16
Last Modified: 2013-12-12
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 = a.owner"
	. "\n LEFT JOIN #__users AS r ON = a.assoc_agent"
    . "\n WHERE a.published = '1' AND = $id";
    $database->setQuery( $query );
    $rows = $database->loadObjectList();
	$row = $rows[0];

Open in new window

Question by:adamrosshill
    LVL 34

    Accepted Solution

    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!

    Author Comment

    Ya thanks so much! I did a little more goggling and found this article as well. I guess I always just joined long hand.

    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!

    Author Closing Comment


    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
    Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
    The viewer will learn how to dynamically set the form action using jQuery.
    The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now