1st row shows dates starting from today up to 20 days ahead. Each date in separate cell of the row. That works.
2nd row should take the date from the mysql database and check if it corresponds with the date in the cell above (from the first row of the table). If the dates match then echo 'option 1', if there is no match then echo 'option 2'. But the echoing should take the place exactly under the matching date from the first row above.
I have tried various options but so far without success...
P.S. Connection with mysql database works fine and I can get the 'date' from it. In mysql table the date is formatted as DATE format: YYYY-MM-DD
Any suggestions?
Thanks, Raivis
PHP Code:
// *** first row of the table ***
echo '<table width="800px" border="1" bordercolor="#CC66CC"><tr>';
date_default_timezone_set('UTC'); // Set timezone
$date = date('Y-m-d'); // Start date - to be displayed in the first table cell
$end_date = date('Y-m-d', strtotime('+20 days')); // End date - to be displayed in the last table cell
// *** second row of the table ***
include_once "../scripts/connect_to_mysql.php";
$sqlCommand = "SELECT date FROM janis ORDER BY date ASC LIMIT 21";
$query = mysqli_query($myConnection, $sqlCommand) or die (mysqli_error($myConnection));
while($row = mysqli_fetch_array ($query)){
$datematch = $row['date'];
echo '<td bgcolor="#999999" style="padding:2px;" >';
if($datematch==$table_date){ // this was one of my versions to put the IF statement in - to check if date from first row matches the date from database
echo 'option 1';
} else {
echo 'option 2';
}
}
echo "</td>";
echo "</tr></table>";
mysqli_free_result($query);
Firsts, there is an easier way (aside from DateInterval as well which is hit or miss on machines support since its so new). Use a range:
PHP Code:
<?php
$iDay = 60 * 60 * 24; // Seconds in a day, or 86400 if you will. $iStart = time(); // Start today. $aDays = range($iStart, strtotime('+20 day', $iStart), $iDay);
$aDays represents a timestamp (to current time btw, although that's based off of the $iStart) until 20 days from now. So you can now use that with a foreach in a table head:
Okay, now with that out of the way, lets look at the data itself. The easiest way to select it is to use the CURDATE() and DATEADD. BUT, you won't be able to control it directly in PHP this way for the date changes. So I'd just make use of the $aDays:
PHP Code:
$start = min($aDays); $end = max($aDays); $sQry = "SELECT date FROM janis WHERE date BETWEEN '" . date('Y-m-d', $start) . "' AND '" . date('Y-m-d', $end) . "' ORDER BY date ASC";
I don't want to put a limit on that since I don't know the potential results. This will pull anything between the start and end of the date range, but you may need to play with the range itself as well.
Now, I'm not 100% sure what you are wanting here. How exactly do you want to show the data records in conjunction with the table heads listed? Is it unique for a single row, or is it that each td can contain multiple result rows? A quick HTML mockup of the end result as well as the dates expected from the query would help.
The idea is to create "availability" table - if a resource (lets say 'Peter') is available on the respective date, then there is a checkbox (available to book) and if Peter is not available then checkbox is not shown.
Dates in the table are dynamic - starts today and show 20 days ahead.
Hmm... not necessarily. I mean, the resource names can be set up simply by html. Although pulling out them from database is also an option, but probably more complicated..?
Mysql table contains also resource name, availability (ENUM), date (DATE) and few other data as well.
No, pulling from the SQL is probably the easiest option as you don't need to concern yourself with aligning the data with an owner.
Since you are dealing with many entries I'll assume that there are actually two tables here, one which contains all resource names, and one with corresponding dates to the resources. A left joined query will pull all the resource names and any corresponding entries:
PHP Code:
$iDay = 60 * 60 * 24; // Seconds in a day, or 86400 if you will. $iStart = time(); // Start today. $aDays = range($iStart, strtotime('+20 day', $iStart), $iDay); $iDays = count($aDays);
// You should use styles here instead echo '<table width="800px" border="1" bordercolor="#CC66CC"><tr>'; echo '<th>Name</th>'; foreach ($aDays AS $day) { printf('<th>%s</th>', date('d', $day)); } print '</tr>';
$start = min($aDays); $end = max($aDays);
$sQry = 'SELECT r.resourceName, j.date FROM resourceTable r LEFT JOIN janis j ON j.resourceName = r.resourceName WHERE j.date BETWEEN "' . date('Y-m-d', $start) . '" AND "' . date('Y-m-d', $end) . '" ORDER BY j.date ASC';
foreach ($aRecords AS $record) { if (!isset($aNewRecords[$record['resourceName']])) { $aNewRecords[$record['resourceName']] = array(); } $aNewRecords[$record['resourceName']][] = $record['date']; }
// You should use styles here instead echo '<table width="800px" border="1" bordercolor="#CC66CC"><tr>'; echo '<th>Name</th>'; foreach ($aDays AS $day) { printf('<th>%s</th>', date('d', $day)); } print '</tr>';
Which I can rewrite to this to use the above query:
PHP Code:
$iDay = 60 * 60 * 24; // Seconds in a day, or 86400 if you will. $iNumDays = 20; $iStart = time(); // Start today. $aDays = range($iStart, strtotime("+$iNumDays day", $iStart), $iDay); $iDays = count($aDays);
$aRecords = array(); $start = min($aDays); $end = max($aDays); $sQry = 'SELECT r.resourceName, j.date FROM resourceTable r LEFT JOIN janis j ON j.resourceName = r.resourceName WHERE j.date BETWEEN "' . date('Y-m-d', $start) . '" AND "' . date('Y-m-d', $end) . '" ORDER BY j.date ASC';
if ($qry = mysql_query($sQry)) { while ($row = mysql_fetch_assoc($qry)) { $aRecords[] = $row; } }
$aNewRecords = array();
foreach ($aRecords AS $record) { if (!isset($aNewRecords[$record['resourceName']])) { $aNewRecords[$record['resourceName']] = array(); } $aNewRecords[$record['resourceName']][] = $record['date']; }
// You should use styles here instead echo '<table width="800px" border="1" bordercolor="#CC66CC"><tr>'; echo '<th>Name</th>'; foreach ($aDays AS $day) { printf('<th>%s</th>', date('d', $day)); } print '</tr>';
Looks like I missed a ', use this: $sChecked = date('Y-m-d', $aDays[$i]) == $row['date'] ? ' checked="checked"' : '';
Interesting though, I couldn't have tested it if I missed it :/
And your first part of edited code works - the one with manually created resources array within PHP.
But the one with real connection to db does not show the second row of the table at all. No errors as well. Dates in db table rows are displayed in YYYY-MM-DD format.
Maybe that makes some issue?
And your first part of edited code works - the one with manually created resources array within PHP.
But the one with real connection to db does not show the second row of the table at all. No errors as well. Dates in db table rows are displayed in YYYY-MM-DD format.
Maybe that makes some issue?
Has the queried been altered to match your existing database structure? I don't know the relationships between the resources and the janis table.
yes i have. i attache the picture of db table.
and $sQry is
PHP Code:
$sQry = 'SELECT r.resource, j.date
FROM janis r
LEFT JOIN janis j ON j.resource = r.resource
WHERE j.date BETWEEN "' . date('Y-m-d', $start) . '" AND "' . date('Y-m-d', $end) . '"
ORDER BY j.date ASC';
And when I added printf line after the while() retrieval then it shows following:
nothing appears when it is within while()
nothing appears when it is within if ($qry = mysql_query($sQry))
and just name 'Array ()' appears when outside if() and while()
Is the only record of a resource listed in this Janis table? If so, then you don't need to join; I did that assuming that records may not exist within janis, but should still be listed within the selectable options. If no, then you must omit that join and the use of a simple:
PHP Code:
$sQry = 'SELECT resource, date FROM janis WHERE date BETWEEN "' . date('Y-m-d', $start) . '" AND "' . date('Y-m-d', $end) . '" ORDER BY date ASC';
Before you post, read our: 
