I'm new in this field so please don't be harsh on me. It's looks simple yet I can't figured it out. So, I have dropdown select, like:
<select name="mode" id="mode">
<option value="None">All</option>
<option value="300-400">Inside</option>
<option value="200-299">Outside</option>
</select>
Then I currently have this
$cMode=($mode == "None") ? '"%"' : $mode;
SELECT * FROM sections WHERE code LIKE '.$cMode.'
How can I change the query so if I select Inside
to search between values 300 and 400?
You can use if-else
with explode()
and list()
if($mode == "None"){
$cMode = '"%"';
SELECT * FROM sections WHERE code LIKE '.$cMode.'
}else{
list($start,$eand) = explode('-',$mode);
SELECT * FROM sections WHERE code BETWEEN $start AND $end
}
As you asked in the comment:-
$cMode ='';
if($mode == "None"){
$cMode = 'LIKE "%"';
}else{
list($start,$eand) = explode('-',$mode);
$cMode ="BETWEEN $start AND $end";
}
$query = "SELECT * FROM sections WHERE code $cMode";
You can make $cMode
dynamically by using if and else
condition as:
$cMode='';
if($mode == "None"){
$cMode = 'LIKE "%"';
}else{
$arr = explode('-',$mode);
$cMode ="BETWEEN $arr[0] AND $arr[1]";
}
$query = "SELECT * FROM sections WHERE code $cMode";
For more details SQL BETWEEN Operator
I haven't quite understood what you were asking for, this is an example of how I print out data from rows with filters
<form action="PAGE.php" method="POST" id="users_selection">
<select name="mode" id="mode">
<option value="None">All</option>
<option value="300-400">Inside</option>
<option value="200-299">Outside</option>
</select>
<input type="submit" value="choose" name="choosing_user">
</form>
<?php
$servername = "";
$susername = "";
$spassword = "";
$dbname = "";
$conn = mysqli_connect($servername, $susername, $spassword, $dbname);
$mode = $_POST['mode'];
$sql = "SELECT * FROM sections";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
$MYSQL_data = $row['data'];
if($mode=="None"){
echo '<p>'.$MYSQL_data.'</p>';
}else if($mode=="300-400"){
if($MYSQL_data>299 && $MYSQL_data <401){
echo '<p>'.$MYSQL_data.'</p>';
}
}else if($mode=="200-299"){
if($MYSQL_data>199 && $MYSQL_data <300){
echo '<p>'.$MYSQL_data.'</p>';
}
}
}
}
?>