how to match array with database table?

Question

I want to show calender as what the data in a particular month it shows all data in that month.

Suppose, I have data in my database as..

Jan=124;   Jan=243;   Jan=354
Feb=978;  Feb=765;   Feb=756;

It can be as:

I have did this code:

<?php
$q2="select * from  calendar";
$res2=mysql_query($q2);
$row2=mysql_fetch_assoc($res2);
$mnth = array('Jan','Feb');
$j= count($mnth);
for ($i=0; $i<= $j; $i++){
    if($row2['month']=='June'){ 
        ?><tr><td><?php 
        echo $row2['month']; ?> , <?php echo $row2['year']; 
        ?></td></tr><tbody><div><?php

        include("connection.php");
        $q1="select * from  calendar";
        $res=mysql_query($q1);

        while($row=mysql_fetch_assoc($res)){
            ?><tr><td><?php 
            echo $row['dates']; 
            ?></td></tr></tbody></div><?php 
        } 
    }
}

Could you please format your code so that it is readable? Thank you! Also, what does this have to do with JavaScript? — Felix Kling
– Felix Kling, Commented Jul 8, 2013 at 12:27
Don't use mysql_ functions - they are deprecated. Switch to mysqli_ or PDO. — Aleks G
– Aleks G, Commented Jul 8, 2013 at 12:52

Travesty3 · Accepted Answer · 2013-07-08 13:33:37Z

2

I would reconsider breaking out of PHP every time you need to print HTML tags. It makes your code hard to read.

Also, you're doing a query within a loop, which is a bad idea, and looks completely unnecessary for your problem.

You also have an opening <tbody> tag in your loop with no closing </tbody> tag.

And, as @AleksG mentioned in the comments, you shouldn't use mysql_* functions as they are deprecated. I recommend switching to PDO.

So on to the answer...

Assuming your table structure looks like this:

+-------+------+-------+
| month | year | dates |
+-------+------+-------+
| Jan   | 2013 | 124   |
| Jan   | 2013 | 243   |
| Jan   | 2013 | 354   |
| Feb   | 2013 | 978   |
| Feb   | 2013 | 765   |
| Feb   | 2013 | 756   |
+-------+------+-------+

Based on your question title, this will put the values into an array, with the keys being the month and year, and the values being the numeric "dates" field:

// replace the below line with your mysql connection string
$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
$result = $dbh->query('
    SELECT month, year, dates FROM calendar ORDER BY year, month
');

$mnth = array();
foreach ($result as $row) {
    $key = $row['month'] .','. $row['year']; // e.g. 'Jan,2013'
    // push the new value. The inner array will be created automatically
    $mnth[$key][] = $row['dates'];
}

print_r($mnth);
/* will look like this:
Array(
    [Jan,2013] => Array(
        [0] => 124,
        [1] => 243,
        [2] => 354
    ),
    [Feb,2013] => Array(
        [0] => 978,
        [1] => 765,
        [2] => 756
    )
) */

But based on your code, it looks like you want to output this as an HTML table. So here's how you could do that. The query is the same, so this just replaces the loop:

echo '<table>';
$current = '';
foreach ($result as $row) {
    // check to see if we've switched to a new month/year
    $next = $row['month'] .','. $row['year'];
    if ($current != $next) {
        // if we have moved to a new month/year, print a new header row
        echo '<tr><th>'. $next .'</th></tr>';
        // and update $current
        $current = $next;
    }

    echo '<tr><td>'. $row['dates'] .'</td></tr>';
}
echo '</table>';

edited Jul 8, 2013 at 13:33

answered Jul 8, 2013 at 13:08

Travesty3

14.5k7 gold badges64 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jimbo Over a year ago

Good answer! Although personal opinion, I would do: $current = ($current != sprintf('%s,%d', $row['month'], $row['year'])) ? $row['month'] : $row['year']; :)

Travesty3 Over a year ago

@Jimbo: That would assign $row['month'] to $current if it is equal, or $row['year'] to $current if it is not equal. But what's wrong with a simple concatenation? Is there an advantage I'm unaware of in using sprintf? And I tend to avoid ternaries...they can be difficult to follow.

Jimbo Over a year ago

No of course not, hence why it's just opinion. Not sure why, but I love sprintf() and ternaries. Maybe I've become a bit of a code masochist over the years...

Collectives™ on Stack Overflow

how to match array with database table?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related