Php/mysql search from multiple columns

PHP
1

I have already a php page to call my bibliographical database.
Here is the code of the main file:

<?php
/* [SEARCH FOR USERS] */
if (isset($_POST['search'])) {
  require "bibliografie-search.2.php";
}

/* [DISPLAY HTML] */ ?>
    <!-- [SEARCH FORM] -->
    <form method="post" arction="bibliografie-search.2.php">
      <input type="text" name="search" required/>
      <input type="submit" value="cerca"/>
    </form>

    <!-- [SEARCH RESULTS] -->
<?php
         function hash_link_format($key){
           return "<a href=\"hashtag-biblio.php?tag=$key\">$key</a>";
         }
	 function buildLinks($str, $jdel=',' ,$formatFooName='hash_link_format', $del=',' ){
			  return  implode($jdel.' ', array_map($formatFooName, explode($del, $str)));
		 }
    
    if (isset($_POST['search'])) {
      if (count($results) > 0) {
        foreach ($results as $r) {
        $key = explode(',', $r['keywords']);
        //printf("<div><p><b>%s</b></p><h2>%s</h2><blockquote><p>%s</p></blockquote><p>%s</p></div>", $r['autore'], $r['titolo'],  $r['contenuti'], buildLinks($r['keywords']));
        printf("<div><p><b>%s</b>, %s</p>\n<h2>%s</h2>\n<p>%s, %s %s</p>\n<blockquote><p>%s</p></blockquote>\n<p>%s</p></div>", $r['autore'], $r['autore_nome'], $r['titolo'], $r['edizione'], $r['luogo'], $r['data'], $r['contenuti'], /*$r['fonte'], $r['fonte_spec'],*/ buildLinks($r['keywords']));
        }
      } else {
        echo "No results found";
      }
    }
    ?>

and the code of the of the other file:

<?php
// (1) DATABASE CONFIG
// ! CHANGE THESE TO YOUR OWN !
define('DB_HOST', 'localhost');
define('DB_NAME', 'bibliografia');
define('DB_CHARSET', 'utf8');
define('DB_USER', 'myuser');
define('DB_PASSWORD', 'mypsw');

// (2) CONNECT TO DATABASE
try {
  $pdo = new PDO(
    "mysql:host=" . DB_HOST . ";charset=" . DB_CHARSET . ";dbname=" . DB_NAME,
    DB_USER, DB_PASSWORD, [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES => false ]
  );
} catch (Exception $ex) {
  die($ex->getMessage());
}

// (3) SEARCH
$stmt = $pdo->prepare("SELECT * FROM `bibliografie` WHERE `autore` LIKE ? OR `titolo` LIKE ? OR `keywords` LIKE ?");
$stmt->execute(["%" . $_POST['search'] . "%", "%" . $_POST['search'] . "%", "%" . $_POST['search'] . "%"]);
$results = $stmt->fetchAll();
if (isset($_POST['ajax'])) { echo json_encode($results); }
?>

I’d like to add some further option, adding other columns (field), i.g. search 1) a title, 2) an author, 3) a keyword and so on not merging them (as it is now), but choosing where search to.
What code should I use?
Thank you

1 Like
2

Validate the value you receive, and then insert it into the query as a standard string replacement. You cant parameterize the field name.

2 Likes
3

But you can check if the value from the frontend is a valid table column name by using

SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
           WHERE TABLE_NAME='table name' AND column_name=:value
2 Likes
4

uhm…
Thank you, I will try it. Even it quite difficult for my knowledge…

1 Like
5

uhm…
Thank you, I will try it. Even it quite difficult for my knowledge…

EDIT

I wonder why this code works

$stmt = $pdo->prepare("SELECT * FROM `bibliografie` WHERE `autore` LIKE ? OR `titolo` LIKE ? OR `keywords` LIKE ?");

and this other works as well


$stmt = $pdo->prepare("SELECT * FROM `bibliografie` WHERE `autore` LIKE ? OR `titolo` LIKE ? OR `tipologia` LIKE ?");

but this other doesn’t:

$stmt = $pdo->prepare("SELECT * FROM `bibliografie` WHERE `autore` LIKE ? OR `titolo` LIKE ? OR `keywords` LIKE ? OR `tipologia` LIKE ?  OR `reperibilita` LIKE ?");
1 Like
6

If you are asking about submitting the column name(s) to search in the query, you would dynamically build the WHERE part of the sql query statement, after validating that the submitted column name(s) are exactly and only permitted columns, i.e. there may be some columns that you don’t want to be searchable. You can also add a level of indirectness, by assigning numerical indexes, e.g. 1,2,3… to the columns, submitting the index values, instead of the actual column names, then mapping the submitted index(es) to the actual column names.

That your last posted examples show that you aren’t building the sql query statement in a php variable, indicates you are probably trying to write out code for every possibility. This is not what is being suggested.

You would have two arrays, one holds the where terms that will be ORed together and the other holds the input values, including any wild-card characters, for the execute call.

You would loop over an array of the permitted columns, and test if there is a search input for each column. If there is, you would add expressions to both of the arrays.

After the end of the code putting the expressions into these arrays, you would implode() the where terms with the ’ OR ’ keyword, and use this expression after the WHERE … keyword in the sql query. You would use the array of input values as the parameter to the ->execute(…) call.

2 Likes
7

Sorry, thank you, but I don’t understand.
I wonder why in my code I can put only three “where”, and not four or five…

1 Like
8

We cannot answer that question, because you didn’t state what result you got, what result you expected, and only posted a small snippet of code. Best guess is that you didn’t make a corresponding change to the number of values in the array that’s being supplied to the ->execute(…) call, which would be producing either php or pdo errors.

3 Likes
9

You are right. I’m aware that my whole question is a bit confusing.
After your first answers I realized that the way you suggested was too difficult for my knowledge of mysql queries, therefore I tried to adapt my existing code with small modifications.

  1. What I get with the not winking code, that is
$stmt = $pdo->prepare("SELECT * FROM `bibliografie` WHERE `autore` LIKE ? OR `titolo` LIKE ? OR `keywords` LIKE ? OR `tipologia` LIKE ?  OR `reperibilita` LIKE ?");

Is … nothing. No error messages, but no results at all (with a webpage without the form, and with the other, correct, formatting).

  1. What I expected is to see all the items with the inputted value in any of the five columns.

  2. Sorry, maybe it is still not enough clear. I posted the whole php code related with a mysql query. At least, I believe to have done so.

1 Like
10

There’s nothing technically wrong with the posted sql query. The problem is somewhere else. Post all the current code.

1 Like
11

This isn’t a fix, but an improvement that will make it simpler to keep track of the parameter numbers, and tidy the code a little.
Instead of:-

Try something like:-

$search = '%' . trim($_POST['search']) . '%';
$stmt->execute([$search, $search, $search]);

The array is easier to read/count and not quite so repetitive.
Though you still may want to build the query and array dynamically, if for example you let the user choose which columns to search in.

3 Likes
12

Wow, it works (so far, at least)!


<?php
define('DB_HOST', 'localhost');
define('DB_NAME', 'bibliografia');
define('DB_CHARSET', 'utf8');
define('DB_USER', '***');
define('DB_PASSWORD', '***');

try {
  $pdo = new PDO(
    "mysql:host=" . DB_HOST . ";charset=" . DB_CHARSET . ";dbname=" . DB_NAME,
    DB_USER, DB_PASSWORD, [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES => false ]
  );
} catch (Exception $ex) {
  die($ex->getMessage());
}

// (3) SEARCH
$stmt = $pdo->prepare("SELECT * FROM `bibliografie` WHERE `autore` LIKE ? OR `titolo` LIKE ? OR `keywords` LIKE ? OR `tipologia` LIKE ?  OR `reperibilita` LIKE ?");
$search = '%' . trim($_POST['search']) . '%';
$stmt->execute([$search, $search, $search, $search, $search]);

$results = $stmt->fetchAll();
if (isset($_POST['ajax'])) { echo json_encode($results); }
?>

Thank you very, very much! :smile:

1 Like