Quick SQL santax question.

[anthonydamasco]

Alright, ive been debbuging this forever does anyone see something wrong in this sql script

[code=php:0]
$sql = "INSERT INTO jobs VALUES (NULL, '$jobtitle', '$location', '$payrate', '$hours', '$jobdes', '$jobcat' '$branch', '$time', '$poscla', SYSDATE(), '$expire')";
mysql_query($sql) or die ( "Problem with the query: $sql<br>" . mysql_error() );
[/code]

the table looks like this

jobid  integer
jobtitle varchar
location varchar
payrate varchar
hours varchar
jobdescription varchar
jobcat varchar
branch varchar
time varchar
position varchar
date DATE
expire varchar


This is the error


Problem with the query: INSERT INTO jobs VALUES (NULL, 'test', 'test', 'test', 'test', 'test', 'test' 'pennsauken', 'test', 'test', SYSDATE(), '1')


ahhh! help!

[HuggieBear]

I don't know much about MySQL, but why not try...

[code]
$sql = "INSERT INTO jobs
        (jobtitle, location, payrate, hours, jobdescription, jobcat, branch, time, position, date, expire)
        VALUES
        ('$jobtitle', '$location', '$payrate', '$hours', '$jobdes', '$jobcat' '$branch', '$time', '$poscla', SYSDATE(), '$expire')";

mysql_query($sql) or die ( "Problem with the query: $sql<br>" . mysql_error() );
[/code]

All I've done is specify which columns to explicitly insert into, and dropped your initial column as I assumed it's an autoincrementing primary key.  Which is probably where the problem lies if you're trying to insert 'null' into it.

Rich

[anthonydamasco]

that just changed the error :-p

Problem with the query: INSERT INTO jobs (jobtitle, location, payrate, hours, jobdescription, jobcatagory, branch, timeneeded, positionclass, date, expire) VALUES ('test', 'test', 'test', 'test', 'test', 'test' 'cherryhill', 'test', 'test', SYSDATE(), '1')
Column count doesn't match value count at row 1

[HuggieBear]

oh ok.... I've noticed the problem it's around the date format....

[b]sysdate()[/b] will give you a value like [color=green]'0000-00-00 00:00:00'[/color] but you're trying to insert into a [color=red]date[/color] field that looks like this [color=green]'0000-00-00'[/color]

So you need to change the [color=red]date[/color] field to [color=red]datetime[/color] or swap [b]sysdate()[/b] for [b]curdate()[/b].

Regards
Rich