Jump to content

getting count of ids


mythri
 Share

Go to solution Solved by mac_gyver,

Recommended Posts

Hello,

 

I am trying to get the report of my sales table. I want to get total number of leads for each month. And then i have to use this data to create graph using google graph. 

 

But i am not getting how eactly i can get this.

 

Here is my code

 <?php 
if(isset($_POST['submit']))
{
	$type = $_POST['type'];
	


$sql="select * from leads where lead_customer='".$type."'";



$query=mysql_query($sql);

while ($row = mysql_fetch_array($query))
{
	
list($year,$month,$day)=explode("-", $row['last_modified']);
$l = $row['last_modified'];
$count=mysql_num_rows($month);
echo $count;


$myurl[] = "['".$month."', ".$count."]";
}

 print_r($myurl);
 echo implode(",", $myurl);
}


?>

But for $count, it doesn't show any values. below is my database.

 

post-168283-0-58440200-1410185373_thumb.png

 

Please suggest me

Link to comment
Share on other sites

There are a lot of problems with that code, the least of which is not getting the 'count'.

 

1. Don't use the mysql_ functions - they have been deprecated a long time. Use either mysqli_ or PDO

2. YOu are completely open to SQL injection

3. No need to check for $_POST['submit']. You are only interested in the 'type' so just check for that. Or, where there are multiple values being submitted, you should check the request method.

4. You're not checking for errors

 

As to your specific request, you are defining $month from the exploded string of the last modified date, and then you define count from the mysql_num_rows() of $month. That makes absolutely no sense. mysql_num_rows() is used to count the number of rows of a database result. If you don't want the data and only need a cont - then query just the count and not the data. I think what you really want is the data count broken out by year and month.

 

Start with this:

 

<?php 
 
if(isset($_POST['type']))
{
    $type = mysql_real_escape_string($_POST['type']);
    $query = "SELECT YEAR(last_modified) as year, MONTH(last_modified) as month, COUNT(id) as count
              FROM leads
              WHERE lead_customer ='{$type}'
              GROUP BY year, month
              ORDER BY year, month";
    $result = mysql_query($query);
    while ($row = mysql_fetch_array($result))
    {
        echo "Year: {$row['year']}, Month: {$row['month']}, Count: {$row['count']}<br>\n";
    }
}
 
?>
  • Like 1
Link to comment
Share on other sites

Actually, i have tried like this. and now am able to display the chart also but i want to get the graph for each month with complete total for that month. But here its repeating. Instead of 1 bar, its showing 3 for each amount not the total. And instead of 9 i have to get 'September'

 

Here is my code

if($_POST['whatt'] == 'Order')
{
 $type = mysql_real_escape_string($_POST['whatt']);
    $query = "SELECT YEAR(last_modified) as year, MONTH(last_modified) as month, orders.order_id, orders.company_id, order_line_items.order_id, order_line_items.item, order_line_items.unit, (order_line_items.unit_cost * order_line_items.quantity) AS 'Total', order_line_items.tax from orders INNER JOIN order_line_items ON orders.order_id = order_line_items.order_id where orders.order_quote = '".$type."' ";
             
			 // echo $query;
    $result = mysql_query($query);
	$tota = 0;
    while ($row = mysql_fetch_array($result))
    {
       // echo "Year: {$row['year']}, Month: {$row['month']}, Count: {$row['count']}<br>\n";
		
		$tota += $row['Total'];
		$myurl[] = "['".$row['month']."', ".$row['Total']."]";
		
		
		
    }
	
	

echo $tota;



// print_r($myurl);
//echo implode(",", $myurl);

?>

 <script type="text/javascript">
      google.load("visualization", "1", {packages:["corechart"]});
      google.setOnLoadCallback(drawChart);
      function drawChart() {
		  
        var data = google.visualization.arrayToDataTable([
          ['Month', 'Orders'],
         /* ['2004',  1000,      400],
          ['2005',  1170,      460],
          ['2006',  660,       1120],
          ['2007',  1030,      540]*/
		 
		 
            <?php echo implode(",", $myurl); 
			
			?>
			
        ]);

        var options = {
          title: 'Orders Graph',
          vAxis: {title: 'Month',  titleTextStyle: {color: 'red'}}
        };

        var chart = new google.visualization.BarChart(document.getElementById('chart_div'));
        chart.draw(data, options);
      }
    </script>
    <?php	
}
}
?>



<div id="chart_div" style="width: 900px; height: 500px;"></div>

and the graph i am getting 

 

post-168283-0-70719200-1410243996_thumb.png

 

 

 

Link to comment
Share on other sites

  • Solution

the code is doing exactly what it was written to do. if you want it to produce different specific data, you will have to write the query and code that does what you want.

if you want to graph one bar for each/any month, with the total for that month, you will have to produce data that does that. [month name,total for that month],[another month name, total for that month], ...

you should do this in the query, like the example that Psycho posted (modified for a total and $type you are actually doing), which if you are going to do this in general for any range of dates, would be the best place.

to get the month name, you would either convert the 9 to the name in your php code or you can use the mysql MONTHNAME() function in the query.

 

edit: in addition to what others have mentioned, i recommend that you use table alias names in your query to reduce the clutter and make it easier to read and easier to write in the first place.

Link to comment
Share on other sites

This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.