I have a form which has select options for age and radiobuttons for gender. The idea is that the form can be used to search for a specific user by age and gender.
Currently, the form sometimes executes the header (see below) and sometimes not. So Assume, I am logged in as Conor, Conor specifies that he wants to search for a user aged between 20-21 and is male. Upon clicking submit, sometimes the form will find someone, sometimes it will not. I want the query to keep running until a user is found, unless no one exists in the database.
In this case, the header should be executed, taking the user to messages.php because a male aged 20 exists in the database.
Here is my approach:
Form:
<form action="random_chat.php" method="POST" enctype="multipart/form-data"> <input type="hidden" name="age_from" id="age_from" value="0"/> <input type="hidden" name="age_to" id="age_to" value="50"/> <label for="amount">Age:</label> from: <select name="age_from" id="age_a" onchange="checkages_a()"> <option value="none"></option> <?php for($i = 17; $i <= 50; ++$i) { echo "\t", '<option value="', $i. '">', $i, '</option>', "\n"; } ?> </select> to: <select name="age_to" id="age_b" onchange="checkages_b()"> <option value="none"></option> <?php for($i = 18; $i <= 50; ++$i) { echo "\t", '<option value="', $i, '">', $i, '</option>', "\n"; } ?> </select> <!-- I have input type submit above the radio buttons due to table layout --> <input type="submit" class="btn btn-info" name="submit" value="Click to start chat! " /> <label for="amount">Gender:</label> <input type="radio" name="gender" value="male">Male</input> <br /> <input type="radio" name="gender" value="female">Female</input><br /> <input type="radio" name="gender" value="any">Any</input> </form> PHP code processing the form:
<?php $refined_gender = htmlentities (strip_tags(@$_POST['gender'])); $age_from = htmlentities (strip_tags(@$_POST['age_from'])); $age_to = htmlentities (strip_tags(@$_POST['age_to'])); mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); if (isset($_POST['submit'])){ // if age parameter used... $defined_chat = mysqli_prepare ($connect, "SELECT * FROM users WHERE gender =? AND age BETWEEN ? AND ? ORDER BY RAND() LIMIT 1"); mysqli_stmt_bind_param($defined_chat, "sss", $refined_gender, $age_from, $age_to); mysqli_stmt_execute ($defined_chat); while ($get_user = mysqli_fetch_assoc($defined_chat)){ $rand_name = $get_user['username']; $acc_type = $get_user['account_type']; if ($acc_type != "admin" ){ // if the name genereated by db is same as logged in users name, then run query again until name is found. if ($rand_name == $username){ $defined_chat; } else { header ("Location: /messages.php?u=$rand_name"); } } else { echo "No user found fitting those requirements."; } } // while closed mysqli_stmt_close($defined_chat); } ?> I have tried to change the form action to '#', thinking it may be just be refreshing the page, but it didn't work.
Also, how can I make this so that even if one parameter is filled, then still execute search? For example, if I search for a male, with no age defined, it will find a male user. If I search for someone ages between 26-31 and no gender defined, then still execute header?
Edit:
$username is the session variable, which is defined at the very start of random_chat.php.
5 Answers
Answers 1
At first sight, what you are attending to do looks to me simpler than the way you are actually trying to achieve it.
Construction you SQL query correctly may be the only thing complicated in here. Only changing your query could actually already remove your need of the if/else for the account_type and the if/else to check if the current user is the same as the queried one :
$sql = "SELECT * FROM users WHERE gender like ? AND age BETWEEN ? AND ? AND # with this condition you do not need to test if the user logged is the queried one username != ? AND # and with this one, you do not care about exclude adimn either account_type != 'admin' ORDER BY RAND() LIMIT 1"; $defined_chat = mysqli_prepare ( $connect, $sql ); mysqli_stmt_bind_param( $defined_chat, "ssss", $refined_gender, $age_from, $age_to, $username ); Then about the fact that you want to be able to search even without any selection on both gender and age, you can use a combinaison of the wildcard % of SQL, the operator like and the ternary operator of PHP (you did maybe already see that I changed gender =? to gender like ? in the query above).
// Means if gender is different than 'any', it will assign the posted value to the variable, otherwise, it will assign the sql wildcard % $refined_gender = (htmlentities (strip_tags(@$_POST['gender'])) != 'any' ? htmlentities (strip_tags(@$_POST['gender'])) : '%'); // Means if age is different than 'none', it will assign the posted value to the variable, otherwise, it will assign the lowest possible age, 0 $age_from = (htmlentities (strip_tags(@$_POST['age_from'])) != 'none' ? htmlentities (strip_tags(@$_POST['age_from'])) : '0'); // Means if age is different than 'none', it will assign the posted value to the variable, otherwise, it will assign an age bigger than anyone could attain, 9999 $age_to = (htmlentities (strip_tags(@$_POST['age_to'])) != 'none' ? htmlentities (strip_tags(@$_POST['age_to'])) : '9999'); see ternary operators in PHP doc and see MySQL like and wildcard usage
All in one, your processing PHP script could look like this :
$refined_gender = (htmlentities (strip_tags(@$_POST['gender'])) != 'any' ? htmlentities (strip_tags(@$_POST['gender'])) : '%'); $age_from = (htmlentities (strip_tags(@$_POST['age_from'])) != 'none' ? htmlentities (strip_tags(@$_POST['age_from'])) : '0'); $age_to = (htmlentities (strip_tags(@$_POST['age_to'])) != 'none' ? htmlentities (strip_tags(@$_POST['age_to'])) : '9999'); mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); if (isset($_POST['submit'])){ $sql = "SELECT * FROM users WHERE gender like ? AND age BETWEEN ? AND ? AND # with this condition you do not need to test if the user logged is the queried one username != ? AND # and with this one, you do not care about exclude adimn either account_type != 'admin' ORDER BY RAND() LIMIT 1"; $defined_chat = mysqli_prepare ( $connect, $sql ); mysqli_stmt_bind_param( $defined_chat, "ssss", $refined_gender, $age_from, $age_to, $username ); mysqli_stmt_execute ($defined_chat); while ($get_user = mysqli_fetch_assoc($defined_chat)){ $rand_name = $get_user['username']; header ("Location: /messages.php?u=$rand_name"); } // while closed echo "No user found fitting those requirements."; mysqli_stmt_close($defined_chat); } Answers 2
You have some mixed logic, so some explanations might help.
1) header('location: ...') will tell the browser to reload the page to the new location. This does not appear to be what you want - you just want to continue execution? NOTE: You should also [nearly] always have "exit();" after a header('location: ... '); line otherwise execution continues which is [nearly] never what you want!)
2) a while loop will continue "while" the condition is true. So the loop continues while there are rows being returned.
3) Running the query again will not return anything new - you can use the same results. So just skip over until you find the result you need!
So, written in English, what you want to do after running the DB query is:
set a tally count to zero while we have some rows coming from the db { if that row is not admin { if that row does not match the current user { show the result increase tally count } } } if tally count is zero { say "no entries found" } So, in code, this is
$foundUsers = 0; while ($get_user = mysqli_fetch_assoc($defined_chat)){ $rand_name = $get_user['username']; $acc_type = $get_user['account_type']; if ($acc_type !== "admin" ){ // if the name genereated by db is same as logged in users name, then run query again until name is found. if ($rand_name !== $username) { $foundUsers = $foundUsers + 1; // Or $foundUsers++ for short echo 'Matched User: ' . $rand_name . '<br>'; } } } // while closed if ($foundUsers == 0) { echo "No user found fitting those requirements."; } Answers 3
Ok, first of all, if you want to exclude a parameter from the query, you're going to have to build some logic to exclude that variable.
So if $refined_gender = "any", then you need to exclude it from the query. I would change your combobox default values to:
<select name="age_from" id="age_a" onchange="checkages_a()"> <option value="-1"></option> <?php for($i = 17; $i <= 50; ++$i) { echo "\t", '<option value="', $i. '">', $i, '</option>', "\n"; } ?> </select> to: <select name="age_to" id="age_b" onchange="checkages_b()"> <option value="999"></option> <?php for($i = 18; $i <= 50; ++$i) { echo "\t", '<option value="', $i, '">', $i, '</option>', "\n"; } ?> </select> Then, now you've fixed the age between, to filter the gender out. Also, I've added a clause to your WHERE clause: AND account_type != 'admin', this will filter out the admins accounts on the SQL side rather than checking on the PHP side.
// If gender is specified, query gender if($refined_gender !== "any"){ $defined_chat = mysqli_prepare ($connect, "SELECT * FROM users WHERE gender =? AND age BETWEEN ? AND ? AND account_type != 'admin' ORDER BY RAND() LIMIT 1"); mysqli_stmt_bind_param($defined_chat, "sii", $refined_gender, $age_from, $age_to); } else { $defined_chat = mysqli_prepare ($connect, "SELECT * FROM users WHERE age BETWEEN ? AND ? AND account_type != 'admin' ORDER BY RAND() LIMIT 1"); mysqli_stmt_bind_param($defined_chat, "ii", $age_from, $age_to); } mysqli_stmt_execute ($defined_chat); Answers 4
Suggestion #1: Possible race condition see note in code.
if ($acc_type != "admin" ){ // if the name genereated by db is same as logged in users name, then run query again until name is found. if ($rand_name == $username){ $defined_chat;<-- don't you need to re-execute this? Seems like you are hitting a race condition since the statement result will never change } else { header ("Location: /messages.php?u=$rand_name"); } } else { echo "No user found fitting those requirements."; } Suggestion #2:
Outside of that you should be sure you aren't getting the current user with a WHERE name NOT LIKE '%?%' up front in the initial query and get rid of that if statement.
Suggestion #3:
Or better, use the user IDs. What if there's another user with the same name as the searcher, but they're a different person? Base the current user match on UID, not name.
Suggestion #4:
You should absolutely almost never run a select query/statment inside a PHP (or any scripting language) loop. There's always a better way. Filter your data in the database where it's efficient. Even for inserts you can do a single bulk insert much more efficiently than a bunch of insert queries.
Answers 5
Do not rely on the value of a submit button to determine if your form was submitted or not. This will not work on all browsers, especially older ones, this value is not always passed back to the server, instead just look at any value inside the form to verify if submission has occurred, or the existence of $_POST in general.
0 comments:
Post a Comment