Mysql / PDO - Setting Variable

Question

I'm having trouble trying to set a variable then use it in a select statement. I keep getting a "general error" and can't figure out what I'm doing wrong. Any input would be appreciate. I'm trying to set a variable using subqueries with named parameters.

$query = $dbh->prepare("Set @available = (SELECT SUM(payments) FROM payments WHERE customer = :customer) - (SELECT SUM(charges) FROM charges WHERE customer = :customer); SELECT @available");
$query->bindParam(":customer", $customer);
$query->execute();

I don't think PHP allows you to run more than one SQL query at once (to prevent SQL injection attacks). — scrowler
– scrowler, Commented Nov 7, 2013 at 0:32
possible duplicate of PDO support for multiple queries (PDO_MYSQL, PDO_MYSQLND) — Phil
– Phil, Commented Nov 7, 2013 at 0:34
@scowler mysqli::multi_query() specifically allows multiple queries and mysqli provides methods for returning multiple result sets. There's no support for prepared multiple queries - I think that's a limitation of MySQL. One can always handle the escaping with mysqli::real_escape_string() — user1864610
– user1864610, Commented Nov 7, 2013 at 1:13
@scrowler 1. PHP has nothing to do with multiple queries. 2. multiple queries has nothing to do with SQL injection attacks — Your Common Sense
– Your Common Sense, Commented Nov 7, 2013 at 7:33
@your common sense - incorrect, multiple queries form the base behind many of the common SQL injection attacks, wiki it for more info — scrowler
– scrowler, Commented Nov 7, 2013 at 10:29

Your Common Sense · Accepted Answer · 2024-03-05 11:11:49Z

8

If you want to use MySQL user variables, for some reason, you don't need multi-queries support. They (user variables) live as long as you session (connection) is open. Therefore you can do something like this

$customer = 1;

$db = new PDO('mysql:host=localhost;dbname=dbname;charset=UTF8', 'user', 'password');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

$sql = "SET @available = (SELECT SUM(payments) FROM payments WHERE customer = ?) - 
                         (SELECT SUM(charges)  FROM charges  WHERE customer = ?)";
$query = $db->prepare($sql);
$query->execute(array($customer, $customer));

$query = $db->prepare("SELECT @available");
$query->execute();
$result = $query->fetchAll(PDO::FETCH_ASSOC);

var_dump($result);

Sample output:

array(1) {
  [0]=>
  array(1) {
    ["@available"]=>
    string(6) "100.00"
  }
}

edited Mar 5, 2024 at 11:11

Your Common Sense

158k42 gold badges226 silver badges373 bronze badges

answered Nov 7, 2013 at 1:21

peterm

93k16 gold badges157 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user1864610 Over a year ago

Why use a prepared statement for SELECT @available? It has no variables - a prepared statement generates an extra call to the database.

peterm Over a year ago

@MikeW I hope that OP wants to use MySQL user variables for something more meaningful than just SELECT @variable. Therefore this more meaningful query most likely need to be prepared. Other then that you right, there is no need to prepare just SELECT @available.

peterm Over a year ago

@Ralph Did it help? Do you need more help with your question?

user1864610 · Accepted Answer · 2013-11-07 01:11:42Z

PDO doesn't seem to offer any formalised support for multi-queries (but some support does seem to be available), but mysqli does. Neither offer support for prepared multiple statements, though.

You can use mysqli like this:

$mysqli = new mysqli('servername', 'username', 'password', 'dbname');
$query = sprintf("Set @available = (SELECT SUM(payments) FROM payments WHERE customer = %1$s)" .
            " - (SELECT SUM(charges) FROM charges WHERE customer = %1$s);".
            " SELECT @available",
            $mysqli->real_escape_string($customer) );

 // Following code lifted from PHP Manual.
 // This code will read multiple results, if they're available.
 // Your query only returns one.

 /* execute multi query */
 if ($mysqli->multi_query($query)) {
    do {
        /* store first result set */
        if ($result = $mysqli->store_result()) {
            while ($row = $result->fetch_row()) {
                printf("%s\n", $row[0]);
            }
            $result->free();
        }
        /* print divider */
        if ($mysqli->more_results()) {
            printf("-----------------\n");
        }
    } while ($mysqli->next_result());
}

The reference is here

Collectives™ on Stack Overflow

Mysql / PDO - Setting Variable

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related