problem with date time inserting into database.

[aron]

im requesting and formating my dates and time into a string from my form (they come in as individual droplist.)

$auction_date = "";
$auction_date .= ("$date1Day/$date1Month/$date1Year");
$auction_time_open = "";
$auction_time_open .= ("$time1Hour:$time1Minute");

then im inserting them into fileds in my dtaabase.

auction_date == date (type in database)
auction_time_open == time (type n database)

$sql = "INSERT into Listings values($auction_date, $auction_time_open, '$suburb',.....")
$result = mysql_query($sql)
or die("Invalid query: " . mysql_error());

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 ':10, 'AM', 12:30, 'PM', 'venue', 4, 6, 3, 4, 2, 2, 1, 1, 1, 1,

from that im guessing i need to insert the time into the database as a time. so need to convert it to time.

anybody able to help.

thanks.

[pipe_girl]

I would start by changing your syntax to:

$sql = "INSERT into Listings (column1,column2,column3) values('$auction_date','$auction_time_open','$suburb')"or die("Invalid query: " . mysql_error());
$result = mysql_query($sql);

I also moved the double quotes to the end of the query (outside of the parentheses) and added the ending semi colon to the "$sql" and "$result" lines.

That may solve the problem, but if it still doesn't work we can figure out how you have the time formatted.

Sound good?
Jamie

[LordShryku]

Originally posted by pipe_girl

$sql = "INSERT into Listings (column1,column2,column3) values('$auction_date','$auction_time_open','$suburb')"or die("Invalid query: " . mysql_error());
$result = mysql_query($sql);

That won't provide the error since mysql hasn't performed anything yet.

I'd suggest echoing your sql to the screen to check the syntax.

Also, mysql's date format is Y-m-d. Your auction_date is d/m/Y.

[aron]

is this date being the american form going to be a problem.

also my sql insert string is tool long and is wrapping. how do i break it so i can return it to another line.

eg.,

$sql = "INSERT into Listings (street_number,street_name,suburb,type,status,property_id,price_prefix,price,auction_display,auction_date,auction_time_open,auction_open_select,auction_time_close,auction_close_select,auction_venue,floor_area,land_area,beds,toilets,baths,lugarage,air_con,fireplace,Security,home_auto,spa,pool,ensuite,tennis_court,home_theatre,central_vacuum,study,horses,jetty,terrace_balcony,extra_features,headline,brief_description,description,agent) values('$street_number', '$street_name', '$suburb', '$type', '$status', $property_id, '$price_prefix', $price, '$auction_display', '$auction_date', '$auction_time_open', '$auction_open_select', '$auction_time_close', '$auction_close_select', '$auction_venue', $floor_area, $land_area, $beds, $toilets, $baths, $lugarage, $air_con, $fireplace, $Security, $home_auto, $spa, $pool, $ensuite, $tennis_court, $home_theatre, $central_vacuum, $study, $horses, $jetty, $terrace_balcony, '$extra_features', '$headline', '$brief_description', '$description', $agent)" or die("Invalid query: " . mysql_error());

$sql = "INSERT into Listings (street_number,street_name,suburb,type,status,property_id,price_prefix,price,auction_display,auction_date,auction_time_open,auction_open_select,auction_time_close,auction_close_select,auction_venue,floor_area,land_area,beds,toilets,baths,lugarage,air_con,fireplace,Security,home_auto,spa,pool,ensuite,tennis_court,home_theatre,central_vacuum,study,horses,jetty,terrace_balcony,extra_features,headline,brief_description,description,agent)
*break here* is it just ". (in ASP its just "_)
values('$street_number', '$street_name', '$suburb', '$type', '$status', $property_id, '$price_prefix', $price, '$auction_display', '$auction_date', '$auction_time_open', '$auction_open_select', '$auction_time_close', '$auction_close_select', '$auction_venue', $floor_area, $land_area, $beds, $toilets, $baths, $lugarage, $air_con, $fireplace, $Security, $home_auto, $spa, $pool, $ensuite, $tennis_court, $home_theatre, $central_vacuum, $study, $horses, $jetty, $terrace_balcony, '$extra_features', '$headline', '$brief_description', '$description', $agent)" or die("Invalid query: " . mysql_error());

[aron]

it all works now. apart from the date. it just dosent insert anything in.

anyboday abale to help.

[LordShryku]

Like I said. The date format is wrong. To store it in mysql, use the Y-m-d format. You can reformat it anyway you want in you select statement when you pull it out.