Search for the results that only hold the submitted search inputs values

Question

Hi there looking for a little help if possible with a search script.

I have a search function that retrieves data from a database about vehicles using three types of search input with one submit button.

Input 1. An options list search (searcha)
Input 2. Text search (searchb)
Input 3. 2 Radio buttons selection search (either button selected or both)
Plus a submit button

I have the below PHP code which runs when I submit the search. It does show the result but only it matches the first correct result from the database then shows that. What I would like it to do is to search for the results that only hold the submitted search inputs values.

For example, if I say chose an option from Input 1 & a radio button from Input 3 the search would find only row(s) that hold both those values if that makes sense.

<?
// Vehicles
if (in_array($_POST['searcha'], $search_a_option_list)) {
    $stmt = $pdo->prepare('SELECT * FROM coun WHERE inth = ?');
    $stmt->execute([ $_POST['searcha'] ]);
    $search_results = $stmt->fetchAll(PDO::FETCH_ASSOC);
} else if ($_POST['searchb']) {
    $stmt = $pdo->prepare('SELECT * FROM coun WHERE bunty LIKE ?');
    $stmt->execute([ '%' . $_POST['searchb'] . '%' ]);
    $search_results = $stmt->fetchAll(PDO::FETCH_ASSOC);
} else if (isset($_POST['check1'], $_POST['check2'])) {
    $stmt = $pdo->prepare('SELECT * FROM coun WHERE ref = ? OR ref1 = ?');
    $stmt->execute([ 'In', 'On' ]);
    $search_results = $stmt->fetchAll(PDO::FETCH_ASSOC);
} else if (isset($_POST['check1'])) {
    $stmt = $pdo->prepare('SELECT * FROM coun WHERE ref = ?');
    $stmt->execute([ 'In' ]);
    $search_results = $stmt->fetchAll(PDO::FETCH_ASSOC);
} else if (isset($_POST['check2'])) {
    $stmt = $pdo->prepare('SELECT * FROM coun WHERE ref1 = ?');
    $stmt->execute([ 'On' ]);
    $search_results = $stmt->fetchAll(PDO::FETCH_ASSOC);
}  else {
    $error = 'No options selected!';
}
?>

Is there a simple way to adjust this script to reflect the inputted searches? Just starting to learn PHP so a little nudge in the right direction would be perfect.

Regards

Rather than a series of totally independent queries, you need a process where you examine each POST value in turn and gradually build a sql string (and parameter array) with relevant clauses as needed depending on the inputs provided, then execute the finalised query at the end — ADyson, Jul 17 '23 at 22:20

score 0 · Answer 1 · answered Jul 18 '23 at 03:58

0

First, I don't get how it can be possible to select both of radio buttons, do you mean checkboxes? About your question - anyway you should execute one query, but build it depending on your $_POST values.

It can be something like

// make an empty array to hold all the conditions
$whereValues = [];

// populate the array with conditions
if (in_array($_POST['searcha'], $search_a_option_list)) {
    $whereValues[] = "inth = '$_POST[searcha]'";
} else if ($_POST['searchb']) {
    $whereValues[] = "bunty LIKE '%$_POST[searchb]%'"
} else if (isset($_POST['radio1'], $_POST['radio2'])) {
    $whereValues[] = "ref = 'In' OR ref1 = 'On'"
} else if (isset($_POST['radio1'])) {
    $whereValues[] = "ref = 'In'"
} else if (isset($_POST['radio2'])) {
    $whereValues[] = "ref1 = 'On'"
} 

// if we have something then build the result string of `WHERE` conditions
if ($whereValues) {
    $where = implode(' AND ', $whereValues);
    $stmt = $pdo->prepare("SELECT * FROM coun WHERE $where");
    $stmt->execute();
    $search_results = $stmt->fetchAll(PDO::FETCH_ASSOC);
} else {
    $error = 'No options selected!';
}

answered Jul 18 '23 at 03:58

Lothric

1,283
2
6
11

Downvoted due to complete lack of SQL injection or syntax error protection. The query needs to be parameterised wherever PHP variables are being used in the SQL. See also [How to include a PHP variable inside a MySQL statement](https://stackoverflow.com/questions/7537377/how-to-include-a-php-variable-inside-a-mysql-statement) – ADyson Jul 18 '23 at 07:58
`how it can be possible to select both of radio buttons`....presumably they are in different groups, seeing as they have different names (and hence different parameter names in $_POST) – ADyson Jul 18 '23 at 08:00
Radio1 & Radio2 are checkboxes. Think I should rename them ‍♂️ – Me WD Jul 18 '23 at 09:03
@MeWD good idea. P.S. This answer is on the right lines with the general approach, but completely the wrong lines with regards to security and reliability of the SQL code. So it gets you halfway there, probably. – ADyson Jul 18 '23 at 09:07
I never realised how tricky a search function is to build. – Me WD Jul 18 '23 at 09:21
1

Changed radio1 to check1 and radio2 to check2 – Me WD Jul 18 '23 at 20:27

Search for the results that only hold the submitted search inputs values

1 Answers1