date error

[Justafriend]

Ok i have a code that is supposed to put the checkbox number into a data base the table is set up as

playernick card1 card 2 card 3 random(to be used later so null in this application)  and the date

 

but when i fill out the form i get this error

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 'CURRENT_DATE()')' at line 1

 

here is the php code

from below where it connects to database

$checkBox = implode(',', $_POST['whatcard']);
if(isset($_POST['submit']))
{       
  $query="INSERT INTO earlyreg VALUES (playernick,'" . $checkBox . "',.,CURRENT_DATE()')";     
mysql_query($query) or die (mysql_error() );
echo "Complete";
}
?>

[cyberRobot]

There is a rogue quote after the call to CURRENT_DATE(). Try changing this

$query="INSERT INTO earlyreg VALUES (playernick,'" . $checkBox . "',.,CURRENT_DATE()')";

To this

$query="INSERT INTO earlyreg VALUES (playernick,'" . $checkBox . "',.,CURRENT_DATE())";

Also, strings need to be surrounded by quotes. So I imagine you'll need to do something like this

$query="INSERT INTO earlyreg VALUES ('playernick', '" . $checkBox . "', '.', CURRENT_DATE())";

Edited December 23, 2015 by cyberRobot

[Justafriend]

i used a code snippet to get the checkbox numbers into the db  but im not sure if its pasting it right as now i get a  error

Column count doesn't match value count at row 1

 

to be more precise I have  a form where they enter player name  select 3 images that are named with just numbers  and the current date I also moved the column for random which is  something I will be implementing at a later date with random picks of  images

 

 `Name` VARCHAR(16) 
 `Card1` INT(2) 
 `Card2` INT(2) 
 `Card3` INT(2) 
 `Date` DATE NOT NULL,
 `Random` TINYTEXT
 

card 1 card 2 and card 3 are 3 card numbers that  are used from the selection

 

any help will be appreciated

 

[benanamen]

Un Checked check boxes do not submit any data whatsoever.

[Justafriend]

yes only the checked boxes submit data   there are 40 checkboxes users can only select a maximum of 3

[mac_gyver]

your current error is because the number of columns in your db table doesn't match the number of data items.

 

you need to echo out the sql query statement in $query so that you can see what it is.

 

also, you should ALWAYS use the form of INSERT query syntax where you list out the columns. this will help you insure that you are building the sql query statement correctly since the column list will be right there in the code when you are writing the code to put the data into the query.

 

next, your database design needs some help. you should be storing a user_id, not a name, in the table and you should not have columns with names like card1, card2, card3. each piece of data should be stored as a separate row.

 

lastly, you should not be putting external data directly into an sql query statement (you should be using a prepared query or properly escaping/casting data as the correct type) and the msyql_ database functions are obsolete and have been removed from the latest version of php. the PDO database class is the best choice for replacing the mysql_ functions.

[Justafriend]

ok updated code as suggested about  the insert query syntax

echo $query="INSERT INTO earlyreg 
(name, card1, card2, card3, date) VALUES ('playernick', '" . $checkBox . "',  CURRENT_DATE())";

Now when I run it  and fill out the form I get 

 

INSERT INTO earlyreg (name, card1, card2, card3, date) VALUES ('playernick', '5,6,7', CURRENT_DATE())Column count doesn't match value count at row 1

 

even though I have 5  columns now and showing all 5  in the echo  I am still getting Column count doesn't match value count at row 1

 

the reason I am using  the 3 cards into one row is that I am trying to  keep it  as simple for the people that need to see it and change things so they arent having to look in the tables for 3 different entries  for one player.

 

as for the name column its just the title of the column it is actually getting the player nickname from the form

[benanamen]

Dont use if(isset($_POST['submit']))

 

It can fail in certain circumstances in Internet Explorer.

 

 

Your quotes outside the numbers are messing you up. It is trying to insert a single value as 5,6,7

Edited December 24, 2015 by benanamen

[Justafriend]

​so change this to

echo $query="INSERT INTO earlyreg 
(name, card1, card2, card3, date) VALUES ('playernick', '" . $checkBox . "',  CURRENT_DATE())";

to this

echo $query="INSERT INTO earlyreg 
(name, card1, card2, card3, date) VALUES ('playernick', ' . $checkBox . ',  CURRENT_DATE())";

and where should I look to replace the if(isset($_POST['submit']))

[benanamen]

if ($_SERVER['REQUEST_METHOD'] == 'POST')