PHP Array to string conversion, mysql_fetch_assoc() expects parameter

Question

Can someone please help me with my code, I can't get it to work.

I have an html input form where I type for example "This is a sample". (data is saved in $_POST['Begriff'])

I want to achive a simple translation, so that the table "drinks" in column "English" is checked for existence of every single word from my input sample sentence and output if found every entry from the corresponding row in one line.

Right now I have two problems: As soon as I add " where English in $wert" to the select statement I get:

Notice: Array to string conversion in nachlesen.php on line 34
Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given 

Second Problem: How to I put the sentence together again from the returned results? (Right now I get the output for every found word below each other, instead of one line)

Here is my code:

if ( $_POST['Begriff'] <> "") 
{
$wert = explode(' ',$_POST['Begriff']);
$select = mysql_query ("SELECT * FROM drinks where English in $wert");
while ( $row = mysql_fetch_assoc($select))
  {
  echo ("$row[German] <br>");
  echo ("$row[English]<br>");
  }
}

Thanks in Advance, Daniel

Answer 1

<?php
// premise: the user input in $_POST['Begriff'] is a string like 'This is a sample'
//first split it into single words
// preg_split: \s+ -> one or more whitespaces , PREG_SPLIT_NO_EMPTY -> no "empty" words
// e.g. " Mary had  a      little    lamb" -> array('Mary','had','a','little','lamb')
$words = preg_split('!\s+!', $_POST['Begriff'], -1, PREG_SPLIT_NO_EMPTY);

// now encode each string so that it can be used
// safely as a string-literal within your sql query
// see: sql injection
// this should be:
// $words = array_map(function($e) use($mysql) { return mysql_real_escape_string($e, $mysql); }, $words);
// but apparently you're not storing the resource that is returned by mysql_connect
// mysql_real_escape_string() is applied to each single element in $words
// e.g. array("it's", "been") -> array("it\\'s", "been")
$words = array_map('mysql_real_escape_string', $words);

// now put the string literals into your query 
// format: ... IN ('x','y','z')
// join(",", $words) gives you x,y,z
// join("','", $words) gives you x','y','z
// i.e. the first and the last ' has to be added "manually"
// keep in mind that for an empty array $words this will produce WHERE ... IN ('')
// better test that before even trying to build the query
$query = sprintf("
    SELECT
        German,English
    FROM
        drinks
    WHERE
        English IN ('%s')
", join("','", $words));

// send the query to the MySQL server
// should be: $result = mysql_query($query, $mysql);
$result = mysql_query($query);

// database query -> failure is always an option
if ( !$result ) {
    // add error handling here
}
else {
    // in case there is not a single match in the database
    // your script would print ...nothing
    // I don't like that - but that debatable
    // anway: wrapped in a fieldset
    echo '<fieldset><legend>results:</legends>';
    while( false!==($row=mysql_fetch_array($result, MYSQL_FETCH_ASSOC)) ) {
        printf('%s<br />%s<br />', 
          // just like on the input-side you had to worry about
          // sql injections
          // on the output side you want to avoid
          // that characters from the database can break your html structure
            htmlentities($row['German']),
            htmlentities($row['English'])
        );
    }
    echo '</fieldset>';
}

(script is untested)

Answer 2

"SELECT * FROM drinks where English in ('". implode("','", $wert) . "')"

EDIT: SQL Injection safe query:

$dsn = 'mysql:dbname=' . $settings['dbname'] . ';host=' . $settings['dbhost'];
$pdo = new PDO($dsn, $settings['dbuser'], $settings['dbpass'], array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

if ( $_POST['Begriff'] <> "") 
{
  $wert = explode(' ',$_POST['Begriff']);

  $placeholders = implode(',', array_fill(0, count($wert), '?'));


  $sth = $pdo->prepare("SELECT * FROM drinks WHERE English in ({$placeholders})");
  $sth->execute($wert);

  $rows = $sth->fetchAll();

  foreach($rows as $row) {
      print_r( $row );
  }

}

Answer 3

why don't you try implode() and convert your array to string??

 if ( $_POST['Begriff'] <> "") 
 {

  //you'l have to replace all "spaces with ',' "
  $pattern = '/\s*,\s*/';
  $replace = "','";
  $wert = preg_replace($pattern, $replace, $_POST['Begriff']);

 $select = mysql_query ("SELECT * FROM drinks where English in ('$wert')");
 while ( $row = mysql_fetch_assoc($select))
   {
   echo ("$row[German] <br>");
   echo ("$row[English]<br>");
   }
 }

ANOTHER SOLUTION (TO PREVENT SQL INJECTION)

 if ( $_POST['Begriff'] <> "") 
 {

  //replace multiple spaces
  $str1 = preg_replace( "/\s+/", " ", $_POST['Begriff'] );
  //convert to array, separated by space
  $arr=explode(" ",$str1);
  $safe_params=array();
  foreach($arr as $param){
     $safe_params[]=mysql_real_escape_string($param);
  }

  $wert=implode("','",$safe_params);

 $select = mysql_query ("SELECT * FROM drinks where English in ('$wert')");
 while ( $row = mysql_fetch_assoc($select))
   {
   echo ("$row[German] <br>");
   echo ("$row[English]<br>");
   }
 }

EDIT

Processing query output according to language

$german_words=array();
while ( $row = mysql_fetch_assoc($select))
{
   $german_words[$row['English']]=$row['Gernam'];
}

//$str1 is having english string
echo "English: ".$str1."<br/>";
echo "German : ";

//$arr is having array of words of $str1
foreach($arr as $eng_word){
   echo $german_words[$eng_word]." ";
}