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>£<?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.
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.
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.
Any query that is being sent to a database server should be escape, the preferred method nowadays is the use of prepared statements.
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.
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.
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
}
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.
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.
Ok so its just going to be me inserting data for the moment so I will come back to this later.
What is escape?
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.
Yes at the moment all of them are visible.
I have no idea how to do this.
I’m not doing too well am I!! Thanks for posting anyway
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?
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.
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.
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);