Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    New Coder
    Join Date
    May 2012
    Posts
    17
    Thanks
    2
    Thanked 0 Times in 0 Posts

    matching dates in loop

    Hi Guys!

    I have a table with 2 rows.

    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

        
    while (strtotime($date) <= strtotime($end_date)) {            
            
    $table_date date('d',strtotime($date)) ;
            echo 
    '<td bgcolor="#999999" style="padding:2px;" >';
            echo 
    $table_date;
            echo 
    "</td>";
            
    $date date ('Y-m-d'strtotime('+1 day'strtotime($date)));
        }
            echo 
    "</tr><tr>";

        
    // *** 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); 

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    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($iStartstrtotime('+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:
    PHP Code:
    echo '<table  width="800px" border="1" bordercolor="#CC66CC"><tr>';  
    foreach (
    $aDays AS $day)
    {
        
    printf('<th>%s</th>'date('d'$day));
    }
    print 
    '</tr>'
    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.

  • #3
    New Coder
    Join Date
    May 2012
    Posts
    17
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thanks Fou-Lu,

    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.

    Here is small html just for visualization:

    Code:
    <table width="599" border="1" cellspacing="0" cellpadding="2">
      <tr>
        <th width="100" scope="col">Date</th>
        <th width="100" scope="col">17</th>
        <th width="100" scope="col">18</th>
        <th width="100" scope="col">19</th>
        <th width="100" scope="col">20</th>
        <th width="100" scope="col">21</th>
      </tr>
      <tr>
        <td>Peter</td>
        <td>&nbsp;</td>
        <td><input type="checkbox" name="checkbox" id="checkbox">
        <label for="checkbox"></label></td>
        <td><input type="checkbox" name="checkbox2" id="checkbox2">
        <label for="checkbox2"></label>
        <label for="checkbox3"></label></td>
        <td>&nbsp;</td>
        <td><input type="checkbox" name="checkbox3" id="checkbox3">
        <label for="checkbox3"></label></td>
      </tr>
    </table>

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Okay, are you wanting to pull the names out as well in the query?

  • #5
    New Coder
    Join Date
    May 2012
    Posts
    17
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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.

  • #6
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    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($iStartstrtotime('+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'
    ;

    if (
    $qry mysql_query($sQry))
    {
        while (
    $row mysql_fetch_assoc($qry))
        {
            
    printf('<tr><td>%s</td>'$row['resourceName']);
            for (
    $i 0$i $iDays; ++$i)
            {
                
    $sChecked date('Y-m-d'$aDays[$i]) == $row['date'] ? ' checked="checked" : '';
                printf('
    <td><input type="checkbox" name="chk[%s]" value="%s"%/></td>', $row['resourceName'], $row['date'], $sChecked);
            }
            print '
    </tr>;
        }

    Something like this. I'll go test this quickly with some flat array data and see how it comes out.

    Edit:
    Okay, so that won't end up working since I obviously forgot a group concat which I don't recommend using anyway. I tested using this:
    PHP Code:
    $iDay 60 60 24// Seconds in a day, or 86400 if you will.
    $iNumDays 20;
    $iStart time(); // Start today.
    $aDays range($iStartstrtotime("+$iNumDays day"$iStart), $iDay);
    $iDays count($aDays);

    $start min($aDays);
    $end max($aDays);

    $aRecords = array(
        array(
    'resourceName' => 'John''date' => '2012-05-18'),
        array(
    'resourceName' => 'John''date' => '2012-05-23'),
        array(
    'resourceName' => 'Jane''date' => '2012-06-02'),
    );

    $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>'

    $sLast null;
    print 
    '<tr>';
    foreach (
    $aNewRecords AS $resourceName => $dates)
    {
        if (
    $sLast != $resourceName)
        {
            if (
    $sLast !== null)
            {
                print 
    '</tr><tr>';
            }
            
    printf('<td>%s</td>'$resourceName);
            
    $sLast $resourceName;
        }

        for (
    $i 0$i $iDays; ++$i)
        {
            
    $sChecked in_array(date('Y-m-d'$aDays[$i]), $dates) ? ' checked="checked"' '';
            
    printf('<td><input type="checkbox" name="chk[%s]" value="%s"%s /></td>'$resourceName$aDate[$i], $sChecked);
        }
    }
    print 
    '</tr></table>'
    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($iStartstrtotime("+$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>'

    $sLast null;
    print 
    '<tr>';
    foreach (
    $aNewRecords AS $resourceName => $dates)
    {
        if (
    $sLast != $resourceName)
        {
            if (
    $sLast !== null)
            {
                print 
    '</tr><tr>';
            }
            
    printf('<td>%s</td>'$resourceName);
            
    $sLast $resourceName;
        }

        for (
    $i 0$i $iDays; ++$i)
        {
            
    $sChecked in_array(date('Y-m-d'$aDays[$i]), $dates) ? ' checked="checked"' '';
            
    printf('<td><input type="checkbox" name="chk[%s]" value="%s"%s /></td>'$resourceName$aDate[$i], $sChecked);
        }
    }
    print 
    '</tr></table>'
    That should work depending entirely on the resourceName to janis allocation.
    Last edited by Fou-Lu; 05-17-2012 at 08:53 PM.

  • #7
    New Coder
    Join Date
    May 2012
    Posts
    17
    Thanks
    2
    Thanked 0 Times in 0 Posts
    OK, Thanks Fou-Lu!
    I will check from my side as well!

  • #8
    New Coder
    Join Date
    May 2012
    Posts
    17
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I got Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRING
    for the code lines below:

    PHP Code:
                $sChecked date('Y-m-d'$aDays[$i]) == $row['date'] ? ' checked="checked" : '';
                printf('
    <td><input type="checkbox" name="chk[%s]" value="%s"%/></td>', $row['resourceName'], $row['date], $sChecked); 

  • #9
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    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 :/

  • #10
    New Coder
    Join Date
    May 2012
    Posts
    17
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Fou-Lu, that error appeared from the first code you gave (before edited one).

    So now I test the edited version but unfortunately second row of the table does not appear... First row of the table with the dates is ok!

    Of course I have replaced the names in code to respond to real names of the columns of the database.

    Any ideas?

  • #11
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    After the while() retrieval from the fetch add a:
    PHP Code:
    printf('<pre>%s</pre>'print_r($aRecordstrue)); 
    What does that show is in the array?

  • #12
    New Coder
    Join Date
    May 2012
    Posts
    17
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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?

  • #13
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Quote Originally Posted by raivis View Post
    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.

  • #14
    New Coder
    Join Date
    May 2012
    Posts
    17
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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()
    Attached Thumbnails Attached Thumbnails matching dates in loop-db_table.jpg  

  • #15
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    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'
    Would be sufficient


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •