SET statement prepared mysqli

2

What is the correct way to perform the following query?

I have a function to which I want to assign a series, if I execute it directly in the engine it generates the div concatenated, but in the PHP it throws an error. If I declare it and assign it to a variable it throws a NULL .

$stmt = $mysqli->prepare("SET @serie=0; 
            SELECT DISTINCT id_semana, 
            CONCAT('Semana ', id_semana, ' - ', tramo) AS semana, 
            CONCAT('div_', @serie:[email protected]+1) AS div_
            FROM ab_route, ab_mandante
            WHERE ab_route.id_mandante = ab_mandante.id and ab_route.id_mandante = 1");
    
asked by Veronica 09.02.2016 в 14:14
source

2 answers

1

Make a modification in the function, the declaration of the set is outside the select in this way I managed to recover the complete array. Staying this way:

function fetchRoute_chart($idmandante)
{
  global $mysqli,$db_table_prefix; 
  $stmt = $mysqli->prepare("SET @a = 0;");
  $stmt->execute();
  $stmt = $mysqli->prepare("SELECT DISTINCT id_semana, 
            tramo,
            CONCAT('Semana ', id_semana, ' - ', tramo) AS semana, 
            CONCAT('chart_div_', @a:[email protected]+1) AS chart_div
            FROM ".$db_table_prefix."route, ".$db_table_prefix."mandante
            WHERE ab_route.id_mandante = ab_mandante.id and ab_route.id_mandante = ?
            ");

  $stmt->bind_param("i", $idmandante);
  $stmt->execute();
  $stmt->bind_result($id_semana, $tramo, $semana, $chart_div);
  while ($stmt->fetch()){
    $row[] = array('id_semana'=>$id_semana, 'tramo' => $tramo, 'semana' => $semana, 'chart_div' => $chart_div);
  }
  $stmt->close();
  return ($row);
}

It works for what I need!

    
answered by 09.02.2016 в 16:04
1

What you are looking for is to execute multiple queries to the database. To achieve that you can use mysqli->multi_query . But consider the note at the end:

  

Prepared sentences

     

The use of multiple statements with prepared statements is not supported.

Therefore you will not be able to execute this query using mysqli->prepare .

Apart from this solution, I propose two more:

  • Based on this tutorial to get the number row, you can change your query to look like this:

    SELECT DISTINCT id_semana, 
    CONCAT('Semana ', id_semana, ' - ', tramo) AS semana, 
    CONCAT('div_', @serie:[email protected]+1) AS div_,
    (SELECT @serie:=0) AS no_me_selecciones
    FROM ab_route, ab_mandante
    WHERE ab_route.id_mandante = ab_mandante.id and ab_route.id_mandante = ?
    

    And so you only execute 1 query.

  • What you're looking for is that it's not appropriate for it to run directly in the database. Since you are going to iterate over the results of the query, you can declare a variable $serie at PHP level that is the counter and achieve the result right there.

  • answered by 09.02.2016 в 15:36