How explode values within a PDO query

I am not an expert of php and PDO, but I managed to create a script to search a word in a table. I have two files:

the first, citazioni-search.2.php, has this content:

<?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 `citazioni__olon` WHERE `autore` LIKE ? OR `titolo` LIKE ? OR `keywords_dottorato` LIKE ?");
$stmt->execute(["%" . $_POST['search'] . "%", "%" . $_POST['search'] . "%", "%" . $_POST['search'] . "%"]);
$results = $stmt->fetchAll();
if (isset($_POST['ajax'])) { echo json_encode($results); }
?>

The second, citazioni-search.php, has this content:

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

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

    <!-- [SEARCH RESULTS] -->
    <?php
    if (isset($_POST['search'])) {
      if (count($results) > 0) {
        foreach ($results as $r) {
          $key = explode(',', $r['keywords']);
        
          printf("<div><h2>%s</h2> <blockquote><p>%s</p></blockquote><p><b>%s</b>, <i>%s</i>, <span>%s</span></p><p><a href=\"hashtag.php?tag=$key\">%s</a></p></div>", $r['titolo'], $r['testo'], $r['autore'], $r['fonte'], $r['fonte_spec'], $r['keywords']);
        }
      } else {
        echo "No results found";
      }
    }
?>

It works, but I don’t manage to explode the keywords (values), as I can do in other (not PDO) php files.
The rows (strings) I have created recently, unsuccessfully are:

  $key = explode(',', $r['keywords']);

and

 <a href=\"hashtag.php?tag=$key\">%s</a>

As example of keywords values: philosophy, space, Earth.
I like to have an hyperlink for each of them (with the matching value).

Could you help me?

Let me try and highlight something in that sentence and see if it points you to what you’re missing…

1 Like

It looks like you are trying to output an array ($key) as a string that wont work, it also looks like you want it to loop over the array… printf is not the tool for that.

Try it this way:
Add the following functions to your citations-search.php

         function hash_link_format($key){
           return "<a href=\"hashtag.php?tag=$key\">$key</a>";
         }

		 function buildLinks($str, $jdel=',' ,$formatFooName='hash_link_format', $del=',' ){
			  return  implode( $jdel.' ', array_map($formatFooName, explode($del, $str)));
		 }
		 

then update your printf statement to:
printf("<div><h2>%s</h2> <blockquote><p>%s</p></blockquote><p><b>%s</b>, <i>%s</i>, <span>%s</span></p><p><a href=\"hashtag.php?tag=$key\">%s</a></p></div>", $r['titolo'], $r['testo'], $r['autore'], $r['fonte'], $r['fonte_spec'], buildLinksFromString($r['keywords']));

BONUS:
When calling buildLinksFromString
• If you wanted the links to be separated by something other than ‘,’, pass that as a second optional argument.
• If you need to format the links differently, you can write your own formatting function and pass the name of that as your third optional argument.
• If your dataset changed and the keywords the links to be separated by something other than ‘,’, pass that as a fourth optional argument.

Hope this helped

1 Like

Thank you! Almost done (with a little error buildLinksFromString instead of buildLinks :slight_smile: )
Today afternoon I will try to fine tuning.
Very good!

Your code works, but almost perfectly: I mean, I have two small problems:

  • there is always an empty key (with link to nowhere),
  • and there is an error message " Array to string conversion in /mnt/dati/schede/intell/dottorato-2018/citazioni-search.php on line 40"

My code now is:

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

/* [DISPLAY HTML] */ ?>
    <!-- [SEARCH FORM] -->
    <form method="post" arction="citazioni-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.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><h2>%s</h2> <blockquote><p>%s</p></blockquote><p><b>%s</b>, <i>%s</i>, <span>%s</span></p><p><a href=\"hashtag.php?tag=$key\">%s</a></p></div>", $r['titolo'], $r['testo'], $r['autore'], $r['fonte'], $r['fonte_spec'], $r['keywords']);*/
        
        printf("<div><h2>%s</h2> <blockquote><p>%s</p></blockquote><p><b>%s</b>, <i>%s</i>, <span>%s</span></p><p><a href=\"hashtag.php?tag=$key\">%s</a></p></div>", $r['titolo'], $r['testo'], $r['autore'], $r['fonte'], $r['fonte_spec'], buildLinks($r['keywords']));
        
        }
      } else {
        echo "No results found";
      }
    }
?>

line 40 will be your printf line. You forgot to remove the link from your original string.

1 Like

yes , line 40 was printf line. But even removing the commeted text I get this new message:

Notice: Array to string conversion in /mnt/dati/schede/intell/dottorato-2018/citazioni-search.php on line 38  

line 38 is the same printf line.
the complete code is

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

/* [DISPLAY HTML] */ ?>
    <!-- [SEARCH FORM] -->
    <form method="post" arction="citazioni-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.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><h2>%s</h2> <blockquote><p>%s</p></blockquote><p><b>%s</b>, <i>%s</i>, <span>%s</span></p><p><a href=\"hashtag.php?tag=$key\">%s</a></p></div>", $r['titolo'], $r['testo'], $r['autore'], $r['fonte'], $r['fonte_spec'], buildLinks($r['keywords']));
        }
      } else {
        echo "No results found";
      }
    }
?>

If I remove the line $key = explode(',', $r['keywords']); I get not one, but two errors messages (variable undefined).
What should I change?

You could try changing the thing you were told to change?

1 Like

sorry! You’re right!
The right code is

printf("<div><h2>%s</h2> <blockquote><p>%s</p></blockquote><p><b>%s</b>, <i>%s</i>, <span>%s</span></p><p>%s</p></div>", $r['titolo'], $r['testo'], $r['autore'], $r['fonte'], $r['fonte_spec'], buildLinks($r['keywords']));

Thank you very much!