SitePoint Sponsor

User Tag List

Results 1 to 20 of 20
  1. #1
    SitePoint Member
    Join Date
    Jun 2012
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    PHP MYSQL WHERE Clause

    Hi, I've got a problem with using a WHERE clause in my code. Bear with me as I'm new to php. Here it is:

    PHP Code:
    <body>
    <?php
    $username
    ="USERNAME";
    $password="PASSWORD";
    $database="DATABASE";

    mysql_connect(localhost,$username,$password);
    @
    mysql_select_db($database) or die( "Unable to select database");
    $query="SELECT * FROM searchacts";
    $result=mysql_query($query);

    // process form when posted 
    if(isset($_POST['value'])) { 
        if(
    $_POST['value'] == 'PriceLow') { 
           
            
    $query "SELECT * FROM searchacts ORDER BY price ASC";   
        }   
        elseif(
    $_POST['value'] == 'PriceHigh') {   
            
            
    $query "SELECT * FROM searchacts ORDER BY price DESC";   
        }
        elseif(
    $_POST['value'] == 'NameAZ') {   
            
            
    $query "SELECT * FROM searchacts ORDER BY name ASC";   
        } 
        
        elseif(
    $_POST['value'] == 'NameZA') {   
            
            
    $query "SELECT * FROM searchacts ORDER BY name DESC";   
        }
        
        elseif(
    $_POST['value'] == 'partybands') {   
            
            
    $query "SELECT * FROM searchacts
    WHERE category='Party Bands'"
    ;   
        }
        
        else {   
            
            
    $query "SELECT * FROM searchacts";   
        }   
        
        
    $result mysql_query($query); 

        } 
        
    $result mysql_query("SELECT * FROM searchacts
    WHERE category='Party Bands'"
    );
        
        
    $num=mysql_numrows($result);

    ?>


    <form action='<?php echo $_SERVER['PHP_SELF']; ?>' method='post' name='form_filter' >  
        <select name="value">  
            <option value="all">All</option>  
            <option value="PriceLow">Price (Low to High)</option>  
            <option value="PriceHigh">Price (High to Low)</option>  
            <option value="NameAZ">Name (A-Z)</option>  
            <option value="NameZA">Name (Z-A)</option> 
        </select>  
        <br />  
        <input type='submit' value = 'Re-Order'>  
    </form>

    <a href="partybands">Party Bands</a>



    <?php
    $i
    =0;
    while (
    $i $num) {
        
    $image=mysql_result($result,$i,"image");
    $name=mysql_result($result,$i,"name");
    $category=mysql_result($result,$i,"category");
    $description=mysql_result($result,$i,"description");
    $stamps=mysql_result($result,$i,"stamps");
    $stickmen=mysql_result($result,$i,"stickmen");
    $price=mysql_result($result,$i,"price");
    $view=mysql_result($result,$i,"view");
    $actpagelink=mysql_result($result,$i,"actpagelink");


    ?>


    <a href="<?php echo $actpagelink?>" class="searchitem">
    <div class="searchimage"><img src="<?php echo $image?>"/></div>
    <div class="searchtext">
      <div class="searchname"><?php echo $name?></div>
      <div class="searchcategory"><?php echo $category?></div>
      <div class="searchdescription"><?php echo $description?></div>
    </div>
    <div class="searchstamps"><img src="<?php echo $stamps?>" /></div>
    <div class="searchstickmen"><img src="<?php echo $stickmen?>" /></div>
    <div class="searchprice"><span class="pricefrom">from</span>&pound;<?php echo $price?></div>

    <div class="searchview"><img src="<?php echo $view?>" /></div>


    </a>

    <?php
    $i
    ++;
    }


    mysql_close();
    ?>

     
    </body>
    So as you can see I'm outputting the data into divs and there is also a dropdown with ''ORDER BY'' sorting the data. I've also put a WHERE clause after that but when that is there it disables the ''order by''. I also need to connect the WHERE to a link which will activate it as there will be quite a lot of them so it can filter the data.

    Thanks
    Last edited by SpacePhoenix; Feb 25, 2013 at 00:38. Reason: placed php tags around php code

  2. #2
    SitePoint Enthusiast rajeev13's Avatar
    Join Date
    Nov 2012
    Location
    New Delhi,India
    Posts
    35
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Techd View Post
    Hi, I've got a problem with using a WHERE clause in my code. Bear with me as I'm new to php. Here it is:


    So as you can see I'm outputting the data into divs and there is also a dropdown with ''ORDER BY'' sorting the data. I've also put a WHERE clause after that but when that is there it disables the ''order by''. I also need to connect the WHERE to a link which will activate it as there will be quite a lot of them so it can filter the data.

    Thanks
    Try to echo your query and run on mysql directly ,then then check what error comes...

  3. #3
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,067
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    1. The query you're selecting with the if-else block, you're then running
      PHP Code:
      $result mysql_query("SELECT * FROM searchacts
      WHERE category='Party Bands'"
      ); 
      and then any function that acts on the last result set will act on the result set from that query.
    2. NEVER TRUST any user submitted data no matter who it comes from, that includes cookies. Any user submitted data should be sanitized to make sure that it's what your expecting, eg the right type, the right size/length, etc.
    3. Any query that is being sent to a database server should be escape, the preferred method nowadays is the use of prepared statements.
    4. The mysql_* extension is deprecated, any scripts that use it should be migrated over to either the mysqli_* extension or PDO. The mysql_* extension will very likely be removed when the next version of php is released.
    5. Do you really need all the fields from the searchacts table in the result set? If you don't then specify the ones required in the SELECT clause otherwise it's a waste of bandwidth and memory.
    6. The way the script gets the result set is not too efficient, the result set can be got using the function to get the next row as what is evaluated, with NULL being returned when there are no more rows left to be fetched, provided that you're only selecting the fields needed you won't get any unneeded data in the array holding the results. If you migrate over to either the mysqli_* extension or PDO then you have got functions which will return the entire result set in one hit.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  4. #4
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    PHP Code:
    <?php

    // connect to db etc


    // if all your if/elses fail - default is:
    $query "SELECT * FROM searchacts WHERE category='Party Bands'";

    // a line of debug. Now, did your if/else work as you expected?  Are there any matching records?
    // copy/paste this into your db as a sanity check, and to check your sql statement is well formed.
    echo $query '<hr />';


    // get your result
    $result mysql_query($query);

    // output your form
    ?>

    <form action='<?php echo $_SERVER['PHP_SELF']; ?>' method='post' name='form_filter' >  
        <select name="value">  
            <option value="all">All</option>  
            <option value="PriceLow">Price (Low to High)</option>  
            <option value="PriceHigh">Price (High to Low)</option>  
            <option value="NameAZ">Name (A-Z)</option>  
            <option value="NameZA">Name (Z-A)</option> 
        </select>  
        <br />  
        <input type='submit' value = 'Re-Order'>  
    </form>

    <?php

    // foreach through the array of results

    foreach(mysql_fetch_array($result) as $row){

    echo 
    '<div>' $row['image'] . '</div>';

    // etc

    }
    Try it that way ...

  5. #5
    SitePoint Member
    Join Date
    Jun 2012
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by SpacePhoenix View Post
    1. The query you're selecting with the if-else block, you're then running
      PHP Code:
      $result mysql_query("SELECT * FROM searchacts
      WHERE category='Party Bands'"
      ); 
      and then any function that acts on the last result set will act on the result set from that query.
    2. NEVER TRUST any user submitted data no matter who it comes from, that includes cookies. Any user submitted data should be sanitized to make sure that it's what your expecting, eg the right type, the right size/length, etc.
    3. Any query that is being sent to a database server should be escape, the preferred method nowadays is the use of prepared statements.
    4. The mysql_* extension is deprecated, any scripts that use it should be migrated over to either the mysqli_* extension or PDO. The mysql_* extension will very likely be removed when the next version of php is released.
    5. Do you really need all the fields from the searchacts table in the result set? If you don't then specify the ones required in the SELECT clause otherwise it's a waste of bandwidth and memory.
    6. The way the script gets the result set is not too efficient, the result set can be got using the function to get the next row as what is evaluated, with NULL being returned when there are no more rows left to be fetched, provided that you're only selecting the fields needed you won't get any unneeded data in the array holding the results. If you migrate over to either the mysqli_* extension or PDO then you have got functions which will return the entire result set in one hit.

    As I said I am only just learning php mysql so I really dont have much idea of what you've said . Anyway i'll go over it and try make sense of it.

    1. Ok so i've put the where clause in the 'if-else' block and it now works but only in the same dropdown. I need it in a separate link.

    2. Ok so its just going to be me inserting data for the moment so I will come back to this later.

    3. What is escape?

    4. Ok so how do I do that? The tutorials I've been using have been mysql. So does that mean the majority of php mysql tutorials are useless? I cant seem to find any tutorials on how to convert to mysqli or pdo.

    5. Yes at the moment all of them are visible.

    6. I have no idea how to do this.

    I'm not doing too well am I!! Thanks for posting anyway

  6. #6
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    3 What is escape?
    http://terrychay.com/article/php-adv...e-output.shtml - that article might help (features the glorious "Little Bobby Tables" cartoon) so might learning the mnemonic FIEO.

  7. #7
    SitePoint Member
    Join Date
    Jun 2012
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok so i've got my sorting options and WHERE clauses:

    Code:
    if(isset($_POST['value'])) { 
        if($_POST['value'] == 'PriceLow') { 
           
            $query = "SELECT * FROM searchacts ORDER BY price ASC";   
        }   
    	elseif($_POST['value'] == 'PriceHigh') {   
            
            $query = "SELECT * FROM searchacts ORDER BY price DESC";   
        }
        elseif($_POST['value'] == 'NameAZ') {   
            
            $query = "SELECT * FROM searchacts ORDER BY name ASC";   
        } 
    	
    	elseif($_POST['value'] == 'NameZA') {   
            
            $query = "SELECT * FROM searchacts ORDER BY name DESC";   
        }
    	
    	else {   
            
            $query = "SELECT * FROM searchacts";   
        }   
    	
         
    
        } 
      
            if($_REQUEST['value'] == 'rockandpop') {   
            
            $query = "SELECT * FROM searchacts
    WHERE category='Rock and Pop'";   
        } 
            
    		if($_REQUEST['value'] == 'tributebands') {   
            
            $query = "SELECT * FROM searchacts
    WHERE category='Tribute Bands'";   
        }   
    	
        $result = mysql_query($query);
    and i'm passing the variable through url:
    Code:
    <a href="http://www.example.co.uk/searchtesting.php?value=rockandpop">Rock and Pop</a>
    <a href="http://www.example.co.uk/searchtesting.php?value=tributebands">Tribute Bands</a>
    So then if the user clicks on say 'Tribute Bands' it will filter all the tribute bands, but if you then want to use one of the ORDER BY functions it will forget the WHERE clause. How can I make it remember which data it has filtered so it can then sort the filtered data?

    Thanks

  8. #8
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Looks as though you are mixing GET and POST, which is not a problem as long as you are careful.

    You will need to persist the band type by using a html `hidden` field.
    d
    Here is roughly what you want to be doing ... creating an "sql query builder"

    I added in some basic white-lists of values you should be checking for based on your example. You should be able to spot how you could then use these arrays to generate your HTML form elements too.

    PHP Code:
    $type $_POST['type'];  // you could give these better names in your html form
    $order $_POST['value']; // ditto

    $permitted_types = array(
        
    'tributebands' => 'Tribute Bands'
        
    'rockandpop' => 'Rock and Pop',
    );

    $permitted_order = array(
      
    'PriceHigh' => 'price',
      
    'PriceLow' => 'price ASC',
      
    'NameAZ' => 'name',
      
    'NameZA' => 'name ASC',
    );

    $sql "SELECT * FROM searchacts ";  // start of your select
    $where ""
    $order_by "";

    if( 
    array_key_exists($type$permitted_types) ){  // filter incoming vs a white list of permitted values
    $where "WHERE category = ' . $permitted_types[$type]. ' ";
    }

    if( 
    array_key_exists($order$permitted_order) ){  // filter incoming vs a white list of permitted values
    $where "ORDER BY " $permitted_order[$order];
    }

    echo 
    $sql $where $orderby 
    And:
    Code:
    <input type=hidden name=type value ='tributeband' >
    Thats all untested btw, does not check if POST values are missing and so on .... just showing you one way to do it.

  9. #9
    SitePoint Member
    Join Date
    Jun 2012
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok I think I understand

    So this is what i have now:

    Code:
    <body>
    <?php
    $username="USERNAME";
    $password="PASSWORD";
    $database="DATABASE";
    
    mysql_connect(localhost,$username,$password);
    @mysql_select_db($database) or die( "Unable to select database");
    $query="SELECT * FROM searchacts";
    $result=mysql_query($query);
    
    
    $type = $_POST['type'];  // you could give these better names in your html form
    $order = $_POST['value']; // ditto
    
    $permitted_types = array(
        'tributebands' => 'Tribute Bands', 
        'rockandpop' => 'Rock and Pop',
    );
    
    $permitted_order = array(
      'PriceHigh' => 'price',
      'PriceLow' => 'price ASC',
      'NameAZ' => 'name',
      'NameZA' => 'name ASC',
    );
    
    $sql = "SELECT * FROM searchacts ";  // start of your select
    $where = ""; 
    $order_by = "";
    
    if( array_key_exists($type, $permitted_types) ){  // filter incoming vs a white list of permitted values
    $where = "WHERE category = ' . $permitted_types[$type]. ' ";
    }
    
    if( array_key_exists($order, $permitted_order) ){  // filter incoming vs a white list of permitted values
    $where = "ORDER BY " . $permitted_order[$order];
    }
    
    echo $sql . $where . $orderby ;  	
    
    
    $result = mysql_query($query);
    	
    $num=mysql_numrows($result);
    
    ?>
    
    
    <form action='<?php echo $_SERVER['PHP_SELF']; ?>' method='post' name='form_filter' >  
        <select name="value">  
            <option value="all">All</option>  
            <option value="PriceLow">Price (Low to High)</option>  
            <option value="PriceHigh">Price (High to Low)</option>  
            <option value="NameAZ">Name (A-Z)</option>  
            <option value="NameZA">Name (Z-A)</option>  
        </select>  
        <br />  
        <input type='submit' value = 'Re-Order'>  
    </form>
    
    <input type=hidden name=type value ='tributebands' >
    
    <a href="http://www.EXAMPLE.co.uk/searchtesting.php?value=rockandpop">Rock and Pop</a>
    <a href="http://www.EXAMPLE.co.uk/searchtesting.php?value=tributebands">Tribute Bands</a>
    <?php
    $i=0;
    while ($i < $num) {
    	
    $image=mysql_result($result,$i,"image");
    $name=mysql_result($result,$i,"name");
    $category=mysql_result($result,$i,"category");
    $description=mysql_result($result,$i,"description");
    $stamps=mysql_result($result,$i,"stamps");
    $stickmen=mysql_result($result,$i,"stickmen");
    $price=mysql_result($result,$i,"price");
    $view=mysql_result($result,$i,"view");
    $actpagelink=mysql_result($result,$i,"actpagelink");
    
    
    ?>
    
    
    <a href="<?php echo $actpagelink; ?>" class="searchitem">
    <div class="searchimage"><img src="<?php echo $image; ?>"/></div>
    <div class="searchtext">
      <div class="searchname"><?php echo $name; ?></div>
      <div class="searchcategory"><?php echo $category; ?></div>
      <div class="searchdescription"><?php echo $description; ?></div>
    </div>
    <div class="searchstamps"><img src="<?php echo $stamps; ?>" /></div>
    <div class="searchstickmen"><img src="<?php echo $stickmen; ?>" /></div>
    <div class="searchprice"><span class="pricefrom">from</span>&pound;<?php echo $price; ?></div>
    
    <div class="searchview"><img src="<?php echo $view; ?>" /></div>
    
    
    </a>
    
    <?php
    $i++;
    }
    
    
    mysql_close();
    ?>
    
    </body>
    So this is what I understand:

    The array_key_exists checks the inputted data against the permitted values. But I'm unsure of how to execute them through my form and URL variable?

  10. #10
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Why don't you just stick to POST values and forget the URL links for a moment, and just get the whole thing working as it should.

    If you don't know how to use a hidden field in a form, then you could be in for a bad day

    Code:
    <form action='<?php echo $_SERVER['PHP_SELF']; ?>' method='post' name='form_filter' >  
        <select name="value">  
            <option value="all">All</option>  
            <option value="PriceLow">Price (Low to High)</option>  
            <option value="PriceHigh">Price (High to Low)</option>  
            <option value="NameAZ">Name (A-Z)</option>  
            <option value="NameZA">Name (Z-A)</option>  
        </select>  
        <br />  
    
    <!--hidden elements have to be inside the form tags-->
    
    <input type=hidden name=type value ='tributebands' >
    
        <input type='submit' value = 'Re-Order'>  
    </form>
    You could even do this:
    Code:
    <form action='<?php echo $_SERVER['PHP_SELF']; ?>' method='post' name='form_filter' >  
        <select name="value">  
            <option value="all">All</option>  
            <option value="PriceLow">Price (Low to High)</option>  
            <option value="PriceHigh">Price (High to Low)</option>  
            <option value="NameAZ">Name (A-Z)</option>  
            <option value="NameZA">Name (Z-A)</option>  
        </select>  
        <br />  
    <select name=type>
    <option value ='tributebands' >Tribute Bands</option>
    <option value ='rockandroll' >Rock and Roll</option>
    </select>
        <input type='submit' value = 'Re-Order'>  
    </form>
    Now, what is it you really want to do? Have a POST form or a GET form, or just have url links that reorder your data? for example:

    Code:
    <a href="?type=tributebands&value=NameAz">Tribute Bands (alphabetically)</a>
    You should experiment with that too, now you can access the incoming vars as $_GET vars.

  11. #11
    SitePoint Member
    Join Date
    Jun 2012
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm ok. So technically my code before should have been working anyway, which it isn't.

    Here's where i'm testing it: http://tinyurl.com/aue2hhy

    My goal is to have the sorting options in a dropdown and links that will filter the band types and be able to sort the different band types individually.

    I got to that point yesterday but when you selected a band type and then sorted it the data refreshed so all data was sorted and not just the band type.

    Nothings working atm, so I think there's an error somewhere with the way you suggested with arrays etc, but I cant figure out what it is.

  12. #12
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    SELECT * FROM searchacts ORDER BY price ASC

    These lines of debug appearing at the top of the screen, paste them directly into your db, do you get the results you expect?

    Perhaps if you explicitly add DESC it'll be clearer what should be happening, though I thought order by did that anyway.
    Code:
    $permitted_order = array(
      'PriceHigh' => 'price DESC',
      'PriceLow' => 'price ASC',
      'NameAZ' => 'name DESC',
      'NameZA' => 'name ASC',
    );
    ps how is price stored in your db? Show me an example of one as it is in the table.

  13. #13
    SitePoint Member
    Join Date
    Jun 2012
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yep pasted them into phpmyadmin and it correctly sorted the data.

    Here's my structure:

    screenshot1.jpg

    and an example:

    screenshot2.jpg

    It works here with my old code as you can see: http://tinyurl.com/ctlb5dx

    but with the new code: http://tinyurl.com/aue2hhy

  14. #14
    SitePoint Member
    Join Date
    Jun 2012
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok i'm going back to my old code as it was at least working to an extent.

    So if you see here: http://tinyurl.com/ayx6gbr

    Ive got categories on the left using the WHERE clause and using GET. So for example clicking rock and pop you will see:

    example.co.uk/searchtestingv2.php?categories=rockandpop

    what i need is when someone clicks a category they can then sort that category without resetting the data and no categories being specified.

    So at the moment the where clause will disappear when you sort a category and it will sort all data.

    Iv'e even tried to put it in manually e.g: example.co.uk/searchtestingv2.php?categories=rockandpop&sort=PriceLow

    which still ignores the category where clause

    Anybody got any ideas?

  15. #15
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    If you want to mix GET and POST then you have to engineer so that it is a 2 step process.

    home
    - user selects either rock or tribute

    category page
    - user selects order by clause

    In that category page your POST form must then have the action

    <form method=POST action="searchtestingv2.php?categories=rockandpop">
    // your select elements


    Then you can assemble your sql statement from a mixture of GET and POST methods

    Either that or make your entire form use the GET method instead, then assemble your sql string with GET variables.

    Either way, mixing GET and POST can be confusing when starting out.

  16. #16
    SitePoint Member
    Join Date
    Jun 2012
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok so probably using just GET would be a better way of doing it?

    Would all that explain why it wasnt working when I was putting it in the url manually?

    Sent from my GT-I9300 using Tapatalk 2

  17. #17
    SitePoint Member
    Join Date
    Jun 2012
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok so here's my updated code using only the GET method:

    Code:
      <?php
    $username="USERNAME";
    $password="PASSOWRD";
    $database="DATABASE";
    
    mysql_connect(localhost,$username,$password);
    @mysql_select_db($database) or die( "Unable to select database");
    
    
    
    $query="SELECT * FROM searchacts";
    $result=mysql_query($query);
    
    
    
    		if($_GET['categories'] == 'rockandpop') {   
            
            $query = "SELECT * FROM searchacts WHERE category='Rock and Pop'";   
        } 
            
    		if($_GET['categories'] == 'tributebands') {   
            
            $query = "SELECT * FROM searchacts WHERE category='Tribute Bands'";   
        }   
    	
    	 
    
    
    
    // process form when posted 
    if(isset($_GET['upto'])) { 
        if($_GET['upto'] == 'upto100') { 
           
            $query = "SELECT * FROM searchacts WHERE price <= '100'";   
        }   
    	elseif($_GET['upto'] == 'upto200') {   
            
            $query = "SELECT * FROM searchacts WHERE price <= '200'";    
        }
        elseif($_GET['upto'] == 'upto300') {   
            
            $query = "SELECT * FROM searchacts WHERE price <= '300'";    
        } 
    	
    	elseif($_GET['upto'] == 'upto400') {   
            
            $query = "SELECT * FROM searchacts WHERE price <= '400'";   
    		 
        }
    	elseif($_GET['upto'] == 'upto500') {   
            
            $query = "SELECT * FROM searchacts WHERE price <= '500'";    
        }
        elseif($_GET['upto'] == 'upto600') {   
            
            $query = "SELECT * FROM searchacts WHERE price <= '600'";    
        } 
    	
    	elseif($_GET['upto'] == 'upto700') {   
            
            $query = "SELECT * FROM searchacts WHERE price <= '700'";   
    		 
        }
    	elseif($_GET['upto'] == 'upto800') {   
            
            $query = "SELECT * FROM searchacts WHERE price <= '800'";    
        }
        elseif($_GET['upto'] == 'upto900') {   
            
            $query = "SELECT * FROM searchacts WHERE price <= '900'";    
        } 
    	
    	elseif($_GET['upto'] == 'upto1000') {   
            
            $query = "SELECT * FROM searchacts WHERE price <= '1000'";   
    		 
        }
    	elseif($_GET['upto'] == 'upto1500') {   
            
            $query = "SELECT * FROM searchacts WHERE price <= '1500'";    
        }
        elseif($_GET['upto'] == 'upto2000') {   
            
            $query = "SELECT * FROM searchacts WHERE price <= '2000'";    
        } 
    	
    	elseif($_GET['upto'] == 'upto2500') {   
            
            $query = "SELECT * FROM searchacts WHERE price <= '2500'";   
    		 
        }
    	elseif($_GET['upto'] == 'upto3000') {   
            
            $query = "SELECT * FROM searchacts WHERE price <= '3000'";   
    		 
        }
    	elseif($_GET['upto'] == 'upto3500') {   
            
            $query = "SELECT * FROM searchacts WHERE price <= '3500'";    
        }
        elseif($_GET['upto'] == 'upto4000') {   
            
            $query = "SELECT * FROM searchacts WHERE price <= '4000'";    
        } 
    	
    	elseif($_GET['upto'] == 'upto4500') {   
            
            $query = "SELECT * FROM searchacts WHERE price <= '4500'";   
    		 
        }
    	elseif($_GET['upto'] == 'upto5000') {   
            
            $query = "SELECT * FROM searchacts WHERE price <= '5000'";    
        }
        elseif($_GET['upto'] == 'upto5500') {   
            
            $query = "SELECT * FROM searchacts WHERE price <= '5500'";    
        } 
    	
    	elseif($_GET['upto'] == 'upto6000') {   
            
            $query = "SELECT * FROM searchacts WHERE price <= '6000'";   
    		 
        }
    	
    
    	else {   
            
            $query = "SELECT * FROM searchacts";   
        }   
    	
         
    
        } 
    
    
    
    // process form when posted 
    if(isset($_GET['sort'])) { 
        if($_GET['sort'] == 'PriceLow') { 
           
            $query = "SELECT * FROM searchacts ORDER BY price ASC";   
        }   
    	elseif($_GET['sort'] == 'PriceHigh') {   
            
            $query = "SELECT * FROM searchacts ORDER BY price DESC";   
        }
        elseif($_GET['sort'] == 'NameAZ') {   
            
            $query = "SELECT * FROM searchacts ORDER BY name ASC";   
        } 
    	
    	elseif($_GET['sort'] == 'NameZA') {   
            
            $query = "SELECT * FROM searchacts ORDER BY name DESC";  
    		 
        }
    
    	
    
    	else {   
            
            $query = "SELECT * FROM searchacts";   
        }   
    	
         
    
        } 
      
        $result = mysql_query($query);
    	
    $num=mysql_numrows($result);
    
    
    
    
    
    ?>
      
      <div id="sortingcontainer"> <span class="sortbyheader">Sort By:</span>
    <form action='<?php echo $_SERVER['PHP_SELF']; ?>' method='get' name='form_filter' class="sortoptions" >  
      <select name="sort">  
          <option value="all">All</option>  
          <option value="PriceLow">Price (Low to High)</option>  
          <option value="PriceHigh">Price (High to Low)</option>  
          <option value="NameAZ">Name (A-Z)</option>  
          <option value="NameZA">Name (Z-A)</option>  
          </select>  
        <br />  
        <input type='submit' value = 'Re-Order your results'>  
    </form>
      </div>
        <div id="sortingcontainer"> <span class="sortbyheader">Sort By:</span>
    <form action='<?php echo $_SERVER['PHP_SELF']; ?>' method='get' name='form_filter' class="sortoptions" >  
      <select name="upto">  
          <option value="upto100">Up to 100</option>  
          <option value="upto200">Up to 200</option>  
          <option value="upto300">Up to 300</option>  
          <option value="upto400">Up to 400</option>  
          <option value="upto500">Up to 500</option>  
          <option value="upto600">Up to 600</option>  
          <option value="upto700">Up to 700</option>  
          <option value="upto800">Up to 800</option>  
          <option value="upto900">Up to 900</option>  
          <option value="upto1000">Up to 1000</option> 
          <option value="upto1500">Up to 1500</option>  
          <option value="upto2000">Up to 2000</option>  
          <option value="upto2500">Up to 2500</option>  
          <option value="upto3000">Up to 3000</option>  
          <option value="upto3500">Up to 3500</option> 
          <option value="upto4000">Up to 4000</option>  
          <option value="upto4500">Up to 4500</option>  
          <option value="upto5000">Up to 5000</option>  
          <option value="upto5500">Up to 5500</option>  
          <option value="upto6000">Up to 6000</option> 
          </select>  
        <br />  
        <input type='submit' value = 'Re-Order your results'>  
    </form>
      </div>
      
      <?php
    $i=0;
    while ($i < $num) {
    	
    $image=mysql_result($result,$i,"image");
    $name=mysql_result($result,$i,"name");
    $category=mysql_result($result,$i,"category");
    $description=mysql_result($result,$i,"description");
    $stamps=mysql_result($result,$i,"stamps");
    $stickmen=mysql_result($result,$i,"stickmen");
    $price=mysql_result($result,$i,"price");
    $view=mysql_result($result,$i,"view");
    $actpagelink=mysql_result($result,$i,"actpagelink");
    
    
    ?>
      
      
      <a href="<?php echo $actpagelink; ?>" class="searchitem">
      <div class="searchimage"><img src="<?php echo $image; ?>"/></div>
      <div class="searchtext">
        <div class="searchname"><?php echo $name; ?></div>
        <div class="searchcategory"><?php echo $category; ?></div>
        <div class="searchdescription"><?php echo $description; ?></div>
      </div>
      <div class="searchstamps"><img src="<?php echo $stamps; ?>" /></div>
      <div class="searchstickmen"><img src="<?php echo $stickmen; ?>" /></div>
      <div class="searchprice"><span class="pricefrom">from</span>&pound;<?php echo $price; ?></div>
        
      <div class="searchview"><img src="<?php echo $view; ?>" /></div>
        
        
      </a>
      
      <?php
    $i++;
    }
    
    
    mysql_close();
    ?>
    But its still not working when I put ?categories=rockandpop&sort=PriceLow at the end of the url?

  18. #18
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Is it because you are calling mysql_numrows instead of mysql_num_rows, exacerbated by the fact you do not appear to have error reporting turned on for this page?
    PHP Code:
    <?php

    // top of your script, temporary lines, turn off when you go live
    error_reporting(E_ALL);
    ini_set("display_errors"1);

  19. #19
    SitePoint Member
    Join Date
    Jun 2012
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok i've added the error reporting + changed numrows to num_rows.

    Still the same though :S

  20. #20
    SitePoint Member
    Join Date
    Jun 2012
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Any ideas? Really need to get this sorted


Tags for this Thread

Bookmarks

Posting Permissions

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