How do you dynamically add additional variables to a PDO SQL query?
We are building a bespoke PHP website, using MySQL DB.
It will have a list of people on it, called 'Infuencers'. They will have added their town, DOB, favourite interests and so on.
The 'admins' need to be able to filter on these database columns, so for example:
Where Town = London, DOB age range 25-35, interests = Shopping.
I can do queries of course, but how do you dynamically add to a query? Bearing in mind you have to add it in the main 'SELECT * FROM...', plus the 'execute' params.
I've tried this, following a suggestion from someone, but it failed.
I have this at the top to put the filter variable into a sessions:
$filtertown = isset($_POST['town']) ? $_POST['town'] : null; if(isset($_POST['town']))
{
$_SESSION['filtertown'] = $filtertown;
}
elseif (isset($_SESSION['filtertown']))
{ $filtertown = $_SESSION['filtertown']; }
And this further down: ` $params = array(); $sqlStart = "SELECT * FROM users WHERE usertype = 'influencer'"; $sqlEnd = " "; $sqlFilters = "";
if(isset($filtertown)) { $sqlFilters .= " AND town =:town"; $params["filtertown"] = $filtertown; } $sql = $sqlStart.$sqlFilters.$sqlEnd; $result = $pdo->prepare($sql); $result->execute($params); ` But while it is not throwing an error, it using filtering it either. Is the $params ok?
Made a mistake in the top bit: $filtertown = isset($_POST['filtertown']) ? $_POST['filtertown'] : null; if(isset($_POST['filtertown'])) { $_SESSION['filtertown'] = $filtertown; } elseif (isset($_SESSION['filtertown'])) { $filtertown = $_SESSION['filtertown']; }
$params = array(); $sqlStart = "SELECT * FROM users WHERE usertype = 'influencer'"; $sqlEnd = " "; $sqlFilters = ""; if(isset($filtertown)) { $sqlFilters .= " AND town =:town"; $params["filtertown"] = $filtertown; } $sql = $sqlStart.$sqlFilters.$sqlEnd; $result = $pdo->prepare($sql); $result->execute($params); echo "$filtertown"; This renders "test2" on screen correctly (value of $filtertown), but also warns this: Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: parameter was not defined in C:\........\.....inc on line 75
Line 75 is: $result = $pdo->prepare($sql); $result->execute($params);
I don't know where I am going wrong.
Happy to start over, but it just needs to be able to add the variables in the two places I think, but I've never done such a thing.