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:

<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 :slight_smile:

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

  1. The query you’re selecting with the if-else block, you’re then running
    php $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.

<?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 …

As I said I am only just learning php mysql so I really dont have much idea of what you’ve said :D. 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 :slight_smile:

3 What is escape?

http://terrychay.com/article/php-advent-security-filter-input-escape-output.shtml - that article might help (features the glorious “Little Bobby Tables” cartoon) so might learning the mnemonic FIEO.

Ok so i’ve got my sorting options and WHERE clauses:

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:

<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

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.


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


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

Ok I think I understand :rolleyes:

So this is what i have now:

<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?

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 :wink:


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


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


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

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.

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.


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

Yep pasted them into phpmyadmin and it correctly sorted the data.

Here’s my structure:

and an example:

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

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

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?

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.

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

Ok so here’s my updated code using only the GET method:

  <?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?

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

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

Ok i’ve added the error reporting + changed numrows to num_rows.

Still the same though :S

Any ideas? Really need to get this sorted :slight_smile: