SET statement prepared mysqli

Question

SET statement prepared mysqli

Navigation

#1 by (1 votes)
#2 by (1 votes)

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:=@serie+1) AS div_
            FROM ab_route, ab_mandante
            WHERE ab_route.id_mandante = ab_mandante.id and ab_route.id_mandante = 1");

php mysqli

asked by Veronica 09.02.2016 в 14:14

source

2 answers

Specify Bundler versions in the Gemfile Show Selected text Select an option

score 1 · Answer 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:=@a+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!

score 1 · Answer 2

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:=@serie+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.