|
PHP - Formatting Mysql Timestamp
Any ideas how I can turn "2010-10-20 19:10:56" to 20th October 2010, 19:10
Thanks lots guys, Jake Similar TutorialsHi guys, I'm putting together a small event system where I want the user to add his own date and time into a textfield (I'll probably make this a series of drop-downs/a date picker later). This is then stored as a timestamp - "0000-00-00 00:00:00" which displays fine until I try to echo it out as a UK date in this format - jS F Y, which just gives today's date but not the inputted date. Here's the code I have right now: Code: [Select] $result = mysql_query("SELECT * FROM stuff.events ORDER BY eventdate ASC"); echo "<br />"; echo mysql_result($result, $i, 'eventvenue'); echo ", "; $dt = new DateTime($eventdate); echo $dt->format("jS F Y"); In my mysql table eventdate is set up as follows: field - eventdate type - timestamp length/values - blank default - current_timestamp collation - blank attributes - on update CURRENT_TIMESTAMP null - blank auto_increment - blank Any help as to why this could be happening would be much appreciated, thanks. Hello again; I'm trying to build a little php site and some with mysql, when a record is saved on the DB i want a column with the timestamp. I've been doing a bit of reading and *i think* the database part is done but i cant figure how the insert should be done. The column on mysql is: Type: timestamp Default: current_timestamp Attributes: on update current_timestamp The structure on the table is: id, timestamp, column1, column2. I thought that since it is 'on update current_timestamp' it would fill itself properly, but when i do my insert: $query = "INSERT INTO tbl_process VALUES ('','','$value1','$value2')"; mysql_query($query) or die ("Error in query: $query " . mysql_error()); The only saved value is "0000-00-00 00:00:00" so obviusly i'm missing something... for extra points: the 'id' value is autoincrement so on the insert i just leave the value empty with ''. Is that the "correct" way of filling that field or is there a "best practice" for that? Thanks everyone How to output a MySQL timestamp field to correct local time with daylight saving with PHP?
Local time is set in php.ini with: date.timezone = "Europe/London"
Confirmed set with: <?php echo date_default_timezone_get(); ?>
But when I echo the timestamp field it does not display correct UK time for the record (an hour behind correct UK time).
<?php echo date('jS F Y - g:ia', strtotime($row['Date'])); ?>
Hi, Whenever it comes to dates I go blank and just hear/read "blah blah blah". I'm sure there's a name for a condition like that but it's causing me a problem right now. I am making a RSS feed aggregator, inserting RSS feed posts into a mysql db and want to be able to extract those posts in order by date. (so that I can create a new feed of the last posts of multiple rss feeds) My problem is that the pubDate format for feeds is Fri, 29 Oct 2010 11:22:58 +0000 and that doesn't work with mysql. Apart from having to reformat the date I have to take into consideration the +0000 part could be different depending on where in the world the feed was published. Any pointers would be gratefully received! What's the best way to format results from a MySQL table? I've found very vague examples of PHP code utilizing html tables. Below is my current PHP. Thanks! <?php $con = mysql_connect("localhost","xxxxx","xxxxx") or die('Could not connect: ' . mysql_error()); mysql_select_db("addresses", $con); $result = mysql_query("SELECT * FROM addresses"); while($row = mysql_fetch_array($result)) { echo $row['first_name'] . " " . $row['last_name'] . " " . $row['extra_info'] . " " . $row['address'] . " " . $row['city'] . " " . $row['state'] . " " . $row['zip']; echo "<br />"; } mysql_close($con); ?> I have a mysql database that has your typical data in it, ie. name address, etc. The problem I am having is with the address. The address is setup as a varchar with 200 as the length and the data is street, city, st, zip on 1 line. I need to format the output with street on 1 line and city, st, zip on another. I cannot change input format because of the way the program uses the address string to fetch the lat and lng before it writes it to the database. Is there a way to add a line break or <br> to the address before it is written or change it after it is written using sql or php script or possible as I format the output? The street, city, st, zip are separated by commas in the record. Thanks in advance for all the reply's I'm trying to modify code from a book called "PHP with MySQL" where data is read from a MySQL db, edited via a form and written back. It's all technically working but the data is written to the screen from left to right as follows (where the top row is the heading and the second row is the data retrieved from the database in an edit box.): First Name | Last Name | <David> | <Beckham> | I have tons of fields (but only have firstname/lastname in the script for now), so the headings scroll left to right on the screen and I find it really annoying. I'd like things to go top-to-bottom so it looks like this: First Name | <David> Last Name | <Beckham> I've been playing around with the code and trying to add <tr>'s here and there but I can't get it. At best, it looks like this: First Name Last Name <David> <Beckham> I'm hoping it's something simple, but I just can't see it. Any help would be appreciated... the full code is below Code: [Select] # cat editrecord.php <html> <head> <style> body {font-family:arial;} .error {font-weight:bold; color:#FF0000;} </style> <title>Edit Contact Records</title> </head> <body> <h2>Edit Contact Records:</h2> <? //connect to the database include("dbinfo.php"); $link = mysqli_connect($server, $username, $password, $database); // has the form been submitted? if ($_POST) { foreach($_POST as $k => $v) { $v = trim($v); $$k = $v; } // build UPDATE query $update = "UPDATE contact_records SET firstname='$firstname', lastname='$lastname' WHERE Id=$id"; // execute query and check for success if (!mysqli_query($link, $update)) { $msg = "Error updating data"; } else { $msg = "Record successfully updated:"; // write table row confirming data $table_row = ' <tr> <td>' . $firstname . '</td> <td>' . $lastname . '</td> </tr>'; } // if not posted, check that an Id has been passed via the URL } else { if (!IsSet($_GET['id'])) { $msg = "No customer selected!"; } else { $id = $_GET['id']; //build and execute the query $select = "SELECT firstname, lastname FROM contact_records where id=$id"; $result = mysqli_query($link, $select); // check that the record exists if (mysqli_num_rows($result)<1) { $msg = "No customer with that ID found!"; } else { // set vars for form code $form_start = "<form method=\"post\"action=\"" . $_SERVER['PHP_SELF'] . "\">"; $form_end = ' <tr> <td colspan="2"><input type="submit" value="Submit changes" /></td> <td colspan="3"><input type="reset" value="Cancel" /></td> </tr> </form>'; // assign the results to an array while ($row = mysqli_fetch_array($result)) { $firstname = $row['firstname']; $lastname = $row['lastname']; // write table row with form fields $table_row = ' <tr> <td><input type="text" name="firstname" value="' . $firstname . '" size="10" /></td> <td><input type="text" name="lastname" value="' . $lastname . '" size="10" /></td> </tr>'; } // end 'if record exists' if } //end 'if ID given in URL' if } // end 'if form posted' if } // close connection mysqli_close($link); // print error/success message echo (IsSet($msg)) ? "<div class=\"error\">$msg</div>" : ""; ?> <table border="1" cellpadding="5"> <!-- Show start-of-form code if form needed --> <? echo (IsSet($form_start)) ? $form_start : ""; ?> <input type="hidden" name="id" value="<? echo $id ?>" /> <tr> <th>First Name</th> <th>Last Name</th> </tr> <!-- Show appropriate table row code (none set if there were errors) --> <? echo (IsSet($table_row)) ? $table_row : ""; ?> <!-- Show end-of-form code if we are displaying the form --> <? echo (IsSet($form_end)) ? $form_end : ""; ?> </table> <br /><a href="records.php">Back to customer list</a> </body> </html> How would I put today date for instance into mysql_query? I just want the date and have the format be year, month, day. The format in the database looks like 2010-04-05. That being used as an example. And I have three drop down menus, which you can select the year, month, and day, but it doesn't seem to be working. What am I doing wrong? Hello Everyone, I think this might be a bit of a challenge but its always worth getting some input for solutions. I'm using jqplot to create graphs from data held in a database. And im having trouble thinking of a way to lay the data out in an acceptable way. To plot a line the javascript takes a statement in this form: line1 = [['x-axis1'],y-axis1],['x-axis2',y-axis2] I'm running this query to get the data: <?php $query2 = "SELECT * FROM tracker WHERE username = '" . mysql_real_escape_string($usera) . "'"; $result = mysql_query($query2) or die('Error, query failed : ' . mysql_error()); while($row = mysql_fetch_assoc($result)) { $data[] = $row; } ?> This returns things like this: $data[0]['date'], $data[0]['reps'] $data[1]['date'], $data[1]['reps'] etc... Values are likely to be added or removed so I need to construct a loop of some kind to format it like this, line1 = [['{$data[0]['date']}',{$data[0]['reps']}],['{$data[1]['date']}',{$data[1]['reps']}],['{$data[2]['date']}'],{$data[2]['reps']}]]; Can someone point me in the right direction? Thanks in Advanced I have a update query that should be updating the timestamp when a "month" field is changed. I have the following code:
$month_update->bindParam(':timestamp', strtotime (date("Y-m-d H:i:s")), PDO::PARAM_INT);
The result that is returned is 1403198033. I need it to look like this 2014-03-26 07:09:06. I know I am missing something and I would greatly appreciate your help.
Thank you so much!
$epocTime = $math.newInt($math.divide("$today.getTime()", "1000"))) I have this date in database I must convert it to Unix Timestamp How ? 2010-11-11 09:04:44 I try this echo mktime($mydate); but its not work its return wrong Timestamp becase if I convert back it I have wrong date 2011-02-16 21:01:33 Hey there, Thanks for taking the time to read my thread. I've encountered a problem that I cannot figure out a way to solve. I need to calculate the remaining days until a specified unix timestamp. I haven't yet figured out how to get the amount of days until the timestamp. If anybody could tell me how to get the remaining days until a unix timestamp I would be grateful, thanks. Hi, I have in my DB a column with a timestamp as definition. Now I would like to have only the month and year of that column for further use in a form. How can I achieve that? Thanks! What is the proper way to turn a date that is in "m/d/Y" format, into a correct timestamp. When I try strtotime, then try to change it back to a date, it shows up incorrectly (defaulting to the 1969 date that it does when it messes up. I just need to be able to turn $date = '10/15/2011'; to and from a timestamp correctly. Any advice? How do i correctly update a timestamp? Code: [Select] $date = date("Y-m-d H:i:s"); mysql_query("UPDATE mmembers set time=now() where email=$myusername"); I have a timestamp field in my mySQL table and need to check if it's 14 day or older. What's the best way to do this? Any help will be appreciated I've been looking at converting my php timestamps to display as month, day, and the hour and minute the event occurred on, i tried; Code: [Select] $time = date("j/n H:i", $Events['timestamp']); But no success can anyone point me in the correct direction? Thanks I need some help. I have this script that pulls timestamps from the database. What I want to do is create a count of how many of those timestamps that were returned were from the morning(6am-12pm), Afternoon (12pm-6pm), Evening (6pm-12am), or Night (12am-6am). How do I do I parse the timestamps to do that and display the count. Thanks! Code: [Select] <?php /** * @author William Morris * @copyright 2010 */ include('inc/db.php'); //function to show date and time based on timestamp function showTIME($timestamp){ $date = date('F j Y h:i:s A', $timestamp); return $date; } $startdate = "2010-07-01"; $enddate = "2010-08-05"; //query to pull time stamps in between dates //convert dates into timestamp //$startdate = strtotime($_POST['startdate']); //$enddate = strtotime($_POST['enddate']); //convert dates into morning timestamp $startdate1 = strtotime($startdate."06:00:00"); $enddate1 = strtotime($enddate."11:59:59"); $sql = mysqli_query($conn, "SELECT event_time FROM event WHERE event = 'Registration' AND event_time BETWEEN '$startdate1' AND '$enddate1'"); while($row = mysqli_fetch_array($sql)){ echo showTIME($row['event_time'])."<br>"; } ?> How would I go about adding a timestamp to my image uploads so I don't erase image files with the same name? Also, what would be the correct use of the mysql_escape_string <?php require_once('storescripts/connect.php'); mysql_select_db($database_phpimage,$phpimage); $uploadDir = 'upload/'; if(isset($_POST['upload' . $config])) { foreach ($_FILES as $file) { $fileName = $file['name']; $tmpName = $file['tmp_name']; $fileSize = $file['size']; $fileType = $file['type']; if($fileName==""){ $filePath = 'upload/'; } else{ $filePath = $uploadDir . $fileName; } $filePath = str_replace(" ", "_", $filePath); $result = move_uploaded_file($tmpName, $filePath); if(!get_magic_quotes_gpc()) { $fileName = addslashes($fileName); $filePath = addslashes($filePath); } $fileinsert[]=$filePath; } } $mid = mysql_real_escape_string(trim($_POST['mid'])); $cat = mysql_real_escape_string(trim($_POST['cat'])); $item = mysql_real_escape_string(trim($_POST['item'])); $price = mysql_real_escape_string(trim($_POST['price'])); $about = mysql_real_escape_string(trim($_POST['about'])); $fields = array(); $values = array(); $updateVals = array(); for($i=1; $i<=4; $i++) { $fields[$i] = 'name'.$i; $values[$i] = mysql_real_escape_string(basename(trim($_FILES[$fields[$i]]['name']))); if($values[$i] != '') { $updateVals[] = "{$fields[$i]} = '{$values[$i]}'"; } } $updateNames = ''; if(count($updateVals)) { $updateNames = ", " . implode(', ', $updateVals); } $update = "INSERT INTO image (mid, cid, item, price, about, name1, name2, name3, name4) VALUES ('$mid', '$cat', '$item', '$price', '$about', '$values[1]', '$values[2]', '$values[3]', '$values[4]') ON DUPLICATE KEY UPDATE cid = '$cat', item = '$item', price = '$price', about = '$about' $updateNames"; $result = mysql_query($update) or die (mysql_error()); $id = mysql_insert_id(); ?> <p style="font-size:35px; font-family:Arial, Helvetica, sans-serif; color:#255E67; margin-left:25px;">Your Item Has Been Uploaded!</p> <script type="text/javascript"> setTimeout('ourRedirect()', 2000) function ourRedirect() { location.href='protator_php.php?mid=<?php echo $id ?>' } </script> |
|||||||