Database and HTML/PHP?

[Thor]

So what exactly is the question here? How to write the reports?

I just want to see how one would use HTML, PHP and SQL to create a page where I can get a report from the data captured in the DB and be displayed in a more human friendly way on the website

 

[Thor]

SELECT * FROM TableName WHERE DateField BETWEEN :fromDate AND :toDate

Figuring out the SQL is easy, I want to see how people use that to actually create a decent report page.
But I think you know what my end goal is and are just being difficult.

 

[Thor]

A simple SQL query would get that data out.

What date range would you be looking at though? - I would suggest having an extra column and storing the date that the entry was inserted into the DB...

I do have a column that takes the date, I enter it in DD/MM/YYYY format

 

[Necropolis]

Figuring out the SQL is easy, I want to see how people use that to actually create a decent report page.
But I think you know what my end goal is and are just being difficult.

For a simple report like that you could just use a grid styled with some CSS to display the data.

I'm sure there are some PHP bases reporting solutions out there - but I don't know of any of these off the top of my head.

 

[Gezza]

Better than nothing

I currently have it that the form data gets posted to the php page and the php page uses the mail function to send the mail and then a separate part writes it to the DB

Regarding the reports:

I want to be able to type in an date range and then receive a report of all the the captured data for that period ie

Name Surname Email Phone
xxxx xxxx xxxx xxxx
xxxx xxxx xxxx xxxx
xxxx xxxx xxxx xxxx
xxxx xxxx xxxx xxxx
xxxx xxxx xxxx xxxx
xxxx xxxx xxxx xxxx
xxxx xxxx xxxx xxxx
xxxx xxxx xxxx xxxx
xxxx xxxx xxxx xxxx
xxxx xxxx xxxx xxxx

Coolio.

Okay, when the form posts, post the info to your DB so everything is there and can be used.

Next send the mailers (using a php template/class you created) using the info from the DB.

This is the mysql request to get the list sorted by date.

select * from hockey_stats where game_date between '2012-03-11 00:00:00' and '2012-05-11 23:59:00' order by game_date desc;

 

[Thor]

For a simple report like that you could just use a grid styled with some CSS to display the data.

I'm sure there are some PHP bases reporting solutions out there - but I don't know of any of these off the top of my head.

This is what I used:

http://jdorn.github.io/php-reports/#About

But I want to see what orthers would have done instead as I think this was overkill and perhaps there are other newer/better ways to achieve this with something other than php/MySQL

 

[Thor]

Coolio.



This is the mysql request to get the list sorted by date.

select * from hockey_stats where game_date between '2012-03-11 00:00:00' and '2012-05-11 23:59:00' order by game_date desc;

Okay, when the form posts, post the info to your DB so everything is there and can be used.

Next send the mailers (using a php template/class you created) using the info from the DB.

Ahh I will have a look at this currently I do this just the other way around sending the mail and then posting to DB

 

[Gezza]

'date' is the name of the column for date on the db

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT Name, Surname, Email, Cellphone FROM $dbname" WHERE date between '2012-03-11 00:00:00' and '2012-05-11 23:59:00' order by date desc;;
$result = $conn->query($sql);

if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
echo "Name: " . $row["Name"]. " - Surname: " . $row["Surname"]. " " . $row["Email"]. " " . $row["Cellphone"]. "<br>";
}
} else {
echo "0 results";
}
$conn->close();
?>

 

[Gezza]

Probably nothing wrong with it but it makes more sense to save data before using it.

 

[Thor]

'date' is the name of the column for date on the db

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT Name, Surname, Email, Cellphone FROM $dbname" WHERE date between '2012-03-11 00:00:00' and '2012-05-11 23:59:00' order by date desc;;
$result = $conn->query($sql);

if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
echo "Name: " . $row["Name"]. " - Surname: " . $row["Surname"]. " " . $row["Email"]. " " . $row["Cellphone"]. "<br>";
}
} else {
echo "0 results";
}
$conn->close();
?>

Thank you for this, this is where I am struggling with the php part of things

$sql = "SELECT Name, Surname, Email, Cellphone FROM $dbname" WHERE date between '2012-03-11 00:00:00' and '2012-05-11 23:59:00' order by date desc;;
$result = $conn->query($sql);

The date there, how does it get there as it can't be hard coded since I'd like to generate reports based on the date that is being entered.

Sorry if that is a noob question

 

[Necropolis]

http://php.net/manual/en/function.getdate.php

 

[giggity]

The date there, how does it get there as it can't be hard coded since I'd like to generate reports based on the date that is being entered.

Sorry if that is a noob question

Set the table field type to current timestamp using NOW().

 

[Nod]

http://www.phpeveryday.com/articles/PHP-MySQL-Creating-Form-Insert-Data-P280.html

 

[Thor]

http://www.phpeveryday.com/articles/PHP-MySQL-Creating-Form-Insert-Data-P280.html

Saw that webpage, but the comments made me steer clear, and looks outdated.

Is there another language that would do this sort of thing better or is PHP the "best" for this sort of thing?

 

[Gezza]

In Mysql. You need to insert the DATE column in to your table you want to use (The table with name, surname, email etc.) There is a setting to update to current timestamp that you need to set in mysql for the date column. that way, whenever a new entry is created, the date will automatically get updated/written too.

 

[Gezza]

Stop posting links. He wants hands on help. Any crayon can google for results, but it still takes experience to make the results work.

 

[Gezza]

Are you using phpmyadmin? If not, maybe try to get WAMP. Very easy to use and phpmyadmin gives you a GUI control panel to set up databases etc.

 

[Necropolis]

Saw that webpage, but the comments made me steer clear, and looks outdated.

Is there another language that would do this sort of thing better or is PHP the "best" for this sort of thing?

I'm sure MySQL must have something like that as well.

In SQL Server GetDate() does this for you and in C# DateTime.Now() will give you what you want as well.

 

[diabolus]

Thank you for this, this is where I am struggling with the php part of things



The date there, how does it get there as it can't be hard coded since I'd like to generate reports based on the date that is being entered.

Sorry if that is a noob question

You need to create a form/page to capture parameters and plug it into your SQL query. The same way you capture data, and submit it into the DB, you will need another form/page with 2 DatePicker controls (From Date | To Date) and a submit button (typically this button would say something like "Run Report") . When person click the button, you take the values in the datepickers and send it into that SQL query as variables.

 

[Thor]

Is the information in this link valid and relevant? ( I am so scared to learn out dated stuff)

https://www.binpress.com/tutorial/using-php-with-mysql-the-right-way/17

Secondly what is the difference between:

$connection = mysqli_connect('localhost',$username,$password,$dbname);

and

$connection = new mysqli('localhost',$username,$password,$dbname);