Page 1 of 1

inserting more than one item into one db column

Posted: Mon Jan 09, 2006 2:29 pm
by dru_nasty
I've got a basic form that adds content to my database. It's for sports matchups and the results of those games.
Everything is working fine except for one part of the script. I know this because i tested adding each individual item.
Here is the code for the form of the part i'm having problems with:

Code: Select all

<select class="tblack" name="month2">
                            <? select_month(); ?>
                          </select> <select class="tblack" name="day2">
                            <? select_day($day2); ?>
                          </select> <select class="tblack" name="year2">
                            <? select_year(); ?>
                          </select>
And here is the script that adds the items from the form:

Code: Select all

<?PHP
(connection info up here)

$sql ="INSERT INTO `results_archives` (sport, date, day, play, stars, w_l_p, win, loss, push, bb_win, bb_loss, bb_push) values ('$_POST[sport]','$_POST[month2]-$_POST[day2]-$_POST[year2]','$_POST[day]','$_POST[play]','$_POST[stars]','$_POST[win_lose_push]','$_POST[win]','$_POST[loss]','$_POST[push]','$_POST[bb_win]','$_POST[bb_loss]','$_POST[bb_push]')";

if (mysql_query($sql,$db_conn)){
echo "records added!";
}else{
echo "something went wrong";
}
?>
my problem lies in this part of the script:

Code: Select all

'$_POST[month2]-$_POST[day2]-$_POST[year2]'
I'm tryin to add those three items into one field so the date would show like 1-9-2006, but I keep getting my "something went wrong" echo.

Posted: Mon Jan 09, 2006 2:35 pm
by timvw
You've got a couple of errors...

1-) missing posted input validation

2-) wrong use of index in array:
$_POST['play'] not $_POST[play]

3-) missing sql output preparation:
mysql_real_escape_string($_POST['play']);

4-) wrong mysql date(time) format:
yyyy-mm-dd

Posted: Mon Jan 09, 2006 2:42 pm
by dru_nasty
Sorry, I really don't know what 1, 3, and 4 mean. I'm still learning.

Posted: Mon Jan 09, 2006 3:41 pm
by feyd
4 is talking about the MySQL date format. In plain english the format is: four digit year, dash, two digit month (leading zero), dash and two digit day of month (leading zero).

Posted: Mon Jan 09, 2006 3:47 pm
by dru_nasty
K I caught that and changed to order to year, month, date.
In my database i have the date column set to date.
I just don't know what is wrong with that line.

Posted: Mon Jan 09, 2006 3:51 pm
by mickd
you can try changing

Code: Select all

'$_POST[month2]-$_POST[day2]-$_POST[year2]'
to either

Code: Select all

'" . $_POST['month2'] . "-" . $_POST['day2'] . "-" . $_POST['year2'] . "'
'{$_POST['month2']}-{$_POST['day2']}-{$_POST['year2']}'
the array index has to be quoted if it is a string, if its a interger or constant then you dont have to.

Posted: Mon Jan 09, 2006 4:01 pm
by dru_nasty
No dice with that either.... :?

Posted: Mon Jan 09, 2006 5:06 pm
by timvw
1-) you should validate if the user really posted a year, month and day... What happens if someone posts XXXX ?
3-) if you want to write a mysql query you have to make sure all data is conform that language (meaning ' has to be escaped, ...) The easiest to achieve that is by using http://www.php.net/mysql_real_escape_string
4-) if you insert a value of type date mysql wants you to pass that value in the format of yyyy-mm-dd (so 4 numbers for the year, a bar , 2 numbers for the month, a bar and 2 numbers for the day)

Code: Select all

$query = "INSERT INTO table (somedate) VALUES ('{$_POST['year']}-{$_POST['month']}-{$_POST['day']}');";

Posted: Mon Jan 09, 2006 5:41 pm
by dru_nasty
Here's what i've got now and still the same error:

Code: Select all

$sql ="INSERT INTO `results_archives` (sport, date, day, play, stars, w_l_p, win, loss, push, bb_win, bb_loss, bb_push) values ('$_POST[sport]','{$_POST['year2']}-{$_POST['month2']}-{$_POST['day2']}','$_POST[day]','$_POST[play]','$_POST[stars]','$_POST[win_lose_push]','$_POST[win]','$_POST[loss]','$_POST[push]','$_POST[bb_win]','$_POST[bb_loss]','$_POST[bb_push]')";

Posted: Tue Jan 10, 2006 3:57 am
by Jenk
change:

Code: Select all

if (mysql_query($sql,$db_conn)){ 
echo "records added!"; 
}else{ 
echo "something went wrong"; 
}
to

Code: Select all

mysql_query($sql,$db_conn) or die(mysql_error());
and see what the error says.

Posted: Tue Jan 10, 2006 7:05 am
by dru_nasty
Apparently I couldn't have a column named date. So I changed it to dates and made it varchar instead of date and it worked!

Posted: Tue Jan 10, 2006 9:14 am
by feyd
you can have a column named date, however because it is a keyword in the syntax, you must place it in backticks:

` vs '
The former being a backtick.

Example:

Code: Select all

SELECT `field1`, `field2` FROM `table` WHERE `field3` = 2
http://dev.mysql.com/doc/refman/4.1/en/legal-names.html

Posted: Tue Jan 10, 2006 3:13 pm
by dru_nasty
Ahhh gotchya!
Thanks to everyone for all the help and new tips :wink: