No clue, I am new....need a way to get data out of mysql

[alisonjane]

Hi,
First, I apologize, but I am a biologist not a programmer!

Second, I made a web-based scheduling system in mysql. It works great but I need to generate a report by specifying a range of dates on another webpage.

I made the query to give me the columns I want, but I don't know how to create the front-end page on which I would like select the range of dates to show me. Here is the query that returns ALL records:

<?
// script to display all reservations

// connection information
$hostName = "localhost" ;
$userName = "*********" ;
$password = "********** ";
$dbName = "********** **";

// make connection to database
mysql_connect($ hostName, $userName, $password) or die("Unable to connect to host $hostName");

mysql_select_db ($dbName) or die( "Unable to select database $dbName");

// Select all the fields in all the records of the Computers table
$query = "SELECT li.fname Name, li.lname Last_Name, rs.name Equipment, rv.summary Project, (TO_DAYS( from_unixtime( rv.end_date ) ) - TO_DAYS( from_unixtime( rv.start_date ) )) + 1 Days_Out , rs.notes ID_Cost
FROM reservations rv, resources rs, reservation_use rs ru, login li
WHERE rs.machid = rv.machid
AND ru.resid = rv.resid
AND li.memberid = ru.memberid
AND from_unixtime( rv.end_date ) <= CURRENT_DATE( )
UNION
SELECT li.fname Name, li.lname Last_Name, rs.name Equipment, rv.summary Project, (TO_DAYS( CURRENT_DATE( ) ) - TO_DAYS( from_unixtime( rv.start_date ))) + 1 Days_Out, rs.notes ID_Cost
FROM reservations rv, resources rs, reservation_use rs ru, login li
WHERE rs.machid = rv.machid
AND ru.resid = rv.resid
AND li.memberid = ru.memberid
AND from_unixtime( rv.end_date ) > CURRENT_DATE( )
AND from_unixtime( rv.start_date ) < CURRENT_DATE()
ORDER BY 4
LIMIT 0 , 30";
$result = mysql_query($qu ery);

// Determine the number of computers
$number = mysql_numrows($ result);

// print the computer names

print "<h2>There are $number equipment charge-outs between your specified date and now:</h2>
<table cellpadding=5>
<tr bgcolor=black>
<td><font color=white><b> Name</b></font></td>
<td><font color=white><b> Equipment</b></font></td>
<td><font color=white><b> Project</b></font></td>
<td><font color=white><b> Days_Out</b></font></td>
<td><font color=white><b> ID_Cost</b></td></font></tr>";

for ($i=0; $i<$number; $i++) {
$Name = mysql_result($r esult,$i,"Name" );
$Last_Name = mysql_result($r esult,$i,"Last_ Name");
$Equipment = mysql_result($r esult,$i,"Equip ment");
$Project = mysql_result($r esult,$i,"Proje ct");
$Days_Out = mysql_result($r esult,$i,"Days_ Out");
$ID_Cost = mysql_result($r esult,$i,"ID_Co st");
// print "$a $b $c $d $e $f $g<br>";

if ($i%2 == 0) {
print "<tr bgcolor=lightgr ey>";
} else {
print "<tr>";
}
print "<td>$Name $Last_Name</td>
<td>$Equipmen t</td>
<td>$Project</td>
<td>$Days_Out </td>
<td>$ID_Cost</td></tr>";
}
print "</table>";
// Close the database connection
mysql_close();
?>

<p>

[kaioshin00]

Can't you pass the start date and end date to the page that executes the query via a form and select only those reservations that fall within that range with mySQL?

[alisonjane]

Yes, but I don't know how to make that form.

[Motoma]

Take a look at this tutorial on PHP POST. It will give you the basics behind building user driven content. Next you will want to built two entry boxes that allow the user to specify the date range. Your page with then post the range to your PHP script, which will place those dates as restrictions on the dataset in your MySQL query.