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

AshleighCo · May 27, 2014

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">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>Search</title>
    </head>
    
    <body>
    
    <form name="search" action="" method="POST">
    
      <p>Search:</p>
      <p>
        
        Achievements/Associations: <input type="text" name="find1" /> in 
      <Select NAME="field1">
        <Option VALUE="achievements">Achievements</option>
        <Option VALUE="associations">Associations</option>
      </Select>
      <br><br>
      
      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>
      </Select>
      <br><br>
         
      <input type="hidden" name="searching" value="yes" />
      <input type="submit" name="search" value="Search" />
      </p>
    </form>
    
    <?php
    $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>
    												  </tr>";
    
    //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 
     $anymatches=mysql_num_rows($data);
    if ($anymatches == 0) {
    echo "Sorry, but we can not find an entry to match your query";
    }
     }
     
    ?>
    
    </body>
    </html>

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.

Barand · May 27, 2014

Your data base design is creating your problems and is in serious need of normalization

See http://forums.phpfreaks.com/topic/273634-best-way-to-set-up-tables-when-multiple-values/?do=findComment&comment=1408360

AshleighCo · May 27, 2014

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....

Sign In

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

Recommended Posts

AshleighCo

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

AshleighCo

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information