Unknown column 'Date' in 'where clause'

Question

I have the following query:

select * from invoice WHERE Date >= '$date' AND status='$status'

It returns an error:

Unknown column 'Date' in 'where clause'

I am not too good in PHP or MYSQL, but I dont see anything wrong. There is probably everything wrong though. I need assistance!

EDIT:

$var = $_GET['date1_year'] ."-" . $_GET['date1_month'] ."-" . $_GET['date1_day'];
$query = "select * from invoice WHERE DateOfCreation >= '$var' AND status='$status'";

And ofcourse it is logged in(authenticated) and is being executed with no errors, but no proper result either. My DateOfCreation is a DATE type and there are actually the $_GET variables. I want it to search for all records before $var.

Is there a `Date` column in the table...? Can it be spelled `date` instead of `Date`? More info please. If it isn't a spelling/table not having column then I can think of perhaps a conflict with a Date function.. — meder omuraliev, Jan 19 '11 at 18:35
As a begginer, I assumed this would search for when the record was created and not a "date" Column. — DogPooOnYourShoe, Jan 19 '11 at 18:36
You may want to look at [surrounding your column name in backticks](http://stackoverflow.com/questions/261455/using-backticks-around-field-names) just to avoid any data-type collisions. — Brad Christie, Jan 19 '11 at 18:37
If your using Linux based MySql, the columns are case sensitive i belive, on windows it does not matter, does your column start with an upper case ? — RobertPitt, Jan 19 '11 at 18:38
It is not a column, I thought date is when the record is created or is there something else? — DogPooOnYourShoe, Jan 19 '11 at 18:39
No. You are referencing a column. You can use the Mysql date function if you need it, though. — meder omuraliev, Jan 19 '11 at 18:40
@Brad Christie, does the `'` single quotes not have the same effect as the backticks? — RobertPitt, Jan 19 '11 at 18:40
@DogPooOnYourShoe: No, if you want the date the row was created you'll need to create a column for it. Date reference here: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html — Jonah, Jan 19 '11 at 18:41
@DogPooOnYourShoe: That depends on what you need to do with it. — Jonah, Jan 19 '11 at 18:42
Ok, Now I have a Column named DateOfCreation and a Variable called $date I want the query to search for all records which are before $date. — DogPooOnYourShoe, Jan 19 '11 at 18:48
DateOfCreation obviously contains when it the row was created in a VarChar which has "19-01-2011" inside of it — DogPooOnYourShoe, Jan 19 '11 at 18:49
Don't use VarChar. Use the date type. That way you can manipulate it better and do comparisons. If you MUST use varchar, at least sort your date like YYYY-MM-DD so that you can do < and > easier. — Bob Baddeley, Jan 19 '11 at 18:54
DateOfCreation should be a DATE or DATETIME type. Your comparison won't work as expected otherwise. — jlasarte, Jan 19 '11 at 18:54
mno4k and Bob Baddeley, I have changed it to Date and edited it properly. — DogPooOnYourShoe, Jan 19 '11 at 19:00
Unfortunately, it does not search for before $date. I will edit my question to show what my code is. — DogPooOnYourShoe, Jan 19 '11 at 19:05
Doesnt matter, I just had my variables set incorrectly. It is now working correctly. — DogPooOnYourShoe, Jan 19 '11 at 19:27

score 1 · Answer 1 · edited May 23 '17 at 12:04

1

select * from invoice WHERE DateOfCreation >= '$date' AND status='$status'

While it would work, I encourage you to look into parameterized queries in order to prevent SQL injection attacks. See here: How can I prevent SQL injection in PHP?

edited May 23 '17 at 12:04

Community

1
1

answered Jan 19 '11 at 18:53

Rob Allen

17,381
5
52
70

score 0 · Answer 2 · answered Jan 19 '11 at 20:11

for all records before $var

So you mean this then? (changed >= to <)

$var = $_GET['date1_year'] ."-" . $_GET['date1_month'] ."-" . $_GET['date1_day'];
$query = "select * from invoice WHERE DateOfCreation < '$var' AND status='$status'";

With your EDIT, I assume this error is gone?

Unknown column 'Date' in 'where clause'

Unknown column 'Date' in 'where clause'

2 Answers2