Pulling records on from a database using a drop downmenu

[Owz2004]

Hi All,

Im new to php and have hit a break wall trying to get a drop down menu to pull records onto my page based on the drop down menu select value.

I have a table called wrappers which stores information based on a personalized chocolate wrapper. The wrapper table holds the following details.

id
title
Description
category
img_thumbnail_source
img_source

I have setup an input form which captures all this information and puts it into the database.

Now I want to be able to pull down certain wrappers based on the category its from.

So for example. I have wrappers personalised for valentines days..

So when the user selects "valentines" from the drop down menu it goes away and pulls the records matching the category->valentines.

I have a basic query which pulls the whole database which is as follows:

function getwrapper() {
    require 'database.php';
    require 'config.php';
    $q = "SELECT id, title, description, tn_src FROM wrappers ORDER BY id desc";
    
    $result = $mysqli->query($q) or die($mysqli_error($mysqli));
    
    if($result) {
        while($row = $result->fetch_object()) {
            $title = $row->title;
            $d = $row->description;
            $tn_src = $row->tn_src;
            $src = $row->src;
            $id = $row->id;
        
            print '<li>
                     <a href="wrapper_review.php?id=' . $id . '">
                     <h1 class="smallh1">' . $title . '</h1>
                      <img src="' . $tn_src . '" alt="' . $title . '" id="' . $id . '" />
                    </a>
                     <p class="smalldesc">' . $d . '</p> 
                   </li>';
                   
            print "\n";
        }
    }
} 

This grabs all the wrappers in the database.

How do i do it for the selected category? I also want it to go fetch the results as soon as an option is selected. (not via a submit button).

I hope this makes sense...

Any help would be greatly appreciated..

[corbyboy]

Your SQL would need to look something like this:

$q = "SELECT id, title, description, tn_src FROM wrappers WHERE category='" . mysql_real_escape_string($_GET['category'] . "' ORDER BY id desc";

Assuming that your category is contained in the GET variable "category."

As for auto-changing you need to use javascript to auto-submit the form when the select box is changed. Soemthing like this:

<select onchange="this.form.submit();">

[Owz2004]

So would something like this work

<?php


require 'config.php';
require 'functions.php';
require 'database.php';


if(isset($_POST['category'])) {

    $category = $_POST['category'];
   
   $q = "SELECT id, title, description, tn_src FROM wrappers WHERE category='" . mysql_real_escape_string($_GET['category'] . "' ORDER BY id desc"; 
    
     $result = $mysqli->query($q) or die($mysqli_error($mysqli)); 
     
    if($result) { 
        while($row = $result->fetch_object()) { 
            $title = $row->title; 
            $d = $row->description;
            $category = $row->category; 
            $tn_src = $row->tn_src; 
            $src = $row->src; 
            $id = $row->id; 
         
            print '<li> 
                     <a href="wrapper_review.php?id=' . $id . '"> 
                     <h1 class="smallh1">' . $title . '</h1> 
                      <img src="' . $tn_src . '" alt="' . $title . '" id="' . $id . '" /> 
                    </a> 
                     <p class="smalldesc">' . $d . '</p> 
                   </li>'; 
                    
            print "\n"; 
        } 
    } 
}

I quickly tried running it but got an error which said unexpected ; on the line

$category = $_POST['category']; 

my drop down menu looks like the following:

<form method="post" action="occasions.php">
	<select name="category">
	 <option value="valentines" onchange="this.form.submit();">Valentines</option>
	  <option Value="birthday" onchange="this.form.submit();">Birthdays</option>
	  <option value="weddings" onchange="this.form.submit();">Weddings</option>
          <option value="christmas" onchange="this.form.submit();">Christmas</option>
	  <option value="prom" onchange="this.form.submit();">Prom</option>
          <option value="haloween" onchange="this.form.submit();">Haloween</option>
									
	</select>
	</form>

[corbyboy]

Your error is here:

$q = "SELECT id, title, description, tn_src FROM wrappers WHERE category='" . mysql_real_escape_string($_GET['category'] . "' ORDER BY id desc";

You missed the closing bracket so it should be:

$q = "SELECT id, title, description, tn_src FROM wrappers WHERE category='" . mysql_real_escape_string($_GET['category']) . "' ORDER BY id desc";

The example javascript I posted had the javascript in the <select> tag so I don't know why you have moved it to the <option> tag. Something like this should work:

<form name="occform" method="post" action="occasions.php">
	<select name="category" onchange="this.form.submit();">
	 <option value="valentines">Valentines</option>
	  <option Value="birthday">Birthdays</option>
	  <option value="weddings">Weddings</option>
          <option value="christmas">Christmas</option>
	  <option value="prom">Prom</option>
          <option value="haloween">Haloween</option>
 
	</select>
	</form>

Hope that works for you.

[Owz2004]

Thanks for your help, I'l give this a go over the weekend...

Will let you know if it worked...

Thanks again

Ow

[Owz2004]

I made the changes and the page now displays.. but when I select one of the categories from the drop down i get the following error:

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: Access denied for user 'owz2004'@'localhost' (using password: NO) in /home/owz2004/public_html/Choc_bars_Website/occasions.php on line 25

line 25 is the query string:

$q = "SELECT id, title, description, tn_src FROM wrappers WHERE category='" . mysql_real_escape_string($_GET['category']) . "' ORDER BY id desc";

I have "required" my database.php connection file before running the query so I dont understand why It's not connecting to the database..

Any ideas?

[Chameleon]

Just a quick look but your form is using post and in your query it is trying to get.

<form method="post" action="occasions.php">
	<select name="category">
	 <option value="valentines" onchange="this.form.submit();">Valentines</option>
	  <option Value="birthday" onchange="this.form.submit();">Birthdays</option>
	  <option value="weddings" onchange="this.form.submit();">Weddings</option>
          <option value="christmas" onchange="this.form.submit();">Christmas</option>
	  <option value="prom" onchange="this.form.submit();">Prom</option>
          <option value="haloween" onchange="this.form.submit();">Haloween</option>
									
	</select>
	</form>

Try this:

$q = "SELECT id, title, description, tn_src FROM wrappers WHERE category='" . mysql_real_escape_string($_POST['category']) . "' ORDER BY id desc"; 

[Chameleon]

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: Access denied for user 'owz2004'@'localhost' (using password: NO) in /home/owz2004/public_html/Choc_bars_Website/occasions.php on line 25

Is your connection to your database before you call the mysql_real_escape_string? usually if your connection isnt before the call to that function or isnt connected to the database you will recieve that warning.

[Owz2004]

I've changed the form now to

<form method="post" action="occasions.php">
    <select name="category" onchange="this.form.submit();">
     <option value="valentines">Valentines</option>
      <option Value="birthday">Birthdays</option>
      <option value="weddings">Weddings</option>
          <option value="christmas">Christmas</option>
      <option value="prom">Prom</option>
          <option value="haloween">Haloween</option>
                                    
    </select>
    </form>

As advised by corby boy...

I have also altered the query string... but get that warning now...

I have used the " require 'database.php' " which is a couple of lines before the query steing.. The code looks like this:

<?php
require 'config.php';
require    'database.php';


if(isset($_POST['category'])) {

    $category = $_POST['category'];
   
    $q = "SELECT id, title, description, tn_src FROM wrappers WHERE category='" . mysql_real_escape_string($_GET['category']) . "' ORDER BY id desc"; 
    
     $result = $mysqli->query($q) or die($mysqli_error($mysqli)); 
     
    if($result) { 
        while($row = $result->fetch_object()) { 
            $title = $row->title; 
            $d = $row->description;
            $category = $row->category; 
            $tn_src = $row->tn_src; 
            $src = $row->src; 
            $id = $row->id; 
         
            print '<li> 
                     <a href="wrapper_review.php?id=' . $id . '"> 
                     <h1 class="smallh1">' . $title . '</h1> 
                      <img src="' . $tn_src . '" alt="' . $title . '" id="' . $id . '" /> 
                    </a> 
                     <p class="smalldesc">' . $d . '</p> 
                   </li>'; 
                    
            print "\n";     
        } 
    } 
} 
        
?>

The database.php is called on other pages and works fine So i don't understand why It's not working on this occasion..

[Chameleon]

try this

$q = "SELECT id, title, description, tn_src FROM wrappers WHERE category='" . mysqli_real_escape_string($_POST['category']) . "' ORDER BY id desc"; 

I see your using mysqli so try using the mysqli_real_escape_string instead of the mysql_real_escape_string

[Owz2004]

Ive done that.. I now get this error

Warning: mysqli_real_escape_string() expects exactly 2 parameters, 1 given in /home/owz2004/public_html/Choc_bars_Website/occasions.p

[Chameleon]

try this:

<?php 
require 'config.php'; 
require    'database.php';   
    
    if(isset($_POST['category'])) {      
    
    $category = $_POST['category'];
    $category_Clean = $mysqli->real_escape_string($category);
    
    mysqli_real_escape_string($this->db_connect_id, $string)
    $q = "SELECT id, title, description, tn_src FROM wrappers WHERE category='" . $category_Clean . "' ORDER BY id desc";
    $result = $mysqli->query($q) or die($mysqli_error($mysqli));           
        
        if($result) {         
            while($row = $result->fetch_object()) {             
                $title = $row->title;             
                $d = $row->description;             
                $category = $row->category;             
                $tn_src = $row->tn_src;             
                $src = $row->src;             
                $id = $row->id;                       
         print '<li>
                    <a href="wrapper_review.php?id=' . $id . '">
                    <h1 class="smallh1">' . $title . '</h1>
                    <img src="' . $tn_src . '" alt="' . $title . '" id="' . $id . '" />
                    </a>
                    <p class="smalldesc">' . $d . '</p>                    
                </li>';                                  
        print "\n";              
        }     
    } 
}          
?>

[Owz2004]

Im getting a Parse error: syntax error, unexpected T_VARIABLE in /home/owz2004/public_html/Choc_bars_Website/occasions.php on line 28 error

line 28 is the following:

   $q = "SELECT id, title, description, tn_src FROM wrappers WHERE category='" . $category_Clean . "' ORDER BY id desc"; 

[Chameleon]

opps, forgot to delete a part above that line.


this should work without that error.

<?php 
require 'config.php'; 
require    'database.php';   
    
    if(isset($_POST['category'])) {      
    
    $category = $_POST['category'];
    $category_Clean = $mysqli->real_escape_string($category);
    
    $q = "SELECT id, title, description, tn_src FROM wrappers WHERE category='" . $category_Clean . "' ORDER BY id desc";
    $result = $mysqli->query($q) or die($mysqli_error($mysqli));           
        
        if($result) {         
            while($row = $result->fetch_object()) {             
                $title = $row->title;             
                $d = $row->description;             
                $category = $row->category;             
                $tn_src = $row->tn_src;             
                $src = $row->src;             
                $id = $row->id;                       
         print '<li>
                    <a href="wrapper_review.php?id=' . $id . '">
                    <h1 class="smallh1">' . $title . '</h1>
                    <img src="' . $tn_src . '" alt="' . $title . '" id="' . $id . '" />
                    </a>
                    <p class="smalldesc">' . $d . '</p>                    
                </li>';                                  
        print "\n";              
        }     
    } 
}          
?>

[Owz2004]

Thank you very much...

That worked.. Can you explain to me why you needed to use that $category_clean variable?

If I want to have a category specific page i.e valentines.php which could be accessed through a tab in the navigation...

Would I use the same code but rather than use the drop down. I would change the query string to something along the lines of:

$q = "SELECT id, title, description, tn_src FROM wrappers WHERE category='" valentines "' ORDER BY id desc"; 

[Chameleon]

the $category_Clean variable is just a reminder to the coder that your using a variable thats already escaped.(its up to you, dont have to use it.)

I think you can even do it this way if you dont want to make another variable.

<?php 
require 'config.php'; 
require    'database.php';   
    
    if(isset($_POST['category'])) {      
    
    $category = $_POST['category'];
    
    $q = "SELECT id, title, description, tn_src FROM wrappers WHERE category='" . $mysqli->real_escape_string($category) . "' ORDER BY id desc";
    $result = $mysqli->query($q) or die($mysqli_error($mysqli));           
        
        if($result) {         
            while($row = $result->fetch_object()) {             
                $title = $row->title;             
                $d = $row->description;             
                $category = $row->category;             
                $tn_src = $row->tn_src;             
                $src = $row->src;             
                $id = $row->id;                       
         print '<li>
                    <a href="wrapper_review.php?id=' . $id . '">
                    <h1 class="smallh1">' . $title . '</h1>
                    <img src="' . $tn_src . '" alt="' . $title . '" id="' . $id . '" />
                    </a>
                    <p class="smalldesc">' . $d . '</p>                    
                </li>';                                  
        print "\n";              
        }     
    } 
}          
?>

if you want to make a page specific result page, the query would be like this:

$q = "SELECT id, title, description, tn_src FROM wrappers WHERE category='valentines' ORDER BY id desc"; 

[Owz2004]

Thank you I managed to get this to work.. Your help is appreciated...