Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    Regular Coder
    Join Date
    Feb 2005
    Posts
    136
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to shrink mysql statement

    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!

    PHP Code:
    <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";
            }

  • #2
    Senior Coder
    Join Date
    Jul 2005
    Location
    New York, NY
    Posts
    1,084
    Thanks
    4
    Thanked 19 Times in 19 Posts
    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:

    Code:
    $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.

  • #3
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.

  • #4
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Cold Water.

    <ducks and runs>

  • #5
    Regular Coder
    Join Date
    Feb 2005
    Posts
    136
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Last edited by bphein1980; 07-27-2006 at 03:57 AM.

  • #6
    Regular Coder
    Join Date
    Feb 2005
    Posts
    136
    Thanks
    0
    Thanked 0 Times in 0 Posts
    adding a 4th drop menu seems like it would make it alot more complex. Is there any easier way to do this?

  • #7
    Senior Coder
    Join Date
    Jul 2005
    Location
    New York, NY
    Posts
    1,084
    Thanks
    4
    Thanked 19 Times in 19 Posts
    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:

    Code:
    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.

  • #8
    Regular Coder
    Join Date
    Feb 2005
    Posts
    136
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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!

  • #9
    Regular Coder
    Join Date
    Feb 2005
    Posts
    136
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you for the help! This is what I ended up with, and it works:

    PHP Code:
    $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


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •