getting count of ids

[mythri]

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.

 

 

Please suggest me

[Psycho]

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";
    }
}
 
?>

[mythri]

Thank you very much for analyzing and suggesting on my code. And your solution really solved my problem. Thank a ton for that. But my data is not getting displayed as barChart. I am using googlechart. Do i need to rise a new topic for that?

[jcbones]

Show us what you have tried, and we can guide you from there.  We cannot and will not design and build anything for you in the help section.   You would need to go to freelance for that.

[mythri]

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 

 

 

 

 

[mac_gyver]

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.

[mythri]

Yeah, I modified my code and it did work. Thank you

[jcbones]

Well, I'll mark it solved on the last relevant post then.