SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Enthusiast
    Join Date
    Aug 2008
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Column 'LecturerID' in field list is ambiguous

    I want do do a search based on two tables:
    Lecturers: LecturerID, FirstName, LastName, Department
    Modules:ModleCode, ModuleName, LecturerID, Level, Semester, Credits
    The code is based on Kevin Yanks PHP and MySQL book. I think I need to join the two tables together but my attempt shows the following error: Error: Column 'LecturerID' in field list is ambiguous

    This is the code for the form to enter in the search criteria:
    PHP Code:
    <h1>Search Modules</h1>
    <?php

    //connect to the database server  
    $dbcnx = @mysql_connect('localhost''root''els6014');
    if (!
    $dbcnx) {
      exit(
    '<p>Unable to connect to the ' .
          
    'database server at this time.</p>');
    }

    //select the prototype database
    if (!@mysql_select_db('prototype')) {
      exit(
    '<p>Unable to locate the ARD ' .
          
    'database at this time.</p>');
    }

    //obtain department list from the database with each department listed only once
    $departments = @mysql_query('SELECT DISTINCT Department FROM lecturers');
    if (!
    $departments) {
      exit(
    '<p>Unable to obtain department list from the database.</p>');
    }

    //obtain module codes from the database with each code listed only once
    $mCode = @mysql_query('SELECT DISTINCT ModuleCode FROM modules');
    if (!
    $mCode) {
      exit(
    '<p>Unable to obtain module codes from the database.</p>');
    }

    ?>

    <!--Search form -->
    <form action="moduleslist.php" method="post">
    <p><strong>View moduless satisfying the following criteria:</strong></p>

    <!--Drop down list of module codes -->
    <label>By module code:
    <select name="modulecode" size="1">
      <option selected value="">Any Module Code</option>
      <option value="">--------</option>
    <?php
     
    while ($mCodes mysql_fetch_array($mCode)) 
      {
        echo 
    "<option>"$mCodes['ModuleCode'] ."</option>\n";  
      }

    ?>
    </select></label><br /><br />

    <label>By text in module name: <input type="text" name="modulename" /></label><br /><br />
    <label>By lecturer's first name: <input type="text" name="firstname" /></label><br /><br />
    <label>By Lecturer's last name: <input type="text" name="lastname" /></label><br /><br />
    <!--Drop down list of departments -->
    <label>By department:
    <select name="dep" size="1">
      <option selected value="">Any Department</option>
          <option value="">--------</option>
    <?php
     
    while ($department mysql_fetch_array($departments)) 
      {
        echo 
    "<option>"$department['Department'] ."</option>\n";  
      }
    ?>
    </select></label><br /><br />

    <label>By level: <select name="level" size="1">
      <option selected value="">Any Level</option>
      <option value="">--------</option>
      <option>1</option>
      <option>2</option>
      <option>3</option>
        <option>4</option>
          </select>
    </label><br /><br />

    <label>By Semester: <select name="semester" size="1">
      <option selected value="">Any Semester</option>
      <option value="">--------</option>
      <option>Semester 1</option>
      <option>Semester 2</option>
      <option>Semester 1 & 2</option>
          </select>
    </label><br /><br />
      
    <input type="submit" value="Search" />
    </form>

    <br />

    <a href="ardindex.html">Retun to index.</a>
    </body>
    </html>
    This is the code to show the list of modules that meet the criteria(This is where I am having the problem.)
    PHP Code:
    <h1>Modules</h1>
    <?php
      
    //connect to the database server    
    $dbcnx = @mysql_connect('localhost''root''els6014');
    if (!
    $dbcnx) {
      exit(
    '<p>Unable to connect to the ' .
          
    'database server at this time.</p>');
    }
    //select the prototype database
    if (!@mysql_select_db('prototype')) {
      exit(
    '<p>Unable to locate the ARD ' .
          
    'database at this time.</p>');
    }

    // The basic SELECT statement
    $select 'SELECT ModuleCode, ModuleName, LecturerID, Level, Semester, Credits, Department, FirstName, LastName';
    $from   ' FROM modules, lecturers';
    $where  ' WHERE 1=1 AND modules.LecturerID = lecturers.LecturerID';



    $modulecode $_POST['modulecode'];
    if (
    $modulecode != '') { // A module has been selected
      
    $where .= " AND ModuleCode='$modulecode'";
    }

    $modulename $_POST['modulename'];
    if (
    $modulename != ''// A first name has been entered

      
    $where .= " AND ModuleName LIKE '%$modulename%'";
    }

    $firstname $_POST['firstname'];
    if (
    $firstname != ''// A first name has been entered

      
    $where .= " AND FirstName LIKE '$firstname%'";
    }

    $lastname $_POST['lastname'];
    if (
    $lastname != ''// A last name has been entered

      
    $where .= " AND LastName LIKE '$lastname%'";
    }

    $dep $_POST['dep'];
    if (
    $dep != '') { // A department has been selected
      
    $where .= " AND Department='$dep'";
    }

    $level $_POST['level'];
    if (
    $level != '') { // A department has been selected
      
    $where .= " AND Level='$level'";
    }

    $semester $_POST['semester'];
    if (
    $semester != '') { // A department has been selected
      
    $where .= " AND Semester='$semester'";
    }

    ?>
    <!--Table with search results -->
    <table border="1">
    <tr>
    <td><strong>Module Code</strong></td>
    <td><strong>Module Name</strong></td>
    <td><strong>Lecturer's First Name</strong></td>
    <td><strong>Lecturer's Last Name</strong></td>
    <td><strong>Department</strong></td>
    <td><strong>Level</strong></td>
    <td><strong>Semester</strong></td>
    <td><strong>Credits</strong></td>
    </tr>


    <?php
    $modules 
    = @mysql_query($select $from $where);
    if (!
    $modules) {
      echo 
    '</table>';
      exit(
    '<p>Error retrieving modules from database!<br />'.
          
    'Error: ' mysql_error() . '</p>');
    }

    while (
    $module mysql_fetch_array($modules)) {
      echo 
    "<tr valign='top'>\n";
      
    $ModuleCodehtmlspecialchars($module['ModuleCode']);
      
    $ModuleNamehtmlspecialchars($module['ModuleName']);
      
    $FirstNamehtmlspecialchars($module['FirstName']);
      
    $LastNamehtmlspecialchars($module['LastName']);
      
    $Departmenthtmlspecialchars($module['Department']);
      
    $Levelhtmlspecialchars($module['Level']);
      
    $Semesterhtmlspecialchars($module['Semester']);
      
    $Creditshtmlspecialchars($module['Credits']);

    echo 
    "<tr>
    <td>
    $ModuleCode</td>
    <td>
    $ModuleName</td>
    <td>
    $FirstName</td>
    <td>
    $LastName</td>
    <td>
    $Department</td>
    <td>
    $Level</td>
    <td>
    $Semester</td>
    <td>
    $Credits</td> "
      echo 
    "</tr>\n";
    }
    ?>

    </table>

    <p><a href="searchlecturers.php">New search</a></p>
    <p><a href="ardindex.html">Return to main index</a></p>

    </body>
    </html>
    Any help would really be appriciated, thanks.

  2. #2
    SitePoint Enthusiast
    Join Date
    Aug 2008
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Error: Column 'LecturerID' in field list is ambiguous

    I want do do a search based on two tables:
    Lecturers: LecturerID, FirstName, LastName, Department
    Modules:ModleCode, ModuleName, LecturerID, Level, Semester, Credits
    The code is based on Kevin Yanks PHP and MySQL book. I think I need to join the two tables together but my attempt shows the following error: Error: Column 'LecturerID' in field list is ambiguous

    This is the code for the form to enter in the search criteria:
    PHP Code:
    <h1>Search Modules</h1>
    <?php

    //connect to the database server  
    $dbcnx = @mysql_connect('localhost''root''els6014');
    if (!
    $dbcnx) {
      exit(
    '<p>Unable to connect to the ' .
          
    'database server at this time.</p>');
    }

    //select the prototype database
    if (!@mysql_select_db('prototype')) {
      exit(
    '<p>Unable to locate the ARD ' .
          
    'database at this time.</p>');
    }

    //obtain department list from the database with each department listed only once
    $departments = @mysql_query('SELECT DISTINCT Department FROM lecturers');
    if (!
    $departments) {
      exit(
    '<p>Unable to obtain department list from the database.</p>');
    }

    //obtain module codes from the database with each code listed only once
    $mCode = @mysql_query('SELECT DISTINCT ModuleCode FROM modules');
    if (!
    $mCode) {
      exit(
    '<p>Unable to obtain module codes from the database.</p>');
    }

    ?>

    <!--Search form -->
    <form action="moduleslist.php" method="post">
    <p><strong>View moduless satisfying the following criteria:</strong></p>

    <!--Drop down list of module codes -->
    <label>By module code:
    <select name="modulecode" size="1">
      <option selected value="">Any Module Code</option>
      <option value="">--------</option>
    <?php
     
    while ($mCodes mysql_fetch_array($mCode)) 
      {
        echo 
    "<option>"$mCodes['ModuleCode'] ."</option>\n";  
      }

    ?>
    </select></label><br /><br />

    <label>By text in module name: <input type="text" name="modulename" /></label><br /><br />
    <label>By lecturer's first name: <input type="text" name="firstname" /></label><br /><br />
    <label>By Lecturer's last name: <input type="text" name="lastname" /></label><br /><br />
    <!--Drop down list of departments -->
    <label>By department:
    <select name="dep" size="1">
      <option selected value="">Any Department</option>
          <option value="">--------</option>
    <?php
     
    while ($department mysql_fetch_array($departments)) 
      {
        echo 
    "<option>"$department['Department'] ."</option>\n";  
      }
    ?>
    </select></label><br /><br />

    <label>By level: <select name="level" size="1">
      <option selected value="">Any Level</option>
      <option value="">--------</option>
      <option>1</option>
      <option>2</option>
      <option>3</option>
        <option>4</option>
          </select>
    </label><br /><br />

    <label>By Semester: <select name="semester" size="1">
      <option selected value="">Any Semester</option>
      <option value="">--------</option>
      <option>Semester 1</option>
      <option>Semester 2</option>
      <option>Semester 1 & 2</option>
          </select>
    </label><br /><br />
      
    <input type="submit" value="Search" />
    </form>

    <br />

    <a href="ardindex.html">Retun to index.</a>
    </body>
    </html>
    This is the code to show the list of modules that meet the criteria(This is where I am having the problem.)
    PHP Code:
    <h1>Modules</h1>
    <?php
      
    //connect to the database server    
    $dbcnx = @mysql_connect('localhost''root''els6014');
    if (!
    $dbcnx) {
      exit(
    '<p>Unable to connect to the ' .
          
    'database server at this time.</p>');
    }
    //select the prototype database
    if (!@mysql_select_db('prototype')) {
      exit(
    '<p>Unable to locate the ARD ' .
          
    'database at this time.</p>');
    }

    // The basic SELECT statement
    $select 'SELECT ModuleCode, ModuleName, LecturerID, Level, Semester, Credits, Department, FirstName, LastName';
    $from   ' FROM modules, lecturers';
    $where  ' WHERE 1=1 AND modules.LecturerID = lecturers.LecturerID';



    $modulecode $_POST['modulecode'];
    if (
    $modulecode != '') { // A module has been selected
      
    $where .= " AND ModuleCode='$modulecode'";
    }

    $modulename $_POST['modulename'];
    if (
    $modulename != ''// A first name has been entered

      
    $where .= " AND ModuleName LIKE '%$modulename%'";
    }

    $firstname $_POST['firstname'];
    if (
    $firstname != ''// A first name has been entered

      
    $where .= " AND FirstName LIKE '$firstname%'";
    }

    $lastname $_POST['lastname'];
    if (
    $lastname != ''// A last name has been entered

      
    $where .= " AND LastName LIKE '$lastname%'";
    }

    $dep $_POST['dep'];
    if (
    $dep != '') { // A department has been selected
      
    $where .= " AND Department='$dep'";
    }

    $level $_POST['level'];
    if (
    $level != '') { // A department has been selected
      
    $where .= " AND Level='$level'";
    }

    $semester $_POST['semester'];
    if (
    $semester != '') { // A department has been selected
      
    $where .= " AND Semester='$semester'";
    }

    ?>
    <!--Table with search results -->
    <table border="1">
    <tr>
    <td><strong>Module Code</strong></td>
    <td><strong>Module Name</strong></td>
    <td><strong>Lecturer's First Name</strong></td>
    <td><strong>Lecturer's Last Name</strong></td>
    <td><strong>Department</strong></td>
    <td><strong>Level</strong></td>
    <td><strong>Semester</strong></td>
    <td><strong>Credits</strong></td>
    </tr>


    <?php
    $modules 
    = @mysql_query($select $from $where);
    if (!
    $modules) {
      echo 
    '</table>';
      exit(
    '<p>Error retrieving modules from database!<br />'.
          
    'Error: ' mysql_error() . '</p>');
    }

    while (
    $module mysql_fetch_array($modules)) {
      echo 
    "<tr valign='top'>\n";
      
    $ModuleCodehtmlspecialchars($module['ModuleCode']);
      
    $ModuleNamehtmlspecialchars($module['ModuleName']);
      
    $FirstNamehtmlspecialchars($module['FirstName']);
      
    $LastNamehtmlspecialchars($module['LastName']);
      
    $Departmenthtmlspecialchars($module['Department']);
      
    $Levelhtmlspecialchars($module['Level']);
      
    $Semesterhtmlspecialchars($module['Semester']);
      
    $Creditshtmlspecialchars($module['Credits']);

    echo 
    "<tr>
    <td>
    $ModuleCode</td>
    <td>
    $ModuleName</td>
    <td>
    $FirstName</td>
    <td>
    $LastName</td>
    <td>
    $Department</td>
    <td>
    $Level</td>
    <td>
    $Semester</td>
    <td>
    $Credits</td> "
      echo 
    "</tr>\n";
    }
    ?>

    </table>

    <p><a href="searchlecturers.php">New search</a></p>
    <p><a href="ardindex.html">Return to main index</a></p>

    </body>
    </html>
    Any help would really be appriciated, thanks.

  3. #3
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    If you join two tables and a field in each has the same name, you have to preceed the field name with the table name.

    so this bit
    $select = 'SELECT ModuleCode, ModuleName, LecturerID, Level, Semester, Credits, Department, FirstName, LastName';

    $from = ' FROM modules, lecturers';

    $where = ' WHERE 1=1 AND modules.LecturerID = lecturers.LecturerID';


    should say lecturers.lecturerID in the $select part

    PS It's a bit strange to assign the above to three different variables, I'd have made $select the entire query string. There's nothing to gain from using three variables

    $select = 'SELECT ModuleCode, ModuleName, LecturerID, Level, Semester, Credits, Department, FirstName, LastName
    FROM modules, lecturers
    WHERE 1=1 AND modules.LecturerID = lecturers.LecturerID';

    I'd also neatly indent it in the PHP, as sql ignores line returns and tabs and white space.

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,501
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    because the field is present in more than one table, you use the following syntax in the WHERE clause to identify the exact field you want to use:

    AND modules.LecturerID = lecturers.LecturerID

    You must do the same in the SELECT clause, otherwise MySQL won't know which field you want to select, and give the ambiguity error:

    SELECT ModuleCode, ModuleName, modules.LecturerID,

    In this case you can choose any of the two fields, since they contain the same value.

  5. #5
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,501
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by Dr John View Post
    If you join ...
    I'm too slow...

  6. #6
    SitePoint Enthusiast
    Join Date
    Aug 2008
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dr John View Post
    PS It's a bit strange to assign the above to three different variables, I'd have made $select the entire query string. There's nothing to gain from using three variables

    $select = 'SELECT ModuleCode, ModuleName, LecturerID, Level, Semester, Credits, Department, FirstName, LastName
    FROM modules, lecturers
    WHERE 1=1 AND modules.LecturerID = lecturers.LecturerID';

    I'd also neatly indent it in the PHP, as sql ignores line returns and tabs and white space.
    Ye I though that too, but I am following an example and that is what it does, although it it seems strange

  7. #7
    SitePoint Enthusiast
    Join Date
    Aug 2008
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Also I was wondering how I could do the following:
    If a user enters search crieria that yields no results intstead of the table coming up blank, how could I make it so that a message along the llines of "no value matching the search criteria was found, Change your selection and try again." comes up?

  8. #8
    Django Jedi neron-fx's Avatar
    Join Date
    Sep 2007
    Location
    Bristol/Bath, UK
    Posts
    274
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sure just say something like

    Code:
    if($numRows = mysql_num_rows($result) != 0)
    	{
                $outputYourNormalTableHere;
            }
    else
           {
               echo "No results to display";
           }
    Neron-Fx
    Everytime a user opens Internet Explorer, a web developer dies...
    http://www.savethedevelopers.org/

  9. #9
    SitePoint Enthusiast
    Join Date
    Aug 2008
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    whats $result equal to? It says mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\wamp\www\lecturerslist.php on line 78 when I try that.


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
  •