Link to home
Start Free TrialLog in
Avatar of Daniele Brunengo
Daniele BrunengoFlag for Italy

asked on

HTML/PHP search form not working as expected

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
Avatar of PortletPaul
PortletPaul
Flag of Australia image
have you considered "case sensitivity"?

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

what is the the collation being used?
Avatar of Daniele Brunengo
Daniele Brunengo
Flag of Italy image

ASKER

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.
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image
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

Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image
If you're new to PHP, you might find some good suggestions and learning resources here:
https://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:
https://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.
https://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
Avatar of Daniele Brunengo
Daniele Brunengo
Flag of Italy image

ASKER

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

Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image
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.
Avatar of Daniele Brunengo
Daniele Brunengo
Flag of Italy image

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Daniele Brunengo
Daniele Brunengo
Flag of Italy image

ASKER

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.
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image
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
Avatar of Daniele Brunengo
Daniele Brunengo
Flag of Italy image

ASKER

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.
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image
Yes, that makes sense.  The example here does exactly that.
Avatar of Daniele Brunengo
Daniele Brunengo
Flag of Italy image

ASKER

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?
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image
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).
Avatar of Daniele Brunengo
Daniele Brunengo
Flag of Italy image

ASKER

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?
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image
Examine the $_GET array to see if there is user input.  If it is empty, there is no user input.
Avatar of Daniele Brunengo
Daniele Brunengo
Flag of Italy image

ASKER

No I explained badly, but I'll open a new question I guess when I cross over to the dropdown lists.