Help with my php code and URL parameters

[dan292]

Hello all! I need a bit of help with my coding. I'm new to php so be nice!

 

My goal is to fetch data from a database, display it in divs and be able to sort, filter etc. And I have succeeded to a point but have run into a few problems.

 

First you might want to have a look at the users point of view: http://tinyurl.com/bv6fm7b

 

And yes I am using mysql and not mysqli or pdo which I will convert to in the future, but most of the tutorials I've found use mysql so to get my head around it I am using that, I also probably have not put it all together the best way so any suggestions would be great.

 

Ok so the main problem at the moment are my sort/filtering functions. For example if you go to my page, click 'Rock and Pop' and then sort it by 'price high to low' it will forget the 'Rock and Pop' Where clause and vice versa. Also with this problem are the other sorting options and the pagination.

 

I'm using the GET method so I thought I'd try and enter say ?categories=rockandpop&sort=PriceHigh manually to the end of the url, but it only applied the price sorting and not the category.

 

There is a lot of code so I wont post all of it but try to put examples of each function.

 

Categories:

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'";   
    } 
 

Sort By:

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";   
    }
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'";         }
 

Example of one of the forms:

<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>   
      </select>  
    <br />  
    <input type='submit' value = 'Re-Order your results'>  
</form> 

And the data into the divs:

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

I've also attached my full code if that helps.

 

Thanks

 

 

code.txt

[davidannis]

You need to build the query in pieces and then connect them:

 

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'";   
    } 

// if we have a category we start the select by filtering just that category;
// then we add to it if it alread exists

if(isset($_GET['sort'])) { 
    if($_GET['sort'] == 'PriceLow') { 
       if ($query==''){
             $query = "SELECT * FROM searchacts ORDER BY price ASC"; 
          }else {
               $query.= " ORDER BY price ASC"; // Adds this to the end of the query              
}
    }   
elseif($_GET['sort'] == 'PriceHigh') {   
        
if ($query==''){
        $query = "SELECT * FROM searchacts ORDER BY price DESC";  
}else{
      
$query .= " ORDER BY price DESC";
   }

 

Your code will get hard to manage with so many ifs, but I understand you're just learning. Nonetheless may I suggest looking at switch / case in the php manual.

[davidannis]

note that when I append there is a period before the equal sign (it's hard to see but it is there).

[dan292]

Hi, thanks for the reply.

 

I've got it half working with your suggestion but Its only working for sorting. For example this now works fine: ?categories=rockandpop&sort=PriceHigh

 

but this doesn't: ?categories=rockandpop&upto=upto600 and returns the error: Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in

 

I used the same method:

 

 

 

if(isset($_GET['upto'])) { 

    if($_GET['upto'] == 'upto100') { 


       if ($query==''){
  
             $query = "SELECT * FROM searchacts WHERE price <= '100'"; 


          }else {
               $query.= " WHERE price <= '100'"; // Adds this to the end of the query              
}
    }   




elseif($_GET['upto'] == 'upto200') {   
        
if ($query==''){
        $query = "SELECT * FROM searchacts WHERE price <= '200'";  
}


else{
      
$query .= " WHERE price <= '200'";
   }
}

etc, etc

 

I also need a way of adding it automatically to the url if you go into a category. eg. if i'm here: ?categories=rockandpop and sort by 'high to low' I get this:

?categories=rockandpop&sort=PriceHigh

 

I've also got rid of the pagination to make it simpler for the meantime. http://tinyurl.com/c3pxuzf

 

Thanks

Edited March 6, 2013 by dan292

[PaulRyan]

Try something like this:

Edit: I hate the code formatter.

 

<?PHP

  //### Initialize variables
  $category = isset($_GET['categories']) ? $_GET['categories'] : FALSE ;
  $sort     = isset($_GET['sort']) ? $_GET['sort'] : FALSE ;
  $upto     = isset($_GET['upto']) ? $_GET['upto'] : FALSE ;
 
  //### Create the URL parameters
  $urlParameters = '?categories='. $category .'&upto='. $upto .'&sort='. $sort .'';

  //### Start the query variable
  $query = 'SELECT * FROM `searchacts`';
 
  //### Initialize query options
  $queryOptions = array();
 
  //### Now check each of the sorting values
  //### Check which category we are searching in
  if($category == 'rockandpop') {
    $queryOptions[] = "`category` = 'Rock and Pop'";
  } else if($category == 'tributebands') {
    $queryOptions[] = "`category` = 'Tribute Bands'";
  }
 
  //### Check what value upto we are returning
  if($upto == 'upto100') {
    $queryOptions[] = "`price` <= 100";
  } else if($upto == 'upto200') {
    $queryOptions[] = "`price` <= 200";
  }
 
  //### Create final query
  $finalQuery = $query. ' WHERE '.implode(' AND ', $queryOptions);
 
  //### Add the order by to the end of the query
  if($sort == 'PriceLow') {
    $finalQuery .= ' ORDER BY `price` ASC';
  } else if($sort == 'PriceHigh') {
    $finalQuery .= ' ORDER BY `price` DESC';
  }
 
  //### Final query to search and return records from database
  echo $finalQuery;
 
  //### URL parameters, add this to the form action
  echo '<br>'.$urlParameters;
?>

<form method="GET" action="<?PHP echo $urlParameters;?>">
  <input type="text" name="sort" value="<?PHP echo $sort;?>">
  <input type="text" name="upto" value="<?PHP echo $upto;?>">
  <input type="text" name="categories" value="<?PHP echo $category;?>">
  <input type="submit" value="Submit">
</form>

Edited March 6, 2013 by PaulRyan

[dan292]

Hi PaulRyan. Your code makes sense and the comments help to understand it 
 
So this is what ive got:

error_reporting(E_ALL);
ini_set("display_errors", 1);

$username="username";
$password="passwordd";
$database="database";

mysql_connect('localhost',$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

  
  //### Initialize variables
  $category = isset($_GET['categories']) ? $_GET['categories'] : FALSE ;
  $sort     = isset($_GET['sort']) ? $_GET['sort'] : FALSE ;
  $upto     = isset($_GET['upto']) ? $_GET['upto'] : FALSE ;


  //### Create the URL parameters
  $urlParameters = '?categories='. $category .'&upto='. $upto .'&sort='. $sort .'';


  //### Start the query variable
  $query = 'SELECT * FROM `searchacts`';


  //### Initialize query options
  $queryOptions = array();


  //### Now check each of the sorting values
  //### Check which category we are searching in
  if($category == 'rockandpop') {
    $queryOptions[] = "`category` = 'Rock and Pop'";
  } else if($category == 'tributebands') {
    $queryOptions[] = "`category` = 'Tribute Bands'";
  }


  //### Check what value upto we are returning
  if($upto == 'upto100') {
    $queryOptions[] = "`price` <= 100";
  } else if($upto == 'upto200') {
    $queryOptions[] = "`price` <= 200";
  }


  //### Create final query
  $finalQuery = $query. ' WHERE '.implode(' AND ', $queryOptions);


  //### Add the order by to the end of the query
  if($sort == 'PriceLow') {
    $finalQuery .= ' ORDER BY `price` ASC';
  } else if($sort == 'PriceHigh') {
    $finalQuery .= ' ORDER BY `price` DESC';
  }


  //### Final query to search and return records from database
  echo $finalQuery;


  //### URL parameters, add this to the form action
  echo '<br>'.$urlParameters;


$result = mysql_query($query);

$num=mysql_num_rows($result);

?>
  <div id="sortingcontainer"> <span class="sortbyheader">Sort By:</span>
<form action='<?PHP echo $urlParameters;?>' 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>

<li><a href="?categories=rockandpop">Rock and Pop</a></li>
<li><a href="?categories=tributebands">Tribute Bands</a></li>  </div>

But at the moment its printing the query on the page like: SELECT * FROM `searchacts` WHERE `category` = 'Rock and Pop'
?categories=rockandpop&upto=&sort=
 
See here: http://tinyurl.com/c3pxuzf
 
So I'm not sure what's going wrong.
 
And how can I use the echos: <?PHP echo $sort;?> <?PHP echo $upto;?> <?PHP echo $category;?> on my select forms and links

 
Also out of interest what is the relevance of the `` eg. in: SELECT * FROM `searchacts`
 
Thanks!

[PaulRyan]

Hello again, I'm glad you found my code useful.

 

You can comment out the following lines:

echo $finalQuery; 
echo '<br>'.$urlParameters;
 

These are only used to display the values of the the query and url parameters.

 

The variable $finalQuery is used in the MySQL, like this:

$result = mysql_query($finalQuery);
 

This part:

 

<li><a href="?categories=rockandpop">Rock and Pop</a></li>

Should look like this, add the href to the other links and change the category to the correct value:

 

<li><a href="<?PHP echo '?categories=rockandpop&upto='. $upto .'&sort='. $sort .'';?>">Rock and Pop</a></li>

 

The backticks are used in the MySQL query to surround the table and column names, it also allows you to use keywords as both the table and column names (I think)

I just use them as it makes it look neater as you know where the names of the table and column start and end.

 

Regards, PAulRyan.

[dan292]

Hi, ok so at the moment when going onto the page initially there is no category selected.. so no results are displaying i'm guessing if I add this:

 

  //### Check which category we are searching in
  if($category == 'rockandpop') {
    $queryOptions[] = "`category` = 'Rock and Pop'";
  } else if($category == 'tributebands') {
    $queryOptions[] = "`category` = 'Tribute Bands'";
  }

  else if($category == '') {
    $queryOptions[] = "`category` = 'ALL HERE?'";  } 
 

So if nothing is selected it will display all but is there a way I can specify All records?

 

Also to echo in the select form, how would that be done? would I have to echo the options individually? Something like:

 

 

<form action='<?PHP echo $urlParameters;?>' method='get' name='form_filter' class="sortoptions" >  
  <select name="sort" >  
      <option value="all">All</option>  
      <option value="<?PHP echo '?categories= $categories . &upto='. $upto .'&sort=PriceLow'. $sort .'';?>">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>
 

Thanks

[PaulRyan]

I'm guessing when selecting all, that you don't actually use a term when getting all records, so you don't need the last elseif in the code you posted.

 

  //### Check which category we are searching in
  if($category == 'rockandpop') {
    $queryOptions[] = "`category` = 'Rock and Pop'";
  } else if($category == 'tributebands') {
    $queryOptions[] = "`category` = 'Tribute Bands'";
  }

 

It will return the following MySQL query, which is exactly what is needed to return all the records:

 

SELECT * FROM `searchacts`

Note: You should't use " * ", you should select only the columns you actually require to save on overhead and response.

 

You do not need to touch the values of the select options, leave them as they were. Just plain values, the forms action takes care of everything else.

 

You need to put my code BEFORE the links you output to the page, so before All Acts, Rock and Pop ect.

This allows the links to take the values of the variables that are set based on the GET values in the URL.

 

Also, like I said, COMMENT OUT the following lines:

 

  echo $finalQuery;
 
  echo '<br>'.$urlParameters;

 

They should look like this:

 

  //echo $finalQuery;
 
  //echo '<br>'.$urlParameters;

[dan292]

Hmm that's what I thought about returning all the records but it doesn't seem to: http://tinyurl.com/c3pxuzf

 

And with the select forms It only seems to apply itself and not to the end of the url, so resets everthing. It works with your input form at the top but i'm assuming that's because its submiting it in one go.

 

Heres my updated code:

 

//### Initialize variables
  $category = isset($_GET['categories']) ? $_GET['categories'] : FALSE ;
  $sort     = isset($_GET['sort']) ? $_GET['sort'] : FALSE ;
  $upto     = isset($_GET['upto']) ? $_GET['upto'] : FALSE ;


  //### Create the URL parameters
  $urlParameters = '?categories='. $category .'&upto='. $upto .'&sort='. $sort .'';


  //### Start the query variable
  $query = 'SELECT * FROM `searchacts`';


  //### Initialize query options
  $queryOptions = array();


  //### Now check each of the sorting values
  //### Check which category we are searching in
  if($category == 'rockandpop') {
    $queryOptions[] = "`category` = 'Rock and Pop'";
  } else if($category == 'tributebands') {
    $queryOptions[] = "`category` = 'Tribute Bands'";
  }


  //### Check what value upto we are returning
  if($upto == 'upto100') {
    $queryOptions[] = "`price` <= 100";
  } else if($upto == 'upto200') {
    $queryOptions[] = "`price` <= 200";
  }
  else if($upto == 'upto300') {
    $queryOptions[] = "`price` <= 300";
  }


  //### Create final query
  $finalQuery = $query. ' WHERE '.implode(' AND ', $queryOptions);


  //### Add the order by to the end of the query
  if($sort == 'PriceLow') {
    $finalQuery .= ' ORDER BY `price` ASC';
  } else if($sort == 'PriceHigh') {
    $finalQuery .= ' ORDER BY `price` DESC';
  }

  //echo $finalQuery;


  //echo '<br>'.$urlParameters;


$result = mysql_query($finalQuery);


$num = mysql_num_rows($result);


?>

    <li><a href="searchtestingv6.php">All Acts</a></li>
      <li><a href="<?PHP echo '?categories=rockandpop&upto='. $upto .'&sort='. $sort .'';?>">Rock and Pop</a></li>
      <li><a href=<?PHP echo '?categories=tributebands&upto='. $upto .'&sort='. $sort .'';?>>Tribute Bands</a></li>


<form method="GET" action="<?PHP echo $urlParameters;?>">
  <input type="text" name="sort" value="<?PHP echo $sort;?>" >
  <input type="text" name="upto" value="<?PHP echo $upto;?>">
  <input type="text" name="categories" value="<?PHP echo $category;?>">
  <input type="submit" value="Submit">
</form>


  <div id="sortingcontainer"> <span class="sortbyheader">Sort By:</span>
<form action='<?PHP echo $urlParameters;?>' 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>  


      </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 $urlParameters;?>' 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>  


    <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>£<?php echo $price; ?></div>
    
  <div class="searchview"><img src="<?php echo $view; ?>" /></div>
    
    
  </a>
  
  <?php
$i++;
}

mysql_close();
?>

I was actually using these: 

 

 

echo $finalQuery;
echo '<br>'.$urlParameters;

to see what it was outputting

 

Thanks

[PaulRyan]

I see what you mean, you could try having hidden form fields in each form, to keep the data that has been selected already maybes?

 

Look for the the code similar to this below and replace with the code below:

 

  <li><a href="searchtestingv6.php">All Acts</a></li>
  <li><a href="<?PHP echo '?categories=rockandpop&upto='. $upto .'&sort='. $sort .'';?>">Rock and Pop</a></li>
  <li><a href="<?PHP echo '?categories=tributebands&upto='. $upto .'&sort='. $sort .'';?>">Tribute Bands</a></li>

  <div id="sortingcontainer"> <span class="sortbyheader">Sort By:</span>
    <form action='?' method='get' name='form_filter' class="sortoptions" >
      <input type="hidden" name="upto" value="<?PHP echo $upto;?>">
      <input type="hidden" name="categories" value="<?PHP echo $category;?>">
      <select name="sort">  
        <option value="all">All</option>  
        <option value="PriceLow">Price (Low to High)</option>  
        <option value="PriceHigh">Price (High to Low)</option>  
      </select>  
      <br />  
      <input type='submit' value = 'Re-Order your results'>  
    </form>

    <form action='?' method='get' name='form_filter' class="sortoptions" >  
      <input type="hidden" name="sort" value="<?PHP echo $sort;?>">
      <input type="hidden" name="categories" value="<?PHP echo $category;?>">
      <select name="upto">  
        <option value="upto100">Up to £100</option>  
        <option value="upto200">Up to £200</option>  
        <option value="upto300">Up to £300</option>  
      </select>
      <input type='submit' value = 'Re-Order your results'>  
    </form>
  </div>

 

Regarding the code to select all records, it should work with the code I have given you, I'm guessing you maybes overwrite the query with your own code? Or don't have my code in the correct place.

Edited March 8, 2013 by PaulRyan

[dan292]

Brilliant! All the sorting/filter functions are working. 

 

Yeah I agree about the all records. So just to go over it:

 

 

this should select all records:

  //### Start the query variable
  $query = 'SELECT * FROM `searchacts`';

which is picked up by this to create final query:

 

  //### Create final query
  $finalQuery = $query. ' WHERE '.implode(' AND ', $queryOptions);

which is picked up by this to output the results:

 

$result = mysql_query($finalQuery);

Is it expecting there to be a WHERE in the query or something?

 

My code is exactly as I posted on my last post so I haven't changed anything except for the forms.

 

And the only code you haven't seen is the connection:

 

<?php
error_reporting(E_ALL);
ini_set("display_errors", 1);

$username="username";
$password="password";
$database="database";

But it cant be that surely 

 

Cheers

[PaulRyan]

To find out why the records aren't returning, and you get the error about the mysql_num_rows, you need to debug your code. Look at mysql_error

Also, you should really look to move onto MySQLi and ditch MySQL, as it is currently in soft deprecated, soon to be fully deprecated.

[PaulRyan]

I totally missed where I went wrong, and it is indeed the WHERE issue.

 

This line:

 

 $finalQuery = $query. ' WHERE '.implode(' AND ', $queryOptions);

Should infact be:

 

  $queryWhere = isset($queryOptions[0]) ? ' WHERE '.implode(' AND ', $queryOptions) : '' ;
 
  $finalQuery = $query . $queryWhere;

 

That should now work

[dan292]

Hi PaulRyan, I thought it was that! 

 

So now thats all working.. on to the pagination.

 

I found this script : http://papermashup.com/easy-php-pagination/

 

and this is the bit where you 'plug it in':

 

 

$tableName="countries"; 
$targetpage = "index.php";  
$limit = 10; 

$query = "SELECT COUNT(*) as num FROM $tableName";
$total_pages = mysql_fetch_array(mysql_query($query));
$total_pages = $total_pages[num];

$stages = 3;
$page = mysql_escape_string($_GET['page']);
if($page){
$start = ($page - 1) * $limit; 
}else{
$start = 0; 
} 

    // Get page data
$query1 = "SELECT * FROM $tableName LIMIT $start, $limit";
$result = mysql_query($query1);

I've tried numerous ways but can't seem to get it to work and obviously its going to collide with this part of the code:

 

 

  //### Start the query variable
  $query = 'SELECT * FROM `searchacts`';

So I only need one of these and have tried to combine them with no luck.

 

Maybe I'm missing something? I also didnt include $result = mysql_query($query1); twice either