How to use dynamic variable in function with query in PHP and MySQL

Question

I would like to use a query on a function dynamically. I would like to use the same function multiple times. How do I call this function based on the $type variable?

This what I attempted. I know it is not complete, but I don't know what I have to do to get it to work.

function GeneralQuery($connMysqli){

  // Query mounting according condition of $type
if(empty($type)){
  if($type = "")
  {
    if($type == "school")
    {
      $type = 'GROUP BY school
      ORDER BY school';
    }
    else if($type == "class")
    {
      $type = 'GROUP BY class
      ORDER BY class';
    }
    else
    {
      $type == "student";
      $type = 'GROUP BY class, student
      ORDER BY class, avg DESC';
    }
  }
}


   $sql = "SELECT avg(finalgrade) as avg, u.lastname as school, u.department as class, u.id as student from fhrw_grade_grades gg
inner join fhrw_user u on u.id=gg.userid
where gg.rawgrade is not null " . $type . "";

   $res = mysqli_query($connMysqli,$sql)or die(mysqli_error($connMysqli));
   $list = array();
   while($item = mysqli_fetch_assoc($res)){
      $list[] = $item;
   }

  return $list;
  }

// I don't know what to do here to call query result for School -  
$data = GeneralQuery($connMysqli);
foreach($data as $item){
   echo $item['avg'] . ' - ' . $item['School'] . '<br>';
}

// I don't know what to do here to call query result for Class
$data = GeneralQuery($connMysqli);
foreach($data as $item){
   echo $item['avg'] . ' - ' . $item['class'] . '<br>';
}

// I don't know what to do here to call query result for Student
$data = GeneralQuery($connMysqli);
foreach($data as $item){
   echo $item['avg'] . ' - ' . $item['Student'] . '<br>';
}

// close connection
mysqli_close($connMysqli);

`if($type = "")` this is always going to compute to `true` because you are using assignment, not equality-checking — Patrick Q, Nov 18 '19 at 19:44
`$type == "student";` This is likely doing nothing at all, as it is an equality check outside of an `if` conditional. — Patrick Q, Nov 18 '19 at 19:48
@YourCommonSense I am using variable $type to chage query according condictions, then call them. — Miguel Silva, Nov 18 '19 at 19:52
@MiguelSilva _What_ was a mistake? I've pointed out a number of errors in your code. — Patrick Q, Nov 18 '19 at 19:58
@PatrickQ, "Your queries don't have a `FROM` clause anywhere" rsrs — Miguel Silva, Nov 18 '19 at 20:01
why do you want this function at all? What problem it is intended to solve? — Your Common Sense, Nov 18 '19 at 20:05
Where is `$type` being defined? Is your table actually called "table"? Because that's what `from table ` implies. — Patrick Q, Nov 18 '19 at 20:08
`$item['avg']` You don't have an `avg` column selected in your query. Same with `$item['School']` and `$item['Student']`. Honestly, I think you need to go back to the drawing board and spend some time mapping out exactly what the logic is that you want. — Patrick Q, Nov 18 '19 at 20:11
`mysqli_connect_errno()` is to check if the connection failed, not the query, so you're using the wrong function. It should be `mysqli_error($connMysqli)`. — Funk Forty Niner, Nov 18 '19 at 20:21

Your Common Sense · Answer 1 · 2019-11-19T12:50:41.597

Now, when your intention is clear, here is your function refactored

function GeneralQuery($connMysqli)
{
    $sql = "SELECT avg(finalgrade) as avg, u.lastname as school, 
            u.department as class, u.id as student 
            FROM fhrw_grade_grades gg
            INNDER JOIN fhrw_user u on u.id=gg.userid
            WHERE gg.rawgrade is not null ";

    // Query mounting according condition of $type
    if($type == "school")
    {
        $sql .= 'GROUP BY school ORDER BY school';
    }
    else if($type == "class")
    {
        $sql .= 'GROUP BY class ORDER BY class';
    }
    else if($type == "student")
    {
        $sql .= 'GROUP BY class, student ORDER BY class, avg DESC';
    } 
    else 
    {
        trigger_error("Wrong query type");
    } 

    $res = mysqli_query($connMysqli,$sql);
    return mysqli_fetch_all($res);
}

All you had to do it to add $type as a function parameter and then append the query with different endings.

Then you can call your function with different types

$data = GeneralQuery($connMysqli, 'student');

score -1 · Accepted Answer · edited Nov 19 '19 at 17:15

-1

How about using switch structure?

<?php

function GeneralQuery($connMysqli, string $type = null)
{
switch($type) {
    case "school": 
        $sqlQuery = 'GROUP BY school
        ORDER BY school';

        break;


    case "class": 
        $sqlQuery = 'GROUP BY school, class
        ORDER BY class';

        break;


    case "student": 
        $sqlQuery = 'GROUP BY school, class, student
        ORDER BY class, avg DESC';

        break;


    default: 

        //your default case when
        //none of above mathes
        //for exampe no $type was passed
        //to the function
        $sqlQuery = 'GROUP BY class, u.id
        ORDER BY class, avg DESC';

        break;


}



 $sql = "SELECT avg(finalgrade) as avg, u.lastname as school, u.department as class, u.id as student from fhrw_grade_grades gg
    inner join fhrw_user u on u.id=gg.userid
    where gg.rawgrade is not null " . $sqlQuery . "";

    $res = mysqli_query($connMysqli,$sql)or die(mysqli_error($connMysqli));
    $list = array();
    while($item = mysqli_fetch_assoc($res)){
        $list[] = $item;
    }

    return $list;
}

$type = 'school';
$data = GeneralQuery($connMysqli, $type);
?><h4>AVG by School</h4><?
foreach($data as $item){
 echo round($item['avg'],2) . ' - ' . $item['school'] . '<br>';
}

$type = 'class';
$data = GeneralQuery($connMysqli, $type);
?><h4>AVG by Class</h4><?
foreach($data as $item){
 echo round($item['avg'],2) . ' - ' . $item['class'] . '<br>';
}

$type = 'student';
$data = GeneralQuery($connMysqli, $type);
?><h4>AVG by Student</h4><?
foreach($data as $item){
 echo round($item['avg'],2) . ' - ' . $item['student'] . '<br>';
}


// close connection
mysqli_close($connMysqli);

Note a must read about SQL Injection

Note there is no FROM in the SELECT query, the syntax for SELECT statemet or here

edited Nov 19 '19 at 17:15

Miguel Silva

147
6

answered Nov 18 '19 at 19:56

Jimmix

5,644
6
44
71

1

this question is not about a control structure. your function is as unusable as the original function – Your Common Sense Nov 18 '19 at 20:03
I'll try it. I would like to know if this method above is more eficientilly performance than to have three separeted queries? – Miguel Silva Nov 18 '19 at 20:03
@YourCommonSense I disagree, Have you read the comments in the code? Perhaps this one `I don't know what to do here to call query result for Student` ? – Jimmix Nov 18 '19 at 20:06
The original function makes no sense at all. Adding a switch operator to it doesn't make it any better. It wouldn't return any sensible result either. – Your Common Sense Nov 18 '19 at 20:08
1

@YourCommonSense Let OP give their time since `I know it is not complete` and the question objective is: `I would like to use a query on a function dinamically.` not "I would like to make my SELECT statement working since it does not work at all". Useful comments ware provided, plus switch control and how to use function having it. I see no other issues. – Jimmix Nov 18 '19 at 20:14
1

@MiguelSilva My very subjective opinion about performance is: don't worry about it yet. Check your SELECT queries. Probably the best way would be to use phpMyAdmin (or MySQL cli) to build single queries and check if they are working as expected. Once you're sure they are good implement them in PHP as single ones - without any function at all. Then check the notes about MySQL injection and rewrite them so they are secure. Once that done, use the proposed function with switch and make queries dynamic. Test and if you are not satisfied with the performance ask another question that targets that. – Jimmix Nov 18 '19 at 20:31
@Jimmix, I edited a little bit your code to work and now it's ok, can I edit your answer with what I did? – Miguel Silva Nov 19 '19 at 12:30
I don't know why this answer was downvote, it is working – Miguel Silva Nov 19 '19 at 12:40

How to use dynamic variable in function with query in PHP and MySQL

2 Answers2