SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Wizard
    Join Date
    Feb 2007
    Location
    Southern California
    Posts
    1,338
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Error says that id is ambiguous

    I get an error message saying that id in field is ambiguous. I tried adding the table name to the id (with a period between) but that doesn't help either. What is wrong with the script?

    This page loads a drop-down box with options taken from the 2-col table "model_listing." What the user selects is called "cid" (this is a portion of the page. The script was modified from Kevin's Build Your Own DB Driven Website):

    PHP Code:
    </select></label><br />
    <label><p>By vehicle:
    <select name="cid" size="1">
      <option selected value="">Any Vehicle</option></p>
    <?php
    while ($cat mysql_fetch_array($cats)) {
      
    $cid $cat['id'];
      
    $cname htmlspecialchars($cat['model_name']);
      echo 
    "<p><option value='$cid'>$cname</option></p>\n"
    }
    The above script calls the following script. It links the id of Model_listing to a table Racer_spotlight, and that table links to id of Spotlight_veh. The photoid column of Spotlight_veh links to Spotlight_photo to pull the corresponding photo.

    PHP Code:
    // The basic SELECT statement if no constraints were specified in the spotlight1.php form. Select everything from one table.
    $select 'SELECT DISTINCT id, vehicle_name, owner_name, descr';
    $from   ' FROM spotlight_veh';
    $where  ' WHERE 1=1'

    PHP Code:
    //Make a join from spotlight_veh to racer_spotlight, then to model_listing
    $cid $_POST['cid'];
    if (
    $cid != '') {
      
    $from  .= ', model_listing, racer_spotlight';
      
    $where .= " AND id=spotlight_vehid AND model_listingid='$cid'";

    How is the id ambiguous in this script? (I show only a portion of the two scripts--what I think are the relevant portions; I'm not sure you want to slog through three pages of code!)

    Thanks!
    Steve

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    it ambiguous because at least two of your tables have a column in them called "id". you need to use table aliases.

  3. #3
    SitePoint Wizard
    Join Date
    Feb 2007
    Location
    Southern California
    Posts
    1,338
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I specified Spotlight_veh.id and that did not work. Do you mean something else when you said "table alias"? I'm not familiar with the term--still learning all this.

    Thanks!
    Steve

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    try running your queries outside of php before you parameterize and generalize and dynamicize then

    this will give us a much better error message than "that did not work"

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    you have id in two locations. both of them need to be qualified.

    see http://www.1keydata.com/sql/sqlalias.html for a tutorial on aliases.

  6. #6
    SitePoint Wizard
    Join Date
    Feb 2007
    Location
    Southern California
    Posts
    1,338
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    try running your queries outside of php before you parameterize and generalize and dynamicize then

    this will give us a much better error message than "that did not work"

    Sorry, I don't know what you mean by running queries outside of php, and the rest of your sentence--I'm new to all this.

    "That did not work" means that attempting to make id more definite by appending the table name resulted in the same error message.

    Thanks,
    Steve

  7. #7
    SitePoint Wizard
    Join Date
    Feb 2007
    Location
    Southern California
    Posts
    1,338
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck View Post
    you have id in two locations. both of them need to be qualified.

    see http://www.1keydata.com/sql/sqlalias.html for a tutorial on aliases.
    Thank you. I'll look up more about aliases, including your tutorial.

    Regards,
    Steve


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
  •