Insert string before a certain word within another string

Question

I'm combining my MySQL queries so the user can select multiple dropdowns and filter based on those dropdowns. This works as intended as I've checked using $echo query. However, I need to insert an "AND " before each occurence of the word "WHERE" except for the first one. At the moment my query comes out like this if I select multiple drodowns:

SELECT a.MembershipID,
       a.FirstName,
       a.Surname,
       t.RaceID,
       t.Time,
       r.RaceID,
       r.RaceName,
       r.Distance,
       r.ClubYear
FROM Athlete AS a
INNER JOIN Time AS t ON a.MembershipID=t.MembershipID
INNER JOIN Race AS r ON t.RaceID=r.RaceID
WHERE r.RaceName= 'Parkrun' WHERE r.Distance= '5 kilometres'
ORDER BY a.Surname

As you can see on line 4, there are two WHERE queries with no AND. How can I insert an AND before the second WHERE? How can I do this for every subsequent WHERE except the first? I thought about using explode to create substrings, insert the AND and then join them back together, but this seems a little over the top. Any help? Thanks.

Edit: Add PHP code that generates the above:

$query = "SELECT a.MembershipID, a.FirstName, a.Surname, t.RaceID, t.Time, r.RaceID, r.RaceName, r.Distance, r.ClubYear
                            FROM Athlete AS a 
                            INNER JOIN Time AS t  
                                ON a.MembershipID=t.MembershipID
                            INNER JOIN Race AS r
                                ON t.RaceID=r.RaceID";
            if ($_POST['raceName']!= null) {
                $raceName = mysqli_real_escape_string($mysqli, $_POST["raceName"]);
                $filter = " WHERE r.RaceName= '$raceName'";
                $query .= $filter;
            } 
            if ($_POST['athlete']!= null) {
                $athlete = mysqli_real_escape_string($mysqli, $_POST["athlete"]);
                $firstWord = explode(' ',trim($athlete));
                $firstName = $firstWord[0];
                $lastWord = explode(' ', $athlete);
                $surname = array_pop($lastWord);
                $filter = " WHERE a.FirstName= '$firstName' AND a.Surname = '$surname'";
                $query .= $filter;
            } 
            if ($_POST['distance']!= null) {
                $distance = mysqli_real_escape_string($mysqli, $_POST["distance"]);
                $filter = " WHERE r.Distance= '$distance'";
                $query .= $filter;
            } 
            if ($_POST['clubYear']!= null) {
                $clubYear = mysqli_real_escape_string($mysqli, $_POST["clubYear"]);
                $filter = " WHERE r.ClubYear= '$clubYear'";
                $query .= $filter;
            }

            $fullquery = $query . " ORDER BY a.Surname";

Please give the PHP code that generates this query since the problem is there. You'll also need to replace the second WHERE with AND and not append the END before it, as that would create an AND WHERE which is not valid — Kypros
– Kypros, Commented Oct 25, 2014 at 12:11
Added the PHP that generates it. And also good point, forgot about that! — Adam Short
– Adam Short, Commented Oct 25, 2014 at 12:19

Kypros · Accepted Answer · 2014-10-25 12:36:52Z

1

A simple solution would be to first add an always true statement like 1=1 so you don't have to check if it's the first statement or not, and then keep building you query by appending AND CONDITION_N accordingly

$query = "SELECT a.MembershipID, a.FirstName, a.Surname, t.RaceID, t.Time, r.RaceID, r.RaceName, r.Distance, r.ClubYear
                            FROM Athlete AS a 
                            INNER JOIN Time AS t  
                                ON a.MembershipID=t.MembershipID
                            INNER JOIN Race AS r
                                ON t.RaceID=r.RaceID";

$query .= " WHERE 1 = 1 ";
if ($_POST['raceName']!= null) {
    $raceName = mysqli_real_escape_string($mysqli, $_POST["raceName"]);
    $filter = " AND r.RaceName= '$raceName'";
    $query .= $filter;
}

Explanation:

If none or your if conditions are true, your query would be whatever your initial query was followed WHERE 1=1 which is exactly the same as not having it in the first place
If any of your conditions are true then it would be:

INITIAL_QUERY WHERE 1=1 AND CONDITION_N
For multiple conditions being true it would be:

INITIAL_QUERY WHERE 1=1 AND CONDITION_N AND CONDITION_N+1

This makes you PHP code cleaner and faster since you don't have to check about whether it is the first condition in order to decide whether to use WHERE or AND when appending to your select statement

edited Oct 25, 2014 at 12:36

answered Oct 25, 2014 at 12:26

Kypros

2,9865 gold badges23 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Adam Short Over a year ago

How does this work for the first statement though? Where does the initial WHERE get added?

Kypros Over a year ago

Updated the answer with an explanation for this.

Belzelga · Accepted Answer · 2014-10-25 12:12:28Z

0

Maybe you could make something like

WHERE id > 0

before the

WHERE r.RaceName= 'Parkrun' WHERE r.Distance= '5 kilometres'

you'll have something like

WHERE id > 0 AND r.RaceName= 'Parkrun' AND r.Distance= '5 kilometres'

answered Oct 25, 2014 at 12:12

Belzelga

1531 gold badge5 silver badges16 bronze badges

Collectives™ on Stack Overflow

Insert string before a certain word within another string

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related