CODEWITHSUNDEEP
phphtmlmysqlselect
user2827404
user2827404

Reputation: 49

PHP and Mysql (pulling information)

I am working on a little project of my own building a PHP form that submits to a Mysql database. I have now managed to connect the form to the database and all is working on that side.

I am now looking at creating a page that displays my information from mysql if the date field and todays date is over 14 days.

I have managed to create a page that displays all of my information however i appear to have got stuck on the 14 days part.

This is the code i have so far:

<title>Force 10 - Reminder app</title>
<h2>F10 - Reminder app - all invoice</h2>

<?php
include 'db-connect.php'; 

$result = mysqli_query($con,"SELECT * FROM invoices");

echo "<table border='0' align='center'>
<tr>
<th>Company Name:</th>
<th>Email:</th>
<th>Address:</th>
<th>Price:</th>
<th>Date:</th>
<th>File:</th>
</tr>";

while($row = mysqli_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['companyname'] . "</td>";
  echo "<td>" . $row['email'] . "</td>";
  echo "<td>" . $row['address'] . "</td>";
  echo "<td>" . $row['price'] . "</td>";
  echo "<td>" . $row['date'] . "</td>";
  echo "<td>" . $row['file'] . "</td>";
  echo "</tr>";
  }
echo "</table>";

mysqli_close($con);
?>

If somone can help me out on this would be much appreciated.

Upvotes: 0

Views: 45

Answers (2)

Professor Abronsius
Professor Abronsius

Reputation: 33823

I believe you are asking how to query the db to select records that are more than 14 days older than the current date?

select * from `invoices` where datediff(now(),`date`) > 14

Upvotes: 1

Qirel
Qirel

Reputation: 26490

The way you're using $row['companyname'] is not for when using mysqli_fetch_array($result). This pulls an array out of the database for each column, so the first column would be $row[0]instead of $row['companyname'].

If you want to pull columns by name, use mysqli_fetch_assoc($result) instead, like this:

while($row = mysqli_fetch_assoc($result)) {
    echo "<tr>";
    echo "<td>" . $row['companyname'] . "</td>";
    echo "<td>" . $row['email'] . "</td>";
    echo "<td>" . $row['address'] . "</td>";
    echo "<td>" . $row['price'] . "</td>";
    echo "<td>" . $row['date'] . "</td>";
    echo "<td>" . $row['file'] . "</td>";
    echo "</tr>";
}

You could also look into using prepared statement and such, they are used a bit differently than this, but it'll become a much more secure script when using variables in your SQL-queries.

If you wish to only select invoices that is older than 14 days, you have to modify your SELECT-query, adding a WHERE-clause:

$result = mysqli_query($con,"SELECT * FROM invoices WHERE date < CURRENT_DATE - INTERVAL 14 DAY");

Upvotes: 1

Related Questions