0

After a quick search, I haven't find solution of my problem so I post a new one.

So I have to create view in a MySQL database from a Web interface (using PHP).

I use a PEAR framework for the connection with MySQL(5.0.26)

I have the SQL request : 

CREATE VIEW test AS SELECT cswc.code_de_la_copie, cswc.service_de_reference, cswc.libelle_de_la_copie, cswc.direction_de_securite_logique
FROM  pressi_copiesServiceWithCibles cswc LEFT OUTER JOIN pressi_servicesReferenceWithCibles srwc ON cswc.service_de_reference = srwc.code_du_service
WHERE cswc.cible is null
  AND (srwc.cible LIKE '%£DOMAIN£%' OR srwc.cible LIKE '%$DOMAIN$%');

When I execute this request directly on mon local MySQL Database, I obtain a result with 470 lines.

However, when I execute this request in my PHP code, I have a different result (I have 386 line), and I don't know why !

$values['request'] = "SELECT cswc.code_de_la_copie, cswc.service_de_reference, cswc.libelle_de_la_copie, cswc.direction_de_securite_logique
FROM  pressi_copiesServiceWithCibles cswc LEFT OUTER JOIN pressi_servicesReferenceWithCibles srwc ON cswc.service_de_reference = srwc.code_du_service
WHERE cswc.cible is null
  AND (srwc.cible LIKE '%£DOMAIN£%' OR srwc.cible LIKE '%$DOMAIN$%');";

$baseView = "test";

$sqlView = 'CREATE VIEW '.$baseView.' AS '.$values['request'];
$res =& $this->mdb2->query($sqlView);
if (PEAR::isError($res)) {
   return false;
}

Moreover, I have already create 6 views before this one without any problem (same result in PHP and in MySQL)

Thank you for your help

Ark4ntes
  • 15
  • 5
  • take out the rows that are not in your php result and then look all columns to find something strange or different between others. Maybe a charset issue – Alessandro Jun 26 '13 at 09:06
  • someone should vote to close this as "too localized". I would, if i could. – STT LCU Jun 26 '13 at 09:23

1 Answers1

1

Note that your connection to the database also has a CHARSET and a COLLATION for any string values you include in your query. Although your query looks the same to you in both situations, it must not from the MySQL servers point of view.

Maybe the client CHARSET (and/or COLLATION) differ when you connect via PHP from when you connect via MySQL console.

See the MySQL manual for more information on the client charset and collation.

For comparison, you can use this query:

SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';
Kaii
  • 20,122
  • 3
  • 38
  • 60
  • The problem come from the charset, but apparently not between PHP and MySQL but with the text I get from the user. So I use utf8_decode($values['request']) before execute the resquest. And I have uniformized the charset between PHP and MySQL – Ark4ntes Jun 26 '13 at 09:52