CODEWITHSUNDEEP
phpmysqldatabasetimestamp
user2981188
user2981188

Reputation:

Filter results by date in timestamp field

I have already had some help but not sure why this isn't working.

I am trying to use a form to let a user filter their activity (which is stored in a DB)

My code:

$_GET['from'] = '01/11/2013';
$_GET['to']   = '25/11/2013';

$from = DateTime::createFromFormat('d/m/Y', $_GET['from']);
$to   = DateTime::createFromFormat('d/m/Y', $_GET['to']);

$sql = "
    SELECT * FROM transfer 
    WHERE personID = $user AND DATE(time) BETWEEN '%s' AND '%s'
";
$sql = sprintf($sql, $from->format('Y-m-d'), $to->format('Y-m-d'));

print_r($sql);

This prints SELECT * FROM transfer WHERE personID = 84587749 AND DATE(time) BETWEEN '2013-11-01' AND '2013-11-14'

When I query this in PHPmyadmin it shows the record, however not showing in my page?

Upvotes: 2

Views: 700

Answers (2)

Arian Faurtosh
Arian Faurtosh

Reputation: 18491

Here is an example how you print out your results.

$dbserver = "localhost";
$dbname = "nameofDB";
$dbusername = "username";
$dbpassword = "password";

$mysqli = new mysqli($dbserver, $dbusername, $dbpassword, $dbname);

$query = "SELECT * FROM transfer WHERE personID = 84587749 AND DATE(time) BETWEEN ? AND ?";

if($stmt = $mysqli->prepare($query)){

    /*
    Binds variables to prepared statement

    i    corresponding variable has type integer
    d    corresponding variable has type double
    s    corresponding variable has type string
    b    corresponding variable is a blob and will be sent in packets
    */

   $to = $_POST['to'];
   $from = $_POST['from'];

   $stmt->bind_param('ss', $from, $to);


   /* execute query */
   $stmt->execute();

   /* Get the result */
   $result = $stmt->get_result();

   while ($row = $result->fetch_assoc()) {
        // Configure this how you want to print out each row.
        echo 'Details: '.$row['details'].'<br>';
        echo 'Time: '.$row['time'].'<br>';
        echo 'Balance: '.$row['balance'].'<br>';
        echo '<br><br>';
   }

   /* free results */
   $stmt->free_result();

   /* close statement */
   $stmt->close();
}

/* close connection */
$mysqli->close();

Upvotes: 0

branty1970
branty1970

Reputation: 826

The SQL looks fine but you don't appear to have issued the executed the SQL query in the database and retrieved the results?? Maybe I'm missing something but you need to connect to your database:

class DBi {
  public static $mysqli;
}

DBi::$mysqli = new mysqli('servername', 'database', 'password', 'user');

if (mysqli_connect_error()) {
  die('Connect Error (' . mysqli_connect_errno() . ') '
        . mysqli_connect_error());
}

Then you need to perform the query:

$result = DBi::$mysqli->query($sql) or die ("Unable to execute SQL command:".$sql);

And finally, retrieve and use the result:

$row = $result->fetch_assoc();
echo $row["fieldname"];

Upvotes: 1

Related Questions