Wierd PHP/MySQL result

Wierd PHP/MySQL result

am 22.09.2009 22:02:06 von Mogens Melander

Hi guys

I'm getting rather confused on this thing.

Results returned from mysql console/query-browser is
different from what's returned from same query in PHP.

Maybe someone smarter than me could shed some light on
what's going on. I'm clueless :(

Environment is as follow:

Mysql Server version: 5.1.22-rc-log Source distribution
PHP 5.2.9 (cli) (built: Mar 31 2009 18:41:50)
Apache/2.2.9 (Unix) mod_ssl/2.2.9 OpenSSL/0.9.8k DAV/2 PHP/5.2.9

The SQL:

select c.id, c.owner, if(c.owner > 0, s.name, 'Unknown') oname
, c.fname, c.lname, c.addr1, c.addr2, c.zipcode, c.city
, c.phone, c.mobile, c.email, c.web, c.saldo
from customer c
left join shop s on s.id=c.owner
where c.id = 1;

Result from PHP:

"id":"1"
,"owner":"2"
,"oname":null <======= Problem data
,"fname":"Jane"
,"lname":"Doe"
,"addr1":"Solvej 1"
,"addr2":""
,"zipcode":"1234"
,"city":"Sommerby"
,"phone":"11 22 33 44"
,"mobile":""
,"email":""
,"web":""
,"saldo":"-7500.00"

Result from MySQL console/query-browser:

1
, 2
, 'Long description' <==== Correct data
, 'Jane'
, 'Doe'
, 'Solvej 1'
, ''
, '1234'
, 'Sommerby'
, '11 22 33 44'
, ''
, ''
, ''
, -7500.00

--
Later

Mogens Melander



--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Wierd PHP/MySQL result

am 22.09.2009 22:35:35 von Mogens Melander

Well, no typos.

The relevant piece of code:

if (! $cust = mysql_fetch_assoc($res))
{
echo "{success: false, errors: { reason: 'Fetch Customer failed: " .
htmlspecialchars(mysql_error(),ENT_QUOTES) .
"
" . htmlspecialchars($sql,ENT_QUOTES) . "'}}";
}
else
{
echo "{success: true, total: 1, results:[" . json_encode($cust) . "]}";
}

On Tue, September 22, 2009 22:11, lists-mysql wrote:
> You didn't show your php code, but I'd bet you have a typo, likely
> in assigning the returned value to the variable, or in the name of
> the variable you're printing.
>
> - Rick
>
>
>
> ------------ Original Message ------------
>> Date: Tuesday, September 22, 2009 10:02:06 PM +0200
>> From: Mogens Melander
>> To: mysql@lists.mysql.com
>> Subject: Wierd PHP/MySQL result
>>
>> Hi guys
>>
>> I'm getting rather confused on this thing.
>>
>> Results returned from mysql console/query-browser is
>> different from what's returned from same query in PHP.
>>
>> Maybe someone smarter than me could shed some light on
>> what's going on. I'm clueless :(
>>
>> Environment is as follow:
>>
>> Mysql Server version: 5.1.22-rc-log Source distribution
>> PHP 5.2.9 (cli) (built: Mar 31 2009 18:41:50)
>> Apache/2.2.9 (Unix) mod_ssl/2.2.9 OpenSSL/0.9.8k DAV/2 PHP/5.2.9
>>
>> The SQL:
>>
>> select c.id, c.owner, if(c.owner > 0, s.name, 'Unknown') oname
>> , c.fname, c.lname, c.addr1, c.addr2, c.zipcode, c.city
>> , c.phone, c.mobile, c.email, c.web, c.saldo
>> from customer c
>> left join shop s on s.id=c.owner
>> where c.id = 1;
>>
>> Result from PHP:
>>
>> "id":"1"
>> ,"owner":"2"
>> ,"oname":null <======= Problem data
>> ,"fname":"Jane"
>> ,"lname":"Doe"
>> ,"addr1":"Solvej 1"
>> ,"addr2":""
>> ,"zipcode":"1234"
>> ,"city":"Sommerby"
>> ,"phone":"11 22 33 44"
>> ,"mobile":""
>> ,"email":""
>> ,"web":""
>> ,"saldo":"-7500.00"
>>
>> Result from MySQL console/query-browser:
>>
>> 1
>> , 2
>> , 'Long description' <==== Correct data
>> , 'Jane'
>> , 'Doe'
>> , 'Solvej 1'
>> , ''
>> , '1234'
>> , 'Sommerby'
>> , '11 22 33 44'
>> , ''
>> , ''
>> , ''
>> , -7500.00
>>


--
Later

Mogens Melander



--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Wierd PHP/MySQL result (Solved)

am 22.09.2009 22:51:19 von Mogens Melander

Ok, i got it.

It's the json_encode($cust) that seems to have problems
with danish characters ( æ ø å Æ Ø Å ).

json_encode is supposed to work with utf8 only, and
my tables are all utf8 so that's wierd too :(

Seems like i get to do the json_encoding by hand.

On Tue, September 22, 2009 22:35, Mogens Melander wrote:
> Well, no typos.
>
> The relevant piece of code:
>
> if (! $cust = mysql_fetch_assoc($res))
> {
> echo "{success: false, errors: { reason: 'Fetch Customer failed: " .
> htmlspecialchars(mysql_error(),ENT_QUOTES) .
> "
" . htmlspecialchars($sql,ENT_QUOTES) . "'}}";
> }
> else
> {
> echo "{success: true, total: 1, results:[" . json_encode($cust) . "]}";
> }
>
> On Tue, September 22, 2009 22:11, lists-mysql wrote:
>> You didn't show your php code, but I'd bet you have a typo, likely
>> in assigning the returned value to the variable, or in the name of
>> the variable you're printing.
>>
>> - Rick
>>
>>
>>
>> ------------ Original Message ------------
>>> Date: Tuesday, September 22, 2009 10:02:06 PM +0200
>>> From: Mogens Melander
>>> To: mysql@lists.mysql.com
>>> Subject: Wierd PHP/MySQL result
>>>
>>> Hi guys
>>>
>>> I'm getting rather confused on this thing.
>>>
>>> Results returned from mysql console/query-browser is
>>> different from what's returned from same query in PHP.
>>>
>>> Maybe someone smarter than me could shed some light on
>>> what's going on. I'm clueless :(
>>>
>>> Environment is as follow:
>>>
>>> Mysql Server version: 5.1.22-rc-log Source distribution
>>> PHP 5.2.9 (cli) (built: Mar 31 2009 18:41:50)
>>> Apache/2.2.9 (Unix) mod_ssl/2.2.9 OpenSSL/0.9.8k DAV/2 PHP/5.2.9
>>>
>>> The SQL:
>>>
>>> select c.id, c.owner, if(c.owner > 0, s.name, 'Unknown') oname
>>> , c.fname, c.lname, c.addr1, c.addr2, c.zipcode, c.city
>>> , c.phone, c.mobile, c.email, c.web, c.saldo
>>> from customer c
>>> left join shop s on s.id=c.owner
>>> where c.id = 1;
>>>
>>> Result from PHP:
>>>
>>> "id":"1"
>>> ,"owner":"2"
>>> ,"oname":null <======= Problem data
>>> ,"fname":"Jane"
>>> ,"lname":"Doe"
>>> ,"addr1":"Solvej 1"
>>> ,"addr2":""
>>> ,"zipcode":"1234"
>>> ,"city":"Sommerby"
>>> ,"phone":"11 22 33 44"
>>> ,"mobile":""
>>> ,"email":""
>>> ,"web":""
>>> ,"saldo":"-7500.00"
>>>
>>> Result from MySQL console/query-browser:
>>>
>>> 1
>>> , 2
>>> , 'Long description' <==== Correct data
>>> , 'Jane'
>>> , 'Doe'
>>> , 'Solvej 1'
>>> , ''
>>> , '1234'
>>> , 'Sommerby'
>>> , '11 22 33 44'
>>> , ''
>>> , ''
>>> , ''
>>> , -7500.00
>>>
>
>
> --
> Later
>
> Mogens Melander
>
>
>
> --
> This message has been scanned for viruses and
> dangerous content by MailScanner, and is
> believed to be clean.
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=mogens@fumlersoft.dk
>


--
Later

Mogens Melander



--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Wierd PHP/MySQL result

am 22.09.2009 22:56:47 von Mark Goodge

Mogens Melander wrote:
> Well, no typos.
>
> The relevant piece of code:
>
> if (! $cust = mysql_fetch_assoc($res))
> {
> echo "{success: false, errors: { reason: 'Fetch Customer failed: " .
> htmlspecialchars(mysql_error(),ENT_QUOTES) .
> "
" . htmlspecialchars($sql,ENT_QUOTES) . "'}}";
> }
> else
> {
> echo "{success: true, total: 1, results:[" . json_encode($cust) . "]}";
> }
>

What happens if you simplify the PHP?


$cust = mysql_fetch_assoc($res);
print_r($cust);


That will tell you what PHP is seeing from MySQL. If the results have
the correct values, then the problem is elsewhere in the code.

I suspect that the problem is in the json_encode() function. According
to the PHP documentation this requires utf8-encoded data. If your values
in oname are not utf8, then that may explain why the function is
returning a 'null' where it should have a string.

Mark


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Wierd PHP/MySQL result

am 22.09.2009 23:11:34 von Mogens Melander

Yes, you are right. It's the json_encode that fail.

It's strange though. The data that fail is "Humlebæk".

Another query, in another form, returning "Værksted" don't
fail in json_encode.

echo $cust['oname']; give the correct value.

On Tue, September 22, 2009 22:56, Mark Goodge wrote:
> Mogens Melander wrote:
>> Well, no typos.
>>
>> The relevant piece of code:
>>
>> if (! $cust = mysql_fetch_assoc($res))
>> {
>> echo "{success: false, errors: { reason: 'Fetch Customer failed: " .
>> htmlspecialchars(mysql_error(),ENT_QUOTES) .
>> "
" . htmlspecialchars($sql,ENT_QUOTES) . "'}}";
>> }
>> else
>> {
>> echo "{success: true, total: 1, results:[" . json_encode($cust) . "]}";
>> }
>>
>
> What happens if you simplify the PHP?
>
>
> $cust = mysql_fetch_assoc($res);
> print_r($cust);
>
>
> That will tell you what PHP is seeing from MySQL. If the results have
> the correct values, then the problem is elsewhere in the code.
>
> I suspect that the problem is in the json_encode() function. According
> to the PHP documentation this requires utf8-encoded data. If your values
> in oname are not utf8, then that may explain why the function is
> returning a 'null' where it should have a string.
>
> Mark
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=mogens@fumlersoft.dk
>
>
> --
> This message has been scanned for viruses and
> dangerous content by MailScanner, and is
> believed to be clean.
>


--
Later

Mogens Melander



--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org