Jump to content

SELECT Error: Unknown column 'achievements' in 'where clause'


Recommended Posts

I have searched this forum as well as over 200 other forums and have not found the answer that is specific to my question. I have shortened my code drastically to assist in resolving this quickly -


I have a search form that has criteria for the search criteria with "virtual" "columns" in an array but it's not working. If I search one column at a time it works just fine but when I try to search 8 columns with one select I get the following error: SELECT Error: Unknown column 'achievements' in 'where clause'.


When a user selects search in Achievements, I need it to look at all 8 columns that are associated with achievements and bring back the results that match - the same as if the user selects search in Associations, I need it to look at all 5 columns and bring back the results that match.


My shortened code is as follows:

        <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <form name="search" action="" method="POST">
        Achievements/Associations: <input type="text" name="find1" /> in 
      <Select NAME="field1">
        <Option VALUE="achievements">Achievements</option>
        <Option VALUE="associations">Associations</option>
      Secondary Education: <input type="text" name="find2" /> in 
      <Select NAME="field2">
        <Option VALUE="edu1sectype">Highest Certificate Attained</option>
        <Option VALUE="edu1secname">Highest Grade Passed</option>
        <Option VALUE="edu1secinst">Name of High School</option>
        <Option VALUE="edu1secdate">Date Completed</option>
        <Option VALUE="edu1secinsttyp">Type of Institution</option>
        <Option VALUE="subjects">Subjects</option>
      <input type="hidden" name="searching" value="yes" />
      <input type="submit" name="search" value="Search" />
    $searching = $_POST['searching'];
    $find1 = $_POST['find1'];
    $field1 = $_POST['field1'];
    $find2 = $_POST['find2'];
    $field2 = $_POST['field2'];
    if ($searching =="yes") 
     echo "<br><b>Searched For:</b>  $find1 $find2<br>"; 
     echo "<br><h2>Results</h2><p>"; 
     //If they did not enter a search term we give them an error 
     // Otherwise we connect to our Database 
    include_once "connect_to_mysql.php";
    mysql_select_db("table_name") or die(mysql_error());
     // We preform a bit of filtering 
    $find = strtoupper($find);
    $find = strip_tags($find);
    $find = trim($find);
    $find = mysql_real_escape_string($find);
    $field = mysql_real_escape_string($field);
    $data = mysql_query("SELECT * FROM table_name
    WHERE upper(".$field1.")  LIKE '%$find1%'
    AND upper(".$field2.")  LIKE '%$find2%'
    ") or die("SELECT Error: ".mysql_error());
    $result = mysql_query("SELECT * FROM table_name 
     WHERE upper($field1) LIKE '%$find1%'
    AND upper($field2) LIKE '%$find2%'
    ") or die("SELECT Error: ".mysql_error());
    $num_rows = mysql_num_rows($result);
    echo "There are $num_rows records:<br>";
     echo '<center>';
    									    echo "<table border='1' cellpadding='5' width='990'>";
                                            // set table headers
                                            echo "<tr><th>Reference</th>
    										          <th>First Name</th>
    										          <th>Last Name</th>
    //get images and names in two arrays
    $name= $row["name"];
    $surname= $row["surname"];
    $achieve1 = $row["achieve1"];
    $achieve2 = $row["achieve2"];
    $achieve3 = $row["achieve3"];
    $achieve4 = $row["achieve4"];
    $achieve5 = $row["achieve5"];
    $achieve6 = $row["achieve6"];
    $achieve7 = $row["achieve7"];
    $achieve8 = $row["achieve8"];
    $assoc1 = $row["assoc1"];
    $assoc2 = $row["assoc2"];
    $assoc3 = $row["assoc3"];
    $assoc4 = $row["assoc4"];
    $assoc5 = $row["assoc5"];
    $edu1sectype = $row["edu1sectype"];
    $edu1secinst = $row["edu1secinst"];
    $edu1secname = $row["edu1secname"];
    $edu1secdate = $row["edu1secdate"];
    $edu1secinsttyp = $row["edu1secinsttyp"];
    $subject1 = $row["subject1"];
    $subject2 = $row["subject2"];
    $subject3 = $row["subject3"];
    $subject4 = $row["subject4"];
    $subject5 = $row["subject5"];
    $subject6 = $row["subject6"];
    $subject7 = $row["subject7"];
    $subject8 = $row["subject8"];
    $compsoft1name = $row["compsoft1name"];
    $compsoft2name = $row["compsoft2name"];
    $compsoft3name = $row["compsoft3name"];
    $compsoft4name = $row["compsoft4name"];
    $compsoft5name = $row["compsoft5name"];
    $compsoft6name = $row["compsoft6name"];
    $achievements = array('achieve1', 'achieve2', 'achieve3', 'achieve4', 'achieve5', 'achieve6', 'achieve7', 'achieve8');
    $associations = array('assoc1', 'assoc2', 'assoc3', 'assoc4', 'assoc5');
    $subjects = array('subject1', 'subject2', 'subject3', 'subject4', 'subject5', 'subject6', 'subject7', 'subject8' );
    $compsoft = array('compsoft1name', 'compsoft2name', 'compsoft3name', 'compsoft4name', 'compsoft5name', 'compsoft6name');
    while ($row = mysql_fetch_array($result))
     echo "<tr>";
    												echo "<td ALIGN=LEFT>" . $row['id'] . "</td>";
                                                    echo "<td ALIGN=LEFT>" . $row['name'] . "</td>";
                                                    echo "<td ALIGN=LEFT>" . $row['surname'] . "</td>";
    												echo "</tr>";
                                            echo "</table>";
     //This counts the number or results - and if there wasn't any it gives them a little message explaining that 
    if ($anymatches == 0) {
    echo "Sorry, but we can not find an entry to match your query";

Any assistance will be greatly appreciated as I have been working on this website for the past 4 months which has totalled over 150 pages and this is one of the last pages left to program and it's taken 6 days to get to this search page to this point.

Link to comment
Share on other sites

Hi Sen Li, thank for your response - I know the problem is: <Option VALUE="achievements">Achievements</option>. I know that is the line causing the problem because I don't have a column with that name ($achievements) - I've tried by creating an array for achievements and listed achieve1, achieve2, achieve3 etc....but it's not reading it... please accept my apologies for the sloppy coding - I'm not a programmer by trade but became a programmer after I became disabled and lost my job and I'm trying to get my business online... any help will be very gratefully appreciated....

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.