CODEWITHSUNDEEP
phpmysqlwhere-clause
Hector
Hector

Reputation: 682

WHERE Clause syntax

What's up with my sysntax, I can't get it right. Is there a better way?

<?
            $uid = $_GET["uid"];
            $month= $_GET["month"];
            $year = $_GET["year"];
            $query = "SELECT taskname,uid,month,year, SUM(tasktime) FROM tictoc WHERE uid = $uid, month = $month, year = $year GROUP BY taskname"; 

            $result = mysql_query($query) or die(mysql_error());

            // Print out result
            while($row = mysql_fetch_array($result)){
                echo "<br />";
                echo "Total ". $row['taskname']. " = <strong>". $row['SUM(tasktime)']."</strong>";
                echo "<br />";
            }
            ?>

Error is:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' month = 'Aug', year = '2012' GROUP BY taskname' at line 1

Upvotes: 1

Views: 132

Answers (2)

Hernan Velasquez
Hernan Velasquez

Reputation: 2820

To complement the previous answer, check that the parameters are not arriving empty. For example:

 $uid = $_GET["uid"] = $_GET["uid"] == "" ? 0 : $_GET["uid"];
 $month = $_GET["month"] = $_GET["month"] == "" ? 0 : $_GET["month"];
 $year = $_GET["year"] = $_GET["year"] == "" ? 0 : $_GET["year"];

Or choose your checking strategy.

The reason is if they are arriving empty and you don't validate it, you will build your SQL statement incorrectly and you will have the error you are reporting.

Upvotes: 1

D&#39;Arcy Rittich
D&#39;Arcy Rittich

Reputation: 171351

You need to use AND, not a comma, like this:

select taskname, uid, month, year, SUM(tasktime)
from tictoc
where uid = $uid
    and month = $month
    and year = $year
group by taskname

Also, note the line:

echo "Total ". $row['taskname']. " = <strong>". $row['SUM(tasktime)']."</strong>";

will not work. You need to alias the sum in your query and reference that alias, like this:

SELECT taskname,uid,month,year, SUM(tasktime) as SumTaskTime

and then do:

echo "Total ". $row['taskname']. " = <strong>". $row['SumTaskTime)']."</strong>";

Upvotes: 9

Related Questions