3

I want to show the number of sales/buys a sales person has done. I have a search box which when I type in their name it should bring back a number . So to count total buys I am using this code:

<?php

mysql_connect ("****", "****","****")  or die (mysql_error());
mysql_select_db ("*******");

 $term = $_POST['term'];


$sql = mysql_query("select count(*) from car_orders where side='buy' and sales_id like '%$term%'");


while ($row = mysql_fetch_array($sql)){
 echo 'owner_id: '.$row['sales_id'];
echo '<br/> side '.$row['side'];
echo '<br/><br/>';
 }
?>

I have tried finding out how to just get the count figure to show on the page and have failed. Please could somebody help

John Woo
  • 258,903
  • 69
  • 498
  • 492
Sulexk
  • 61
  • 8
  • Your query does not include either the `sales_id` or the `side` columns, but you are attempting to fetch them – Michael Berkowski Oct 29 '12 at 15:35
  • You're not fetching either sales_id or side in your query; and you don't seem to be doing anything with the count(*) you are fetching. – andrewsi Oct 29 '12 at 15:35
  • 1
    Please note that thwe `mysql_xx()` functions are considered obsolete. PHP is in the process of deprecating them, and the PHP manual has big red boxes on every relevent page suggesting using the alternatives (`mysqli_xx()` or the PDO library). For more info see here: http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-function-in-php – SDC Oct 29 '12 at 16:25

2 Answers2

2

you need to add ALIAS

select count(*) AS totalCOUNT from car_orders where ....

and you can now get the value using that

$row['totalCOUNT']

but seeing on your query, neither sales_id nor side were projected. The query below is only an assumption

select `sales_id`,`side`, count(*) AS totalCOUNT
from car_orders 
where side='buy' and 
      sales_id like '%$term%'
GROUP BY `sales_id`,`side`

and now you can fetch all values,

$row['sales_id']
$row['side']
$row['totalCOUNT']

and that query is vulnerable with SQL Injection. Please take time to read the article below how to protect from it,

Community
  • 1
  • 1
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • Is there anyway of seeing the total count for buy and sell? – Sulexk Oct 29 '12 at 16:16
  • Yh the query did work I now can see the total buys done by a specific sales person. But is it possible to see total buys and total sells? – Sulexk Oct 29 '12 at 16:21
  • ah you mean the grand total. where will be the record do you want to be shown? – John Woo Oct 29 '12 at 16:24
  • no not necessarily a total. At present it shows sales persons name: side buy and the number of buys. I also want to add the number of sells? is it possible to append to the sql query to cater for this? – Sulexk Oct 29 '12 at 16:27
  • yes, but i don't know the structure of your table. :( can you post the structure, sample record, and desired output? :D – John Woo Oct 29 '12 at 16:28
  • no that won't be possible. But the sell and buy are in the same column headed side. The desired output is what you just provided but it would be awesome if I could also get a count for total sells – Sulexk Oct 29 '12 at 16:31
  • Another day, another working SQL injection hole. This desperately needs a call to `mysql_real_escape_string`. – tadman Oct 29 '12 at 16:33
  • @user1783326 is this what you want? `select sales_id,side, SUM(CASE WHEN side= 'buy' THEN 1 ELSE 0 END) AS totalbuy, SUM(CASE WHEN side= 'sell' THEN 1 ELSE 0 END) AS totalsell from car_orders where side='buy' and sales_id like '%$term%' GROUP BY sales_id,side` – John Woo Oct 29 '12 at 16:39
  • Thanks for your effort. but the sell query is returning a value of 0. I think the problem lies in the "where side='buy'" how do I concatenate this with side='sell' – Sulexk Oct 29 '12 at 16:56
  • oops try this, `select sales_id, SUM(CASE WHEN side= 'buy' THEN 1 ELSE 0 END) AS totalbuy, SUM(CASE WHEN side= 'sell' THEN 1 ELSE 0 END) AS totalsell from car_orders where side='buy' and sales_id like '%$term%' GROUP BY sales_id` – John Woo Oct 29 '12 at 16:57
  • nope that didnt do it. The strange thing is in the where side='buy' if i change this to where side='sell' I see the sells and the buy returns 0. – Sulexk Oct 29 '12 at 17:03
  • sorry for the query, can you remove the that condition so you arrive on this `select sales_id, SUM(CASE WHEN side= 'buy' THEN 1 ELSE 0 END) AS totalbuy, SUM(CASE WHEN side= 'sell' THEN 1 ELSE 0 END) AS totalsell from car_orders where sales_id like '%$term%' GROUP BY sales_id` – John Woo Oct 29 '12 at 17:05
  • 1
    thanks John I really appreciate that. Thanks really for persevering with me. – Sulexk Oct 29 '12 at 17:12
2
  1. You're only selecting the COUNT(*) column and nothing else.
  2. You're not using mysql_fetch_assoc(), so it's not an associative array.
  3. The mysql_ functions are deprecated, use mysqli or PDO.
Sammitch
  • 30,782
  • 7
  • 50
  • 77