PHP - Formatting Dates For Mysql

I was just wondering if anyone can reccomend the best way to get dates from a database and turn them into dates you can display.  For example if you have a date stored such as 23/08/10, is there an easy way to turn this into 23rd August 2010?  Thanks for any help. 

Hi,

I'm trying to get the records that are between two dates to show on my screen, right now nothing comes up my code I'm using is below

Code: [Select]
$today = date('Y-m-d');
$lastweek = date('Y-m-d',strtotime('-7 days'));
$sql = "SELECT * from `memos` WHERE `date` BETWEEN '$today' AND '$lastweek' AND `delete` !='1'";
$result = mysql_query($sql);
echo "<ol>";
while ($row = mysql_fetch_array($result)) {
echo "<li>".$row['memo']."</li>" ;
}
echo "</ol>";

I am trying to create a simple calendar where a user can add/remove their availability date. This part works so far. Now what I am having trouble with is highlighting the already selected dates that are fetched from a MySQL database.

 

Here's my code.

// HTML
<div id="datetimepicker1"></div>

// Jquery/Ajax

<script>
$(document).ready(function () {

  $('#datetimepicker1').datepicker({
    dateFormat: "yy-mm-dd",
    multidate: true,
    onSelect: function () {
        var getDate = $("#datetimepicker1").val();

        $.ajax({
             type: "POST", //or GET. Whichever floats your boat.
             url: "snippets/adapter-set.php",
             data: { date: getDate },
             success: function(data) {
              // alert(data);
             },
             error: function() {
                 alert("Error.");
             }
        });
    }
  });
});
</script>

// PHP
// adapter-set.php

$post_date = $_POST['date'];

$find_query = $db->prepare("SELECT user_id FROM user_dates WHERE date_available = :date_available");
$find_query->bindParam(':date_available', $post_date);
$find_query->execute();
$result_find = $find_query->fetchAll(PDO::FETCH_ASSOC);
if(count($result_find) > 0) {
  foreach($result_find as $row) {
    $user_id =	$row['user_id'];
  }
  $delete_query = $db->prepare("DELETE FROM user_dates WHERE user_id = :user_id");
  $delete_query->bindParam(':user_id', $user_id);
  $delete_query->execute();
  $result_delete = $delete_query->execute();
  if($result_delete == false) {
   echo 'delete false';
  } else {
   echo 'delete success';
  }
} else {
  $insert_query = $db->prepare("INSERT INTO user_dates(date_available) VALUES(:date_available)");
  $insert_query->bindParam(':date_available', $post_date);
  $result_insert = $insert_query->execute();
  if($result_insert == false) {
   echo 'insert false';
  } else {
   echo 'insert success';
  }

}

 

The above code works fine. It inserts and deletes a date row in MySQL database table based on a click.

Now what I would like to do is to highlight all the "available" dates that are already inserted into the database; so that the user knows which dates he has already selected. This is my code for that. But it doesn't seem to be working. No errors. It's just not highlighting the inserted dates. Can you tell me what I'm doing wrong?

// JQUERY
<script>
    $(document).ready(function() {

        $.post('snippets/adapter-fetch.php', {}, function(data){
            $("#datetimepicker1").datepicker({
              datesEnabled : data.datesEnabled
            });
        }, 'json');

    });
</script>

// PHP
// adapter-fetch.php

$global_user_id = 5;

$find_query = $db->prepare("SELECT date_available FROM user_dates WHERE user_id = :user_id");
$find_query->bindParam(':user_id', $global_user_id);
$find_query->execute();
$result_find = $find_query->fetchAll(PDO::FETCH_ASSOC);
if(count($result_find) > 0) {
  foreach($result_find as $row) {
    $date_available =	$row['date_available'];

    echo $date_available;
  }
} else {
  echo 'not dates available';
}

 

I just have a general question about when other programmers remove rows older than say a year old....

Do most of you:
1) set up a CRON job to run daily at 3:00am
2) Add a routine at login for each user
3) Add a routine when a user moves into a certain portion of the application
4) A maintenance routine that an administrator manually has to envoke

Or is there another way...I have done some research but most of the things google has showed me is the how not when to do this...

Any ideas how I can turn "2010-10-20 19:10:56" to 20th October 2010, 19:10

Thanks lots guys,

Jake

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   

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);
?>

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?

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>

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

Hi guys,

I am trying to do a multidates events availability calender. The script below indicates todays date by highlighting an orange colour and also indicates the start and end date of the event highlighting grey colour on the two dates (The colour are link via css classes as shown).
Code: [Select]
//Today's date
$todaysDate = date("d/m/Y");
            $dateToCompare = $daystring . '/' . $monthstring . '/' . $year;
echo "<td align='center' ";
if($todaysDate == $dateToCompare){
echo "class='today'";
}else{
//Compare's the event dates
$sqlcount = "select event_start,event_end from b_calender where event_start ='".$dateToCompare."' AND event_end='".$dateToCompare."'";
$noOfEvent = mysql_num_rows(mysql_query($sqlcount));
if($noOfEvent >= 1){
echo "class='event'";
}
}
It works ok i.e. if start date = 01/01/2012 and end date = 04/01/2012 both date will be highlighted with grey colour. However I want it to also highlight grey on the dates between the 1st and 4th to show that then anydates between the 1st and 4th are not available and this is when I'm stuck.

Please guys I need help.

Thanks

guys, im having a problem here...

i tried too many ways to convert dates in american format, to brazilian format, but no one is working...

how can i do that, to convert it to dd/mm/yyyy (it comes from my db in mysql)...
 

Hey, I have just recently coded a forum and my topics are ordered by date. This means if there is a topic at 4pm and then there is another topic made at 4:01pm the topic which was posted at 4:01pm will be listed at the top.

I know how to add timezones but there is bit of a problem,  if  person from the UK posts a topic and his/hers timezone is set to:

date_default_timezone_set('Europe/London');()

this means the time at which the topic was posted will be 20:36pm (Just a randome time example).

However if someone posted a topic from america/los_angeles one hour before the one above his/her time will be 11:36 am. Even though this topic was posted one hour before the UK post. The UK topic will override it because it is 8:36pm. In fact, all UK topics will show up at the top and all the america/los_angeles topics will show bellow.

How do I slove this problem? I just don't get it even tough I looked online.

Please help, thanks.

Hey guys,

What I'm trying to do is set 2 dates.  Today's date and then a date 14 days from now.  However, I want it to increase the month if by adding 14 days will bring me to the next month, same with the year.

I have this, but it doesn't increment the month or year.

$week = mktime(11, 59, 59, date("m"), date("d")+14, date("y"));
$date =  date("Y-m-d H:i:s", $week);

i want to created a case like the following need help with the syntax:
Code: [Select]
switch (true)
{
case($fromdate-$todate==17-04-2001-25-04-2011):
echo $finalprice = $result;
        break;
}