Help with simple query

It should work. Are you running the query from any scripting language?

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...)

did you try double-quotes instead of single-quotes?

ike

Yes. It doesn't work either.

Turn on query logging for your mysqld.  What is the actual query string that mysqld receives?

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.

Hi tr5,

I am not sure where does "SHOW KEYS FROM " comes into play in your error message?

It's part of the "graphical" error message that phpMyAdmin gives. I thought it might help.

If you can't access the server, you can't read the logs anyway.

Does the field, exh_company_name, exist in the table? It doesn't show in your example table description.

yes.

NO to be redundadent but can you please post "SHOW create table" result from both MySQL servers?

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

Well, try changing

`exh_submitted` char(2) default NULL,

to

`exh_submitted` char(1) default NULL,

No. It doesn't make a difference.

Is it the MyISAM or InnoDB difference?

>>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

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.

If you post your php code that calls this query, may be we can help identify the culprit.

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.

>>>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());

change do_query($sql, $dblink);
to
mysq_query($sql, $dblink);

sorry it is
mysql_query($sql, $dblink);

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;
}

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.