Combining search and sum statement

Question

im new in php and sql im trying to select a username and count his total column name and display it. im kinda confuse how to do it. please help me out guys. thank you very much.

<?php
if( isset($_POST['query']) ){

$query = $_POST['query'];

$raw_results = mysql_query(
    "SELECT * 
     FROM inventory 
     WHERE 
       (`serialproductkey` LIKE '%".$query."%') OR 
       (`employeename` LIKE '%".$query."%')"
) or die(mysql_error());

if( mysql_num_rows($raw_results) > 0 ){

// and this code that i need to combine with the search part
$tae = "SELECT SUM(total) AS TotalItemsOrdered FROM inventory";

Please avoid using mysql_* extension cause they are deprecated and instead, use mysqli or PDO extensions. In my opinion. http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php — ilpaijin, Jul 12 '14 at 15:49

Simone Nigro · Answer 1 · 2014-07-12T16:07:23.447

mysql_* function are deprecated. Use mysqli_* or pdo, read more
'%".$query."%' is dangerous ... risk SQL_injection
Performance Tips: seeks to limit the number of request to the database

A possible solution to your problem is

$raw_results = mysql_query(
    "SELECT employeename, SUM(total) AS TotalItemsOrdered
    FROM inventory
    WHERE 
        (serialproductkey LIKE '%".mysql_real_escape_string($query)."%') OR 
        (employeename LIKE '%".mysql_real_escape_string($query)."%')
    GROUP BY employeename"
) or die(mysql_error());

online demo

score 0 · Answer 2 · edited Jul 12 '14 at 15:50

0

SELECT SUM(total) AS TotalItemsOrdered
FROM inventory
WHERE (`serialproductkey` LIKE '%".$query."%') OR
      (`employeename` LIKE '%".$query."%')

edited Jul 12 '14 at 15:50

Gordon Linoff

1,242,037
58
646
786

answered Jul 12 '14 at 15:42

Grondag

102
2

1

@Grondag user request `select a username and count his total` you only `count his total` – Simone Nigro Jul 12 '14 at 16:05
Hmmm, well I'm assuming 'employeename' is the username in this case, and I do include it in the selection criteria. I do see that you have in point in that using LIKE does not guarantee that only a single employee will be found, if any. Depending on the need, one could use = instead of LIKE for the comparison to employee name, or handle multiple rows on the return according to whatever logic makes sense. – Grondag Jul 12 '14 at 16:16

Combining search and sum statement

2 Answers2