CODEWITHSUNDEEP
phpmysqlsqldynamic-sql
adib.mosharrof
adib.mosharrof

Reputation: 1634

Group by week,month in mysql depending on php variable. If condition in group by

I have a table, prescription, which the fields

I know how to group by month, week or day. My question is, i want this to be dynamic. I have an interface in the UI where the user will select a category, month, day or week. Which i store in a variable $category. Is there any way to use an if condition in group by to see which category the user has selected, and return the sql query in that format.

For example, if the user selects month, in my sql query, i want it to return, group by month(pres_date), if the user selects day, i want sql to return group by day(pres_date).

Upvotes: 0

Views: 1174

Answers (5)

Mudassir Hasan
Mudassir Hasan

Reputation: 28751

No need of multiple condition checking , get the selected option in variable and use it in sql query,

if(isset($_POST['selected_option']))
    $grp_option=$_POST['selected_option'];  
$sql = "SELECT .... GROUP BY".$grp_option."(pres_date)";

Upvotes: 0

zongweil
zongweil

Reputation: 2051

You don't even need to use conditional logic. Just do something like this:

$sql = "SELECT * FROM prescription GROUP BY " . $category . "(pres_date)";

This way, your query gets built using one line. Of course, make sure you validate the value of $category before you run the query.

Upvotes: 1

Yogesh Suthar
Yogesh Suthar

Reputation: 30488

The code will be like this

if($_POST['select'] == 'month')
{
    $sql = "select * from table group by month(pres_date)";
}
else if($_POST['select'] == 'day')
{
    $sql = "select * from table group by day(pres_date)";
}
else
{
    $sql = "select * from table group by week(pres_date)";
}

Upvotes: 1

Aaron Hathaway
Aaron Hathaway

Reputation: 4315

It sounds like you've already got yourself going on the right path. You should be able to build the base form of the query and then modify it depending on the value that they have selected. Try doing something in that fashion where you add group by month(pres_date) if they select month, etc.

Upvotes: 0

cegfault
cegfault

Reputation: 6632

Simplest way to do this is in PHP, using if or switch statements:

$sql = "SELECT ... FROM ... ";
switch($category) {
    case 'month':
        $sql .= "GROUP BY month(pres_date)";
        break;
    case 'day':
        $sql .= "GROUP BY day(pres_date)";
        break;
    //... etc ...
    default:
        //bad category value; do some error handling
}

// now do $mysqli->query($sql) or $PDO->query($sql)

Upvotes: 0

Related Questions