-1

Successfully connected to database.

I have a table named "meal" and I am using a code like, 

$sql = "SELECT * FROM ( SELECT date, month, day, breakfast, launch, dinner, meal_total, note FROM meal WHERE stid='$_SESSION[stid]'  AND  ORDER BY date DESC LIMIT 35) sub ORDER BY date DESC";

$result = $dbconnection->query($sql);

I am selecting the rows by, stid='$_SESSION[stid]'. The "date" column records "current timestamp".

I am using this code to show the values of rows in a HTML table like, 

  if ($result->num_rows > 0) {
         echo "<table >
<tr>
    <th>Timestamp</th>
    <th>Month</th>
    <th>Day</th>
    <th>Breakfast</th>
    <th>Launch</th>
    <th>Dinner</th>
    <th>Total Meals</th>
</tr>";

         while($row = $result->fetch_assoc()) {

echo "<tr>
<td>" . $row["date"]. "</td>
<td>" . $row["month"]. " </td>
<td>" . $row["day"]. "</td>
<td>" . $row["breakfast"]. "</td>
<td>" . $row["launch"]. "</td>
<td>" . $row["dinner"]. "</td>
<td>" . $row["meal_total"]. "</td>
</tr>";

         }
         echo "</table>";
    } else {
         echo "No results!";
    }

The code is returning all the rows of the User (as ID). Like,

        __________________________________________________________
        Year-Month-Date TIME | Nov 2015 | 10 | Others Details ...| 
         _________________________________________________________
        Year-Month-Date TIME | Nov 2015 | 02 | Others Details ...| 
        __________________________________________________________
        Year-Month-Date TIME | Oct 2015 | 15 | Others Details ...| 
        __________________________________________________________

But the problem is I need to show only the rows from current month. Like,

            _________________________________________________________
            Year-Month-Date TIME | Nov 2015 | 10 | Others Details ...| 
            __________________________________________________________
            Year-Month-Date TIME | Nov 2015 | 02 | Others Details ...| 
            __________________________________________________________

Please help me sir. I know there will be a simple trick when selecting rows from my table but I have failed to reveal one as my need. Thanks.

Rahat Vuban
  • 49
  • 10

1 Answers1

0

Use a date comparison condition in your SQL:

SELECT date, month, day, breakfast, launch, dinner, meal_total, note 
FROM   meal 
WHERE  stid='$_SESSION[stid]'  
AND    date >= DATE_ADD(CURDATE(), INTERVAL -DAY(CURDATE())+1 DAY)    
ORDER BY date DESC
LIMIT 35

Tested in this sql fiddle.

The DATE_ADD function is used here to return the first day of the current month.

Note that the SQL you provided has a syntax error: the sequence AND ORDER BY is invalid.

Also, you wrap an inner query inside an outer query that adds nothing to what the inner already does. Just keep the inner one.

trincot
  • 317,000
  • 35
  • 244
  • 286
  • Oh yes! there is a syntax error. Now I am trying to implement your suggestion. I have a further question. If I want to select Rows from Last Month what will be the code then? - Thanks in advance. – Rahat Vuban Nov 07 '15 at 17:39
  • 2
    @Rahat, you should now look and read about [`DATE_ADD`](http://www.w3schools.com/sql/func_dateadd.asp) and learn how to do it yourself. If you consider your **original** question answered, please accept it. – trincot Nov 07 '15 at 17:48
  • Sorry Sir, Unfortunately the code, AND date >= DATE_ADD(CURDATE -DAY(date)+1 DAY) Returns me no result/output! – Rahat Vuban Nov 07 '15 at 18:03
  • If I look at the output you have given in your question, it seems like your date column has values like `Year-Month-Date TIME`, but that is evidently not a date. And as a consequence, there is no result when comparing it with real dates. I also wonder what those `month` and `day` columns are for, when you have a date column that is supposed to have all a date is about. – trincot Nov 07 '15 at 18:13
  • My Actual "date" format is like : 2015-11-07 13:12:01 And, "month" format is like : Nov 2015 – Rahat Vuban Nov 07 '15 at 18:16
  • Don't run the query. FIrst echo the query. Get output of that query and run in sql of your database to check what exactly it comes. @RahatVuban – Nana Partykar Nov 07 '15 at 18:17
  • @trincot : Actually I am taking all the values from a html input form. The first "date" column is for recording submission time. The "month" & the "day" column is for user choice. – Rahat Vuban Nov 07 '15 at 18:26
  • OK, I have corrected a mistake in the SQL in my answer. – trincot Nov 07 '15 at 18:28
  • @trincot : Sir, This time I am getting all the rows of all months. I think there may be a mistake in my code started from here : if ($result->num_rows > 0) { echo "
     – Rahat Vuban Nov 07 '15 at 18:36
  • No, the PHP code is not going to produce rows that it did not get from the database. Give an example date that you get in the result, that you did not want to have. – trincot Nov 07 '15 at 18:51
  • @trincot : Sir. Thanks a lot. At last I have succeed! Actually there is a simple mistake of mine. And your Last Edit is working perfectly for me. – Rahat Vuban Nov 07 '15 at 19:26
  • @NanaPartykar : Thank you too Sir. Best wishes. – Rahat Vuban Nov 07 '15 at 19:26