2
\$\begingroup\$

I have an accordion that checks if there is data to be displayed and if so, the accordion show the data from the specific month.

Is there a way to simplify the code below?

<?php
    $conn = mysql_connect("localhost","user","pass");
    mysql_select_db("lista_de_analize");
    mysql_set_charset("UTF8", $conn);
?>
<script>
 $(function() 
 {
    var icons = 
    {
        header: "ui-icon-circle-arrow-e",
        activeHeader: "ui-icon-circle-arrow-s"
    };
    $( "#dam" ).accordion({
        icons: icons,
        heightStyle: "content",
        collapsible: false
    });
    $( "#antrecut" ).accordion({
        icons: icons,
        heightStyle: "content",
        collapsible: true
    });
 });
</script>
<?php
    $construct ="SELECT * FROM Anunturi, TabelAnunturi";
    $constructian ="SELECT * from Anunturi WHERE MONTH(DataIntroducere) = 1 AND YEAR(DataIntroducere) = YEAR(CURDATE())";
    $constructfeb ="SELECT * from Anunturi WHERE MONTH(DataIntroducere) = 2 AND YEAR(DataIntroducere) = YEAR(CURDATE())";
    $constructmar ="SELECT * from Anunturi WHERE MONTH(DataIntroducere) = 3 AND YEAR(DataIntroducere) = YEAR(CURDATE())";
    $constructapr ="SELECT * from Anunturi WHERE MONTH(DataIntroducere) = 4 AND YEAR(DataIntroducere) = YEAR(CURDATE())";
    $constructmai ="SELECT * from Anunturi WHERE MONTH(DataIntroducere) = 5 AND YEAR(DataIntroducere) = YEAR(CURDATE())";
    $constructiun ="SELECT * from Anunturi WHERE MONTH(DataIntroducere) = 6 AND YEAR(DataIntroducere) = YEAR(CURDATE())";
    $constructiul ="SELECT * from Anunturi WHERE MONTH(DataIntroducere) = 7 AND YEAR(DataIntroducere) = YEAR(CURDATE())";
    $constructaug ="SELECT * from Anunturi WHERE MONTH(DataIntroducere) = 8 AND YEAR(DataIntroducere) = YEAR(CURDATE())";
    $constructsep ="SELECT * from Anunturi WHERE MONTH(DataIntroducere) = 9 AND YEAR(DataIntroducere) = YEAR(CURDATE())";
    $constructoct ="SELECT * from Anunturi WHERE MONTH(DataIntroducere) = 10 AND YEAR(DataIntroducere) = YEAR(CURDATE())";
    $constructnov ="SELECT * from Anunturi WHERE MONTH(DataIntroducere) = 11 AND YEAR(DataIntroducere) = YEAR(CURDATE())";
    $constructdec ="SELECT * from Anunturi WHERE MONTH(DataIntroducere) = 12 AND YEAR(DataIntroducere) = YEAR(CURDATE())";
    $run = mysql_query($construct) or die(mysql_error());
    $runian = mysql_query($constructian) or die(mysql_error());
    $runfeb = mysql_query($constructfeb) or die(mysql_error());
    $runmar = mysql_query($constructmar) or die(mysql_error());
    $runapr = mysql_query($constructapr) or die(mysql_error());
    $runmai = mysql_query($constructmai) or die(mysql_error());
    $runiun = mysql_query($constructiun) or die(mysql_error());
    $runiul = mysql_query($constructiul) or die(mysql_error());
    $runaug = mysql_query($constructaug) or die(mysql_error());
    $runsep = mysql_query($constructsep) or die(mysql_error());
    $runoct = mysql_query($constructoct) or die(mysql_error());
    $runnov = mysql_query($constructnov) or die(mysql_error());
    $rundec = mysql_query($constructdec) or die(mysql_error());
    $foundnum = mysql_num_rows($run);
    $foundnumian = mysql_num_rows($runian);
    $foundnumfeb = mysql_num_rows($runfeb);
    $foundnummar = mysql_num_rows($runmar);
    $foundnumapr = mysql_num_rows($runapr);
    $foundnummai = mysql_num_rows($runmai);
    $foundnumiun = mysql_num_rows($runiun);
    $foundnumiul = mysql_num_rows($runiul);
    $foundnumaug = mysql_num_rows($runaug);
    $foundnumsep = mysql_num_rows($runsep);
    $foundnumoct = mysql_num_rows($runoct);
    $foundnumnov = mysql_num_rows($runnov);
    $foundnumdec = mysql_num_rows($rundec);

    if ($foundnum==0)
    {
        echo "Nu avem noutăți!";
    }
    else
    {
        echo"
        <div id='dam'>";

        if(mysql_num_rows($rundec) > 0 )
        {

            echo"
            <h3>Decembrie 2013</h3>
            <div><br>";
                while($runrows = mysql_fetch_assoc($rundec))
                {
                    $Luna = $runrows ['Luna'];
                    $Ziua = $runrows ['Ziua'];
                    $Mesaj = $runrows ['Mesaj'];
                    $Anul = $runrows ['Anul'];
                    echo "
                    <table class='noutatitabel'>
                        <tr>
                            <td class='faramargini'>
                                <div class='data'>
                                    <div class='luna'>$Luna $Anul</div>
                                    <div class='ziua'>$Ziua</div>
                                </div>
                            </td>
                            <td class='albastru'>$Mesaj
                            </td>
                        </tr>
                    </table><br>";                      
                }
            echo"
            <br><hr><br></div>";
        }
        if(mysql_num_rows($runnov) > 0 )
        {

            echo"
            <h3>Noiembrie 2013</h3>
            <div><br>";
                while($runrows = mysql_fetch_assoc($runnov))
                {
                    $Luna = $runrows ['Luna'];
                    $Ziua = $runrows ['Ziua'];
                    $Mesaj = $runrows ['Mesaj'];
                    $Anul = $runrows ['Anul'];
                    echo "
                    <table class='noutatitabel'>
                        <tr>
                            <td class='faramargini'>
                                <div class='data'>
                                    <div class='luna'>$Luna $Anul</div>
                                    <div class='ziua'>$Ziua</div>
                                </div>
                            </td>
                            <td class='albastru'>$Mesaj
                            </td>
                        </tr>
                    </table><br>";                      
                }
            echo"
            <br><hr><br></div>";
        }
        if(mysql_num_rows($runoct) > 0 )
        {

            echo"
            <h3>Octombrie 2013</h3>
            <div><br>";
                while($runrows = mysql_fetch_assoc($runoct))
                {
                    $Luna = $runrows ['Luna'];
                    $Ziua = $runrows ['Ziua'];
                    $Mesaj = $runrows ['Mesaj'];
                    $Anul = $runrows ['Anul'];
                    echo "
                    <table class='noutatitabel'>
                        <tr>
                            <td class='faramargini'>
                                <div class='data'>
                                    <div class='luna'>$Luna $Anul</div>
                                    <div class='ziua'>$Ziua</div>
                                </div>
                            </td>
                            <td class='albastru'>$Mesaj
                            </td>
                        </tr>
                    </table><br>";                      
                }
            echo"
            <br><hr><br></div>";
        }
        if(mysql_num_rows($runsep) > 0 )
        {

            echo"
            <h3>Septembrie 2013</h3>
            <div><br>";
                while($runrows = mysql_fetch_assoc($runsep))
                {
                    $Luna = $runrows ['Luna'];
                    $Ziua = $runrows ['Ziua'];
                    $Mesaj = $runrows ['Mesaj'];
                    $Anul = $runrows ['Anul'];
                    echo "
                    <table class='noutatitabel'>
                        <tr>
                            <td class='faramargini'>
                                <div class='data'>
                                    <div class='luna'>$Luna $Anul</div>
                                    <div class='ziua'>$Ziua</div>
                                </div>
                            </td>
                            <td class='albastru'>$Mesaj
                            </td>
                        </tr>
                    </table><br>";                      
                }
            echo"
            <br><hr><br></div>";
        }
        if(mysql_num_rows($runaug) > 0 )
        {

            echo"
            <h3>August 2013</h3>
            <div><br>";
                while($runrows = mysql_fetch_assoc($runaug))
                {
                    $Luna = $runrows ['Luna'];
                    $Ziua = $runrows ['Ziua'];
                    $Mesaj = $runrows ['Mesaj'];
                    $Anul = $runrows ['Anul'];
                    echo "
                    <table class='noutatitabel'>
                        <tr>
                            <td class='faramargini'>
                                <div class='data'>
                                    <div class='luna'>$Luna $Anul</div>
                                    <div class='ziua'>$Ziua</div>
                                </div>
                            </td>
                            <td class='albastru'>$Mesaj
                            </td>
                        </tr>
                    </table><br>";                      
                }
            echo"
            <br><hr><br></div>";
        }
        if(mysql_num_rows($runiul) > 0 )
        {

            echo"
            <h3>Iulie 2013</h3>
            <div><br>";
                while($runrows = mysql_fetch_assoc($runiul))
                {
                    $Luna = $runrows ['Luna'];
                    $Ziua = $runrows ['Ziua'];
                    $Mesaj = $runrows ['Mesaj'];
                    $Anul = $runrows ['Anul'];
                    echo "
                    <table class='noutatitabel'>
                        <tr>
                            <td class='faramargini'>
                                <div class='data'>
                                    <div class='luna'>$Luna $Anul</div>
                                    <div class='ziua'>$Ziua</div>
                                </div>
                            </td>
                            <td class='albastru'>$Mesaj
                            </td>
                        </tr>
                    </table><br>";                      
                }
            echo"
            <br><hr><br></div>";
        }
        if(mysql_num_rows($runiun) > 0 )
        {

            echo"
            <h3>Iunie 2013</h3>
            <div><br>";
                while($runrows = mysql_fetch_assoc($runiun))
                {
                    $Luna = $runrows ['Luna'];
                    $Ziua = $runrows ['Ziua'];
                    $Mesaj = $runrows ['Mesaj'];
                    $Anul = $runrows ['Anul'];
                    echo "
                    <table class='noutatitabel'>
                        <tr>
                            <td class='faramargini'>
                                <div class='data'>
                                    <div class='luna'>$Luna $Anul</div>
                                    <div class='ziua'>$Ziua</div>
                                </div>
                            </td>
                            <td class='albastru'>$Mesaj
                            </td>
                        </tr>
                    </table><br>";                      
                }
            echo"
            <br><hr><br></div>";
        }
        if(mysql_num_rows($runmai) > 0 )
        {

            echo"
            <h3>Mai 2013</h3>
            <div><br>";
                while($runrows = mysql_fetch_assoc($runmai))
                {
                    $Luna = $runrows ['Luna'];
                    $Ziua = $runrows ['Ziua'];
                    $Mesaj = $runrows ['Mesaj'];
                    $Anul = $runrows ['Anul'];
                    echo "
                    <table class='noutatitabel'>
                        <tr>
                            <td class='faramargini'>
                                <div class='data'>
                                    <div class='luna'>$Luna $Anul</div>
                                    <div class='ziua'>$Ziua</div>
                                </div>
                            </td>
                            <td class='albastru'>$Mesaj
                            </td>
                        </tr>
                    </table><br>";                      
                }
            echo"
            <br><hr><br></div>";
        }
        if(mysql_num_rows($runapr) > 0 )
        {

            echo"
            <h3>Aprilie 2013</h3>
            <div><br>";
                while($runrows = mysql_fetch_assoc($runapr))
                {
                    $Luna = $runrows ['Luna'];
                    $Ziua = $runrows ['Ziua'];
                    $Mesaj = $runrows ['Mesaj'];
                    $Anul = $runrows ['Anul'];
                    echo "
                    <table class='noutatitabel'>
                        <tr>
                            <td class='faramargini'>
                                <div class='data'>
                                    <div class='luna'>$Luna $Anul</div>
                                    <div class='ziua'>$Ziua</div>
                                </div>
                            </td>
                            <td class='albastru'>$Mesaj
                            </td>
                        </tr>
                    </table><br>";                      
                }
            echo"
            <br><hr><br></div>";
        }
        if(mysql_num_rows($runmar) > 0 )
        {

            echo"
            <h3>Martie 2013</h3>
            <div><br>";
                while($runrows = mysql_fetch_assoc($runmar))
                {
                    $Luna = $runrows ['Luna'];
                    $Ziua = $runrows ['Ziua'];
                    $Mesaj = $runrows ['Mesaj'];
                    $Anul = $runrows ['Anul'];
                    echo "
                    <table class='noutatitabel'>
                        <tr>
                            <td class='faramargini'>
                                <div class='data'>
                                    <div class='luna'>$Luna $Anul</div>
                                    <div class='ziua'>$Ziua</div>
                                </div>
                            </td>
                            <td class='albastru'>$Mesaj
                            </td>
                        </tr>
                    </table><br>";                      
                }
            echo"
            <br><hr><br></div>";
        }
        if(mysql_num_rows($runfeb) > 0 )
        {

            echo"
            <h3>Februarie 2013</h3>
            <div><br>";
                while($runrows = mysql_fetch_assoc($runfeb))
                {
                    $Luna = $runrows ['Luna'];
                    $Ziua = $runrows ['Ziua'];
                    $Mesaj = $runrows ['Mesaj'];
                    $Anul = $runrows ['Anul'];
                    echo "
                    <table class='noutatitabel'>
                        <tr>
                            <td class='faramargini'>
                                <div class='data'>
                                    <div class='luna'>$Luna $Anul</div>
                                    <div class='ziua'>$Ziua</div>
                                </div>
                            </td>
                            <td class='albastru'>$Mesaj
                            </td>
                        </tr>
                    </table><br>";                      
                }
            echo"
            <br><hr><br></div>";
        }
        if(mysql_num_rows($runian) > 0 )
        {

            echo"
            <h3>Ianuarie 2013</h3>
            <div><br>";
                while($runrows = mysql_fetch_assoc($runian))
                {
                    $Luna = $runrows ['Luna'];
                    $Ziua = $runrows ['Ziua'];
                    $Mesaj = $runrows ['Mesaj'];
                    $Anul = $runrows ['Anul'];
                    echo "
                    <table class='noutatitabel'>
                        <tr>
                            <td class='faramargini'>
                                <div class='data'>
                                    <div class='luna'>$Luna $Anul</div>
                                    <div class='ziua'>$Ziua</div>
                                </div>
                            </td>
                            <td class='albastru'>$Mesaj
                            </td>
                        </tr>
                    </table><br>";                      
                }
            echo"
            <br><hr><br></div>";
        }
?>
\$\endgroup\$
5
  • \$\begingroup\$ What's the point of filtering by month if you want all of them? Are you looking for counts or averages? \$\endgroup\$
    – Mat
    Aug 30 '13 at 7:14
  • \$\begingroup\$ i have an accordion and the header is shown if there are result in the specific month... \$\endgroup\$ Aug 30 '13 at 7:15
  • \$\begingroup\$ I don't quite understand that. Could you explain your use case for this in a bit more detail in your question (by editing it)? \$\endgroup\$
    – Mat
    Aug 30 '13 at 7:18
  • \$\begingroup\$ Thanks! Good news: there are ways to make it shorter indeed :-) Can you tell us also approximately how many entries you expect per month? (i.e. just a few, dozens, hundreds, more?) \$\endgroup\$
    – Mat
    Aug 30 '13 at 7:36
  • \$\begingroup\$ 5 to 10 entries... but not always there will be data in a month... \$\endgroup\$ Aug 30 '13 at 7:39
5
\$\begingroup\$

Hopefully you can understand what I have done, I have written comments in English

<?php

// mysql_ is deprecated use mysqli_
// die on error
$link = mysqli_connect("localhost","user","pass") or die(mysqli_connect_error());
mysqli_select_db($link, "lista_de_analize");
mysqli_set_charset($link, "UTF8");

// select all data at once
$constructian ="SELECT * from Anunturi WHERE YEAR(DataIntroducere) = YEAR(CURDATE()) ORDER BY DataIntroducere DESC";

$runian = mysqli_query($link, $constructian) or die(mysqli_error($link));

// build multi-dimensional array from data
$data = array();

while($runrows = mysql_fetch_assoc($runian)) {
    $month_year = date('F Y', strtotime($runrows['DataIntroducere']));
    $data[$month_year][] = $runrows;
}


?>
    <script>
        $(function()
        {
            var icons =
            {
                header: "ui-icon-circle-arrow-e",
                activeHeader: "ui-icon-circle-arrow-s"
            };
            $( "#dam" ).accordion({
                icons: icons,
                heightStyle: "content",
                collapsible: false
            });
            $( "#antrecut" ).accordion({
                icons: icons,
                heightStyle: "content",
                collapsible: true
            });
        });
    </script>
<?php

if (count($data)==0)
{
    echo "Nu avem noutăți!";
}
else
{
    echo"
        <div id='dam'>";

    foreach ($data as $month_year => $month_year_runrows)
    {
        echo"
                <h3>$month_year</h3>
            <div><br>";

        foreach ($month_year_runrows as $runrows)
        {
            echo "
                <table class='noutatitabel'>
                    <tr>
                        <td class='faramargini'>
                            <div class='data'>
                                <div class='luna'>{$runrows['Luna']} {$runrows['Anul']}</div>
                                <div class='ziua'>{$runrows ['Ziua']}</div>
                            </div>
                        </td>
                        <td class='albastru'>{$runrows ['Mesaj']}
                        </td>
                    </tr>
                </table><br>";
        }

        echo"
            <br><hr><br></div>";
    }
}


?>
\$\endgroup\$
8
  • \$\begingroup\$ This is SHORT!!! too bad that i can't check if it works... i have to go... but i'll be back in 2 hours... \$\endgroup\$ Aug 30 '13 at 8:01
  • \$\begingroup\$ Today everything is short... :) your code, my meeting... Checked your code... you did something there... gorgeous. It doesn't work with mysqli statements... so i reverted to mysql with the proper changes so... it works!!! but the month is not displayed as i wanted (only shows first 3 letters of the month) and something else... in my code, the order of months was from dec to ian (desc) but here is no restriction... \$\endgroup\$ Aug 30 '13 at 8:42
  • \$\begingroup\$ I have made a couple of small changes. $month_year = date('F Y' Use F Y instead of M Y for full month name, and on $constructian ="SELECT I have added and ORDER BY clause, so they are in date order you can choose ASC or DESC \$\endgroup\$
    – bumperbox
    Aug 30 '13 at 10:23
  • \$\begingroup\$ The part with ASC or DESC i have done it... but thank you again for the F Y... i was searching the net for the answer... If i want to have the name of months in another language must I make an array or is there another easy way of doing this? \$\endgroup\$ Aug 30 '13 at 11:22
  • \$\begingroup\$ You could either use an array, or set your locale php.net/manual/en/function.setlocale.php \$\endgroup\$
    – bumperbox
    Aug 30 '13 at 16:44
1
\$\begingroup\$

(Note: I don't know HTML or JavaScript enough to comment on the jQuery part or the layout.)

First things first: you really shouldn't be using the mysql_* functions any more. There are better alternatives available, see for instance this question on Stack Overflow: Why shouldn't I use mysql_* functions in PHP?

That won't directly help you here though. What you need to do is refactor your code a bit.

  • All the code blocks that output the individual rows are identical. That's a sure sign you should have created a function to do so. Create a function output_one_row($row) that takes one record and does the same thing as the blocks inside the while(mysql_fetch_assoc()) loops.
  • All the individual selects on Anunturi are identical except for the month queried. You could also create a function that produces the query for a month (given as a parameter) and returns the result set for that month. Using a bind variable would be even better.

    Also don't use select *, select only the columns you will need to output. This might not matter for you right now, but if that table gets additional columns in the future, you'll be making your database do unnecessary work to retrieve data you're not using.

    An order by for the date column could make sense, if that's relevant for your use case.

  • You're repeating the same code pattern for the twelve distinct months. You should instead have a single function that lays out the table for a given month, and call that function twelve times. The function could take only a month, and do the querying and output based on the two functions above.

    Use an associative array to get the month name from the month number. (Or a PHP builtin if one exists that is properly localized for your environment.)

  • Don't do select * from ... then count in PHP if you only need the count. Rather, you should select count(*) from ..., i.e. let the database do the counting (which it can sometimes optimize from indexes, or actually have buffered already). Don't retrieve the whole dataset just to count it.

If you do that, your code would look like (pseudo-code):

function output_one_row($row) { /* ... */ }
function select_offers($month) {
  // build query for the specified month
  // execute it
  // return result set
}
function output_month_table($month) {
  // get result set for the month
  // check if there is any data (leave if not, returning 0)
  // output table header
  // output individual rows using output_row
  // output table footer
  // return number of rows printed
}
// "main"
$total_rows_output = 0;
for ($month in 1 .. 12) {
   $total_rows_output += output_month_table($month);
}
if ($total_rows_output == 0) {
   // print "no data found"
}

Notice that you don't need the global select count with this, you derive it from the individual months.

You can also avoid the num_rows calls completely by using a bit more state in the output_month_table, something like:

 $rows_processed = 0;
 while ($row = mysql_fetch_assoc(...)) {
   if ($rows_processed == 0) {
     // output month header
   }
   // output the table row
   $rows_processed++;
 }
 if ($rows_processed > 0) {
   // output month footer
 }
 return $rows_processed;

Now that's still twelve queries, when a single one could be enough. If you want to reduce the number of queries, it's possible too but requires a bit more state. The idea for that would be to select the data for the complete year, sorted by date, and include the month(DataIntroducere) in the selected columns (for convenience, you could derive it in PHP too).

Then, as you traverse the (ordered) result set, you do check if the current row is in the same month as the previous one. If it is, you simply output the row. If it isn't, you close the previous month's table, open the (now current) month's header, and output the row.

In pseudo-code:

$result_set = // select [columns] from your_table where [current year] order by date
$current_month = -1; // invalid month number
$total_rows = 0;
foreach ($row in $result_set) {
  if ($row["month"] != $current_month) {
    if ($total_rows != 0) {
      // output the HTML to close the previously output month table
    }
    $current_month = $row["month"];
    // output current month table header
  }
  // output row
  $total_rows++;
}
if ($total_rows > 0) {
  // close last month table
} else {
  // no data found at all
}
\$\endgroup\$
1
  • \$\begingroup\$ A very good answer... one can learn from that! thank you. \$\endgroup\$ Aug 30 '13 at 9:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.