inserting more than one item into one db column

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
dru_nasty
Forum Commoner
Posts: 81
Joined: Sat Sep 10, 2005 10:26 am

inserting more than one item into one db column

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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
dru_nasty
Forum Commoner
Posts: 81
Joined: Sat Sep 10, 2005 10:26 am

Post by dru_nasty »

Sorry, I really don't know what 1, 3, and 4 mean. I'm still learning.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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).
dru_nasty
Forum Commoner
Posts: 81
Joined: Sat Sep 10, 2005 10:26 am

Post 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.
mickd
Forum Contributor
Posts: 397
Joined: Tue Jun 21, 2005 9:05 am
Location: Australia

Post 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.
dru_nasty
Forum Commoner
Posts: 81
Joined: Sat Sep 10, 2005 10:26 am

Post by dru_nasty »

No dice with that either.... :?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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']}');";
dru_nasty
Forum Commoner
Posts: 81
Joined: Sat Sep 10, 2005 10:26 am

Post 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]')";
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post 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.
dru_nasty
Forum Commoner
Posts: 81
Joined: Sat Sep 10, 2005 10:26 am

Post 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!
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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
dru_nasty
Forum Commoner
Posts: 81
Joined: Sat Sep 10, 2005 10:26 am

Post by dru_nasty »

Ahhh gotchya!
Thanks to everyone for all the help and new tips :wink:
Post Reply