Nested If Statements

PC5164 · July 13, 2020, 12:25am

I am trying to put a searchable database up that uses several terms to search for a song. These are $stype - type of search (Title only or Title and words), $skind - Kind of song - Hymn, Chorus etc $stempo - fast, slow etc. I need the database to be searched if they have a word to search on ($srch) or not. I am having problems with using nested if’s to account for all the different combinations. The problem is I’m not sure of the syntax with ifs. I would appreciate any help or if you have a different way it can be done. The offending code is below

$srch = $_POST['srch'];
$stype = $_POST['stype'];
$skind = $_POST['skind'];
$stempo = $_POST['stempo'];
if($srch == '') {$q ="SELECT * FROM songs ";
    if ($skind !== 'All' AND $stempo =='All'){$q = $q."WHERE KIND = '$skind'";}
    elseif ($skind == 'All' AND $stempo !=='All'){$q = $q."WHERE TEMPO = '$stempo'";}
    elseif ($skind !== 'All' AND $stempo !=='All'){$q = $q."WHERE TEMPO = '$stempo' AND KIND = '$skind'";}

elseif ($stype == 'Title')  {$q ="SELECT * FROM songs WHERE title LIKE '%$srch%'";} else  {$q ="SELECT * FROM songs WHERE words LIKE '%$srch%' OR title LIKE '%$srch%'";}
    if ($SKIND !== 'All' AND $stempo =='All'){$q = $q."and KIND = '$skind'";}
    elseif ($SKIND == 'All' AND $stempo !=='All'){$q = $q."and TEMPO = '$stempo'";}
    elseif ($SKIND !== 'All' AND $stempo !=='All'){$q = $q."and TEMPO = '$stempo' AND KIND = '$skind'";}
}
$q = $q." ORDER BY Title";
print $q;

phdr · July 13, 2020, 1:39am

First, you should be using $_GET inputs to determine what will be displayed on a page.

Next, don’t put external, unknown, dynamic values directly into the sql query statement. You should use a prepared query to safely supply the input values to the query when it gets executed.

Don’t write out code for every combination. Edit: [Also, Don’t Repeat Yourself (DRY.) Each part of what you are doing should only exist once.] Instead, dynamically build the term(s) that exist, then combine them to produce the sql query. See the following example code -

<?php

// inputs -
// srch - search term - '' (all) or entered value
// stype - type - Title else title OR words
// skind - kind - All or entered value
// stempo - tempo - All or entered value

// array to hold WHERE terms, to be AND'ed together
$where_terms = [];
// array to hold search terms, to be OR'ed together
$search_terms = [];
// array to hold prepared query input parameters
$params = [];

// trim all input values
$get = array_map('trim',$_GET);

// dynamically build each WHERE term

// search term
if($get['srch'])
{
	// there is always a title term
	$search_terms[] = 'title LIKE ?';
	$params[] = "%{$get['srch']}%";

	if($get['stype'] != 'Title')
	{
		// add the words term
		$search_terms[] = 'words LIKE ?';
		$params[] = "%{$get['srch']}%";
	}
	$where_terms[] = '(' . implode(' OR ', $search_terms) . ')';
}

// kind
if($get['skind'] && $get['skind'] != 'All')
{
	$where_terms[] = 'KIND = ?';
	$params[] = $get['skind'];
}

// tempo
if($get['stempo'] && $get['stempo'] != 'All')
{
	$where_terms[] = 'TEMPO = ?';
	$params[] = $get['stempo'];
}

$where = '';
if($where_terms)
{
	$where = 'WHERE ' . implode(' AND ', $where_terms);
}

// build the sql query

// note: you should list the columns you are selecting
$sql = "SELECT * FROM songs $where ORDER BY Title";

// examine the result
echo $sql;
echo '<pre>'; print_r($params); echo '</pre>';