PHP - Selecting Date From Table Problem

SELECT * FROM `booking_tbl` WHERE booking_status = 'Check In' AND departure_date_time = NOW()"

I use the datetime datatype for the departure_date_time so i can get data from that data because it checking the date and time   but i want it to check on the date from the datetime datatype   So how can i get the data only with that date without the time in the mysql datetime datatype

Hi All ,   I have a small table with 4 fields namely Day_ID, Dues, Last_Visit, Points. where Day_ID is an auto-increment field. The table would be as follows:   Day_ID -- Dues ---  Last_Visit ---  Points. 1  --------- 900 -------- 1/12  --------   6      2  --------- 700 -------- 4/12  --------   7 3  --------- 600 -------- 7/12  --------   5 4  --------- 600 -------- 9/12  --------   6 5  --------- 600 -------- 10/12 -------   6 6  --------- 600 -------- 14/12 -------   6   So this is the record of a person's visit to say a club. The last row indicates the last date of his visit to the club. His points on this date are 6. Based on this point value of 6 in the last row I want to retrieve all the previous BUT adjoining all records that have the same Points i.e. 6.    So my query should retrieve for me, based on the column value of Points of the last row (i.e. Day_ID - 6 ), as follows:   4  --------- 600 -------- 9/12  --------   6 5  --------- 600 -------- 10/12 -------   6 6  --------- 600 -------- 14/12 -------   6   This problem stated above had been completely resolved, thanks to a lot of help from Guru Barand by this following query :-

$query = "SELECT cv.day_id, cv.dues, cv.last_visit, cv.points
FROM clubvisit cv
WHERE last_visit >=
(
SELECT MAX(last_visit) FROM clubvisit
WHERE points <>
(
SELECT points as lastpoints
FROM clubvisit
JOIN
(
SELECT MAX(last_visit) as last_visit
FROM clubvisit
) as latest USING (last_visit)
)
)";

I am using this and it works perfectly except that now there is a slight change in the table because the criteria for points is now dependent on more than one column cv.points and is more like cv.points1, cv.points2, cv.points3 etc. So now I need to make a selection based on each of these cv.points columns.   As of now I can still get the results by running the query multiple times for each of the cv.points columns ( seperately for cv.points1, cv.points2, cv.points3) and it works correctly. However I am wondering if there is a better way to do this in just one go. This not only makes the code repetitive but also since the queries are interconnected, involves the use of transactions which I wish to avoid if possible.    The values that I require for each of the cv.point columns is  1. day_id of the previous / old day on which the cv.points value changed from the current day value, and  2. cv.points on that old/ previous day.    So for  example if the table is as below:   Day_ID -- Dues ---  Last_Visit ---  Points1 --- Points2. 1  --------- 900 -------- 1/12  -----------   9  ------------ 5    2  --------- 600 -------- 4/12  -----------   6  ------------ 6 3  --------- 400 -------- 7/12  -----------   4  ------------ 7 4  --------- 500 -------- 9/12  -----------   5  ------------ 8 5  --------- 600 -------- 10/12 ----------   6  ------------ 8 6  --------- 600 -------- 11/12 ----------   6  ------------ 8 7  --------- 600 -------- 13/12 ----------   6  ------------ 7 8  --------- 500 -------- 15/12 ----------   5  ------------ 7 9  --------- 500 -------- 19/12 ----------   5  ------------ 7   Then I need the following set of values : 1. day_id1 -- Day 7,      points1 ---- 6,          days_diff1 -- (9-7 = 2)  . // Difference between the latest day and day_id1 2. day_id2 -- Day 6,      points2 ---- 8,          days_diff2  -- (9-6 = 3) 3. day_id3 -- ....   and so on for other points.    Thanks all !

I have a table called users with a fieldname called service_id.  In a table called services I have id and name.

I want to query the users table and, based on the service_id, display the name of the service (which is stored in the services table).

However, when I try this code I get
No records returned.

I later echo out under a while statement $row['name']; or $row['id']


$query = "SELECT users.username, users.lname, users.fname, users.service_id, services.name, services.id 
FROM users, services 
WHERE users.inst_id = '".$userarray['inst_id']."' and users.id !='".$userarray['id']."' and users.service_id = services.id ";

Hi,

I've got a basic sign up form but I want a drop down list which will list different catergories that relate to different tables which when selected will input the sign up information into that table which was selected from the catergory drop down.

This is the signup form

<html><head><title>Birthdays Insert Form</title>
<style type="text/css">
td {font-family: tahoma, arial, verdana; font-size: 10pt }
</style>
</head>
<body>
<table width="300" cellpadding="5" cellspacing="0" border="2">
<tr align="center" valign="top">
<td align="left" colspan="1" rowspan="1" bgcolor="64b1ff">
<h3>Insert Record</h3>
<form method="POST" action="test.php">
<?
print "Enter Company Name: <input type=text name=company_name size=30><br>";
print "Enter Contact Name: <input type=text name=contact_name size=30><br>";
print "Enter Telephone: <input type=text name=telephone size=20><br>";
print "Enter Fax: <input type=text name=fax size=30><br>";
print "Enter Email: <input type=text name=email size=30><br>";
print "Enter Address: <input type=text name=address1 size=20><br>";
print "Enter Address: <input type=text name=address2 size=30><br>";
print "Enter Postcode: <input type=text name=postcode size=30><br>";
print "Enter Town / City: <input type=text name=town_city size=20><br>";
print "Enter Website: <input type=text name=website size=30><br>";
print "Enter Company Type: <select name='table'> <option>stationary</option><option>reception</option></select><br>";
print "<br>";
print "<input type=submit value=Submit><input type=reset>";
?>
</form>
</td></tr></table>
</body>
</html>

This is the part which I can't figure out and is probably totally wrong! Im trying to use this script to sort the drop down list to then run the correct script to insert the form data.

<html xmlns="http://www.w3.org/1999/xhtml">

<head>
  <title>Hello!</title>
</head>

<body>

<?php
if($_POST['table']=='stationary' 'birthdays_insert_record.php')

else

if($_POST['table']=='reception' 'insert_reception.php')
?>

</body>

</html>

This is the script which works! that inserts the form data into a specific table

<html><head><title>Birthdays Insert Record</title></head>
<body>
<?
/* Change db and connect values if using online */
$company_name=$_POST['company_name'];
$contact_name=$_POST['contact_name'];
$telephone=$_POST['telephone'];
$fax=$_POST['fax'];
$email=$_POST['email'];
$address1=$_POST['address1'];
$address2=$_POST['address2'];
$postcode=$_POST['postcode'];
$town_city=$_POST['town_city'];
$website=$_POST['website'];
$db="myflawlesswedding";
$link = mysql_connect('localhost', 'root' , '');
if (! $link)
die(mysql_error());
mysql_select_db($db , $link) or die("Select Error: ".mysql_error());
$result=mysql_query("INSERT INTO reception (company_name, contact_name, telephone, fax, email, address1, address2, postcode, town_city, website) VALUES (
'$company_name',
'$contact_name',
'$telephone',
'$fax',
'$email',
'$address1',
'$address2',
'$postcode',
'$town_city',
'$website')") or die("Insert Error: ".mysql_error());
mysql_close($link);
print "Record added";
?>
<form method="POST" action="birthdays_insert_form.php">
<input type="submit" value="Insert Another Record">
</form>
<br>

<form method="POST" action="birthdays_dbase_interface.php">
<input type="submit" value="Dbase Interface">
</form>



</body>
</html>

I hope somebody can help me out here! or can point me in a better way to sort this problem!

Thanks for any advice!

Hi PHPFreaks.com

I wrote in here a while ago about updating table rows by selecting rows which end dates is greater than the current day, much like a experation dates with blog posts in a CMS.

I was told to make sure my datatype for the end dates in the database was the datetime format is YYYY-MM-DD HH:mm:SS now I have now changed that, but I am still have trouble creating a query that does exactly what I mentioned.

mysql_query("UPDATE ". $db_prefix ."events WHERE end_time >= TIMESTAMP: Auto NOW() SET cat = 1" );

Now what I am trying to is that if the current time is greater than the end date I need the "cat" row changed to 1

So thanks in advance if you can help me guys

Hey guys im making a blog and when i make a post it automatically takes the date and created an archive based on what ever i have in the database (date wise) anyways it works almost fine but if i make a date in the same month it duplicates like so:

October 2010
October 2010
November 2010

Is there away where i can limit October by 1 but when i come to make a post next year it will still display like so:

October 2010
November 2010
December 2010
January 2011
...
October 2011

If i show you the code might make more sense haha::


public function create_archive()
{
// Loop through the database grabbing all the dates::
$sql = "SELECT blog_date
FROM subarc_blog";

$stmt = $this->conn->prepare($sql);
$stmt->execute();
$stmt->bind_result($date);

$rows = array();

while($row = $stmt->fetch())
{
$date = explode("-",$date);
$month = date("F",mktime(0,0,0,$date['1']));
$year = $date[0];

$item = array(
'blog_month' => $month,
'blog_year' => $year
);
$rows[] = $item;
}
$stmt->close();
return $rows;
}



and the sidebar looks like so::


<ul class="sidebar">
<?php
$result = $Database->create_archive();
foreach($result as $row) : 
?>
<li><a href=""><?php echo $row['blog_month'] . " " . $row['blog_year']; ?></a></li>
<?php endforeach; ?>
</ul>


Hope someone can help!

Hey, I am grabbing a date from a row in my table which is currently formatted as:
date("Y-m-d")
2010-09-29

So in my code I have something like this:

$row['date'];

How do I use this to rearrange the date to look like:

09-29-2010

I looked at the formatting tutorials, but they explain how to format it for the date you are currently setting into a variable.

Hi all -

I am parsing info from mysql into a php table but the date being returned is in YYYY-MM-DD format and I want it DD-MM-YYYY. How would I do this?

Code at the moment is:

Code: [Select]
$link = connect();

function get_user_posts(){

$getPosts = mysql_query('SELECT a.id as id,a.post as question, a.answer as answer, a.created as created, a.updated as updated, b.vclogin as admin
FROM user_posts as a, admin as b WHERE a.admin_id=b.adminid',connect());
return $getPosts;
}


Code: [Select]
<table class="list">
<tr>
<th><?php echo getlocal("upost.post") ?></th>
<th><?php echo getlocal("upost.answer") ?></th>
<th><?php echo getlocal("upost.admin") ?></th>
<th><?php echo getlocal("upost.created") ?></th>
<th><?php echo getlocal("upost.updated") ?></th>
<th></th>
<th></th>
</tr>
<?php 
$all_questions = get_user_posts();

while ($question = mysql_fetch_assoc($all_posts)):
?>

<tr>
<td><?php echo $post['post']?></td>
<td><?php echo $post['answer']?></td>
<td><?php echo $post['admin']?></td>
<td><?php echo $post['created']?></td>
<td><?php echo $post['updated']?></td>
<td><a href="javascript:user_post('<?php echo $post['id']?>')">edit</a></td>
<td><a href="javascript:delete_post('<?php echo $post['id']?>')">delete</a></td>
</tr>


THANKS!

Hi there,

i'm trying to sum up values from mysql based on their date, specifically only values from entries with the same date should be added together, my db looks like this:

id, codes, value, date

now i'm building a statistics page, the value field is always 50, for showing the total for all the entries i'm just doing Code: [Select]
$totalincome = $codes * 50; and print that on the statistic, i want to have statistics for each day, the date is saved in this format: YYYY-MM-DD

how could i do that ?

Thanks !

Hello,

I have a msql table that has data input every hour of every day, so each date is entered 24 times next to the hour field as seen below the PHP...

How do I get the information between a daterange that comes from the datepickers (start and end date) to show values of Power, Volt, and current for each day and hour in a report ?

Here is what I have written, but I think the time, or multiple of same dates is stopping it from working...

<?php

if(isset($_POST['start1']) && isset($_POST['end1'])){

$start = (isset($_POST['start1'])) ? date("Y-m-d",strtotime($_POST['start1'])) : date("Y-m-d");

$end   = (isset($_POST['end1'])) ? date("Y-m-d",strtotime($_POST['end1'])) : date("Y-m-d");


$con = mysql_connect('xxxxxx', 'xxxxxx', 'xxxxxxxxxx'); 
if (!$con)  { 
die('Could not connect: ' . mysql_error()); 
} 

mysql_select_db("inverters", $con); 

$sql = "SELECT * FROM report WHERE date BETWEEN '$start' AND '$end'";

    echo "<table border='1'> 
    <tr> 
<th>Date</th> 
<th>Hour</th> 
<th>Power</th> 
<th>Volt</th> 
<th>Current</th> 
</tr>"; 

$res = mysql_query($sql) or die(__LINE__.' '.$sql.' '.mysql_error());

while($row = mysql_fetch_array($res)){  

echo "<tr>"; 
echo "<td>" . $row['Date'] . "</td>"; 
echo "<td>" . $row['Time'] . "</td>"; 
echo "<td>" . $row['Power'] . "</td>"; 
    echo "<td>" . $row['Volt'] . "</td>"; 
echo "<td>" . $row['Current'] . "</td>"; 
echo "</tr>"; 
} 

echo "</table>"; 

mysql_close($con); 
}
?>


The DATE is a DATE, the TIME is TIME, the power,volt, current are FLOAT

unit ID | Date | Time | Power | Volts | Current |

1         10/15/2010    21:00:00         0            220          100
1         10/15/2010    22:00:00         0            220          100
1         10/15/2010    23:00:00         0            220          100
1         10/16/2010    00:00:00         0            220          100
1         10/16/2010    01:00:00         0            220          100
1         10/16/2010    02:00:00         0            220          100
1         10/16/2010    03:00:00         0            220          100
1         10/16/2010    04:00:00         0            220          100
1         10/16/2010    05:00:00       245            220          100
1         10/16/2010    06:00:00       360            220          100
1         10/16/2010    07:00:00       596            220          100
1         10/16/2010    08:00:00      1567            220          100
1         10/16/2010    09:00:00      1568            220          100
1         10/16/2010    10:00:00      1598            220          100
1         10/16/2010    11:00:00      1642            220          100
1         10/16/2010    12:00:00      1658            220          100
1         10/16/2010    13:00:00      1664            220          100
1         10/16/2010    14:00:00      1598            220          100
1         10/16/2010    15:00:00      1527            220          100
1         10/16/2010    16:00:00       980            220          100
1         10/16/2010    17:00:00       410            220          100
1         10/16/2010    18:00:00       208            220          100
1         10/16/2010    19:00:00         0            220          100
1         10/16/2010    20:00:00         0            220          100
1         10/16/2010    21:00:00         0            220          100
1         10/16/2010    22:00:00         0            220          100
1         10/16/2010    23:00:00         0            220          100
1         10/17/2010    00:00:00         0            220          100
1         10/17/2010    01:00:00         0            220          100
1         10/17/2010    02:00:00         0            220          100

Here is one part of php code. Here I try to show in last 1 month which dates exactly user made orders. When I run the SQL code in command prompt it works. I think problem is in while loop.

Thanks beforhand for contribution.


$query = "SELECT Date FROM orders WHERE User='$username' and DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= Date;";
$result = mysql_query($query,$link_id) or die (mysql_error());

echo "<table border='1'>
        <tr>
          <td>&nbsp;Last 1 month's orders:</td>
        </tr>";
   
      while($order_date = mysql_fetch_row($result)) {
        echo "<tr>";
          echo "<td>" . $order_date[0] . "</td>";
        echo "</tr>";
      }
     
      echo "</table>

I have a simple mysql database holding information about an image upload including its 'name' and 'date uploaded' [current_timestamp()] to a folder 'image'

I then display it into a table using the php ‘foreach’ facility.

I want the date to be displayed in the ‘dd/mm/yyyy’ format.  not the default yyyy/mm/dd.

Here is the relevant code

<?php

 $conn = mysqli_connect("localhost", "root", "", "dbname");

  $results = mysqli_query($conn, "SELECT * FROM tablename");

  $image = mysqli_fetch_all($results, MYSQLI_ASSOC);

?>

<?php foreach ($image as $user): ?>

<td><p>NAME</p><?php echo $user['name']; ?></td>

<td><p>COMMENT</p><?php echo $user['comment']; ?></td>

<td><p>DATE</p><?php echo $user['date']; ?></td>

What do I need to do?

THANKS- Warren

Hey all,

Im currently working on a site which I save a cookie on users computer for 1 month as well a row in a table with the 1 month expiring date plus cookie id. What I would like to know is if theres a way to make the server check the table every day at say 12pm to see if any corresponding user cookies in each row have expired on that day as to remove them from the table to match the expiring cookie on the users computer? I would like not to have to rely on a user/admin accessing in to make a script run instead have something thats timed to go off automatically instead?

Is there anyway of doing this? 

Thanks!

This is my first real jump into PHP, I created a small script a few years ago but have not touched it since (or any other programming for that matter), so I'm not sure how to start this.

I need a script that I can run once a day through cron and take the date from one table/filed and insert it into a different table/field, converting the human readable date to a Unix date.

Table Name: Ads
Field: endtime_value (human readable date)

to

Table Name: Node
Field: auto_expire (Converted to Unix time)

Both use a field named "nid" as the key field, so the fields should match each nid field from one table to the next.  Following a tutorial I have been able to insert into a field certain data, but I don't know how to do it so the nid's match and how to convert the human readable date to Unix time.  Thanks in advance!.

Hey,

I'm using a script which allows you to click on a calendar to select the date to submit to the database.

The date is submitted like this: 2014-02-08

Is there a really simple way to prevent rows showing if the date is in the past?

Something like this:
if($currentdate < 2014-02-08 || $currentdate == 2014-02-08) {

}

Thanks very much,
Jack