in reply to Dynamic SQL

You should use placeholders.

$query1="(select Name from Customers where CustId=?)";
$query2="(select Name from Sales where SalesId=?)";

$sth1=$dbh->prepare($query1);
$sth2=$dbh->prepare($query2);

if ($x>10) {
 $sth1->execute($custid);
}
else {
 $sth2->execute($salesid);
}

[download]
(minor tweaks to fix typos..)

Replies are listed 'Best First'.
Re^2: Dynamic SQL
by pme (Monsignor) on Apr 06, 2015 at 15:55 UTC
    If you have a complex query you can write this way for the easier readability: 
    $query1 = 'select Name from Customers where CustId = :custid';
$query2 = 'select Name from Sales where SalesId = :salesid';

$sth1 = $dbh->prepare($query1);
$sth2 = $dbh->prepare($query2);

if ($x > 10) {
  $sth1->bind_param('custid', $custid);
  $sth1->execute();
  while ($row = $sth1->fetchrow_hashref) {
    ...
  }
} else {
  $sth2->bind_param('salesid', $salesid);
  $sth2->execute();
  while ($row = $sth2->fetchrow_hashref) {
    ...
  }
}

    [download]
    'prepare' can be replaced with 'prepare_cached' in order to avoid multiple preparation. Excerpt from DBI man page: 
    Like "prepare" except that the statement handle returned will be store
+d in a hash associated with the $dbh. If another call is made to "pre
+pare_cached" with the same $statement and %attr parameter values, the
+n the corresponding cached $sth will be returned without contacting t
+he database server.

    [download]
    Update: I am not sure that this kind of named binding work with MySQL.
[reply]
[d/l]
[select]
Re^2: Dynamic SQL
by Anonymous Monk on Apr 06, 2015 at 15:39 UTC
    because the query parameters varry too : 
    if ($x>10) { $query="(select Name from Customers where CustId='$custid
+')" } else { $query="(select Name from Sales where SalesId='$salesid'
+ and CustId='$custid')" }

    [download]
    so when preparing and executing the query with the first query: 
    $sth->execute(?)

    [download]
    with the second: 
    $sth->execute(?,?)

    [download]
    so I can't have a single execute covering both occasions
[reply]
[d/l]
[select]

      Did you notice that my version has two separate statement handles that are being executed? You can do the same thing with yours. Prepared statements with placeholders will likely be faster than constructing queries on the fly and executing them, and certainly much safer.

      How many possible queries can you need to construct that you can't afford to use prepared statements and placeholders?

[reply]
        prepared statements with placeholders will likely be faster than constructing queries on the fly

        I don't think rather doubt this is generally true. It would be nice to see it demonstrated.

[reply]
      my ( $query, @args );
if ( $x > 10 ) {
    $query = 'select Name from Customers where CustID = ?';
    @args = ( $custid );
} else {
    $query = 'select Name from Sales where SalesId = ? and CustID = ?'
+;
    @args = ( $salesid, $custid );
}

my $sth = $dbh->prepare( $query );
$sth->execute( @args );

      [download]
[reply]
[d/l]

In Section Seekers of Perl Wisdom