Help with simple query

HI

I have a strange problem. I have a table with the following fields
 Field                 | Type         | Null | Key | Default | Extra
exh_id_pk             | int(7)       | NO   | PRI | NULL    | auto_increment
exh_email             | varchar(40)  | YES  |     | NULL
exh_submitted         | char(1)      | NO   |


exh_submitted = 0 or 1

On my home machine which has MySQL version  5.0.18-nt, I do this simple query

select exh_email, exh_company_name from exhibitors where exh_submitted='0';

and it works.

On the machine (Linux I think) where my site is hosted, running MySQL version 4.0.26-standard, the same query gives the following error:

SHOW KEYS FROM
#1064 - You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

Help is urgently needed. Any ideas?
tr5
tr5Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

star_trekCommented:
It should work. Are you running the query from any scripting language?
0
tr5Author Commented:
It fails from the PHP. It also fails when I run it in the phpMyAdmin utility. The wierd thing is that if ude a value that is NOT 0 or 1 (ie a value that is not one of the values in the field)  in the "where exh_submitted=..." it does not give me an error. (Of course that will never give me any records either...)
0
ikeworkCommented:
did you try double-quotes instead of single-quotes?

ike
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

tr5Author Commented:
Yes. It doesn't work either.
0
NovaDenizenCommented:
Turn on query logging for your mysqld.  What is the actual query string that mysqld receives?
0
tr5Author Commented:
select exh_email, exh_company_name from exhibitors where exh_submitted='0';

How do I turn on query logging? Remember I only have phpMyAdmin to work with. I don't have access to the server itself.
0
akshah123Commented:
Hi tr5,

I am not sure where does "SHOW KEYS FROM " comes into play in your error message?
0
tr5Author Commented:
It's part of the "graphical" error message that phpMyAdmin gives. I thought it might help.
0
NovaDenizenCommented:
If you can't access the server, you can't read the logs anyway.
0
awking00Commented:
Does the field, exh_company_name, exist in the table? It doesn't show in your example table description.
0
tr5Author Commented:
yes.
0
akshah123Commented:
NO to be redundadent but can you please post "SHOW create table" result from both MySQL servers?

0
tr5Author Commented:
From not working:
CREATE TABLE `exhibitors` (
 `exh_id_pk` int(7) NOT NULL auto_increment,
 `exh_company_name` varchar(100) NOT NULL default '',
 `exh_company_code` varchar(20) NOT NULL default '',
 `exh_contact` varchar(50) NOT NULL default '',
 `exh_email` varchar(40) default NULL,
 `exh_submitted` char(2) default NULL,
   PRIMARY KEY  (`exh_id_pk`)
) TYPE=MyISAM

From working:
 exhibitors |CREATE TABLE `exhibitors` (
 `exh_id_pk` int(7) NOT NULL auto_increment
 `exh_company_name` varchar(100) NOT NULL,
 `exh_company_code` varchar(20) NOT NULL,
 `exh_contact` varchar(50) NOT NULL,
 `exh_email` varchar(40) default NULL,
 `exh_submitted` char(1) NOT NULL,
  PRIMARY KEY  (`exh_id_pk`)
 ENGINE=InnoDB DEFAULT CHARSET=latin1

0
akshah123Commented:
Well, try changing

`exh_submitted` char(2) default NULL,

to

`exh_submitted` char(1) default NULL,
0
tr5Author Commented:
No. It doesn't make a difference.

Is it the MyISAM or InnoDB difference?
0
akshah123Commented:
>>Is it the MyISAM or InnoDB difference?

I don't think so but you can give it a try.  Also, try setting the column to not null and making sure that both places has the same charset.  I see that you have charset = latin1 for 5.0 but charset is not defined for 4.0
0
tr5Author Commented:
I played around with the attributes (changed char(2) to char(1) in phpMyAdmin. Now it works in phpMyAdmin, but not in from my php script. By thw way, I changed it back to char(2) and it continued to work when I ran the query from inside the phpMyAdmin.
0
akshah123Commented:
If you post your php code that calls this query, may be we can help identify the culprit.
0
tr5Author Commented:
I wrote this

SELECT exh_company_code, exh_contact, exh_email FROM exhibitors WHERE exh_submitted = '0';

in the phpMyAdmin and it works.  

The php code is (192 etc are line numbers):

192 $sql = "SELECT exh_company_code, exh_contact, exh_email, show_id FROM exhibitors WHERE exh_submitted = '0';";

194 $db_result = do_query($sql, $dblink);

196   while($row = mysql_fetch_array($db_result, MYSQL_ASSOC))
197   {
         etc

It gives a warning when it runs:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in manage.php on line 196

and actually it always gives me one record as a result (instead of many that there should be)

I tried using the feature in phpMyAdmin "Create PHP Code", which turned my query from
SELECT exh_contact, exh_company_code, exh_email FROM exhibitors WHERE exh_submitted = '0';
into
 $sql = 'SELECT `exh_contact`, `exh_company_code`, `exh_email` FROM `exhibitors` WHERE `exh_submitted`=\'1\';';
and that also didn't work in the PHP code.
0
akshah123Commented:
>>>Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in manage.php on line 196

You get this warning because $db_result is not a valid resouce.  This could be due to two reasons.

1.  The variable provided is misspelled.
This does not seem like to be the problem.

2.  the query performed had a syntax error and therefore the result resource has a value of false instead of a valid resource indetifying value.
This is the most likely scenario.
I am not sure what is involved in do_query() function but you should try to do something like this ...

if(!$db_result = do_query($sql, $dblink))
  die(mysql_error());

0
star_trekCommented:
change do_query($sql, $dblink);
to
mysq_query($sql, $dblink);
0
star_trekCommented:
sorry it is
mysql_query($sql, $dblink);
0
tr5Author Commented:
This is my do_query():

function do_query($sql_str, $dblink)
{
  if(!($db_result = mysql_query($sql_str, $dblink)))    
  {
    print("ERROR: Query Error!<br>\n");
    print("SQL: " . $sql_str . "<br>\n");
    print("MySQL Reports: " . mysql_error() . "<br>\n");
    exit();
  }
  return $db_result;
}
0
akshah123Commented:
Well, as the warning suggest, something is wrong with the resource identifier and that is what we need to focus on. We just need to debug and narrow down the problem.

can you use var_dump() function to see what exactly $db_result holds inside the do_query() function and also  after the line 194 or
$db_result = do_query($sql, $dblink);

try something like

$db_result = do_query($sql, $dblink);
var_dump($db_result);

Please post back the result.

Note:
For more info on var_dump()
http://us3.php.net/manual/en/function.var-dump.php
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tr5Author Commented:
I found the problem. What I ws doing was inside the
while($row = mysql_fetch_array($db_result, MYSQL_ASSOC))
loop, I did another query:
$sql = "UPDATE exhibitors SET exh_update_page_sent = '1'
WHERE exh_company_code='$row['exh_company_code']';";

and obviously this screwed something up.

Thank you akshah123 for your help.

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.