 |  |  |  |  |  |  |  |
php/mysql display data in row and column
Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases
Thread Solved |
•
•
Join Date: Oct 2006
Posts: 200
Reputation: 
Solved Threads: 2
Posting Whiz in Training
I think generally to store dates into the database its best to use a timestamp. A timestamp shows the number of seconds that have passed since January 1, 1970 00:00:00 GMT. You get the current timestamp in php by using:
You can convert a date into a timestamp using the following:
And you can convert timestamp into a date by using this:
Using these functions, get the user to enter in their date in the format you want (DD/MM/YY) using 3 different text boxes like this:
then on the next page process it like this:
Then later if you want to get the timestamp from the database do something like this:
the good thing about using timestamps is that if you want to order the items in your database by date you can do it easily. Also if you want to get bookings within the last month, do something like this:
And no problem about the help - iv had so much help from people like "nav33n" from this forum over the years, its good to give something back. I hope what im saying isnt too complicated - I get a bit excited xD
time(); //http://uk.php.net/manual/en/function.time.php You can convert a date into a timestamp using the following:
mktime('','','',$month,$day,$year); //http://uk.php.net/manual/en/function.mktime.php And you can convert timestamp into a date by using this:
date('D-M-Y',$timestamp); Using these functions, get the user to enter in their date in the format you want (DD/MM/YY) using 3 different text boxes like this:
<form action="process.php" method="post"> <input type="text" name="day" value="day"/><br /> <input type="text" name="month" value="month"/><br /> <input type="text" name="year" value="year"/><br /> <input type="submit" /><br /> </form>
then on the next page process it like this:
<?php
$day = $_POST['day'];
$month = $_POST['month'];
$year = $_POST['year'];
$timestamp = mktime('','','',$month,$day,$year);
//then store the timestamp into the database
?>Then later if you want to get the timestamp from the database do something like this:
<?php
$get_date = mysql_query('SELECT timestamp FROM table WHERE bookingID = "'.$bookingID.'"');
$timestamp = mysql_fetch_array($get_date);
$timestamp = $date['timestamp'];
$timestamp = date('D-M-Y',$timestamp);
//$timestamp is now a readable version of the timestamp date in the database
?>the good thing about using timestamps is that if you want to order the items in your database by date you can do it easily. Also if you want to get bookings within the last month, do something like this:
$now = time(); $oneMonthInSeconds = 2629743; $lastmonth = $now - $oneMonthInSeconds; $query = 'SELECT * FROM table WHERE timestamp > "'.$lastmonth.'" ';
And no problem about the help - iv had so much help from people like "nav33n" from this forum over the years, its good to give something back. I hope what im saying isnt too complicated - I get a bit excited xD
Ill solve somebody's thread someday! xD
•
•
Join Date: Oct 2006
Posts: 200
Reputation:
Solved Threads: 2
Posting Whiz in Training
Sure,
That should give you what you want.
$sql="INSERT INTO bookings
(clientID, roomID, startdate, enddate, adults, children, roomtype, requirements)
VALUES
('$last_insert_client_id',
'NULL',
' str_to_date($_POST[startdate],'%d-%m-%Y')',
' str_to_date($_POST[enddate],'%d-%m-%Y')',
'$_POST[adults]',
'$_POST[children]',
'$_POST[roomtype]',
'$_POST[requirements]')
";That should give you what you want.
Ill solve somebody's thread someday! xD
•
•
Join Date: Dec 2007
Posts: 74
Reputation:
Solved Threads: 0
Junior Poster in Training
Hi
This is what I get:
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 '%m-%Y')','str_to_date(05/04/2009,'%d-%m-%Y')','1', '0','Single'
Any more advice?
Thanks
HB25
This is what I get:
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 '%m-%Y')','str_to_date(05/04/2009,'%d-%m-%Y')','1', '0','Single'
Any more advice?
Thanks
HB25
echo your query. Execute it in phpmyadmin. See what's the exact error.
P.S. I tried to work with str_to_date and I simply can't get it to work !
P.S. I tried to work with str_to_date and I simply can't get it to work !
Ignorance is definitely not bliss!
*PM asking for help will be ignored*
*PM asking for help will be ignored*
•
•
Join Date: Dec 2007
Posts: 74
Reputation:
Solved Threads: 0
Junior Poster in Training
Hi
I have opened this thread in another form and this is what I have been giving as a code but it will return an error saying (Error: Column 'startdate' cannot be null) any advice?
I have opened this thread in another form and this is what I have been giving as a code but it will return an error saying (Error: Column 'startdate' cannot be null) any advice?
MySQL Syntax (Toggle Plain Text)
Hmm.. Okay! From what I have read so far, this is what STR_TO_DATE do.
STR_TO_DATE function will format string and insert it to the date column. The second parameter 'format' is just to let mysql know in what format is the first parameter 'date_string' is in.
In the above example, date_string is 31/2004/04. I am asking mysql to insert this value to the table and the format in which I have given the date is, %d/%Y/%m. ie., date/Year/month .
mysql Syntax (Toggle Plain Text)
mysql Syntax (Toggle Plain Text)
Ignorance is definitely not bliss!
*PM asking for help will be ignored*
*PM asking for help will be ignored*
•
•
Join Date: Dec 2007
Posts: 74
Reputation:
Solved Threads: 0
Junior Poster in Training
Hi
So how can we apply this to my query if you think it’s going to work?
Regards
HB25
So how can we apply this to my query if you think it’s going to work?
Regards
HB25
My suggestion is, store date in YYYY-mm-dd format itself. You can change the format using php's date function. As maxmumford has already mentioned, you can convert that date to unixtimestamp and use it in whatever format you want.
Ignorance is definitely not bliss!
*PM asking for help will be ignored*
*PM asking for help will be ignored*
•
•
•
•
Originally Posted by HB25 
Hi
So how can we apply this to my query if you think it’s going to work?
Regards
HB25
Why do you want to store it in that format anyway ?
Ignorance is definitely not bliss!
*PM asking for help will be ignored*
*PM asking for help will be ignored*
•
•
Join Date: Dec 2007
Posts: 74
Reputation:
Solved Threads: 0
Junior Poster in Training
As i live in the UK and the format for date is like this.
I think i will go with your advice i shoud leve it as its.
BTW MaxMumford has solved my problem this time how i could mark him as a solver for my thread?
Once again thank you for all your kind help.
Best wishes
HB 25
I think i will go with your advice i shoud leve it as its.
BTW MaxMumford has solved my problem this time how i could mark him as a solver for my thread?
Once again thank you for all your kind help.
Best wishes
HB 25
 |
Similar Threads
- Need Help...Please (MySQL)
- please help (MySQL)
- a little help (PHP)
- Insert Selected data In MySQL (PHP)
- Displaying images from mysql database (PHP)
- Display morethan one coulmn from mysql database in PHP (PHP)
- Selecting info from multiple mysql tables and display (PHP)
- AJAX generated <select> and FIREFOX (JavaScript / DHTML / AJAX)
- How to display rowspan format table? (PHP)
Other Threads in the MySQL Forum
- Previous Thread: MySql DB crashing:
- Next Thread: MySql Search using multiple input.
| Thread Tools | Search this Thread |
You need to join our community in order to build your list of favorite forums. You can visit the forum index for a listing of all forums.
Related Forum Features
Latest MySQL Posts
- Today's Posts
- Unanswered Threads
agplv3 alfresco amazon api artisticlicense aws bizspark breathalyzer camparingtocolumns changingprices cmg communityjournalism contentmanagement contractors copyright count court crm database design developer development distinct drupal dui ec2 email enterprise eudora facebook form foss gartner gnu government gpl greenit groklaw groupware hiring hyperic images innerjoins insert ip joebrockmeier join journalism keyword keywords kickfire laptop law legal license licensing linux maintenance managing mariadb matchingcolumns metron micromanage microsoft microsoftexchange mindtouch montywidenius mozilla music mysql mysqlcolumnupdating mysqldatetimeordermax() mysqlindex mysqlinternalqueries mysqlquery mysqlsearch news open-xchange opendatabasealliance opensource oracle penelope php priceupdating query referencedesign reorderingcolumns resultset saas select sharepoint simpledb sourcecode spotify sql sugarcrm syntax techsupport thunderbird transparency
