Page 1 of 2

Error with SELECT.....WHERE... Statement

Posted: Fri Nov 24, 2006 1:49 pm
by d3chapma
I'm trying to select a field in a row of a table where a boolean field is set to True. The field name is 'title', the table name is 'form' and the boolean field is 'default' and in the same table. The statement I am using is as follows:

Code: Select all

SELECT title FROM form WHERE default IS TRUE
When I try to use this statement in a query it I get the following error:

Code: Select all

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\wamp\www\form\includes\DbConnector.php on line 53
Any ideas on why my statement is giving me this error?

Posted: Fri Nov 24, 2006 3:38 pm
by volka
default is a reserved word. see http://dev.mysql.com/doc/refman/5.1/en/ ... words.html

And you didn't check the return value of mysql_query(), it returns FALSE if something went wrong with a SELECT statement. Use mysql_error() to retrieve the error message then.

Posted: Mon Nov 27, 2006 8:44 am
by d3chapma
Ok, so I changed my column name to defaultform so now the code now reads:

Code: Select all

SELECT * FROM form WHERE defaultform IS TRUE
and the error that I am getting from mysql_error() is:

Code: Select all

Unknown column 'defaultform' in 'where clause'
I have checked and double checked to make sure that there were not any typos in my code or the table. I am 100% certain that there is a column named defaultform.

Any ideas?

(Thanks for the help thus far)

Posted: Mon Nov 27, 2006 9:31 am
by aaronhall
Copy the table schema here

Posted: Mon Nov 27, 2006 9:44 am
by d3chapma

Code: Select all

_______________________________________________________________________________________________________________                             
|id  |  defaultform  | 	 frequency   |	      title        |                   question1   	                    |
|2   |	    1       |         0       |   MMNP Questionnaire | Did you carry your smartphone with you today to sc...|
The table basicly has this format. I don't see why you would need anymore. There are actually over 100 columns so I'd rather not try to post all of that.

Posted: Mon Nov 27, 2006 9:47 am
by aaronhall
And that is definitely the "form" table? Not the "Form" table or the "from" table?

Posted: Mon Nov 27, 2006 9:58 am
by d3chapma
Yes. Definitely.

If I take out the WHERE clause the statement works fine.

Posted: Mon Nov 27, 2006 11:02 am
by RobertGonzalez
Try changing the where clause to 'default = 1', like this...

Code: Select all

SELECT * FROM `form` WHERE `defaultform` = 1;

Posted: Mon Nov 27, 2006 11:16 am
by volka
and please post the (textual) result of

Code: Select all

SHOW CREATE TABLE title

Posted: Mon Nov 27, 2006 12:31 pm
by d3chapma
Everah wrote:Try changing the where clause to 'default = 1', like this...

Code: Select all

SELECT * FROM `form` WHERE `defaultform` = 1;
That gives me a Syntax error. But,

Code: Select all

 SELECT * FROM form WHERE 'defaultform' = 1 
gives me no error. Although it also doesn't return any rows even though in the table there is an entry where defaultform = 1
volka wrote:and please post the (textual) result of

Code: Select all

SHOW CREATE TABLE title
I'm not sure what you are looking for when you say this. If I use that statement I get an error telling me that the table 'title' does not exist.

Posted: Mon Nov 27, 2006 12:43 pm
by volka
oops, sorry. SHOW CREATE TABLE form of course.
SELECT * FROM form WHERE 'defaultform' = 1
' marks a string literal. 'defaultform'=1 compares the string defaultform to 1 and ...they differ ;)

Posted: Mon Nov 27, 2006 12:47 pm
by RobertGonzalez
Do

Code: Select all

SHOW CREATE TABLE `form`;

Posted: Mon Nov 27, 2006 12:53 pm
by d3chapma
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


[color=green][b]Everah[/b] | Edited the original post to replace \n with actual new lines.[/color]
Here are the results:

[syntax="sql"]CREATE TABLE `form` (
  `id` int(11) NOT NULL auto_increment,
  ` defaultform` tinyint(1) NOT NULL,
  `frequency` tinyint(4) NOT NULL,
  `title` text NOT NULL,
  `question1` text NOT NULL,
  `q1type` tinyint(4) NOT NULL,
  `q1ans1` text NOT NULL,
  `q1ans2` text NOT NULL,
  `q1ans3` text NOT NULL,
  `q1ans4` text NOT NULL,
  `q1ans5` text NOT NULL,
  `q1ans6` text NOT NULL,
  `q1ans7` text NOT NULL,
  `q1ans8` text NOT NULL,
  `question2` text NOT NULL,
  `q2type` tinyint(4) NOT NULL,
  `q2ans1` text NOT NULL,
  `q2ans2` text NOT NULL,
  `q2ans3` text NOT NULL,
  `q2ans4` text NOT NULL,
  `q2ans5` text NOT NULL,
  `q2ans6` text NOT NULL,
  `q2ans7` text NOT NULL,
  `q2ans8` text NOT NULL,
  `question3` text NOT NULL,
  `q3type` tinyint(4) NOT NULL,
  `q3ans1` text NOT NULL,
  `q3ans2` text NOT NULL,
  `q3ans3` text NOT NULL,
  `q3ans4` text NOT NULL,
  `q3ans5` text NOT NULL,
  `q3ans6` text NOT NULL,
  `q3ans7` text NOT NULL,
  `q3ans8` text NOT NULL,
  `question4` text NOT NULL,
  `q4type` tinyint(4) NOT NULL,
  `q4ans1` text NOT NULL,
  `q4ans2` text NOT NULL,
  `q4ans3` text NOT NULL,
  `q4ans4` text NOT NULL,
  `q4ans5` text NOT NULL,
  `q4ans6` text NOT NULL,
  `q4ans7` text NOT NULL,
  `q4ans8` text NOT NULL,
  `question5` text NOT NULL,
  `q5type` tinyint(4) NOT NULL,
  `q5ans1` text NOT NULL,
  `q5ans2` text NOT NULL,
  `q5ans3` text NOT NULL,
  `q5ans4` text NOT NULL,
  `q5ans5` text NOT NULL,
  `q5ans6` text NOT NULL,
  `q5ans7` text NOT NULL,
  `q5ans8` text NOT NULL,
  `question6` text NOT NULL,
  `q6type` tinyint(4) NOT NULL,
  `q6ans1` text NOT NULL,
  `q6ans2` text NOT NULL,
  `q6ans3` text NOT NULL,
  `q6ans4` text NOT NULL,
  `q6ans5` text NOT NULL,
  `q6ans6` text NOT NULL,
  `q6ans7` text NOT NULL,
  `q6ans8` text NOT NULL,
  `question7` text NOT NULL,
  `q7type` tinyint(4) NOT NULL,
  `q7ans1` text NOT NULL,
  `q7ans2` text NOT NULL,
  `q7ans3` text NOT NULL,
  `q7ans4` text NOT NULL,
  `q7ans5` text NOT NULL,
  `q7ans6` text NOT NULL,
  `q7ans7` text NOT NULL,
  `q7ans8` text NOT NULL,
  `question8` text NOT NULL,
  `q8type` tinyint(4) NOT NULL,
  `q8ans1` text NOT NULL,
  `q8ans2` text NOT NULL,
  `q8ans3` text NOT NULL,
  `q8ans4` text NOT NULL,
  `q8ans5` text NOT NULL,
  `q8ans6` text NOT NULL,
  `q8ans7` text NOT NULL,
  `q8ans8` text NOT NULL,
  `question9` text NOT NULL,
  `q9type` tinyint(4) NOT NULL,
  `q9ans1` text NOT NULL,
  `q9ans2` text NOT NULL,
  `q9ans3` text NOT NULL,
  `q9ans4` text NOT NULL,
  `q9ans5` text NOT NULL,
  `q9ans6` text NOT NULL,
  `q9ans7` text NOT NULL,
  `q9ans8` text NOT NULL,
  `question10` text NOT NULL,
  `q10type` tinyint(4) NOT NULL,
  `q10ans1` text NOT NULL,
  `q10ans2` text NOT NULL,
  `q10ans3` text NOT NULL,
  `q10ans4` text NOT NULL,
  `q10ans5` text NOT NULL,
  `q10ans6` text NOT NULL,
  `q10ans7` text NOT NULL,
  `q10ans8` text NOT NULL,
  `question11` text NOT NULL,
  `q11type` tinyint(4) NOT NULL,
  `q11ans1` text NOT NULL,
  `q11ans2` text NOT NULL,
  `q11ans3` text NOT NULL,
  `q11ans4` text NOT NULL,
  `q11ans5` text NOT NULL,
  `q11ans6` text NOT NULL,
  `q11ans7` text NOT NULL,
  `q11ans8` text NOT NULL,
  `question12` text NOT NULL,
  `q12type` tinyint(4) NOT NULL,
  `q12ans1` text NOT NULL,
  `q12ans2` text NOT NULL,
  `q12ans3` text NOT NULL,
  `q12ans4` text NOT NULL,
  `q12ans5` text NOT NULL,
  `q12ans6` text NOT NULL,
  `q12ans7` text NOT NULL,
  `q12ans8` text NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 

feyd | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Mon Nov 27, 2006 1:26 pm
by RobertGonzalez
There is a space in the front of the field name 'defaultform'.

Code: Select all

` defaultform`

Posted: Mon Nov 27, 2006 1:34 pm
by feyd
Are there always going to be twelve questions with eight answers for each?