Php select * where

Question

Hi, I am trying to get a search that works on a site. It has 2 inputs for entering information, one is a drop-down list.

<div id="search"> <form action="projectsearchall.php" method="post" enctype="multipart/form-data"> <h3>Search for an Item</h3> <p>Keywords</p><p><input name="keywords" type="text" value="keywords"></p> <p>Select A Location</p><p> <select name="location" id="jumpMenu"> <option>Any Location</option> <option>Antrim</option> <option>Armagh</option> <option>Carlow</option> <option>Cavan</option> </select> </p> <p> 

 </form> </div> 

I can’t understand how to combine 2 inputs to give a result, I can do it separately, but I don’t work together to get a more accurate result.

Php

 $keywords = $_POST['keywords']; $keylocation =$_POST['location']; $username = $_SESSION['username']; //MySQL Database Connect include 'connect.php'; //make sql query $result = mysqli_query($con,"SELECT * FROM projectitem where description like '%$keywords%' or item like '%$keywords%' or location like '%$keywords%'"); 

Thanks in advance!

Answer 1

I think you can do some preprocessing before you run your query.

First, you need to specify your selections to check them.

I do not know your exact database structure, but assuming you are working with selected texts, you can try the following:

 $query = "SELECT * FROM projectitem WHERE (description LIKE '%$keywords%' OR item LIKE '%$keywords%')"; 

This is your basic request and starting it right now will be checked for keywords, but not for location.

 if($keylocation != "Any location") $query .= " AND location = '$keylocation'"; 

This last line will add the location as an additional filter to your query. Run it and see what it does. (I'm not sure about string comparison there)

Oh yes, as a final mysqli_escape_string : be sure to run your input through the mysqli_escape_string exit mysqli_escape_string . Otherwise, you discover SQL injections.

Answer 2

In fact, you are not using the value of $keylocation ; to narrow your search down you need AND instead of OR :

 $stmt = mysqli_prepare($con, 'SELECT * FROM projectitem where (description LIKE ? OR item LIKE ?) AND location LIKE ?'); mysqli_stmt_bind_param($stmt, 'sss', "%$keywords%", "%$keywords%", "%$keylocation%"); mysqli_stmt_execute($stmt); // etc. 

Update

Since there may be “any location” in the drop-down list, you will need to dynamically change your request:

 $sql = 'SELECT * FROM projectitem WHERE 1'; // base query $types = ''; $vars = array(); if (!empty($keywords)) { $sql .= ' AND (description LIKE ? OR item LIKE ?)'; $types .= 'ss'; $vars[] = "%$keywords%"; $vars[] = "%$keywords%"; } if ($keylocation != 'Any Location') { $sql .= ' AND location LIKE ?'; $types .= 's'; $vars[] = $keylocation; } $stmt = mysqli_prepare($con, $sql); if ($types) { mysqli_stmt_bind_param($stmt, $types, $vars); } mysqli_stmt_execute($stmt); 

Answer 3

you have SQL injection first

use mysqli_real_escape_string

if the keywords, for example, are zero, your query will look like this

 $result = mysqli_query($con,"SELECT * FROM projectitem where description like '%%' or item like '%%' or location like '%$keylocation%'"); 

and description like '%%' return the entire string!

you must first check the data

 $query = "SELECT * FROM projectitem where 1=1 " if($keywords) $query .= " AND ( description like '%$keywords%' AND item like '%$keywords%' )"; if($keylocation) $query .= " AND location like '%$keylocation%'";