Dynamic select query in mysql?

Question

I am using mysql as my database and php as server side language. As we know that we can select data from database using select query. Below example is important!!

select * from table
select name from table
select name,salary from table where salary > 10000 etc..........

now, for different select query of a table we need different select method. because every time select * is not good because it takes a huge time.

Now, My Question is how write dynamic single get method of a single table by which we can achieve our requirement (shown in example...)?

I will pass the array of parameters in the argument of the function.. for ex. in php

public get($arr)
{
  //code goes here
}

I want to fetch the $arr and want to change the sql dynamically..

Don't want any join query just simple select as shown in above..

Sgt AJ · Accepted Answer · 2016-06-28 04:25:29Z

3

Depending on how you want to do it, you can do something like this:

public get($arrfield, $arrtable, $arrwhere)
{
    $str = "SELECT " . $arrfield . " FROM " . $arrtable . " WHERE " . $arrwhere;

    return $str;

   // You can return the query string or run the query and return the results
}

answered Jun 28, 2016 at 4:25

Sgt AJ

8636 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sgt AJ Over a year ago

Granted. To meet the OP's requirement, I made it as generic as possible. With more specific cases, a function could be written to use prepared statements and bound variables.

Your Common Sense · Accepted Answer · 2016-06-28 07:05:05Z

Trust me, to write all three queries is not that too hard a job that have to be avoided at any cost.

Please, do not obfuscate a precious SQL language into unreadable gibberish. Not to mention innumerable security breaches of your approach.

What you should think of is a function that lets you to use parameters. Thus, better make our function like this

function mysqli($mysqli, $query, $params, $types = NULL)
{
    $statement = $mysqli->prepare($select);
    $types = $types ?: str_repeat('s', count($params));
    $statement->bind_param($types, ...$params);
    $statement->execute();
    return $statement;
}

and run your every query as is, only providing placeholders instead of variables

select * from table:
you'll never need a query like this

select name from table

$names = mysqli($db, "select name from table")->get_result->fetch_all();

`select name,salary from table:

$stmt = mysqli($db, "select name from table where salary > ?", [10000]);
$names = $stmt->get_result->fetch_all();

See - the query itself is the least code portion. Your concern should be not SQL but useless reprtitive parts of PHP code used to run a query and to fetch the data.

Dipanwita Kundu · Accepted Answer · 2016-06-28 05:16:44Z

1

Here is the structure of the dynamic query.Please add required validation.You can add 'Or' clause also.On the basis of parameter or data type you can do it. Like

public SelectTable($arrfield, $table, $arrwhere, $arrgroup)
{
    if(!empty($arrfield))
    {
     $fields = implode('`,`',$arrfield);
    }
    else
    {
       $fields = '*';
    }

    if(!empty($arrwhere))
    {
        foreach($arrwhere as $fieldName=>$fieldValue)
        {
            if(is_array($fieldValue))
            {
                 $cond .= "`fieldName` in (". implode(',',$fieldValue) );
            }
            else
               $cond .= "`fieldName` = '" . addslashes($fieldValue)."'";
        }
    }
    else
    {
        $cond = '1';
    }
    if(!empty($arrgroup))
    { 
        $groupBy .= " group by ";
        foreach($arrgroup as $field=>$value)
        {
           $groupBy .= $field . " " . $vale;
        } 
    }
}
 $str = "SELECT " . $fields . " FROM " . $table . " WHERE " . $cond . $groupBy;

    return $str;

   // You can return the query string or run the query and return the results
}

answered Jun 28, 2016 at 5:16

Dipanwita Kundu

1,6451 gold badge10 silver badges14 bronze badges

4 Comments

Dipanwita Kundu Over a year ago

I have already mentioned to implement necessary validation & this is only a idea of how we create the function.

Your Common Sense Over a year ago

Just telling is no use.

Dipanwita Kundu Over a year ago

We are here not to give tailor made script, but only for share ideas

Your Common Sense Over a year ago

You are mistaken here. SO is not a forum to "share ideas". We are here to answer questions. If you are unable to provide a proper answer, then don't answer at all.

Collectives™ on Stack Overflow

Dynamic select query in mysql?

3 Answers 3

1 Comment

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related