How to Execute MySQL Script with Variables Using PHP::Pdo

How to execute mysql script with variables using PHP::PDO?

PDO does not allow the execution of multiple statements in one query() request.
But your @ra_LMC variable should be visible in the current connection, so you can put your second line (SELECT) into a new query() call.

To read a whole script, you have to parse the file and run each statement with a call to query().

PHP PDO - Using MySQL Variables

Found the solution here: https://stackoverflow.com/a/4685040/1266457

Thank you :)

To fix:

// Prepare and execute the variables first
$sql = "
SET @prev_value = NULL;
SET @rank_count = 0;
SET @rank_increasing = 0;
";
$sth = $dbh->prepare($sql);
$sth->execute();

// Run the main query
$sql = "
SELECT a.*
     , @rank_increasing := @rank_increasing + 1 AS row_num
     , CASE
       WHEN @prev_value = score 
          THEN @rank_count
       WHEN @prev_value := score 
          THEN @rank_count := @rank_increasing
       END AS rank
  FROM ( 
       -- INLINE VIEW --
       ) a
"; ...

Using PDO Prepared Statements With MySQL Query Variable

You can't use query parameters to insert expressions to your syntax. Parameters are not just string-interpolation. If they were, there would be no benefit to using them, because you can do string-interpolation easily in PHP already.

The whole point of query parameters is that the value is combined with the query on the server, after the SQL syntax has been parsed, so it's too late for you to insert any new syntax, like an expression.

Query parameters are always treated as a single scalar value. You can't use a parameter for:

• Table identifiers
• Column identifiers
• SQL keywords
• Expressions
• Lists of values

As others have explained, in this case, you have no need to use a query parameter anyway. Using the literal expression log + 1 directly in your query is safe. There's no untrusted content (from users or other sources) being inserted into the query, so there's no risk of SQL injection.

Php mysql pdo query: fill up variable with query result

You should use PDOStatement::fetch http://php.net/manual/en/pdostatement.fetch.php

MySQL retrieve variable from Stored Procedure in PHP PDO

It turns out that this is a bug that has been going on for a long time... since 2005!

Here is the original bug report: 2005 through to 2013. And here is the new bug report: From 2013 to the present.

There are various approaches to getting the answer returned, I found one of them and demonstrate it...

The 'trick' is that to get the output from a 'mysql' procedure. It is a 'two stage' process.

• The first part is to run the procedure with your inputs, and also tell it what MYSQL variables to store the result in.

• Then, you run a separate query to 'select' those 'mysql' variables.

It is described quite clearly here: php-calling-mysql-stored-procedures

Update (Jan 2017):

Here is an example showing the use of variables for 'IN', 'INOUT' and 'OUT' Mysql procedure parameters.

Before we start here are some tips:

• When developing: Run PDO in 'emulates mode' as it is more reliable at determining errors in the procedure call.
• Only bind PHP variables to the procedure 'IN' parameters.

You will get some really odd runtime errors when you try binding variables to INOUT and OUT parameters.

As usual I tend to provide rather more comments than are required ;-/

Runtime Environment (XAMPP):

• PHP: 5.4.4
• Mysql: 5.5.16

Source Code:

• SQL Procedure
• PHP with output

SQL Code:

CREATE PROCEDURE `demoSpInOutSqlVars`(IN     pInput_Param  INT, /* PHP Variable will bind to this*/   
                                      /* --- */  
                                      INOUT  pInOut_Param  INT, /* contains name of the SQL User variable that will be read and set by mysql */
                                      OUT    pOut_Param    INT) /* contains name of the SQL User variable that will be set by mysql */
BEGIN
    /*
     * Pass the full names of SQL User Variable for these parameters. e.g. '@varInOutParam'
     * These 'SQL user variables names' are the variables that Mysql will use for:
     *    1) finding values
     *    2) storing results
     *
     * It is similar to 'variable variables' in PHP.  
     */
     SET pInOut_Param      := ABS(pInput_Param) + ABS(pInOut_Param); /* always positive sum  */
     SET pOut_Param        := ABS(pInput_Param) * -3;                /* always negative * 3  */ 
END$$

PHP Code:

DB Connection:

$db = appDIC('getDbConnection', 'default'); // get the default db connection
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);    
$db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

Note: The output is the same with EMULATE_PREPARES = false.

Set all PHP Variables that will be used:

$phpInParam     = 5;                  
$phpInOutParam  = 404;          /* PHP InOut variable  ==> read and should be changed  */
$phpOutParam    = null;         /* PHP Out   variable  ==> should be changed           */

Define and Prepare the SQL procedure call:

$sql = "call demoSpInOut(:phpInParam, 
                         @varInOutParam, /* mysql variable name will be read and updated */
                         @varOutParam)"; /* mysql variable name that will be written to  */

$stmt = $db->prepare($sql);

Bind PHP Variables and Set SQL Variables:

• 1) bind the PHP variables

  $stmt->bindParam(':phpInParam', $phpInParam, PDO::PARAM_INT);

• 2) Set the SQL User INOUT variables

  $db->exec("SET @varInOutParam = $phpInOutParam"); // This is safe as it just sets the value into the MySql variable.

Execute the procedure:

$allOk = $stmt->execute();

Get the SQL Variables into the PHP variables:

$sql = "SELECT @varInOutParam AS phpInOutParam,
               @varOutParam   AS phpOutParam
        FROM dual";
$results = current($db->query($sql)->fetchAll());

$phpInOutParam = $results['phpInOutParam'];
$phpOutParam   = $results['phpOutParam'];

Note: maybe not the best way ;-/

Display the PHP variables

"$phpInParam:"     => "5"
"$phpInOutParam:"  => "409"
"$phpOutParam:"    => "-15"

php pdo to display output of mysql query using variable

You are using a parameter :empid in your query, and you have to pass a value for that parameter. No value means a empty resultset.

When using a binding parameter, you have to prepare the query, bind the parameter and then execute the query.

function get_editusers($db, $id){     
  try { 
    $result = $db->prepare("
     SELECT firstname, middlename,surname,fullname,gender,
     birthdate,homelanguage,department,employeetype,employeestatus,idnumber 
     FROM Persons where employeeid= :empid");

    $result->bindParam(':empid', $id, PDO::PARAM_INT);
    $result->execute();         
    return $result;  
} 
  catch(PDOException $ex) { 
    return $ex; 
  } 
} 

Using array variable in MySQL PDO query

You are missing $order_ids parameter while executing query: $statUpdateRslt = $updateStatus->execute($order_ids);

Passing PHP Session variable through MySQL command in PDO

1. You cannot bind values to identifiers like table names, only to values.
2. You have two ? placeholders in that query but are only binding one value.

using mysql variable in where clause with PDO

You can't execute multiple queries in a single call.

You can initialize a variable in a subquery.

Also, variables aren't replaced inside strings, you need to use CONCAT().

$query = "SELECT from, till
          FROM termin
          CROSS JOIN (SELECT @v1 := :date) AS x
          WHERE from >= CONVERT(CONCAT(@v1, ' 08:00:00'), DATETIME) 
          AND till <= CONVERT(CONCAT(@v1, ' 20:00:00'), DATETIME) 
          AND comp_id=:comp";

But there isn't really a need for the variable, you can use the :date placeholder twice.

$query = "SELECT from, till
          FROM termin
          WHERE from >= CONVERT(CONCAT(:date, ' 08:00:00'), DATETIME) 
          AND till <= CONVERT(CONCAT(:date, ' 20:00:00'), DATETIME) 
          AND comp_id=:comp";

---

Related Topics