?
Solved

Help with insert mysql statement

Posted on 2011-04-21
8
Medium Priority
?
289 Views
Last Modified: 2012-06-27
Need some help with this mysql statement

$outDate =  $_GET["mout"].$_GET["dout"].$_GET["yout"];
$inDate =  $_GET["min"].$_GET["din"].$_GET["yin"];

echo "OutDate:".$outDate; 
echo "inDate:".$inDate;

Open in new window


my output is:
OutDate:04 21 2011 inDate:04 24 2011

outdate and indate are date fields in the database. I want to eliminate the white space and put it in the correct format for my mysql table. Currently the data is inserted however I get an error about the date time data not being correct.

$sql="INSERT INTO orderitem (ID, onum,outdate,indate,itemrentqty,itemnum)
 VALUES
 ( ' ', $_GET[ordernum], '$outDate', '$inDate' ,$_GET[itemqty], $_GET[itemnum])";

if (!mysql_query($sql,$con))
 {
 die('Error: ' . mysql_error());
 }

mysql_close($con);

Open in new window


How do I correctly insert the date into my table? Thanks
0
Comment
Question by:teedo757
  • 3
  • 3
  • 2
8 Comments
 
LVL 3

Author Comment

by:teedo757
ID: 35443589
Also is there a way to fix my bad data or do I need to drop and recreate my table? Toad does not show the corrupt records....not a big deal just wondering.
0
 
LVL 10

Expert Comment

by:ray-solomon
ID: 35443634
Your date string is incorrectly formatted for mysql.

it should look like this:
OutDate: 2011-04-21
inDate: 2011-04-24

Try this code to correct the format:
$outDate = date('Y-m-d', strtotime($outDate));
$inDate = date('Y-m-d', strtotime($inDate));
0
 
LVL 1

Assisted Solution

by:BlackSnowman
BlackSnowman earned 800 total points
ID: 35443638
I think you might need to edit the order and format of your $_GET commands to arrange the date as Y m d which mysql uses by default.


$outDate =  $_GET["yout"]."-".$_GET["dout"]."-".$_GET["mout"];
$inDate =  $_GET["yin"]."-".$_GET["din"]."-".$_GET["min"];

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 1

Expert Comment

by:BlackSnowman
ID: 35443646
wow okay well Ray and I had the same suggestion basically. The difference with mine is that you manually ensure the correct order of the date fields.
0
 
LVL 10

Expert Comment

by:ray-solomon
ID: 35443722
small correction to post above: it should be YYYY-MM-DD, not YYYY-DD-MM.

$outDate =  $_GET["yout"]."-".$_GET["mout"]."-".$_GET["dout"];
$inDate =  $_GET["yin"]."-".$_GET["min"]."-".$_GET["din"];

Open in new window

0
 
LVL 3

Author Comment

by:teedo757
ID: 35443804
Well with Rays i got

OutDate:1969-12-31inDate:1969-12-31

With BlackSnowman I got the correct date but with spaces

OutDate:2011 -21 -04 inDate:2011 -24 -04

Without corrupting more data do you know if this will work or do I need to get rid of the spaces?
0
 
LVL 10

Accepted Solution

by:
ray-solomon earned 1200 total points
ID: 35443925
The trim() function will remove whitespaces for each GET request.

$outDate =  trim($_GET["yout"])."-".trim($_GET["mout"])."-".trim($_GET["dout"]);
$inDate =  trim($_GET["yin"])."-".trim($_GET["min"])."-".trim($_GET["din"]);

Open in new window

0
 
LVL 3

Author Closing Comment

by:teedo757
ID: 35444024
Trim fixed the issue.

Had to drop the table a recreate it for the error to go away. Thanks
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses
Course of the Month15 days, 12 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question