Dropdown list - populated by a mysql db to search another mysql table

Beginners - Learning PHP
#1

Hi, i need some help with my dropdown list which is populated by a mysql db. I also have a fully working search text box. What i want to be able to do is when something is selected from the dropdown list i want it to search like the text box and only bring up that data.

query for dropdown list
Dropdownquery.php
[php]<?php

include_once 'db.con.php';

function connect(){
    mysql_connect(DB_HOST,DB_USER,DB_PASS) or die ('Could not connect to database ' .mysql_error());
	mysql_select_db(DB_NAME);
}

function close(){
    mysql_close();
}
function query(){
    $myData = mysql_query("SELECT * FROM publisher");
	while($record = mysql_fetch_array($myData)){
	    echo'<option value="' . $record['publisher'] . '">' . $record['publisher'] . '</option>';
	
	}
     
}

?>[/php]

search query
search.php

[php]<?php
include_once ‘dropdownquery.php’;
connect();

include_once 'db.con.php';
connect();

?>[/php]

[php] <?php
$k = $_GET[‘k’];
$terms = explode(" ", $k);
$query = "SELECT * FROM search WHERE ";

	foreach ($terms as $each){	
        $i++;
		if ($i == 1)
		    $query .= "keywords LIKE '%$each%' ";
		else
            $query .= "OR keywords LIKE '%$each%' ";
		
	}
	
	// connect
	mysql_connect(DB_HOST,DB_USER,DB_PASS);
	mysql_select_db("search");
	
	$query = mysql_query($query);
	$numrows = mysql_num_rows($query);
	if ($numrows > 0){
	    
		while ($row = mysql_fetch_assoc($query)){
		    $id = $row['id'];
			$title = $row['title'];
			$description = $row['description'];
			$keywords = $row['keywords'];
			$link = $row['link'];
			
			echo "<br /><h3><a href='$link'>$title</a></h3>
			$description";
	    }
	
	}
	else
	    echo "No results found for \"<b>$k</b>\"";
		
	//disconnect
	mysql_close();
	

?>[/php]

Form Code

[code]

    <select name="dropdown" style="font-family: 'ariel'; font-size: 17px; width: 190px; height: 27px; position:absolute; left: 385px; top: 251px">
		<?php query() ?>
	   </select>
        <?php close() ?>
    
      <input type="text" name="k" size="50" onfocus="if (this.name=='k') this.name=''; value="<?php echo $_GET["k"]; ?>" style="width: 203px; position:absolute; left: 586px; top: 251px; height: 27px; right: 567px; border:thin red solid" />
<input id="button" type="submit" value="Search" style="position:absolute; left: 800px; top: 251px; height: 27px;" />
<input type="reset" id="button" style="position:absolute; left: 873px; top: 251px; height: 27px; width: 57px; value="Reset"/>
[/code]

Ive been trying to get this to work for ages if anyone can help that would be great thanks.

#2

  1. To start with, you should be using PDO or mysqli

  2. In Dropdownquery.php - You dont need the close function. Mysql automatically closes when the script is done.
    2a. Same file - function connect() should not be in a function. the code within it should be in db.con.php. When you include db.con you will have your connection

2b. Same file- function query() does not need to be a function. If you were using that same EXACT code in more than one place, then it *could be useful.

3.search.php - You are doing a double connect with your not needed connect function. Just include the db file and you will be connected

  1. the next unamed file repeats your database connection parameters which should only be done once in the db connect file. Just include that file if you need a connection

  2. Form code - Anyone can inject anything they want in your page here: <?php echo $_GET[“k”]
    <input type=“text” name=“k” size=“50” onfocus=“if (this.name==‘k’) this.name=’’; value=”<?php echo $_GET["k"]; ?>

  3. You have no protection whatsoever against sql injection.

  4. In your query’s, you should only grab the columns you need instead of SELECT *

  5. In your search query you have a duplicate query

When you make all the changes, come back with the results of what you did and any errors if any. Most important part is the PDO.

  • To be honest with you, this really needs to be started from scratch. There really isn’t any good usable code here.
#3

Here is a bump start to your PDO rebuild.

  • TO EVERY POSTER USING OLD MYSQL_* CALLS: You now have no excuse to not use PDO. The bulk of what you need is right here.

Get Code here:http://www.phphelp.com/forum/the-occasional-tutorial/beginners-pdo-bumpstart-code-use-it-now!

#4

Hi, thanks for the reply, im trying my best to try and get this to work but im getting these errors come up.

The Log directory at \ is not writable
The error log file does not exist at

Warning: Cannot modify header information - headers already sent by (output started at C:\xampp\htdocs\search2.php:1) in C:\xampp\htdocs\config.php on line 9

Notice: Undefined index: id in C:\xampp\htdocs\search2.php on line 8
SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘id’ in ‘where clause’ in C:\xampp\htdocs\search2.php on line 9
SQL: [50] SELECT publisher FROM publisher WHERE id=publisher Params: 1 Key: Position #0: paramno=0 name=[0] “” is_param=1 param_type=2
Fatal Error! Admin has been notified

database.php
[php]<?php
//-----------------------------------------------------------------------------
// Database Connection
//-----------------------------------------------------------------------------

// Connection data (server_address, database, username, password)
$dbhost = ‘localhost’;
$dbname = ‘search’;
$dbuser = ‘root’;
$dbpass = ‘’;

try
{
$pdo = new PDO(“mysql:host=$dbhost; dbname=$dbname”, $dbuser, $dbpass);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch (PDOException $e)
{
$error_msg = $e->getMessage() . ’ in ’ . $e->getFile() . ’ on line ’ . $e->getLine();

 echo '<div class="error_custom">' . $error_msg . '</div>';
 echo '<div class="error_custom">';
 echo '' . $stmt->debugDumpParams() . '';
 echo '</div>';
 }

?>[/php]

config.php
[php]<?php
$path = realpath(dirname(FILE)) . DIRECTORY_SEPARATOR;
require_once($path . “config/functions.php”);
require_once($path . “config/database.php”);

//SECURITY STUFF

// Block site from third party frames
header(‘X-Frame-Options: SAMEORIGIN’);

// HttpOnly is an additional flag included in a Set-Cookie HTTP response header.
// Using the HttpOnly flag when generating a cookie helps mitigate the risk of a
// client side script accessing the protected cookie (if the browser supports it).
ini_set(‘session.cookie_httponly’, 1);

//----------------------------------------------------------------------------
// Set Error Reporting
//----------------------------------------------------------------------------

error_reporting(E_ALL|E_STRICT);
ini_set(‘display_errors’, ‘1’);

//----------------------------------------------------------------------------
// Set Admin Email
//----------------------------------------------------------------------------

$email_admin = ‘[email protected]’;
$email_from = ‘[email protected]’;

//----------------------------------------------------------------------------
// Dates
//----------------------------------------------------------------------------

// Set Timezone
date_default_timezone_set(‘America/Los_Angeles’);

// MySQL. Format: 2010-07-15 16:33:56
$mysql_datetime = date(“Y-m-d H:i:s”);

//----------------------------------------------------------------------------
// Options
//----------------------------------------------------------------------------

$tableprefix=’’;

$url_website = ‘thiswebsite.com’;

define(“DEBUG”,1); // Display errors, Sql & Sql Parameters. - 0=Off, 1=On

//----------------------------------------------------------------------------
// Error Handling
//----------------------------------------------------------------------------

define(“EMAIL_ERROR”,1); // Email errors to $email_admin. - 0=Off, 1=On
define(“LOG_ERROR”,0); // Log error to file - 0=Off, 1=On

// Error log filename - Use uncommon name for security
$error_log_filename = ‘error.log’;

// Server path to this directory used for error log. Recommend setting error log outside web root dir for security. Default realpath(’.’)
$realpath = realpath(’.’);

$log_directory=“logs”;
$errorlog_path = “$realpath”.DIRECTORY_SEPARATOR."$log_directory".DIRECTORY_SEPARATOR."$error_log_filename"; # Path To Error Log & log filename

//----------------------------------------------------------------------------
// Check Error Log
//----------------------------------------------------------------------------

define(“CHECK_SETUP”,1); // Makes sure error log is writeable. Set to 1 to check

?>[/php]

search2.php (anyPageYouWant.php)
[php] <?php
try
{
require_once("./config.php");
$sql = “SELECT search, publisher FROM publisher WHERE id=publisher”;
$stmt = $pdo->prepare($sql);
$stmt->execute(array(
$_POST[‘id’]
));

 $result = $stmt->fetchAll();
 
 if (count($result))
     {
     foreach ($result as $row)
         {
         echo '<pre>';
         print_r($row);
         echo '</pre>';
         }
     }
 else
     {
     echo "No rows returned.";
     }
 }

catch (PDOException $e)
{
include_once(’./config/pdo_catch_error.php’);
}
?>[/php]

#5

You need to modify the query’s and the parameters to match your database. If you post your database schema I can help you better. This will be really simple once I see your database design.

For now, turn off the check setup

Change the 1 to 0 like so:

[php] define(“CHECK_SETUP”,0); // Makes sure error log is writeable. Set to 1 to check[/php]

What is the unique id name for the publishers?

You are not doing the query correctly. See changes below: (Assumes your unique publisher column is named publisher.)

[php] $sql = “SELECT search, publisher FROM publisher WHERE publisher=?”;
$stmt = $pdo->prepare($sql);
$stmt->execute(array(
publishers_name_here
));[/php]

Also, just for now, add two slashes to this line like so:

[php]//header(‘X-Frame-Options: SAMEORIGIN’);[/php]

#6

how do i post my database schema?

#7

Just paste it in here in a code block

If you need to get it the easiest way is to go into phpmyadmin, export the database / tables in question, and copy/paste it from there

#8

If your still not sure what to do, just post each table name and each column name in that table. I really just need the names of all the columns.

From what you posted, it appears you have at least one table named publisher with the column names search and publisher. What are the rest of them?

#9

Hope this helps

[code]-- phpMyAdmin SQL Dump
– version 3.2.4
– http://www.phpmyadmin.net

– Host: localhost
– Generation Time: Feb 19, 2014 at 11:00 PM
– Server version: 5.1.41
– PHP Version: 5.3.1

SET SQL_MODE=“NO_AUTO_VALUE_ON_ZERO”;

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT /;
/!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS /;
/!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION /;
/!40101 SET NAMES utf8 */;

–
– Database: search

–
– Table structure for table publisher

CREATE TABLE IF NOT EXISTS publisher (
publisher varchar(250) NOT NULL,
PRIMARY KEY (publisher),
UNIQUE KEY publisher (publisher)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

–
– Dumping data for table publisher

INSERT INTO publisher (publisher) VALUES
(‘12-Gauge Comics’),
(‘215 Ink’),
('Aardvark-Vanaheim '),
(‘Abacus Comics’),
(‘About Comics’),
(‘AC Comics’),
(‘Academy Comics’),
(‘ACE Comics’),
(‘Action Lab Entertainment’),
(‘AdHouse Books’),
(‘Adventure Publications’),
(‘All Star DC Comics’),
(‘Alternative Comics’),
(‘Big Bang Comics’),
(‘Caliber Comics’),
(‘CrossGen Entertainment’),
(‘Dark Horse’),
(‘Dark Horse Manga’),
(‘DC’),
(‘Disney Comics’),
(‘Dragon Lady Press’),
('IDW Publishing '),
(‘Marvel’);

–
– Table structure for table search

CREATE TABLE IF NOT EXISTS search (
id int(11) NOT NULL AUTO_INCREMENT,
value varchar(250) NOT NULL,
title varchar(250) NOT NULL,
description varchar(250) NOT NULL,
keywords text NOT NULL,
link varchar(250) NOT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

–
– Dumping data for table search

INSERT INTO search (id, value, title, description, keywords, link) VALUES
(1, ‘DC’, ‘Blackhawks52’, ‘Blackhawks New 52 2011’, ‘blackhawks,new,52,2011,blackhawks52,blackhawks2011,black,hawks,dc,detective comics,’, ‘search/publisherid/dc/b/blackhawks/blackhawksnew52/blackhawks52.html’),
(2, ‘Dark Horse’, 'Mass Effect: Redemption ', '2010 Mini-Series ', ‘dark horse,dark,horse,mass effect,mass effect redemption, 2010,mini series’, ‘search/publisherid/darkhorse/m/masseffectredem/masseffectredem.html’),
(4, ‘Dark Horse’, ‘Xena Warrior Princess’, ‘Xena warrior princess’, ‘dark horse,xena,princess,warrior,gabrielle’, ‘search/publisherid/darkhorse/x/xena/xwp/xwp.html’),
(3, ‘Marvel’, ‘Women Of Marvel’, ‘Marvel 2011’, ‘marvel,women,of,2011,’, ‘search/publisherid/marvel/w/wom/wom.html’);

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT /;
/!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS /;
/!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
[/code]

#10

Yes it does. The problem with your query is that there is no column named “search” in the publisher table

You had: $sql = "SELECT search, publisher FROM publisher WHERE publisher=?";

Change the query to :

$sql  = "SELECT publisher FROM publisher WHERE publisher=?";

If you want data from the publisher AND the search table, you need to do a join query.

You really should have an auto-increment column in the publisher table and use that for your unique id. Having everything named the same can be confusing. Better is:
Table name: publishers
Auto Increment column name: id or publisher_id
Data column as you have it: publisher

Search table is ok except the column “value” is vague and non-descriptive, but it will work. Some of your indexed publisher data has spaces in it which will cause you problems. Your insert scripts need to use trim to remove the trailing and leading spaces.

Use this to remove the spaces:
[php] //---------------------------------------------------------------------------------
// Trim $_POST Array
//---------------------------------------------------------------------------------

$_POST = array_map('trim', $_POST);[/php]
#11

Ok so i made a new table and named it publishers with publisher and publisher_id

[code]-- phpMyAdmin SQL Dump
– version 3.2.4
– http://www.phpmyadmin.net

– Host: localhost
– Generation Time: Feb 20, 2014 at 03:22 PM
– Server version: 5.1.41
– PHP Version: 5.3.1

SET SQL_MODE=“NO_AUTO_VALUE_ON_ZERO”;

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT /;
/!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS /;
/!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION /;
/!40101 SET NAMES utf8 */;

–
– Database: search

–
– Table structure for table publishers

CREATE TABLE IF NOT EXISTS publishers (
publisher varchar(255) NOT NULL,
publisher_id int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (publisher_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

–
– Dumping data for table publishers

INSERT INTO publishers (publisher, publisher_id) VALUES
(‘DC’, 1),
(‘Marvel’, 2),
(‘Dark Horse’, 3),
('Dark Horse Manga ', 4),
(‘Disney Comics’, 5),
(‘Dragon Lady Press’, 6);

–
– Table structure for table search

CREATE TABLE IF NOT EXISTS search (
id int(11) NOT NULL AUTO_INCREMENT,
title varchar(250) NOT NULL,
description varchar(250) NOT NULL,
keywords text NOT NULL,
link varchar(250) NOT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

–
– Dumping data for table search

INSERT INTO search (id, title, description, keywords, link) VALUES
(1, ‘Blackhawks52’, ‘Blackhawks New 52 2011’, ‘blackhawks,new,52,2011,blackhawks52,blackhawks2011,black,hawks,dc,detective comics,’, ‘search/publisherid/dc/b/blackhawks/blackhawksnew52/blackhawks52.html’),
(2, 'Mass Effect: Redemption ', '2010 Mini-Series ', ‘dark horse,dark,horse,mass effect,mass effect redemption, 2010,mini series’, ‘search/publisherid/darkhorse/m/masseffectredem/masseffectredem.html’),
(4, ‘Xena Warrior Princess’, ‘Xena warrior princess’, ‘dark horse,xena,princess,warrior,gabrielle’, ‘search/publisherid/darkhorse/x/xena/xwp/xwp.html’),
(3, ‘Women Of Marvel’, ‘Marvel 2011’, ‘marvel,women,of,2011,’, ‘search/publisherid/marvel/w/wom/wom.html’);

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT /;
/!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS /;
/!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
[/code]

I took away the “value” from search

Im not sure if iv done the trim $_POST right, but here it is.

<?php try { require_once("./config.php"); //--------------------------------------------------------------------------------- // Trim $_POST Array //--------------------------------------------------------------------------------- $_POST = array_map('trim', $_POST); $sql = "SELECT publisher FROM publishers WHERE publisher=?"; $stmt = $pdo->prepare($sql); $stmt->execute(array( $_POST['publisher'] ));

And im getting this again when i try to test it.

The Log directory at \ is not writable
The error log file does not exist at

Notice: Undefined index: publisher in C:\xampp\htdocs\search2.php on line 12
No rows returned.

#12

I previously told you how and where to turn off the log message. So now you want to select the publisher ID. Its standard practice that the id field is always the first column. After you are getting results switch it around

To test do this:

//Temporary
$_POST[‘publisher_id’]=4;

    $sql  = "SELECT publisher FROM publishers WHERE publisher_id=?";
   $stmt = $pdo->prepare($sql);
   $stmt->execute(array(
       $_POST['publisher_id']
#13

I updated your table for you. I also renamed the publisher column to publisher_name. It will be less error prone since the only thing distinguishing it from the table name was one “s”. It also is more clear reading what publisher is.

Also removed space after manga.

I would think the data in search has a publisher. You would want to have a foreign key in that table that identifies the publisher

EDIT* See below for newer tables

#14

Here is a better table setup. I added the foreign keys for you. I guessed at the publisher id’s in the search table. Just edit them if the are wrong before you import the new tables

[php]SET FOREIGN_KEY_CHECKS=0;

– Table structure for publishers

DROP TABLE IF EXISTS publishers;
CREATE TABLE publishers (
publisher_id int(11) NOT NULL AUTO_INCREMENT,
publisher_name varchar(255) NOT NULL,
PRIMARY KEY (publisher_id,publisher_name),
KEY publisher_id (publisher_id)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

– Records of publishers

INSERT INTO publishers VALUES (‘1’, ‘DC’);
INSERT INTO publishers VALUES (‘2’, ‘Marvel’);
INSERT INTO publishers VALUES (‘3’, ‘Dark Horse’);
INSERT INTO publishers VALUES (‘4’, 'Dark Horse Manga ');
INSERT INTO publishers VALUES (‘5’, ‘Disney Comics’);
INSERT INTO publishers VALUES (‘6’, ‘Dragon Lady Press’);

– Table structure for search

DROP TABLE IF EXISTS search;
CREATE TABLE search (
search_id int(11) NOT NULL AUTO_INCREMENT,
publisher_id int(11) DEFAULT NULL,
title varchar(250) NOT NULL,
description varchar(250) NOT NULL,
keywords text NOT NULL,
link varchar(250) NOT NULL,
PRIMARY KEY (search_id),
KEY publisher_id (publisher_id),
CONSTRAINT search_ibfk_1 FOREIGN KEY (publisher_id) REFERENCES publishers (publisher_id) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

– Records of search

INSERT INTO search VALUES (‘1’, ‘3’, ‘Blackhawks52’, ‘Blackhawks New 52 2011’, ‘blackhawks,new,52,2011,blackhawks52,blackhawks2011,black,hawks,dc,detective comics,’, ‘search/publisherid/dc/b/blackhawks/blackhawksnew52/blackhawks52.html’);
INSERT INTO search VALUES (‘2’, ‘2’, 'Mass Effect: Redemption ', '2010 Mini-Series ', ‘dark horse,dark,horse,mass effect,mass effect redemption, 2010,mini series’, ‘search/publisherid/darkhorse/m/masseffectredem/masseffectredem.html’);
INSERT INTO search VALUES (‘3’, ‘2’, ‘Women Of Marvel’, ‘Marvel 2011’, ‘marvel,women,of,2011,’, ‘search/publisherid/marvel/w/wom/wom.html’);
INSERT INTO search VALUES (‘4’, ‘3’, ‘Xena Warrior Princess’, ‘Xena warrior princess’, ‘dark horse,xena,princess,warrior,gabrielle’, ‘search/publisherid/darkhorse/x/xena/xwp/xwp.html’);[/php]

#15

I have imported the new table and everything seems to work fine. I did do what you told me previously to do about changing the “1” to “0” on the define(“CHECK_SETUP”,0); but i am still getting the two messages,

The Log directory at \ is not writable
The error log file does not exist at
Array
(
[publisher_name] => Dark Horse Manga
[0] => Dark Horse Manga
)

apart from that everything you told me to do has worked ok.

#16

I made a mistake in the code

Change this:

if (CHECK_SETUP == 0)
{
check_setup();
}

TO

if (CHECK_SETUP == 1)
{
check_setup();
}

#17

ok i have done that and its gone now.

#18

Here is the css that goes with my files to give you nice colored output of messages. Images that go with it attached.

styles.css
[php]
.info, .success, .warning, .error_custom, .validation {
border: 1px solid;
margin: 10px 0px;
padding:15px 10px 15px 50px;
background-repeat: no-repeat;
background-position: 10px center;
}

.info {
color: #00529B;
background-color: #BDE5F8;
background-image: url(’…/…/img/info.png’);
}

.success {
color: #4F8A10;
background-color: #DFF2BF;
background-image:url(’…/…/img/validgreen.png’);
}

.warning {
color: #9F6000;
background-color: #FEEFB3;
background-image: url(’…/…/img/attention.png’);
}

.error_custom {
color: #D8000C;
background-color: #FFBABA;
background-image: url(’…/…/img/cancel.png’);
}[/php]

attention.png

cancel.png

validgreen.png

info.png

#19

Thanks for that but im not sure how to put the css style into php, i know how to do it with html but have never done it with php before.

#20

You dont do it with php. You do it exactly as you do with html. Add a link to the stylesheet in your tag.

[code]

[/code]
Sponsor our Newsletter | Privacy Policy | Terms of Service