Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Help with simple query

Posted on 2006-04-18
25
Medium Priority
?
285 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:tr5
  • 11
  • 7
  • 3
  • +3
25 Comments
 
LVL 11

Expert Comment

by:star_trek
ID: 16477853
It should work. Are you running the query from any scripting language?
0
 

Author Comment

by:tr5
ID: 16478801
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
 
LVL 20

Expert Comment

by:ikework
ID: 16478918
did you try double-quotes instead of single-quotes?

ike
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:tr5
ID: 16479259
Yes. It doesn't work either.
0
 
LVL 22

Expert Comment

by:NovaDenizen
ID: 16479719
Turn on query logging for your mysqld.  What is the actual query string that mysqld receives?
0
 

Author Comment

by:tr5
ID: 16479900
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
 
LVL 17

Expert Comment

by:akshah123
ID: 16479951
Hi tr5,

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

Author Comment

by:tr5
ID: 16480072
It's part of the "graphical" error message that phpMyAdmin gives. I thought it might help.
0
 
LVL 22

Expert Comment

by:NovaDenizen
ID: 16480548
If you can't access the server, you can't read the logs anyway.
0
 
LVL 32

Expert Comment

by:awking00
ID: 16480688
Does the field, exh_company_name, exist in the table? It doesn't show in your example table description.
0
 

Author Comment

by:tr5
ID: 16480816
yes.
0
 
LVL 17

Expert Comment

by:akshah123
ID: 16480991
NO to be redundadent but can you please post "SHOW create table" result from both MySQL servers?

0
 

Author Comment

by:tr5
ID: 16481289
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
 
LVL 17

Expert Comment

by:akshah123
ID: 16482749
Well, try changing

`exh_submitted` char(2) default NULL,

to

`exh_submitted` char(1) default NULL,
0
 

Author Comment

by:tr5
ID: 16482808
No. It doesn't make a difference.

Is it the MyISAM or InnoDB difference?
0
 
LVL 17

Expert Comment

by:akshah123
ID: 16482900
>>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
 

Author Comment

by:tr5
ID: 16483107
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
 
LVL 17

Expert Comment

by:akshah123
ID: 16487596
If you post your php code that calls this query, may be we can help identify the culprit.
0
 

Author Comment

by:tr5
ID: 16487931
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
 
LVL 17

Expert Comment

by:akshah123
ID: 16488286
>>>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
 
LVL 11

Expert Comment

by:star_trek
ID: 16488319
change do_query($sql, $dblink);
to
mysq_query($sql, $dblink);
0
 
LVL 11

Expert Comment

by:star_trek
ID: 16488323
sorry it is
mysql_query($sql, $dblink);
0
 

Author Comment

by:tr5
ID: 16489306
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
 
LVL 17

Accepted Solution

by:
akshah123 earned 2000 total points
ID: 16490182
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
 

Author Comment

by:tr5
ID: 16490343
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

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question