?
Solved

HTML/PHP search form not working as expected

Posted on 2013-11-13
17
Medium Priority
?
425 Views
Last Modified: 2013-11-14
Hello, I have built my first html/php search form, it searches inside a mysql archive.

The archive contains items with a Name (Nome), Country (Paese), City (Città) and Area. There are other values but they don't matter right now.

I have 5 items:

    Iberflora - Valencia - Spagna - Europa
    Intergift - Madrid - Spagna - Europa
    Foodex Japan - Chiba - Giappone - Asia
    Jewels China - Hong Kong - Cina - Asia
    UsFauna - New York - USA - Nordamerica

There are two search fields, one for the area and the other for the country.

I am experimenting and I can't understand why this code will produce these results:

    "asia" + "" => 2 results, correct
    "europa" + "" => 2 results, correct
    "europa" + "spagna" => 2 results, correct
    "" + "spagna" => 2 results, correct

All other combinations, such as "nordamerica" + "" or "asia" + "cina" give 0 results, which is obviously wrong, and I can't for the life of me understand why.

I think this is a database issue, since the code works for some items and not for others, but I may be mistaken.

So this is the code and the table is attached in sql form, I hope someone can help me.

<?php
error_reporting(E_ALL);
ini_set('display_errors', '1');
$search_output = "";
if((isset($_POST['zonequery']) && $_POST['zonequery'] != "") || (isset($_POST['countryquery']) && $_POST['countryquery'] != "")){
$zonequery = preg_replace('#[^a-z 0-9?!]#i', '', $_POST['zonequery']);
$countryquery = preg_replace('#[^a-z 0-9?!]#i', '', $_POST['countryquery']);

		$sqlCommand = "SELECT Area, Paese, Città, Nome FROM fiere WHERE Area LIKE '%$zonequery%' AND Paese LIKE '%$countryquery%'";		

        // Connect to your MySQL database here
		// Create connection
		$link=mysqli_connect("localhost","root","","fiere");

		// Check connection
		if (mysqli_connect_errno($link))
		  {
		  echo "Failed to connect to MySQL: " . mysqli_connect_error();
		  }
        $query = mysqli_query($link, $sqlCommand) or die(mysql_error());
$count = mysqli_num_rows($query);
if($count > 1){
$search_output .= "<hr />$count risultati per <strong>$zonequery</strong> o <strong>$countryquery</strong><hr />$sqlCommand<hr />";
while($row = mysqli_fetch_array($query)){
   $zone = $row["Area"];
   $country = $row["Paese"];
   $city = $row["Città"];
   $name = $row["Nome"];
   $search_output .= "Fiera: $name - $city - $country - $zone<hr />";
                } // close while
} else {
$search_output = "<hr />0 risultati per <strong>$zonequery</strong> o <strong>$countryquery</strong><hr />$sqlCommand";
}
}
?>
<html>
<head>
</head>
<body>
<h2>Ma quante belle fiere Madama Doré</h2>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
Cerca Area: 
  <input name="zonequery" type="text" size="44" maxlength="88">
  <br />
Cerca Paese:
  <input name="countryquery" type="text" size="44" maxlength="88">
  <br />
<input name="myBtn" type="submit">
<br />
</form>
<div>
<?php echo $search_output; ?>
</div>
</body>
</html>

Open in new window


fiere.sql
0
Comment
Question by:Daniele Brunengo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 8
17 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39644134
have you considered "case sensitivity"?

if "case sensitive":
Cina <> cina
nordamerica <> Nordamerica

what is the the collation being used?
0
 

Author Comment

by:Daniele Brunengo
ID: 39644180
I've tried both, but I think the lines here:

$zonequery = preg_replace('#[^a-z 0-9?!]#i', '', $_POST['zonequery']);
$countryquery = preg_replace('#[^a-z 0-9?!]#i', '', $_POST['countryquery']);

are making everything case insensitive?

The collation is latin1_swedish_ci and I've also tried utf8_unicode_ci but nothing changed.

Also, I have just tried with another database built from scratch and there's no change, so it may be a code issue after all.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39644880
Since this is an information-only script and does not change the data model, it should be a GET-method request.  I'll set up a test case and try to show how this should be done.

Here is what the REGEX means:

#    REGEX DELIMITER
[    START CHARACTER CLASS
^    NEGATION - MATCH NONE OF THESE CHARACTERS
a-z  LETTERS OF THE ALPHABET
     THE BLANK
0-9  NUMBERS
?    THE QUESTION MARK
!    THE EXCLAMATION POINT
]    END OF THE CHARACTER CLASS
#    REGEX DELIMITER
i    FLAG: CASE INSENSITIVE

Open in new window

0
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39644995
If you're new to PHP, you might find some good suggestions and learning resources here:
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11769-And-by-the-way-I-am-new-to-PHP.html

To learn more about how to use the "new" MySQL extensions, look here:
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

To understand why you should not be using the "fetch_array()" statement, see Antipractice #26.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_12293-AntiPHPatterns-and-AntiPHPractices.html

Please see: http://www.laprbass.com/RAY_temp_mayo.php?z=eur 
Experiment with different query strings.  The moving parts start at line 200.

<?php // RAY_temp_mayo.php
error_reporting(E_ALL);
ini_set('display_errors', TRUE);


/* PROBLEM: http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/Q_28292807.html#a39644880

<?php
error_reporting(E_ALL);
ini_set('display_errors', '1');
$search_output = "";
if((isset($_POST['zonequery']) && $_POST['zonequery'] != "") || (isset($_POST['countryquery']) && $_POST['countryquery'] != "")){
$zonequery = preg_replace('#[^a-z 0-9?!]#i', '', $_POST['zonequery']);
$countryquery = preg_replace('#[^a-z 0-9?!]#i', '', $_POST['countryquery']);

        $sqlCommand = "SELECT Area, Paese, Città, Nome FROM fiere WHERE Area LIKE '%$zonequery%' AND Paese LIKE '%$countryquery%'";

        // Connect to your MySQL database here
        // Create connection
        $link=mysqli_connect("localhost","root","","fiere");

        // Check connection
        if (mysqli_connect_errno($link))
          {
          echo "Failed to connect to MySQL: " . mysqli_connect_error();
          }
        $query = mysqli_query($link, $sqlCommand) or die(mysql_error());
$count = mysqli_num_rows($query);
if($count > 1){
$search_output .= "<hr />$count risultati per <strong>$zonequery</strong> o <strong>$countryquery</strong><hr />$sqlCommand<hr />";
while($row = mysqli_fetch_array($query)){
   $zone = $row["Area"];
   $country = $row["Paese"];
   $city = $row["Città"];
   $name = $row["Nome"];
   $search_output .= "Fiera: $name - $city - $country - $zone<hr />";
                } // close while
} else {
$search_output = "<hr />0 risultati per <strong>$zonequery</strong> o <strong>$countryquery</strong><hr />$sqlCommand";
}
}
?>
<html>
<head>
</head>
<body>
<h2>Ma quante belle fiere Madama Doré</h2>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
Cerca Area:
  <input name="zonequery" type="text" size="44" maxlength="88">
  <br />
Cerca Paese:
  <input name="countryquery" type="text" size="44" maxlength="88">
  <br />
<input name="myBtn" type="submit">
<br />
</form>
<div>
<?php echo $search_output; ?>
</div>
</body>
</html>

*/

// THIS SCRIPT DEMONSTRATES SOME OF THE BASICS OF MySQLi


// THE ABSOLUTE MINIMUM YOU MUST UNDERSTAND TO USE PHP AND MYSQLI
// MAN PAGE: http://php.net/manual/en/mysqli.overview.php
// MAN PAGE: http://php.net/manual/en/class.mysqli.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-stmt.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-result.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-warning.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-sql-exception.php <-- DID NOT WORK PHP 5.3+, MySQL 5.1+
// MAN PAGE: http://php.net/manual/en/mysqli.construct.php
// MAN PAGE: http://php.net/manual/en/mysqli.real-escape-string.php
// MAN PAGE: http://php.net/manual/en/mysqli.query.php
// MAN PAGE: http://php.net/manual/en/mysqli.errno.php
// MAN PAGE: http://php.net/manual/en/mysqli.error.php
// MAN PAGE: http://php.net/manual/en/mysqli.insert-id.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.num-rows.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.fetch-array.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.fetch-object.php


// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}

// ACTIVATE THIS TO SHOW WHAT THE DB CONNECTION OBJECT LOOKS LIKE
// var_dump($mysqli);

$sql
=
"
CREATE TEMPORARY TABLE `fiere` (
  `fiere_id2` int(11) NOT NULL AUTO_INCREMENT,
  `Area` varchar(12) DEFAULT NULL,
  `Paese` varchar(12) DEFAULT NULL,
  `Città` varchar(12) NOT NULL,
  `Sigla` varchar(255) NOT NULL,
  `Nome` varchar(12) NOT NULL,
  `Anno` int(11) NOT NULL,
  `Mese` varchar(12) NOT NULL,
  `Data_Inizio` datetime NOT NULL,
  `Data_Termine` datetime NOT NULL,
  `Macrocategoria` varchar(25) NOT NULL,
  `Categoria` varchar(45) NOT NULL,
  `Ente` varchar(12) NOT NULL,
  `Indirizzo` varchar(25) NOT NULL,
  `Telefono` varchar(12) NOT NULL,
  `Fax` varchar(12) NOT NULL,
  `Web` varchar(45) NOT NULL,
  `Email` varchar(45) NOT NULL,
  `Orario` varchar(12) NOT NULL,
  `Periodicità` varchar(12) NOT NULL,
  `Visitatori` int(11) NOT NULL,
  `Espositori_Nazionali` varchar(5) NOT NULL,
  `Espositori_Stranieri` varchar(5) NOT NULL,
  PRIMARY KEY (`fiere_id2`),
  KEY `Anno_idx` (`Anno`),
  KEY `Visitatori_idx` (`Visitatori`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6
"
;
// RUN THE QUERY TO CREATE THE TABLE
$res = $mysqli->query($sql);

// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

//  ACTIVATE THIS TO SHOW THE RESULTS OF THE QUERY
// var_dump($res);

$sql
=
"
INSERT INTO `fiere` (`fiere_id2`, `Area`, `Paese`, `Città`, `Sigla`, `Nome`, `Anno`, `Mese`, `Data_Inizio`, `Data_Termine`, `Macrocategoria`, `Categoria`, `Ente`, `Indirizzo`, `Telefono`, `Fax`, `Web`, `Email`, `Orario`, `Periodicità`, `Visitatori`, `Espositori_Nazionali`, `Espositori_Stranieri`) VALUES
(1, 'Europa', 'Spagna', 'Valencia', '', 'Iberflora', 2013, 'Ottobre', '2013-10-02 00:00:00', '2013-10-04 00:00:00', 'Agricoltura', 'Floricoltura - Orticoltura - Vivaismo', 'Iberflora', 'Via Prova 11', '33333333', '22222222', 'www.iberflora.feriavalencia.com', 'feriavalencia@feriavalencia.com', '8:30-19:30', 'Annuale', 12312, 'Sì', 'Sì'),
(2, 'Europa', 'Spagna', 'Madrid', '', 'Intergift', 2014, 'Febbraio', '2014-02-05 00:00:00', '2014-02-09 00:00:00', 'Moda e Persona', 'Articoli da regalo', 'Intergift', 'Via Try 22', '11111111', '22334411', 'www.ifema.es', 'madrid@prova.com', '8:00-20:00', 'Biennale', 4444, 'Sì', 'Sì'),
(3, 'Asia', 'Giappone', 'Chiba', '', 'Foodex Japan', 2010, 'Marzo', '2010-03-02 00:00:00', '2010-03-05 00:00:00', 'Alimentari', 'Ristorazione', 'Foodex', 'Via JP 12', '33-123443', '14-234123', 'www.jma.or.jp/foodex/', 'tokyo@prova.com', '8:30-19:30', 'Biennale', 32131, 'Sì', 'Sì'),
(4, 'Asia', 'Cina', 'Hong Kong', '', 'Jewels China', 2013, 'Dicembre', '2013-12-20 00:00:00', '2013-12-23 00:00:00', 'Gioielleria e Oreficeria', '', 'Jewels', 'Via Hong 21', '123123', '214523452', 'www.prova.pr', 'hongkong@prova.com', '8:00-20:00', 'Biennale', 1212, 'Sì', 'Sì'),
(5, 'Nordamerica', 'USA', 'New York', '', 'UsFauna', 2014, 'Febbraio', '2014-02-19 00:00:00', '2014-02-25 00:00:00', 'Agricoltura', 'Allevamenti', 'Fauna', 'Oxford St. 22', '78777777', '54456564', 'www.prova.pr', 'ny@prova.com', '8:00-20:00', 'Annuale', 44442, 'Sì', 'Sì');
"
;
// RUN THE QUERY TO LOAD THE TABLE
$res = $mysqli->query($sql);

// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

//  ACTIVATE THIS TO SHOW THE RESULTS OF THE QUERY
// var_dump($res);



// IF THERE IS A REQUEST
if (!empty($_GET['z']))
{
    // PREPARE THE EXTERNAL DATA FOR SAFE USE IN A QUERY
    $z   = $mysqli->real_escape_string($_GET['z']);

    // CREATE THE QUERY AND RUN IT
    $sql = "SELECT Area, Paese, Città, Nome FROM fiere WHERE Area LIKE '%$z%'";
    if (!$res = $mysqli->query($sql))
    {
        $err
        = 'QUERY FAILURE:'
        . ' ERRNO: '
        . $mysqli->errno
        . ' ERROR: '
        . $mysqli->error
        . ' QUERY: '
        . $sql
        ;
        trigger_error($err, E_USER_ERROR);
    }

    // SHOW THE RESULTS, IF ANY
    while ($row = $res->fetch_object())
    {
        echo '<pre>';
        print_r($row);
        echo '</pre>';
        echo PHP_EOL;
    }
}

// CREATE A FORM TO RECEIVE THE CLIENT REQUEST
$crf = <<<EOD
<form>
Area: <input name="z" placeholder="Continent?" />
<input type="submit" />
</form>
EOD;
echo $crf;

Open in new window

HTH, ~Ray
0
 

Author Comment

by:Daniele Brunengo
ID: 39645222
Yes, the Regex should allow only those characters and blank out the others and make everything case insensitive.

Thanks a lot for the links and the wonderful example script.

One question: is this strictly php 5.5? I have 5.4 on my web server and I get an internal server error, whereas it works on my local server.

Anyway, building on your example I've come up with this and it works better but there's still a problem:

if the country is left blank, the search turns up nothing, if the area is left blank you still get the list of results for the country you chose (which is the expected behavior).

<?php

$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "fiere";
$db_user = "root";
$db_word = "";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}

// IF THERE IS A REQUEST
if (!empty($_GET['zonequery']) || !empty($_GET['countryquery']))
{
    // PREPARE THE EXTERNAL DATA FOR SAFE USE IN A QUERY
    $zonequery   = $mysqli->real_escape_string($_GET['zonequery']);
	$countryquery   = $mysqli->real_escape_string($_GET['countryquery']);

    // CREATE THE QUERY AND RUN IT
    $sql = "SELECT Area, Paese, Città, Nome FROM fiere WHERE Area LIKE '%$zonequery%' AND Paese LIKE '$countryquery'";
    if (!$res = $mysqli->query($sql))
    {
        $err
        = 'QUERY FAILURE:'
        . ' ERRNO: '
        . $mysqli->errno
        . ' ERROR: '
        . $mysqli->error
        . ' QUERY: '
        . $sql
        ;
        trigger_error($err, E_USER_ERROR);
    }

    // SHOW THE RESULTS, IF ANY
    while ($row = $res->fetch_object())
    {
        echo '<pre>';
        print_r($row);
        echo '</pre>';
        echo PHP_EOL;
    }
}

// CREATE A FORM TO RECEIVE THE CLIENT REQUEST
$crf = <<<EOD
<form>
Area: <input name="zonequery" placeholder="Area" />
Paese: <input name="countryquery" placeholder="Paese" />
<input type="submit" />
</form>
EOD;
echo $crf;
?>

Open in new window

0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39645261
if the country is left blank, the search turns up nothing,...
That is probably caused by AND in the WHERE clause.  You might try using OR instead.
0
 

Author Comment

by:Daniele Brunengo
ID: 39645542
I've tried both, with OR the Area works but the second input, Paese, doesn't work. Whatever I write as Paese, if I leave Area blank, I get a full list of all 5 items, even if I write random stuff.

If I leave Paese blank Area works though (with OR).

I've fixed my server, so you can check out the effects here:

http://matefisica.it/fiereAND.php

http://matefisica.it/fiereOR.php
0
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 2000 total points
ID: 39645700
What is the effect you want to get?

Maybe something like this will be closer.  Lines 120, et seq.

<?php // RAY_temp_mayo.php
error_reporting(E_ALL);
ini_set('display_errors', TRUE);


// PROBLEM: http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/Q_28292807.html


// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}

// ACTIVATE THIS TO SHOW WHAT THE DB CONNECTION OBJECT LOOKS LIKE
// var_dump($mysqli);

$sql
=
"
CREATE TEMPORARY TABLE `fiere` (
  `fiere_id2` int(11) NOT NULL AUTO_INCREMENT,
  `Area` varchar(12) DEFAULT NULL,
  `Paese` varchar(12) DEFAULT NULL,
  `Città` varchar(12) NOT NULL,
  `Sigla` varchar(255) NOT NULL,
  `Nome` varchar(12) NOT NULL,
  `Anno` int(11) NOT NULL,
  `Mese` varchar(12) NOT NULL,
  `Data_Inizio` datetime NOT NULL,
  `Data_Termine` datetime NOT NULL,
  `Macrocategoria` varchar(25) NOT NULL,
  `Categoria` varchar(45) NOT NULL,
  `Ente` varchar(12) NOT NULL,
  `Indirizzo` varchar(25) NOT NULL,
  `Telefono` varchar(12) NOT NULL,
  `Fax` varchar(12) NOT NULL,
  `Web` varchar(45) NOT NULL,
  `Email` varchar(45) NOT NULL,
  `Orario` varchar(12) NOT NULL,
  `Periodicità` varchar(12) NOT NULL,
  `Visitatori` int(11) NOT NULL,
  `Espositori_Nazionali` varchar(5) NOT NULL,
  `Espositori_Stranieri` varchar(5) NOT NULL,
  PRIMARY KEY (`fiere_id2`),
  KEY `Anno_idx` (`Anno`),
  KEY `Visitatori_idx` (`Visitatori`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6
"
;
// RUN THE QUERY TO CREATE THE TABLE
$res = $mysqli->query($sql);

// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

//  ACTIVATE THIS TO SHOW THE RESULTS OF THE QUERY
// var_dump($res);

$sql
=
"
INSERT INTO `fiere` (`fiere_id2`, `Area`, `Paese`, `Città`, `Sigla`, `Nome`, `Anno`, `Mese`, `Data_Inizio`, `Data_Termine`, `Macrocategoria`, `Categoria`, `Ente`, `Indirizzo`, `Telefono`, `Fax`, `Web`, `Email`, `Orario`, `Periodicità`, `Visitatori`, `Espositori_Nazionali`, `Espositori_Stranieri`) VALUES
(1, 'Europa', 'Spagna', 'Valencia', '', 'Iberflora', 2013, 'Ottobre', '2013-10-02 00:00:00', '2013-10-04 00:00:00', 'Agricoltura', 'Floricoltura - Orticoltura - Vivaismo', 'Iberflora', 'Via Prova 11', '33333333', '22222222', 'www.iberflora.feriavalencia.com', 'feriavalencia@feriavalencia.com', '8:30-19:30', 'Annuale', 12312, 'Sì', 'Sì'),
(2, 'Europa', 'Spagna', 'Madrid', '', 'Intergift', 2014, 'Febbraio', '2014-02-05 00:00:00', '2014-02-09 00:00:00', 'Moda e Persona', 'Articoli da regalo', 'Intergift', 'Via Try 22', '11111111', '22334411', 'www.ifema.es', 'madrid@prova.com', '8:00-20:00', 'Biennale', 4444, 'Sì', 'Sì'),
(3, 'Asia', 'Giappone', 'Chiba', '', 'Foodex Japan', 2010, 'Marzo', '2010-03-02 00:00:00', '2010-03-05 00:00:00', 'Alimentari', 'Ristorazione', 'Foodex', 'Via JP 12', '33-123443', '14-234123', 'www.jma.or.jp/foodex/', 'tokyo@prova.com', '8:30-19:30', 'Biennale', 32131, 'Sì', 'Sì'),
(4, 'Asia', 'Cina', 'Hong Kong', '', 'Jewels China', 2013, 'Dicembre', '2013-12-20 00:00:00', '2013-12-23 00:00:00', 'Gioielleria e Oreficeria', '', 'Jewels', 'Via Hong 21', '123123', '214523452', 'www.prova.pr', 'hongkong@prova.com', '8:00-20:00', 'Biennale', 1212, 'Sì', 'Sì'),
(5, 'Nordamerica', 'USA', 'New York', '', 'UsFauna', 2014, 'Febbraio', '2014-02-19 00:00:00', '2014-02-25 00:00:00', 'Agricoltura', 'Allevamenti', 'Fauna', 'Oxford St. 22', '78777777', '54456564', 'www.prova.pr', 'ny@prova.com', '8:00-20:00', 'Annuale', 44442, 'Sì', 'Sì');
"
;
// RUN THE QUERY TO LOAD THE TABLE
$res = $mysqli->query($sql);

// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

//  ACTIVATE THIS TO SHOW THE RESULTS OF THE QUERY
// var_dump($res);


// INITIAL STATE OF THE REQUEST VARIABLES
$z = $c = NULL;

// IF THERE IS A REQUEST FOR "Area"
if (!empty($_GET['z']))
{
    // PREPARE THE EXTERNAL DATA FOR SAFE USE IN A QUERY
    $z   = $mysqli->real_escape_string($_GET['z']);
}
// IF THERE IS A REQUEST FOR "Paesa"
if (!empty($_GET['c']))
{
    // PREPARE THE EXTERNAL DATA FOR SAFE USE IN A QUERY
    $c   = $mysqli->real_escape_string($_GET['c']);
}

// IF THERE IS ONLY "Area"
if ( (empty($c)) && (!empty($z)) )
{
    $sql = "SELECT Area, Paese, Città, Nome FROM fiere WHERE Area LIKE '%$z%'";
}

// IF THERE IS ONLY "Paesa"
elseif ( (!empty($c)) && (empty($z)) )
{
    $sql = "SELECT Area, Paese, Città, Nome FROM fiere WHERE Paese LIKE '%$c%'";
}

// IF BOTH "Area" and "Paesa"
elseif ( (!empty($c)) && (!empty($z)) )
{
    $sql = "SELECT Area, Paese, Città, Nome FROM fiere WHERE Area LIKE '%$z%' AND Paese LIKE '%$c%'";
}

// IF NO USEFUL REQUEST
else
{
    $sql = NULL;
}

if ($sql)
{
    if (!$res = $mysqli->query($sql))
    {
        $err
        = 'QUERY FAILURE:'
        . ' ERRNO: '
        . $mysqli->errno
        . ' ERROR: '
        . $mysqli->error
        . ' QUERY: '
        . $sql
        ;
        trigger_error($err, E_USER_ERROR);
    }

    // SHOW THE RESULTS, IF ANY
    if ($res->num_rows)
    {
        while ($row = $res->fetch_object())
        {
            echo '<pre>';
            print_r($row);
            echo '</pre>';
            echo PHP_EOL;
        }
    }
    else
    {
        echo "NO RESULTS";
    }
}

// CREATE A FORM TO RECEIVE THE CLIENT REQUEST
$crf = <<<EOD
<form>
Area: <input name="z" placeholder="Continent?" />
Paesa: <input name="c" placeholder="Country?" />
<input type="submit" />
</form>
EOD;
echo $crf;

Open in new window

HTH, ~Ray
0
 

Author Comment

by:Daniele Brunengo
ID: 39646050
I'll check it out and report back, thanks.

Still don't perfectly understand the behavior of the previous version though.

The effect I'm looking for is to have the "search engine" consider a null entry as "Any".

That's because, once I will at least learn how to move around this new stuff, I'll need to make the engine into a series of dropdown menus, each of which will have "Any" as default value.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39646098
I may be missing something, but I don't think you want a null entry to mean "any."  If a null entry means "any" then every query that is not 100% restricted by some specific part of the WHERE clause returns every row of the table.  The way I would do it would be to let a null entry have no effect on the WHERE clause, and let multiple selection variables work with "AND" conditions.  Here's a link:
http://www.laprbass.com/RAY_temp_mayo.php
0
 

Author Comment

by:Daniele Brunengo
ID: 39646200
Yeah, sorry, I forgot to stress that "Any" should be restricted by the other choices you make. So if you choose a country, that's it. If you choose an area, this will show all countries but only from that area.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39646241
Yes, that makes sense.  The example here does exactly that.
0
 

Author Comment

by:Daniele Brunengo
ID: 39648195
It works great, and I think I understood most of it.

Just one question, is there a way to assign a wildcard character to a variable?

For instance, if there is no input in Paese, set that variable to a wildcard, like * for windows searches?
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39648265
SQL queries use the percent sign as a wildcard.  If you wanted to match both Brookville and Brooklyn, your WHERE clause might look like this:

WHERE city LIKE 'Brook%'

But that said, if you simply omit any part of the WHERE clause, you're implying "match everything" based on the other parts of the WHERE clause.  If you omit the WHERE clause entirely, your query applies to every row in the table(s).
0
 

Author Comment

by:Daniele Brunengo
ID: 39648338
Yes, I asked you that because in my final search engine, if you leave all the choices as they are you should get a list of ALL the fairs.

It's the equivalent of leaving everything blank in this example.

So I went with this:

// INITIAL STATE OF THE REQUEST VARIABLES
$zonequery = $countryquery = '%';

// IF THERE IS A REQUEST FOR "Area"
if (!empty($_GET['zonequery']))
{
    // PREPARE THE EXTERNAL DATA FOR SAFE USE IN A QUERY
    $zonequery   = $mysqli->real_escape_string($_GET['zonequery']);
}

// IF THERE IS A REQUEST FOR "Paese"
if (!empty($_GET['countryquery']))
{
    // PREPARE THE EXTERNAL DATA FOR SAFE USE IN A QUERY
    $countryquery   = $mysqli->real_escape_string($_GET['countryquery']);
}

    $sql = "SELECT Area, Paese, Città, Nome FROM fiere WHERE Area LIKE '%$zonequery%' AND Paese LIKE '%$countryquery%'";

Open in new window


which is a little bit shorter too.

The search works. The only problem is, I get a list of all the fairs as soon as the page loads, with no user input, because the script finds empty search queries and fills them with %.

What would be the best way to check if there's been user input or not?
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39648557
Examine the $_GET array to see if there is user input.  If it is empty, there is no user input.
0
 

Author Comment

by:Daniele Brunengo
ID: 39649987
No I explained badly, but I'll open a new question I guess when I cross over to the dropdown lists.
0

Featured Post

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…
Suggested Courses

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question