MySQL Forums
Forum List  »  PHP

How do you dynamically add additional variables to a PDO SQL query?
Posted by: Simon Lewis
Date: November 13, 2019 04:31AM

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.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.