Sentences prepared in PHP

Question

Sentences prepared in PHP

Navigation

#1 by (1 votes)
#2 by (0 votes)
#3 by (0 votes)

0

I have a query in SQL , but I do not know how to do it or pass it in routine PHP , since I must make that report.

In MySQL it works perfect, but how to do a routine in PHP for this query :

SET @sql = NULL ;
SELECT GROUP_CONCAT( DISTINCT CONCAT( 'sum(CASE WHEN fecha = ''', fecha, ''' THEN asistencia else '''' END) AS '', fecha, ''' ) )
INTO @sql
FROM asistencia;
iSET @sql = CONCAT( 'SELECT s.dui, nombres,apellidos, codigo,nombre,depto,muni,', @sql , '
        from docentes1 c
        inner join asistencia s
          on c.dui = s.dui 
       INNER JOIN centros e
       ON c.centro = e.codigo
       AND c.sede=11117
       AND s.asistencia=1
       group by s.dui' );
PREPARE stmt FROM @sql;
EXECUTE stmt;

Thank you.

mysql php phpmyadmin

asked by Marlon Orozco 17.11.2016 в 17:32

source

3 answers

Language C on Linux: chdir returns value -1 always Overwrite responsive.css in a child theme or child theme in wordpress?

score 1 · Answer 1

I start from the understanding that you understand something about PHP and not just about MySQL.

Let's see, a priori you should use a PDO connection: link

So you can use PREPARED: link

I say this so that you can keep the same execution, mount the SQL, add the values, "prepare" it and execute it.

Any questions, tell me ^ _ ^

score 0 · Answer 2

I recommend that you generate that routine in a storeprocedure.

link

then you simply send it to call from php.

$stmt = $dbh->prepare("CALL mystore(:dato)");
$stmt -> bindParam(':dato',$dato);

// call the stored procedure
$stmt->execute();

I hope it helps you.

greetings

score 0 · Answer 3

How could I put the QUERY that I have published in the question? within the prepared sentence, for example I have this:

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "formacion";


    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // set the PDO error mode to exception
    //$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // prepare sql and bind parameters
    $stmt = $conn->query("SELECT . . . ");

I will be grateful to guide me as to put the next QUERY to the SQL tensence.

SET @sql = NULL;

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(CASE WHEN fecha = ''',
      fecha,
      ''' THEN asistencia else '''' END) AS '',
      fecha, '''
    )
  ) INTO @sql
FROM asistencias_nueva1;


SET @sql
 = CONCAT('SELECT s.dui, nombres,apellidos, codigo,nombre,depto,muni,', @sql, '
            from docentes1 c
            inner join asistencias_nueva1 s
              on c.dui = s.dui 
           INNER JOIN centros e
           ON c.centro = e.codigo
           AND c.sede052017u2=12347
          AND s.unidad=2
           group by s.dui');


PREPARE stmt FROM @sql;

EXECUTE stmt;

A thousand thanks for the support in this, that is killing me. :)