How to shrink mysql statement

[bphein1980]

I hope someone can help me shrink this statement.... the query takes the choice from the 3 drop down boxes. I'm not really looking for someone to write the statement for me, but at least point me to an article that will help me make it more compact. I know there HAS to be a better way of doing this. Thanks for any help that is provided!

<form action="index.php?p=records&s=sort" method="post">
<select name="sort_sex">
<option value="both" <?php if($sort_sex == both) echo "selected"; ?>>Men and Women
<option value="male" <?php if($sort_sex == male) echo "selected"; ?>>Men
<option value="female" <?php if($sort_sex == female) echo "selected"; ?>>Women
</select>
<select name="sort_division">
<option value="all_div" <?php if($sort_division == all_div) echo "selected"; ?>>All Divisions
<option value="open" <?php if($sort_division == open) echo "selected"; ?>>Open
<option value="teen" <?php if($sort_division == teen) echo "selected"; ?>>Teen
<option value="junior" <?php if($sort_division == junior) echo "selected"; ?>>Junior
<option value="sub-master" <?php if($sort_division == 'sub-master') echo "selected"; ?>>Sub-Master
<option value="master 40-49" <?php if($sort_division == 'master 40-49') echo "selected"; ?>>Master 40-49
<option value="master 50+" <?php if($sort_division == 'master 50+') echo "selected"; ?>>Master 50+
</select>
<select name="sort_lift">
<option value="all" <?php if($sort_lift == all) echo "selected"; ?>>All Records
<option value="bench only" <?php if($sort_lift == 'bench only') echo "selected"; ?>>Bench Only
<option value="squat" <?php if($sort_lift == squat) echo "selected"; ?>>Squat
<option value="bench" <?php if($sort_lift == bench) echo "selected"; ?>>Bench
<option value="deadlift" <?php if($sort_lift == deadlift) echo "selected"; ?>>Deadlift
<option value="total" <?php if($sort_lift == total) echo "selected"; ?>>Total
</select>
<input type="submit" name="record_sort" value="Go" />
</form>


<?php
if(isset($_GET['s']))
{
$s = $_GET['s'];
if ($s == sort)
{
?>
<tr class="record_table_head">
<th>Name</th>
<th>Sex</th>
<th>Weight Class (kg)</th>
<th>Division</th>
<th>Lift</th>
<th>Kilos</th>
<th>Pounds</th>
<th>Date</th>
</tr>
<?php
if ($sort_sex == both && $sort_lift == all && $sort_division == all_div){
$query = "SELECT * FROM records ORDER BY sex DESC, weight+0, lift ASC";
}
elseif ($sort_sex == both && $sort_division == all_div){
$query = "SELECT * FROM records WHERE lift='$sort_lift' ORDER BY sex DESC, weight+0, lift ASC";
}
elseif ($sort_sex == both && $sort_lift == all){
$query = "SELECT * FROM records WHERE division='$sort_division' ORDER BY sex DESC, weight+0, lift ASC";
}
elseif ($sort_sex == both){
$query = "SELECT * FROM records WHERE lift='$sort_lift' && division='$sort_division' ORDER BY sex DESC, weight+0, lift ASC";
}
elseif ($sort_lift == all && $sort_division == all_div){
$query = "SELECT * FROM records WHERE sex='$sort_sex' ORDER BY sex DESC, weight+0, lift ASC";
}
elseif ($sort_lift == all){
$query = "SELECT * FROM records WHERE sex='$sort_sex' && division='$sort_division' ORDER BY sex DESC, weight+0, lift ASC";
}
elseif ($sort_division == all_div && $sort_sex == both){
$query = "SELECT * FROM records WHERE lift='$sort_lift' ORDER BY sex DESC, weight+0, lift ASC";
}
elseif ($sort_division == all_div){
$query = "SELECT * FROM records WHERE sex='$sort_sex' && lift='$sort_lift' ORDER BY sex DESC, weight+0, lift ASC";
}
else{
$query = "SELECT * FROM records WHERE sex='$sort_sex' && lift='$sort_lift' && division='$sort_division' ORDER BY sex DESC, weight+0, lift ASC";
}

[Beagle]

It's a bit long for me to read atm, but what I would suggest is setting each section of the query independently and then cooncatenating the pieces:


$where = 'WHERE 1 ';
$orderby = 'ORDER BY ';

if ($sex_sort == 'both')
{
$orderby .= 'sex DESC, ';
}
else
{
$where .= 'AND sex=\''.$sex_sort.'\' ';
}

$orderby = rtrim($orderby, ',');

$query = "SELECT * FROM whatever $where $orderby";


Hope that helps you in the right direction.

[guelphdad]

I wouldn't worry about it. The select statement itself is compact, you are merely stepping through a list of IF statements to see which single select to run.

It isn't like you are going to be typing it out again and again now is it?

And there are no where conditions within the select so you aren't going to speed up the select query itself with indexes as you are selecting all items from all columns.

[Fumigator]

Cold Water.

<ducks and runs>

[bphein1980]

Thank you for the replies. I was just considering adding a 4th drop menu and the if/else statements would just get to be too many for my liking.

I do appreciate the replies.

Cold Water.

<ducks and runs>
What does that mean? For some reason it made me laugh though. I just dont understand it.


Beagle, if you don't mind, can you expand a bit more on the code you threw out there. I was looking at it and am a bit confused on what exactly is going on.

[bphein1980]

adding a 4th drop menu seems like it would make it alot more complex. Is there any easier way to do this?

[Beagle]

Fumigator was making a genitalia joke...

Anyway, what I was saying was that if you want to reduce the amount of code you're writing, you need to break up your logic.

Each drop down adds something specific to query based on it's value. So for example, if you say select ALL for sex, then you don't need to have a where clause with the sex column in it, but if you choose to only select 1 sex, value = "male" say, then you are only adding a part to your where clause. And this is true of all our drop downs.

So, instead of trying to explain how everything is wired up from beginning to end, just look at it from the logical perspective:

Let's say you have 4 variables in your script and four columns in database, same names: color, volume, mass, and price

Take it one at a time:


if (trim($color) != '') // *** If $color has some value in it ***
{
$colorWhereClausePart = "color = '$color'";
}
else
{
$colorWhereClausePart = '';
}


So let's assume you did that for each of the 4 variables. Now you can build your where clause:

$whereClause = "WHERE $colorWhereClausePart $volumeWhereClausePart ...."

So that would make things a lot cleaner right away.

If that makes sense, let me know. I actually made it more complicated by being really generic in the way that I did things, but the way I just described is perfectly functional.

Hope that helps.

[bphein1980]

Thank you. The logic makes sense to me, but being pretty new to everything, I'll see if I can complete it.

Thanks for the help. I appreciate it!

[bphein1980]

Thank you for the help! This is what I ended up with, and it works:


$where = 'WHERE 1 ';
$orderby = 'ORDER BY sex DESC, weight+0, lift ASC';

if($sort_sex == 'both'){}
else{
$where .= 'AND sex=\''.$sort_sex.'\' ';
}
if($sort_division == 'all_div'){}
else{
$where .= 'AND division=\''.$sort_division.'\' ';
}
if($sort_lift == 'all'){}
else{
$where .= 'AND lift=\''.$sort_lift.'\' ';
}

I removed the orderby part because I am always going to be ordering the same way no matter what the choices are. I am confident that adding a 4th drop menu will be alot easier now :)