Solved

HTML/PHP search form not working as expected

Posted on 2013-11-13
17
397 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
  • 8
  • 8
17 Comments
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
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 108

Accepted Solution

by:
Ray Paseur earned 500 total points
Comment Utility
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:Daniele Brunengo
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
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 108

Expert Comment

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

Author Comment

by:Daniele Brunengo
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
No I explained badly, but I'll open a new question I guess when I cross over to the dropdown lists.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

What is Node.js? Node.js is a server side scripting language much like PHP or ASP but is used to implement the complete package of HTTP webserver and application framework. The difference is that Node.js’s execution engine is asynchronous and event…
SASS allows you to treat your CSS code in a more OOP way. Let's have a look on how you can structure your code in order for it to be easily maintained and reused.
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
The viewer will learn how to count occurrences of each item in an array.

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now