Hi, As a test I am creating a little jobs database which allows users to input + search for jobs in my database. Currently it is all fine apart from the line: $sql= mysql_query("SELECT * FROM jobs WHERE Company like '%$company_term%' or Hours like '$hours_term' or Job like '$type_term' or Salary like '$salary_term'") or die("MySQL Search Error: " . mysql_error()); PHP: It doesn't work and only outputs every value in the database. Heres the full script: <html> <head> <meta http-equiv="content-type" content="text/html; charset=iso-8859-1" /> <meta name="author" content="ANDY1" /> <title>Untitled 1</title> </head> <body> <table> <form method=POST action='search.php?search1=yes'> <tr><td>Company Name:</td><td><input type=text name='company_term' ></input></td></tr> <tr><td>Job Type:</td><td><input type=text name='type_term' ></td></tr> <tr><td>Salary (Per Month)</td><td><input type=text name='salary_term'></td></tr> <tr><td>Hours (Per Week)</td><td><input type=text name='hours_term'></td></tr> <tr><td><input type=submit value="Look for Your New Jobs!"></td></tr> </form> </table> <?php $company_term=$_POST['company_term']; $type_term=$_POST['type_term']; $salary_term=$_POST['salary_term']; $hours_term=$_POST['hours_term']; $search1=$_GET['search1']; if ($search1 == yes) { mysql_connect ('localhost','root','') or die(mysql_error()); mysql_select_db ('jobs_database') or die (mysql_error('No Database Selected')); echo 'Connected To Database <br />We have found the following jobs for you: <br/> <br/>'; $sql= mysql_query("SELECT * FROM jobs WHERE Company like '%$company_term%' or Hours like '$hours_term' or Job like '$type_term' or Salary like '$salary_term'") or die("MySQL Search Error: " . mysql_error()); while ($row = mysql_fetch_array($sql)){ echo 'Job ID: '.$row['ID']; echo '<br/> Company Name: '.$row['Company']; echo '<br/> Job Type: '.$row['Job']; echo '<br/> Salary: '.$row['Salary']; echo '<br/> Hours: '.$row['Hours']; echo '<br/><br/>'; } } ?> </body> </html> PHP: Assume database is ok and the input system works fine. All comment appreciated! Thanks.
1) there are sql injections possible in the script use mysql_real_escape_string 2) $sql= mysql_query("SELECT * FROM jobs WHERE Company like '%$company_term%' or Hours like '$hours_term' or Job like '$type_term' or Salary like '$salary_term'") or die("MySQL Search Error: " . mysql_error()); that means it will find anything that contains the company term, or the hours term, or the type term or the salary term, if the company term is left empty, it will find everything. Maybe you meant to put ands in there instead of ors?
In response to 2): I want the user to be able to type into 4 boxes which say: So if they just enter the type of job they want, e.g Programmer, it will search the 'Job' field of my database and return any results where the row Job = Programmer. If they search for the Salary it will search the salary row of my database etc. I hope i explained that correctly In response to P2Host: Its just a tester to learn PHP, but there are already many job searching sites out there
Then what you need to do is have 4 different queries. Maybe have a little radio choosing what they want to search with
They are the same. mysql_fetch_assoc return associative array. mysql_fetch_row return numeric array mysql_fetch_array return both associative and numeric array, thus slower
Still to early in the morning but here is some code I came up with. Now you need to add in your stuff, this just concentrates on the query building part. the query assumes you have an id field in your Jobs table: $search1 = trim($_GET['search1']); //If form is submitted we run the search query if($search1 == 'yes') { //Clean your variables always! I just used trim here but strip variables //of any html etc etc to prevent cross site scripting $company_term = trim($_POST['company_term']); $type_term = trim($_POST['type_term']); $salary_term = trim($_POST['salary_term']); $hours_term = trim($_POST['hours_term']); //Make sure you connect to db before using mysql_real_escape_string $db_company = mysql_real_escape_string($company_term); $db_type = mysql_real_escape_string($type_term); $db_salary = mysql_real_escape_string($salary_term); $db_hours = mysql_real_escape_string($hours_term); //We build the search query based on the variables given from the search form $search_company = (!empty($company_term)) ? " OR company LIKE '%$db_company%'" : ''; $search_type = (!empty($type_term)) ? " OR Job LIKE '%$db_type%'" : ''; $search_salary = (!empty($salary_term)) ? " OR Salary LIKE '%$db_salary%'" : ''; $search_hours = (!empty($hours_term)) ? " OR Hours LIKE '%$db_hours%'" : ''; //If any of the search queries is used we then add it to the query if(!empty($search_company) || !empty($search_type) || !empty($search_salart) || !empty($search_hours)) { $query_search = "AND ($search_company $search_type $search_salary $search_hours)"; } //Here we put the query together and do a little search replace to make the query //a correct sql query $sql= "SELECT * FROM jobs WHERE id > 0 $query_search"; $sql = preg_replace('#\([\s]+OR#i','(',$sql); } PHP:
Thanks for your great input (green rep), I am yet to try it as I have been busy. I did play around with it a little but nothing major yet. Just to let you know there is one table, with 4 rows, not 4 tables Thanks!