SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2004
    Location
    cioa
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    returning alias fields

    Hi, I am trying to display the value of the fields below in a webpage, but php doesn't like when I use an alias name in the $row[]. Does anyone know how I would be able to do this? Any help is appreciated, thanks.

    Here is the query I am using.
    Code:
    $search = "SELECT distinct * FROM tblRestaurants t
              Inner join tblCities c On t.CityID = c.CityID 
    	  Where t.RestaurantID = " . $RestID;
    The php to display the values

    Code:
    	echo("<p>" . $row["t.RestaurantName"] . "</p>");
        		echo("<p>" . $row["t.Address"] . "</p>");
        		echo("<p>" . $row["c.City"] . " " . $row["t.Province"] . "</p>");
        		echo("<p>" . $row["t.Phone"] . "</p>");
        		echo("<p>" . $row["t.Fax"] . "</p>");
        		echo("<p>" . $row["t.Hours"] . "</p>");

  2. #2
    SitePoint Enthusiast
    Join Date
    Oct 2004
    Location
    cioa
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I found a way to do it, but it seems to take a lot of coding... Does anyone know a better way to write the query then this?

    Code:
    $select = "SELECT distinct 
    	  t.RestaurantName as 'RestaurantName', 
    	  t.Address as 'Address',
    	  t.Province as 'Province',
    	  t.Phone as 'Phone',
    	  t.Fax as 'Fax',
    	  t.PostalCode as 'Postal',
    	  t.Hours as 'Hours',
    	  c.City as 'City' FROM tblRestaurants t
              Inner join tblCities c On t.CityID = c.CityID 
    	  Where t.RestaurantID = " . $RestID;

  3. #3
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Your query above is correct - but note that you only have to specify the table scope of the field if there is any ambiguity about which table the field is from. In other words, if RestaruantName, etc are fields of both table tblRestaurants and tblCities then you need to specify the scope such as t.RestaurantName. If there is no ambiguity because the field RestaurantName, etc is only appears in tblRestaurants then there is no need to specify the scope nor alias the fields in the result set - which makes accessing the result set in php much easier.

  4. #4
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm not sure why you need to use aliases. When I am doing queries like this I find that the values are available without the table name as part of the array key.

    For example the following works in my experience:
    Code:
     $query = "SELECT table.field1, table2.field2 FROM table, table2 WHERE....";
     $result = mysql_query($query);
     while($r = mysql_fetch_array($result))
     {
       echo $r['field1'];
       echo $r['field2'];
     }

  5. #5
    SitePoint Enthusiast
    Join Date
    Oct 2004
    Location
    cioa
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have to use an alias because of the inner join on tblcities. I'll try removing the alias parts for the tblRestaurant and see if it works. Thanks for the help.

    ** Here is the error I get from this query:
    Error performing query: Column 'CityID' in field list is ambiguous

    Code:
    "SELECT distinct 
    	  RestaurantName, 
    	  Address,
    	  Province,
    	  Phone,
    	  Fax,
    	  PostalCode,
    	  Hours,
    	  c.City as 'City' FROM tblRestaurants 
              Inner join tblCities c On CityID = c.CityID 
    	  Where RestaurantID = " . $RestID;

  6. #6
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you're selecting from more than one table you need to put the table name (or its alias) in the select query, but not as part of the array key in PHP when you are accessing the variables.
    That's why you got the mysql error about your query being ambiguos.

    See my previous code, the query needs the table, the variable afterwards doesn't.

  7. #7
    SitePoint Enthusiast
    Join Date
    Oct 2004
    Location
    cioa
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh I get it. Thanks for the help!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •