phpmyadmin query and browser query give different results
TK
CREATE TABLE `rackets` (
`id` tinyint(4) unsigned zerofill NOT NULL auto_increment,
`img` varchar(20) NOT NULL default '',
`alt` varchar(80) NOT NULL default '',
`description` text NOT NULL,
`grip` varchar(60) NOT NULL default '',
`price` varchar(6) NOT NULL default '',
`stock` varchar(6) NOT NULL default '',
`type` varchar(7) NOT NULL default '',
`weight` varchar(2) NOT NULL default '',
`Number` varchar(2) NOT NULL default '',
PRIMARY KEY (`id`),
KEY `type` (`type`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Rackets Sold'
AUTO_INCREMENT=184 ;
CREATE TABLE `secondpage` (
`id` tinyint(3) unsigned NOT NULL default '0',
`type` varchar(7) collate latin1_general_ci NOT NULL default '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci
COMMENT='Item display on second page';
I have run this query on my development machine and on the host site.
There are 8 records that meet the criteria and are displayed by
phpmyadmin either online or the development machine (XAMMP). The
browser query shows the NOT NULL records from the secondpage table in
both locations.
SELECT rackets.id AS id, img, alt, description, grip, price, stock,
left( rackets.type, 1 ) AS newtype, CHARACTER_LENGTH( `description` ) +
CHARACTER_LENGTH( `grip` ) AS count, Number, secondpage.type AS type2nd
FROM rackets
LEFT JOIN secondpage ON rackets.id = secondpage.id
WHERE `stock` = 'stock'
AND rackets.type = 'paper'
AND secondpage.type IS NULL
)
ORDER BY count ASC
I did discover on MySQL 4.1.14 CHAR_LENGTH() will work in the browser
and not in the phpmyadmin query. CHARACTER_LENGTH() will work in both
locations with that version. (I have that version still on my
development machine because I am working with the Zen-Cart book.)
The online MySQL version is 4.1.22.
I have copied the working query into the php file to test in the browser
to make certain that I did not have a typo in the query.
Why would the results differ from phpmyadmin query to browser query?
In the phpmyadmin query the secondpage.type displays as null - in the
browser query $row[type2nd] == null. ($row[type2nd] is aka secondpage.type)
--
TK
http://www.wejuggle2.com/
The secret to being a successful performer is honesty.
Once you can fake that, you pretty much have it made. (George Burns)
Jerry Stuckle
Two different products, in two different languages - probably using two
different versions of client libraries and/or parsing the text
differently. If you have a problem with phpMyAdmin (i.e. a function
call which should work but doesn't), you need to follow up with
phpMyAdmin support.
But why are you using such an old version of MySQL? Just because you're
using an old book doesn't make it a good idea to use old software. You
should still be using current versions of your products.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstu...@attglobal.net
==================
TK
> TK wrote:
>> Why would the results differ from phpmyadmin query to browser query?
>>
>> In the phpmyadmin query the secondpage.type displays as null - in the
>> browser query $row[type2nd] == null. ($row[type2nd] is aka
>> secondpage.type)
>
> Two different products, in two different languages - probably using two
> different versions of client libraries and/or parsing the text
> differently. If you have a problem with phpMyAdmin (i.e. a function
> call which should work but doesn't), you need to follow up with
> phpMyAdmin support.
The problem (as I see it) is not with phpmyadmin, it gives the results I
am trying to get. The browser result includes the null values whether I
use IS NULL or IS NOT NULL.
> But why are you using such an old version of MySQL? Just because you're
> using an old book doesn't make it a good idea to use old software. You
> should still be using current versions of your products.
>
I left out the most important question, why does the following query
include the portion not meeting the criteria "secondpage.type IS NULL"?
SELECT rackets.id AS id, img, alt, description, grip, price, stock,
left( rackets.type, 1 ) AS newtype, CHARACTER_LENGTH( `description` ) +
CHARACTER_LENGTH( `grip` ) AS count, Number, secondpage.type AS type2nd
FROM rackets
LEFT JOIN secondpage ON rackets.id = secondpage.id
WHERE `stock` = 'stock'
AND rackets.type = 'paper'
AND secondpage.type IS NULL
ORDER BY count ASC
Since the results are identical online and developmentally (in this
case) I do not the problem with the older software. I see the potential
for the problem, that is why I test on both. As soon as I have learned
what I need to know from the Zen-Cart experiment I certainly will update
my local system to the latest and greatest.
Jerry Stuckle
What's your data in the tables? (export the tables and data and post
here, as long as it's not TOO big).
TK
Omitting most of the extraneous fields leaves the following for rackets
table (just where `type`='paper'):
id img stock type
0056 notecards.jpg Stock Paper
0137 postit.jpg Stock Paper
0139 napkin.jpg Stock Paper
0140 papcup.jpg Stock Paper
0141 wrap.jpg Stock Paper
0142 tissue.jpg Stock Paper
0143 giftbags.jpg Stock Paper
0144 balloons.jpg Stock Paper
0145 plates.jpg Stock Paper
0181 snowmanXmas.jpg Stock Paper
0182 santaXmas.jpg Stock Paper
0183 ornamentXmas.jpg Stock Paper
The whole table secondpage is:
id type
138 Gifts
178 Gifts
179 Gifts
181 Christm
182 Christm
183 Christm
The last 3 ids from the secondpage table should stop the matching racket
table records from displaying. That is the result from phpmyamin
testing but sadly not from the browser query.
I just noticed the difference in the display of the id from one table to
the other. Searching on either table with 0181 or 181 will find the
appropriate record, surely that can not be the problem?
I have now changed the secondpage table id to unsigned zerofill from
unsigned - it made no difference in the results. I did not think that
could be the problem but wanted to eliminate it as a suspect.
Axel Schwenke
> TK wrote:
>> I have run this query on my development machine and on the host site.
>> There are 8 records that meet the criteria and are displayed by
>> phpmyadmin either online or the development machine (XAMMP). The
>> browser query shows the NOT NULL records from the secondpage table in
>> both locations.
>>
>> SELECT rackets.id AS id, img, alt, description, grip, price, stock,
>> left( rackets.type, 1 ) AS newtype, CHARACTER_LENGTH( `description` ) +
>> CHARACTER_LENGTH( `grip` ) AS count, Number, secondpage.type AS type2nd
>> FROM rackets
>> LEFT JOIN secondpage ON rackets.id = secondpage.id
>> WHERE `stock` = 'stock'
>> AND rackets.type = 'paper'
>> AND secondpage.type IS NULL
>> )
>> ORDER BY count ASC
This is no valid SQL.
The ")" on the second last line has no counterpart.
>> I did discover on MySQL 4.1.14 CHAR_LENGTH() will work in the browser
>> and not in the phpmyadmin query.
This simply makes no sense. The SQL query string is parsed
by the MySQL server. And only by the MySQL server.
>> I have copied the working query into the php file to test in the browser
>> to make certain that I did not have a typo in the query.
But you did not copy it here.
>> Why would the results differ from phpmyadmin query to browser query?
> Two different products, in two different languages - probably using two
> different versions of client libraries
None of that matters.
> and/or parsing the text differently.
*Normally* a SQL query should not be parsed or modified by
any client tool. However phpMyAdmin is notorious for adding
LIMIT to queries etc. I also don't trust the query browser.
I *guess* that either (or both?) QB or PMA modify the query
before it is sent to the MySQL server. This could be checked
by activating and checking the general query log on the server.
The only trustworthy tool in that respect is the MySQL command
line client.
@TK: please copy and paste your query to the 'mysql' tool.
This will give the definitive result.
XL
TK
>> TK wrote:
>>> I did discover on MySQL 4.1.14 CHAR_LENGTH() will work in the browser
>>> and not in the phpmyadmin query.
>
> This simply makes no sense. The SQL query string is parsed
> by the MySQL server. And only by the MySQL server.
char_lenght and character_length both work from the command line.
>>> I have copied the working query into the php file to test in the browser
>>> to make certain that I did not have a typo in the query.
>
> But you did not copy it here.
Yes I inserted an ) sorry, otherwise the query is correct.
> I *guess* that either (or both?) QB or PMA modify the query
> before it is sent to the MySQL server. This could be checked
> by activating and checking the general query log on the server.
>
> The only trustworthy tool in that respect is the MySQL command
> line client.
>
> @TK: please copy and paste your query to the 'mysql' tool.
> This will give the definitive result.
I just finished running it from the command line and the results matched
the phpmyadmin results.
Axel Schwenke
> Axel Schwenke wrote:
>
>> I *guess* that either (or both?) QB or PMA modify the query
>> before it is sent to the MySQL server. This could be checked
>> by activating and checking the general query log on the server.
>>
>> The only trustworthy tool in that respect is the MySQL command
>> line client.
> I just finished running it from the command line and the results matched
> the phpmyadmin results.
Congratulations, you found a bug in the MySQL Query Browser!
</sarcasm>
QB is end-of-life. This bug will never be fixed :(
You may try MySQL Workbench. It's the designated
successor of QB.
XL
TK
> TK <tkn...@wejuggle2.com> wrote:
>> Axel Schwenke wrote:
>>
>>> I *guess* that either (or both?) QB or PMA modify the query
>>> before it is sent to the MySQL server. This could be checked
>>> by activating and checking the general query log on the server.
>>>
>>> The only trustworthy tool in that respect is the MySQL command
>>> line client.
>
>> I just finished running it from the command line and the results matched
>> the phpmyadmin results.
>
> Congratulations, you found a bug in the MySQL Query Browser!
> </sarcasm>
We must not be connecting here. I am talking about the results in
FireFox (or other browser) when accessing the php file with the same
query in it vs phpmyadmin and command line. phpmyadmin and command line
give what seem to me to be correct results (not including
"secondpage.type IS NULL" values) - the webpage includes the
"secondpage.type IS NULL" and the not null values.
> QB is end-of-life. This bug will never be fixed :(
> You may try MySQL Workbench. It's the designated
> successor of QB.
TK
>> the phpmyadmin results.
>
> Congratulations, you found a bug in the MySQL Query Browser!
> </sarcasm>
Sorry for the confusion, I was unaware of the MySQL Query Browser. I am
dealing with two tiny tables with approximately 120 records.
Axel Schwenke
> Axel Schwenke wrote:
>>
>> Congratulations, you found a bug in the MySQL Query Browser!
>> </sarcasm>
>
> We must not be connecting here. I am talking about the results in
> FireFox (or other browser) when accessing the php file with the same
> query in it vs phpmyadmin and command line.
> phpmyadmin and command line
OMFG!
So when you wrote "the browser query" you really meant "the result
returned by some PHP script"?
> give what seem to me to be correct results (not including
> "secondpage.type IS NULL" values) - the webpage includes the
> "secondpage.type IS NULL" and the not null values.
Then I guess the PHP script runs a different query. Are you sure you
looked at the right file? Add some debug print("...") lines. If you
construct a query string (seems everybody does that) then print() the
query before you send it to MySQL. If that query returns a different
result in the command line client, then your universe is broken.
XL
Jerry Stuckle
Ah, you're looking at the output of a php script! This makes a huge
difference.
The query you're running in the PHP file isn't what you think it is.
Echo the sql just before you make the MySQL call.
TK
> TK wrote:
>> Jerry Stuckle wrote:
>>> TK wrote:
>>>> Jerry Stuckle wrote:
>>>>> TK wrote:
>>>>
>>>>>> Why would the results differ from phpmyadmin query to browser query?
> The query you're running in the PHP file isn't what you think it is.
> Echo the sql just before you make the MySQL call.
Exactly right! Thanks Jerry and Axel - sorry for the unneeded confusion
on my part. I have a handicap - I use English like it is not my native
language.</grin>