How explode values within a PDO query

PHP
1

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?

2

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

1 Like
3

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
4

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

5

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";
      }
    }
?>

db search
db search1015×869 229 KB

6

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

1 Like
7

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?

8

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

1 Like
10

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!

11

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.