0

I need to compare complex strings in mysql-database by using php.

$my_value = '280T ripstop 100% Nylon, 72 g/m2, Lining, 210T taffeta 100% Polyester, 60 g/m2';
$stmt = $db->prepare('SELECT material_mapping.id, material_mapping.material_de, material_mapping.material_en FROM material_mapping_materialien LEFT JOIN material_mapping ON material_mapping_materialien.material_id = material_mapping.id WHERE material_mapping_materialien.material = :material');
$stmt->bindValue(':material',$my_value,PDO::PARAM_STR);
$stmt->execute();
if ($stmt->rowCount() > 0) {
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
[...]

After this code I get none results. I then output the resulting query by using debugDumpParams() and get:

SELECT material_mapping.id, material_mapping.material_de, material_mapping.material_en FROM material_mapping_materialien LEFT JOIN material_mapping ON material_mapping_materialien.material_id = material_mapping.id WHERE material_mapping_materialien.material = '280T ripstop 100% Nylon, 72 g/m2, Lining, 210T taffeta 100% Polyester, 60 g/m2'

I then used this query on my database and got 2 results. I think there must be some problems with the percents or any other character. I establish connection to database using following string:

mysql:host=localhost;dbname=mydbname;charset=utf8mb4

The database is innodb with collation utf8_general_ci

Does anyone has any hints for me?

Dharman
  • 30,962
  • 25
  • 85
  • 135
Jens Körte
  • 35
  • 4
  • https://www.php.net/manual/en/pdostatement.rowcount.php: _"returns the number of rows affected by the last DELETE, INSERT, or UPDATE statement "_ – CBroe May 31 '22 at 14:00
  • 1
    @CBroe Just to point out that MySQL reports the number of selected rows as affected_rows, so rowCount would return the right value. – Dharman May 31 '22 at 14:08
  • @Dharman I read this, from the docs: If the last SQL statement executed by the associated PDOStatement was a SELECT statement, some databases may return the number of rows returned by that statement. However, this behaviour is not guaranteed for all databases and should not be relied on for portable applications. – Giacomo M May 31 '22 at 14:09
  • 1
    I have a reference question/answer exactly for this case. Most likely it's "Problems caused by the data" section – Your Common Sense May 31 '22 at 14:10
  • Check your table charset. Maybe you have two different charsets – Dharman May 31 '22 at 14:10
  • Or, rather, given the query returns some results in the db client, it could be "Problems caused by database credentials" – Your Common Sense May 31 '22 at 14:14
  • Thanks a lot for your support. I found the issue. Some entered the checking value with double space into database. The browser then displayed just one space-character and therefore I was thinking it must have matched. – Jens Körte Jun 01 '22 at 08:42

0 Answers0