PHP - Count And Query Per Date Report

Hi

I'm not at all good at PHP and haven't really played with it for some years, so bare with me if code examples are old

I'm looking for a simple solution to count rows (MySQL), without using multiple queries.

The below code is quite simple, counting rows where 'Department' is 'Sales':

$sql ="SELECT * FROM DB WHERE Department ='Sales'";
$result=mysqli_query($con,$sql);
$rowcount=mysqli_num_rows($result);

But what If I need to count the rows for the different departments?:

$sql ="SELECT * FROM DB WHERE Department ='Sales'";
$result=mysqli_query($con,$sql);
$rowcount=mysqli_num_rows($result);

$sql ="SELECT * FROM DB WHERE Department ='Support'";
$result=mysqli_query($con,$sql);
$rowcount=mysqli_num_rows($result);

$sql ="SELECT * FROM DB WHERE Department ='Online'";
$result=mysqli_query($con,$sql);
$rowcount=mysqli_num_rows($result);

There must be a better / simpler way to this with a single query?

Hope for someone to show me the light

 

PS. Once this is resolved, I'll probably need further help, to accomplish my final result.

Hello there,

Having a nightmare here.  It feels like what I need to do is really easy but I just can't get it to work.  I have a table called "groups2" that holds a unique id and the name of an activity.

I already have a query that finds the users selected groups using a while loop but I also want to show how many other members are in that group by counting the number of times the activity comes up or the id comes up.  I don't know whether I need 2 while loops nested or what but I get the error

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource on line 32 which is highlighted.

Can anyone help.  I am no expert at php and still learning so some advice or example code would be great.


$result = mysql_query("SELECT * FROM groups2 WHERE L_ID = ". $_SESSION['member_ID'] .";");
$data = mysql_query("SELECT COUNT('name') AS num FROM groups2 WHERE name = " . $row['name'] . "");
$count = mysql_fetch_assoc($data);
$numbers = $count['num'];
while ( $row = mysql_fetch_assoc($result) ) {
echo("<tr>
<td><font face='Arial, Helvetica, sans-serif' size='3'><strong>" . $row["name"] . "</strong></font></td>
</tr><tr>
<td><font face='Arial, Helvetica, sans-serif' size='1' color='#0000FF'><strong>Members (" . $numbers . ")</strong></font></td>
</tr><tr>
<td><hr width=95%><br></td>
</tr>");
}

Basically, this part of the code is checking the "scanned" table in my mysql database to get the last scanned date for the item at hand.  It then takes that date and selects all the vendor items in the odbc table "ARVEND" that are greater than the last purchase date.  || This is where I am running into a problem.  When it loops through each vendor item it selects from the "associated" mysql table to see if the vendor item has been scanned.  If it does not find anything I am trying to get the $checkpurdate variable to add 1 to itself. |

In the second script you will see the elseif($checkpurdate>0) .... Here I am saying if there are any vendor items that have not been scanned, highlight the row in yellow.

But, this part of my code $checkpurdate = $checkpurdate + 1; does not seem to be counting correctly.  Sometimes it counts '4' for an item when there are only two vendor items available.

All of this may sound confusing since you may not know what I am trying to do.  But, the main problem I am having is the counting part is returning wrong numbers. $checkpurdate = $checkpurdate + 1;


Code: [Select]
/* $checkpurdate  */

 $query3  = "SELECT date FROM scanned WHERE `item` = '$ITEM' ORDER BY date DESC LIMIT 1";
$result3 = mysql_query($query3);
$checklastdate = "";
while($row3 = mysql_fetch_array($result3, MYSQL_ASSOC))
{$checklastdate= $row3['date'];
$checklastdate=date("m/d/y", strtotime($checklastdate));   }

//checkpurdate loops
$checkassoc="0";
$checkpurdate="0";
$sqlc="SELECT RECNO5 FROM ARVEND WHERE PURDATE1 > '$checklastdate' AND ITEM = '$ITEM'";
$rsc=odbc_exec($conn,$sqlc);
if (!$rsc)
  {exit("Error in SQL");}
 
while (odbc_fetch_row($rsc))
  {
  $checkrecno5=odbc_result($rsc,"RECNO5");
$checkassosquery  = "associated WHERE `VENDORID` = '$checkrecno5' AND ITEM = '$ITEM'";
$checkassoc = get_rows($checkassosquery);
 
  if($checkassoc>0)
  {
  // do nothing
  }
  else
  {
  $checkpurdate = $checkpurdate + 1;
  }
 
 
 }
 
Code: [Select]
  elseif($checkpurdate>0)
{  echo"<TR class=\"NV\"> <TD>";
}

Hi Everyone,

I am working on implementing a blog comment system using the query below to display comments. My question is what is the best way to do a row count for the id column with this query or do I need to do a second db query to accomplish this? The purpose of this is to echo out the number of comments for that post, before displaying them.

Any help is appreciated.

Thanks in advance,

kaiman


<?php
// get url variables
$post_id = mysql_real_escape_string($_GET['id']);

include ("../../scripts/includes/nl2p.inc.php");

// connects to server and selects database
include ("../../scripts/includes/dbconnect.inc.php");

// table name
$tbl_name3="blog_comments";

// select info from comments database
$result3 = mysql_query ("SELECT count(*) FROM $table_name3 WHERE id='$post_id' ORDER BY id DESC LIMIT 1") 
or trigger_error("A mysql error has occurred!");
if (mysql_num_rows($result3) > 0 ) {
while($row = mysql_fetch_array($result3)) {
extract($row);

// display number of comments


// display date
$row_date = strtotime($row['date']);
putenv("TZ=America/Denver");
echo "<p class=\post\">On ".date('F, jS, Y', $row_date)." ";

// display commenter name
if($row['url'] == "") { 
echo $row['name']." wrote:</p>\n"; 
} 
     else { 
echo "<a href=\"".$row['url']."\" target=\"_blank\">".$row['name']."</a> wrote:</p>\n"; 
} 

// display content
$comments = $row['comment'];
echo nl2p($comments);
}
}
else {
echo "<p class=\"large_spacer\">No Comments</p>";
}
?>

The code below is part of some validation code that I have, it is supposed to count the number of entries in the table 'test_selections' where 'userid' equals the user that is logged in and where the 'transfer_in' date is a date within the current month. I am getting an error saying "Error running query Unknown column '10' in 'where clause' ". Does anybody know why this could be? I am hoping that it is something to do with the snippet of code below so I don't need to post the whole lot!!

Any help would be much appreciated.

Code: [Select]
$current_month = date("m");
$query = "SELECT COUNT(`userid`) as `transfers_count`
          FROM `test_selections`
          WHERE `userid` = '{$_SESSION['userid']}' AND Month(`transfer_in`) = `$current_month`
          GROUP BY `userid`";

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=312690.0

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=330871.0

Hi !

I am trying to translate my mysqli count query that works perfectly into prepared statements.

Unfortunately, after playing around and using my knowledge of PS, I have come up with this script which fails to execute and returns a http 500 error.

I may have missed something very silly, I require some guidance on fixing the error.

 

 

<?php


$conn = mysqli_connect("xxxx", "xxxx", "xxxx", "xxx");

$sel_query = "SELECT 
    S1, B1
    COUNT(IF(S1 = ?, 1, NULL)) 'Accepted',
    COUNT(IF(S1 = ?, 1, NULL)) 'Rejected',
    COUNT(IF(S1 = ?, 1, NULL)) 'Under_Review' FROM Enrol";
    
    $stmt = $conn->prepare($sel_query); 
    
	$Accepted="Accepted";
	$Rejected="Rejected";
	$Under_Review="Under Review";

	
	$stmt->bind_param("sss",$Accepted, $Rejected, $Under_Review);
	$stmt->execute();
	$result = $stmt->get_result(); // get the mysqli result
	  
	if($result->num_rows === 0) exit('No records found!');
	while($row = $result->fetch_assoc()) {  ?>
  
   
<tr>
        
        <td><?php echo $row["Accepted"]; ?></td>
        <td><?php echo $row["Rejected"]; ?></td>
        <td><?php echo $row["Under_Review"]; ?></td>
</tr>




</table>

 

 

Edited June 24, 2020 by PythonHelp

Hello there, i'm not sure if this goes to mysql, or this section, but since it's PHP based, i would say here.

So, here's the thing, i have full set up table that reads from mysql database and display it as table. Everything is working just fine, even "order" buttons that i made, now i would like to have below the table some counts, for example:

My table name "canonkickoff" has column named "Prevoz" which contain only Yes and No answers, i would like to see below the table how much "Yes" answers are inside the column. Also i have other column called "VelicinaMajice", which contain 6 different answers ( XS, S, M, L, XL, XXL ), i would like to see (again, below the table) how much of every has been answered.

Like:

There a
XS = 6
S = 1
M = 2
L = 3
And 32 answered with Yes.


Could anyone help me out? Here's the full php table:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>
<title>CanonKickOff 2012 Tabela.</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link rel="stylesheet" href="table.css" type="text/css">
</head>
<body style="margin: 0 0 0 0;">
<?
include("passwd.php");
@$start = $_GET["start"];
if($start =='')
    $start =0;
include("lib.php");
$link = mysql_connect($host,$username,$password);
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
$db_selected = mysql_select_db($db, $link);
if (!$db_selected) {
    die ("Can't use $db : " . mysql_error());
}
//total number of records in the table
$orderBy = array('Kompanija', 'id', 'ImePrezime', 'Email', 'Prevoz', 'VelicinaMajice', 'SlazemSe');

$order = 'id';
if (isset($_GET['orderBy']) && in_array($_GET['orderBy'], $orderBy)) {
    $order = $_GET['orderBy'];
}
$res = mysql_query("SELECT * from `$table` ORDER BY '.$order");
$res2 = mysql_query("SELECT * from `$table` ");
@$rows = mysql_num_rows ($res2);

$result = mysql_query("SELECT * from `$table` ORDER BY $order limit $start,40");
if (!$result) {
    die('Invalid query: ' . mysql_error());
}
 echo 'Sortiraj Po: <br>';
 echo '<a href="?orderBy=id">ID:</a> ';
 echo '<a href="?orderBy=ImePrezime">Ime i Prezime:</a> ';
 echo '<a href="?orderBy=Kompanija">Kompanija:</a> ';
 echo '<a href="?orderBy=email">Email adresi:</a> ';
 echo '<a href="?orderBy=Prevoz">Prevoz:</a> ';
 echo '<a href="?orderBy=VelicinaMajice">Velicina majice:</a> ';
 echo '<a href="?orderBy=SlazemSe">Slazu se:</a> ';
 echo "<p align=center class = 'menu'> Ocitana tabela: $table </p>";
$cols = mysql_num_fields($result);
$records = mysql_num_rows ($result);
 echo "<table align='center' width='1200' >";
 echo "<tr bgcolor='BBCCDD' class='menu'>";
 for ($i = 0; $i < $cols;$i++)
 {
    echo "<td align='center'>".mysql_field_name($result,$i)."</td>";
 }
 echo "</tr>";
 while ($row = mysql_fetch_array($result, MYSQL_NUM))
 {
    echo "<tr bgcolor='F6F6F6' class='normal'>";

   foreach ($row as $value)
   { echo "<td align='center'>".$value ."</td>";
   } 


   echo "</tr>";
 }
 $end = $start + $records;
 echo "<tr  align = 'center' bgcolor = 'BBCCDD' class='menu'><td colspan=$cols> $start  do  $end  od ukupno: $rows </td></tr>";
 echo "<tr  align = 'center' class='mylink'><td colspan=$cols> ";



    if($start != 0)
    {
    $prev = $start - 40;
    echo "<a  href='tabela.php?start=$prev'> Prethodna </a> ";
    }
    if($start<$rows-10)
    {
    $next = $start + 40;
    echo "<a href = 'tabela.php?start=$next'>Sledeca</a> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;";
    }
     echo "</td></tr>";
     echo "</table>";
 ?>
</body>

</html>

I have a database lets say punch_log

The data in the table looks like:
---------------------------------------------------------------------------
|punch_log_id |  user_id | punch_id |   punch_time           |
---------------------------------------------------------------------------
|   10010         |  21         |   1       | 2010-11-10 15:04:59|
|   10011         |  21         |       2       | 2010-11-10 15:50:05|
|   10010         |  21         |   1       | 2010-11-11 15:04:59|
|   10011         |  21         |       2       | 2010-11-11 15:50:05|
|   10010         |  21         |   1       | 2010-11-12 15:04:59|
|   10011         |  21         |       2       | 2010-11-12 15:50:05|
|   10010         |  21         |   1       | 2010-11-13 15:04:59|
|   10011         |  21         |       2       | 2010-11-13 15:50:05|
|   10010         |  21         |   1       | 2010-11-14 15:04:59|
|   10011         |  21         |       2       | 2010-11-14 15:50:05|
|   10010         |  21         |   1       | 2010-11-14 15:50:59|  <-- this is why i need this.
|   10011         |  21         |       2       | 2010-11-14 15:55:05|  <-- this is why i need this.
----------------------------------------------------------------------------

Im currently using :

$kust  = $_POST['AKuu'];
$kuni3 = $_POST['LKuu'];
$valitudtootaja   = $_POST['TNimi'];


mysql_select_db($database, $con);
$query2 = "SELECT *, SUM(punch_id) FROM punch_log WHERE user_id = '".$valitudtootaja."' AND punch_time   BETWEEN '$kust' AND '$kuni3' AND punch_id ='1' ";   
$result2 = mysql_query($query2) or die(mysql_error());
while($row = mysql_fetch_array($result2)){
   $X = $row['SUM(punch_id)'];

When using the current query im getting

Workers days at work = 6
it should be 5 but thats why i need some solution to group dates and then sum them.

Does anybody have any ideas?

I insert data in mysql table row using multiple method : 1#2#3 . 1 is ID of country, 2 is Id of state, 3 is ID of town . now i have this table for real estate listings. for each list(home) i have country/state/town (1#2#3). in country table i have list of country - in country table i have list of state - in country table i have list of town. i need to The number of houses in country / state / town . my mean is :
Code: [Select]
USA [ 13 ] <!-- This Is equal of alabama+alaska+arizona -->
----Alabama [8] <!-- This Is equal of Adamsville+Addison+Akron -->
-------Adamsville [2]
-------Addison[5]
-------Akron[1]
......(list of other City)
----Alaska [ 3 ]
-------Avondale[3]
......(list of other City)
----Arizona [ 2 ]
-------College[2]
......(list of other City)
Lisintg Table :

Code: [Select]
ID -- NAME -- LOCATION -- DATEJOIN -- ACTIVE
 1 -- TEST -- 1#2#3    -- 20110101 -- 1
 2 -- TEST1 -- 1#2#3    -- 20110101 -- 1
 3 -- TEST2 -- 1#3#5    -- 20110101 -- 1
 4 -- TEST3 -- 1#7#6    -- 20110101 -- 1
Country Table :

Code: [Select]
id       -- name 
 1        -- USA 
stats Table :

Code: [Select]
id        -- countryid -- name 
 1        -- 1             -- albama
 2        -- 1             -- alaska
 3        -- 1             -- akron
town Table :
Code: [Select]
id        -- countryid -- statsid -- name 
 1        -- 1             -- 1         -- adamsville
 2        -- 1             -- 1         -- addison
 3        -- 1             -- 1         -- akron
Thanks For Any Help.

Hi everyone

I have a database with table containing my news

it has a `start_date` field and a `end_date` field and I store dates as YYYY-MM-DD

I have a PHP script on my webpage which I have a date set on it, such as

$date = '2010-10-23'

What QUERY would I need to run to return all rows which the date defined falls between the start and end date

Any help would be great

I tried

WHERE `start_date` >= '2010-09-13' AND `end_date` <= '2010-09-13'

but that doesn't seem to work

Hello,
I am doing a hotel reservation website.First am getting the checkin (arrival) and Check out(Departure) date from user.Then in next from i fetch both this values using POST method and store it in variable $arrival and $departure and then am formattind the date as i want it in the form YYYY-MM-DD(My SQL).
Then am using the value of variable $arr and $dep in a query to fetch the records from DB but it is giving error.But when i do it in Hard code way I mean directly inserting the date in query it is running smmothly.Please help!!!! Am using Xampp 2.5
Heres my code...



<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>ROOMS</title>

<?php
   
   $arrival = $_POST['start'];
   $departure = $_POST['end'];
    $adults=6;
 $child=2;
 $room_id=101;
 
 function changeFormatDate($cdate){
list($day,$month,$year)=explode("/",$cdate);
return $year."-".$month."-".$day;
}

// $arr="'".$arr."'";

$arr= changeFormatDate($arrival);
 //settype($arr, "string");

// $timestamp=strtotime($arr1);
// $arr=date("Y-m-d",$timestamp);

$dep= changeFormatDate($departure);

// $timestamp=strtotime($dep1);
// $dep=date("Y-m-d",$timestamp);

?>

<style type="text/css">


<!--
.style2 {
   font-size: 12px;
   font-weight: bold;
}
-->
</style>
</head>

<body>

<!-- TOP -->
<div id="top1"><a href="index.php"></a></div>
<div id="top">

<ul class="menu">
<li class="home"><a href="index.php">Home</a></li>
<li class="about"><a href="about.php">About</a></li>
<li class="contacts"><a href="contact.php">Contacts</a></li>
<li class="renting"><a href="gallery.php">GALLERY</a></li>
<li class="selling"><a href="rates.php">RATES</a></li>


</ul>


</div>




<!-- HEADER -->
<!-- CONTENT -->
<div id="content">

<div id="leftPan">

<div id="services">
<h2>RESERVATION DETAILS </h2>
<p>
  <ul>
      Check In Date :<?php echo $arrival; ?><br />
      Check Out Date :<?php echo $departure; ?>  <br />
 </ul>
    </p>
</p>
</div>




</div>
<div id="featured"><br />
<div>
 <form action="personnalinfo.php" method="post" onsubmit="return validateForm()" name="room">
  <input name="start" type="hidden" value="<?php echo $arrival; ?>" />
  <input name="end" type="hidden" value="<?php echo $departure; ?>" />
  <input name="rooms" id="rooms" type="hidden" />
  <input name="adult" type="hidden" value="<?php echo $adults; ?>" />
  <input name="child" type="hidden" value="<?php echo $child; ?>" />

</div>
 
  <table bgcolor="white" border="1"  width="100%" style="float:left;table-layout:fixed" cellpadding="10" cellspacing="0" >
 <col width="70%">
  <tr>
  <th colspan="2" bgcolor="white"><h2><font color=maroon>Room Type</font></h2></th>
  </tr>
  <tr>
  <td>
  <table border="0" style="float:left;table-layout:fixed" width="100%">
  <col width="55%">
  <tr>
  <td valign="top">
  <img src="img1/apt.jpg" style="float:left" />

 </br>
<div style="margin-top:120px;margin-left:5px">
<img src="img1/apt1.jpg" />
  <img src="img1/apt2.jpg" />
  <img src="img1/apt3.jpg" />
  </div>
  </td>
  <td>
  <h3>Appartment</h3>
  <br>
    <span class="price">Price:</span> <span class="number">Rs. 5,000.00</span><br />
 <a> Apparment in HOTEL BELLA has 2 Rooms with connecting door.It can accomodate 4 Adult and 2 children.
 And are located on Beach side to give you comfort and a panoramic view so that you can have a luxury accommodation.<br>
 *Sitting area
<br>*jacuzzi shower</br>
*Large terrace overlooking the sea
*Jacuzzi
*Light therapy
*Air treatment <a href="#">more...</a></p><br />
  </td>
  </tr>
  </table>
 
  </td>
  <td valign="top">
  <table border=0 width="100%" cellspacing="10">
  <tr>
       <td align="left">
        <label><h3>People : </h3></label>   
</td>
<td align="right">
  <img src="img1/i1.jpg" />
</td>
  </tr>
 
  <tr>
  <td align="left" >
        <label><h3>Rooms : </h3></label>   
   
   </td>
<td align="right" >
<?php gen_options("single")?>
 </td>
  </tr>
  </table>
  </td>
  </tr>
 
 
 
  <tr>
  <td>
  <table border="0" style="float:left;table-layout:fixed" width="100%">
  <col width="55%">
  <tr>
  <td valign="top">
  <img src="img1/double.jpg" style="float:left" />

 </br>
<div id="featured">
<img src="img1/double1.jpg" />
  <img src="img1/double2.jpg" />
  <img src="img1/double3.jpg" />
  </div>
  </td>
  <td>
  <h3>Double</h3>
  <br>
    <span class="price">Price:</span> <span class="number">Rs. 3,000.00</span><br />
 <a>Double rooms in HOTEL BELLA has Double bed. And can accomodate 2 Adults and 2 kids. It is comfortable and pleasant, with balcony and sea view.
 We hope that you will enjoy your summer holidays in Bella.
 BASIC:
 Telephone.
Satellite TV.
Safety Deposit Box.
Mini Bar - Refrigerator.
Air condition.
Shower with or without cabin.
Hair Dryer.
Balcony.<a href="#">more...</a></p><br />
  </td>
  </tr>
  </table>
 
  </td>
  <td valign="top">
  <table border=0 width="100%" cellspacing="10">
  <tr>
       <td align="left">
        <label><h3>People : </h3></label>   
</td>
<td align="right">
  <img src="img1/i2.jpg" />
</td>
  </tr>
  <tr>
  <td align="left">
        <label><h3>Rooms : </h3></label>   
   
   </td>
<td align="right" >
<?php gen_options("double")?>
   
 </td>
  </tr>
  </table>
  </td>
  </tr>
 
 
 
 
  <tr>
  <td>
  <table border="0" style="float:left;table-layout:fixed" width="100%">
  <col width="55%">
  <tr>
  <td valign="top">
  <img src="img1/single.jpg" style="float:left" />

 </br>
<div id="featured">
<img src="img1/single1.jpg" />
  <img src="img1/single2.jpg" />
  <img src="img1/single3.jpg" />
  </div>
  </td>
  <td>
  <h3>Single</h3>
  <br>
    <span class="price">Price:</span> <span class="number">Rs. 2,000.00</span><br />
 <a> Single Room in HOTEL BELLA has single bed, bathroom.Can accomodated single person. is comfortable and pleasant, with balcony and sea view
 Telephone
Satellite TV
Safety Deposit Box
Mini Bar - Refrigerator
Air condition
Hair Dryer
<a href="#">more...</a></p><br />
  </td>
  </tr>
  </table>
 
  </td>
  <td valign="top">
  <table border=0 width="100%" cellspacing="10">
  <tr>
       <td align="left">
        <label><h3>People : </h3></label>   
</td>
<td align="right">
  <img src="img1/i3.jpg" />
 
</td>
  </tr>
  <tr>
  <td align="left">
        <label><h3>Rooms : </h3></label>   
   
   </td>
<td align="right" >
<?php gen_options("apartment")?>
   
 </td>
  </tr>
  </table>
  </td>
  </tr>
</table>
 
<div  style="margin-top:1200px;margin-left:5px;text-align:right;">
 <input type="image" src="img1/book.jpg" name="book" value="submit"/>
 </div>
 
 


 
 
  <?php
 

  function gen_options($type)
  {
  // print "$id";
  // print "$type";
$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("hotel", $con);

      $count=0;   
$result = mysql_query("SELECT * FROM rooms where type='$type'");

 while($row = mysql_fetch_array($result))
   {
  $a=$row['room_no'];
 //print "$a";
  // $query1 = mysql_query("SELECT count(*) FROM `room_inventory` WHERE room_no='$a' and status='active'");
 // $query=mysql_query("SELECT count(*) FROM `room_inventory` WHERE room_no='$a' and ((arrival<='2012-05-11' AND  departure>='2012-05-11')OR(arrival<='2012-05-13' AND  departure>='2012-05-13'))");
 
  //$query=mysql_query("SELECT count(*) FROM `room_inventory` WHERE room_no='$a'  and ((arrival BETWEEN '2012-05-11' AND '2012-05-13') or (departure BETWEEN '2012-05-11' AND '2012-05-13'))");
 
  //$query=mysql_query("SELECT count(*) FROM `room_inventory` WHERE room_no='$a' and ((arrival<='$arr' AND  departure>='$arr')OR(arrival<='$dep' AND  departure>='$dep'))");
  $quer=sprintf("SELECT count(*) FROM `room_inventory` WHERE room_no='$a' and ((arrival<='%s' AND  departure>='%s')OR(arrival<='%s' AND  departure>='%s'))",$arr,$arr,$dep,$dep);
 $query=mysql_query($quer);

//$query=mysql_query("SELECT count(*) FROM `room_inventory` WHERE room_no='$a'  and ((arrival BETWEEN '$arr' AND '2012-05-13') or (departure BETWEEN '$arr' AND '2012-05-13'))");

 $r=mysql_fetch_array($query);
 $v=$r['count(*)'];
// print "$v";
 
 if($v==0)
 {
  $count++;
    // print "$count";
 }
 
 }
 
  echo   '<select name="room1" class="ed" id="r1">';
  for($i=0;$i<=$count;$i++)
   {
     echo '<option>'.$i.'</option>';
    } 
  echo '</select>';
 
mysql_close($con);
}

 
// echo "$arrival\n";
// print "$departure\n";
 echo "$arr";
echo "$dep";
// echo date_format($arrival, 'Y-m-d');

?>




 <input type="hidden" name="result" id="result" />
</form>

</div>

<div class="clear"></div>
 
</div>

<!-- FOOTER -->

<div id="footer">



<p><a href="index.php">HOME</a> |<a href="about.php"> ABOUT US </a>|<a href="contact.php"> CONTACTS </a>|<a href="gallery.php"> GALLERY </a>|<a href="rates.php"> ROOM RATES </a></p>
</div>




</body>
</html>

With unixtimestamps?

I have let's say 20records, and like 5 of them are from January and 15 are in February, how would I go about showing all the records that were just in February (2012) or maybe 2013? (trick is using timestamps)



Pretty much sort the records by each Monthy, by using unixtimestamp as the value in the db field, possible?

I'm creating a line graph using ChartJs. I wanted to display my data according to the month selected by the user. For example, if the user select "2021-03" all the order that is made in the month of March will be plotted in the graph. It works well for the current month, but it does not work for the selected month. 

I use Ajax to get the data but i do not know why my graph does not shown when i click on my `loadchart();` button. Can anyone enlighten me how can i do it? Because i do not know what mistake did i made here. Any kind of explanation will be appreciated. Thanks!

 

This is the where i place my `loadchart()` button and also the javascript to plot the graph.

  <div class="col-md-12 form-group">
            <div class="card" style="box-shadow: 0 0 8px rgb(0,0,0,0.3)">
                <div class="row">
                   <div class="col col-lg-1"></div>
                  <div class="col-2 my-auto">
                    <input type="month" id="month" class="form-control" value="<?php echo date('Y-m'); ?>">
                  </div>

                   <div class="col-2 my-auto">
                    <button type="button" class="btn btn-info" onclick="loadchart();">
                      <i class="nc-icon nc-zoom-split"></i>
                    </button>
                  </div>
                </div>
                <div id="loadchart">
              <h6 class="text-muted text-center p-3"><i class="fa fa-line-chart"></i> Daily Gross Sales (Current Month)</h6>
              <div  class="card-body">
                <canvas id="attChart"></canvas>
              </div>
            </div>
            </div>
          </div>

        </div>
      </div>
      
    </div>
  </div>
  
  <!-- Chart JS -->
  <script src="./assets/js/plugins/chartjs.min.js"></script>
  <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
  <?php
  $days = array();
  $gross_sales = array();
  $average_sales = array();

  $type = CAL_GREGORIAN;
  $month = date('n'); // Month ID, 1 through to 12.
  $year = date('Y'); // Year in 4 digit 2009 format.
  $day_count = cal_days_in_month($type, $month, $year); // Get the amount of days

  //loop through all days
  for ($i = 1; $i <= $day_count; $i++) {
    $sql = "SELECT *, SUM(purchase_price) as purchase_price FROM ordered_items WHERE DAY(order_datetime) = '$i' AND MONTH(order_datetime) = MONTH(NOW()) AND YEAR(order_datetime) = YEAR(NOW()) AND order_status = 5 ";
    $query = $conn->query($sql);
    $total = $query->num_rows;
    $row = $query->fetch_assoc();

      
    if($row['purchase_price'] != 0)
    {      
      $gross_sales[] = $row['purchase_price'];
    }
      
    else if ($row['purchase_price'] == 0 && $i <= date('d')) {
      $gross_sales[] = 0;
    }

    $average_sales[] =  $row['purchase_price'] / $day_count;

    $day =  $i.'/'.$month; //format date
    array_push($days, $day);
  }

  $days = json_encode($days);
  $daily_gross_sales = json_encode($gross_sales);
  $average_gross_sales =  json_encode($average_sales);

?>
  <script>
    const colors = {
      colorcode: {
        fill: '#51cbce',
        stroke: '#51cbce',
      },
    
    };
    var ctx2 = document.getElementById("attChart").getContext("2d");
    const attChart = new Chart(ctx2, {
      type: 'line',
      data: {
        labels: <?php echo $days; ?>,
        
        //labels: ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'],
        datasets: [{
          label: "Gross Sales: RM",
          fill: true,
          //backgroundColor: colors.colorcode.fill,
          pointBackgroundColor: colors.colorcode.stroke,
          borderColor: colors.colorcode.stroke,
          pointHighlightStroke: colors.colorcode.stroke,
          borderCapStyle: 'dot',
          pointRadius: 5,
          pointHoverRadius: 5,
          pointStyle: 'dot',
          data: <?php echo $daily_gross_sales; ?>,
          showLine: true
        },
        {
          label: "Average Sales: RM",
          fill: true,
          //backgroundColor: colors.colorcode.fill,
          pointBackgroundColor:   '#FF0000',
          borderColor: ' #FF0000 ',
          pointHighlightStroke: colors.colorcode.stroke,
          borderCapStyle: 'dot',
          pointRadius: 5,
          pointHoverRadius: 5,
          pointStyle: 'dot',
          data: <?php echo $average_gross_sales; ?>,
          showLine: true
        }

        ]
      },

      options: {
        responsive: true,
        
        // Can't just just `stacked: true` like the docs say
        scales: {
          yAxes: [{
            stacked: false,
            gridLines: {
              display: true,
              color: "#dee2e6"
            },
            ticks: {
              stepSize: 100000,
              callback: function(tick) {
                return 'RM'+tick.toString();
              }
            }
          }],
          
          xAxes: [{
            stacked: false,
            gridLines: {
              display: false
            },
          }]
          
        },
        animation: {
          duration: 3000,
        },
        
        legend:{
          display: false,
        }
      }
    });
  </script>

This is my AJAX method 

   <script type="text/javascript">
    function loadchart()
    {
     $('#spin1').show();
     var month= $('#month').val();
     //alert(date);
      var data = {     // create object
        month: month,
        
      }
      $.ajax({
        method:"GET",
        data:data,
        url:"includes/loadchart.php",
        success:function(data)
        {
            $('#loadchart').html(data);
        }
      });

    }
  </script>

This is where i use to load the graph whenever a month is selected. (loadchart.php)

<?php
include '../session.php';
if(isset($_GET['month']))
{
	$months = $_GET['month'];
?>

 <h6 class="text-muted text-center p-3"><i class="fa fa-line-chart"></i> Daily Gross Sales (<?php echo $months ?>)</h6>
              <div id="loadchart" class="card-body">
                <canvas id="attChart"></canvas>
              </div>
            </div>
          </div>

        </div>
      </div>
      
    </div>
  </div>
  
  <!-- Chart JS -->
  <script src="./assets/js/plugins/chartjs.min.js"></script>
  <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>

  <?php
  $days = array();
  $gross_sales = array();
  $average_sales = array();

  $type = CAL_GREGORIAN; // Gregorian (localized) calendar
  $month = date('n'); // Month ID, 1 through to 12.
  $year = date('Y'); // Year in 4 digit 2009 format.
 
  $day_count = cal_days_in_month($type, $month, $year); // Get the amount of days

  //loop through all days
  for ($i = 1; $i <= $day_count; $i++) {
      $sql = "SELECT *, SUM(purchase_price) as purchase_price FROM ordered_items WHERE DAY(order_datetime) = '$i' AND MONTH(order_datetime) = MONTH('".$months."') AND YEAR(order_datetime) = YEAR('".$months."') AND order_status = 5 ";
    $query = $conn->query($sql);
    $total = $query->num_rows;
    $row = $query->fetch_assoc();

      
    if($row['purchase_price'] != 0)
    {      
      $gross_sales[] = $row['purchase_price'];
    }
      
    else if ($row['purchase_price'] == 0 && $i <= date('d')) {
      $gross_sales[] = 0;
    }

    $average_sales[] =  $row['purchase_price'] / $day_count;

    $day =  $i.'/'.$month; //format date
    array_push($days, $day);
  }

  $days = json_encode($days);
  $daily_gross_sales = json_encode($gross_sales);
  $average_gross_sales =  json_encode($average_sales);

?>
  <script>
    const colors = {
      colorcode: {
        fill: '#51cbce',
        stroke: '#51cbce',
      },
    
    };
    var ctx2 = document.getElementById("attChart").getContext("2d");
    const attChart = new Chart(ctx2, {
      type: 'line',
      data: {
        labels: <?php echo $days; ?>,
        
        //labels: ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'],
        datasets: [{
          label: "Gross Sales: RM",
          fill: true,
          //backgroundColor: colors.colorcode.fill,
          pointBackgroundColor: colors.colorcode.stroke,
          borderColor: colors.colorcode.stroke,
          pointHighlightStroke: colors.colorcode.stroke,
          borderCapStyle: 'dot',
          pointRadius: 5,
          pointHoverRadius: 5,
          pointStyle: 'dot',
          data: <?php echo $daily_gross_sales; ?>,
          showLine: true
        },
        {
          label: "Average Sales: RM",
          fill: true,
          //backgroundColor: colors.colorcode.fill,
          pointBackgroundColor:   '#FF0000',
          borderColor: ' #FF0000 ',
          pointHighlightStroke: '#FF0000',
          borderCapStyle: 'dot',
          pointRadius: 5,
          pointHoverRadius: 5,
          pointStyle: 'dot',
          data: <?php echo $average_gross_sales; ?>,
          showLine: true
        }

        ]
      },

      options: {
        responsive: true,
        
        // Can't just just `stacked: true` like the docs say
        scales: {
          yAxes: [{
            stacked: false,
            gridLines: {
              display: true,
              color: "#dee2e6"
            },
            ticks: {
              stepSize: 100000,
              callback: function(tick) {
                return 'RM'+tick.toString();
              }
            }
          }],
          
          xAxes: [{
            stacked: false,
            gridLines: {
              display: false
            },
          }]
          
        },
        animation: {
          duration: 3000,
        },
        
        legend:{
          display: false,
        }
      }
    });
  </script>
  
<?php	
}
?>

 

Edited April 27 by sashavalentina