Jump to content


Photo

Quick SQL santax question.


  • Please log in to reply
3 replies to this topic

#1 anthonydamasco

anthonydamasco
  • Members
  • PipPipPip
  • Advanced Member
  • 92 posts

Posted 08 September 2006 - 07:42 PM



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

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

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!

#2 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 08 September 2006 - 08:02 PM

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

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

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
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#3 anthonydamasco

anthonydamasco
  • Members
  • PipPipPip
  • Advanced Member
  • 92 posts

Posted 08 September 2006 - 08:06 PM

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

#4 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 08 September 2006 - 08:21 PM

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

sysdate() will give you a value like '0000-00-00 00:00:00' but you're trying to insert into a date field that looks like this '0000-00-00'

So you need to change the date field to datetime or swap sysdate() for curdate().

Regards
Rich
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users