Variable mySQLi prepared statement

[TapeGun007]

This has to do with yesterday's thread found here:

https://forums.phpfreaks.com/topic/303251-can-you-store-con-into-an-array/

 

So the actual mySQL is something like this:

SELECT
                    FirstName, LastName, Email, Mobile, PromoCode
                FROM <database>.<table>
                WHERE (Email LIKE ?)
                    OR (Mobile LIKE ?)
                    OR (PromoCode LIKE ?) 

A user fills out a search INPUT and so they can search based on the email address, cell phone or promo code stored in $Search.

 

Then for the prepared statement I would simply use something like this:

$stmt->bind_param('sss', $Search,$Search,$Search);

So, the code we worked on yesterday pulls the SQL statement from potentially 1 database, or it could be 5 databases using UNION.  So if there were 5 databases, then the bind_param would be set to say 'sssssssssssssss' and $Search x 15 times.

 

I don't know how to accomplish this... I am assuming that for the 'sss' I can store that in a string easy enough, but what about duplicating the $Search field over and over?

 

I'm not sure if I am making coherent sense here.  Hopefully I am.

[Barand]

I use SQL variables to save repetition of bound parameters. For example

$sql = "SELECT
            FirstName, LastName, Email, Mobile, PromoCode
            FROM <database1>.<table>
                JOIN (@srch := ?) init_var
            WHERE (Email LIKE @srch)
                OR (Mobile LIKE @srch)
                OR (PromoCode LIKE @srch) 
        UNION SELECT
            FirstName, LastName, Email, Mobile, PromoCode
            FROM <database2>.<table>
            WHERE (Email LIKE @srch)
                OR (Mobile LIKE @srch)
                OR (PromoCode LIKE @srch) 
        UNION SELECT
            FirstName, LastName, Email, Mobile, PromoCode
            FROM <database3>.<table>
            WHERE (Email LIKE @srch)
                OR (Mobile LIKE @srch)
                OR (PromoCode LIKE @srch) ";
$stmt = $db->prepare($sql);
$search = '%xxx%';
$stmt->bind_param('s', $search);

Note the first has a subquery to set the varaible value to the param value.

Edited February 21, 2017 by Barand

[TapeGun007]

I thought Psycho's way of writing my code was very neat and tidy yesterday, but I couldn't figure out how to apply it.  Now I'm trying to figure out how to apply what you are telling me.  This is my complete rudimentary and failed attempt at coding this:

// $dbs tells us how many databases are in that array.
$dbs = count($statedb);
// Establish all of the connection strings that we will need.  Some states will need 1, some states will need 10.
$con = array();
foreach($statedb as $counter => $db_name){
    $con[$counter] = new mysqli($DBServer, $DBUser, $DBPass, $db_name);
    //echo "$counter - $db_name<br>";
    // Loop through each database name in the array and add it to the $sql
        $sql .= "
                SELECT
                    FirstName, LastName, Email, Mobile, PromoCode
                FROM $db_name.<tablename>
                WHERE (Email LIKE ?)
                    OR (Mobile LIKE ?)
                    OR (PromoCode LIKE ?) 
                ";
    $args .= "sss";
    // This just stops adding to the string 1 count before so we do not get 3 extra $Search's
    if($counter < ($dbs - 1)){
        $searches .= $Search.", ".$Search.", ".$Search;
    }
    // This prevents putting an extra comma at the end of the final string or we would get "$Search, $Search, $Search,"
    if($dbs <> 1 && $counter < ($dbs - 2)){
        $searches .= ", ";
    }
    /*  If there is only 1 database, then we do not need a UNION.  Also, we do not want a UNION at the end of our $sql either.
    */
    if($dbs <> 1 && $counter < ($dbs - 1)){
        // If the State selected has more than 1 database, then add the UNION to the SQL command.
        $sql .= " <p>UNION</p> ";
    }
}
echo "<p> $sql </p>";
echo "<br> $args - $searches<br>";

$stmt = $consd->prepare($sql);
$stmt->bind_param($args,".$searches.");
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($ID, $FirstName, $LastName, $Email, $Mobile, $Code);

But at least I was trying... LOL.  I'll read up on mySQL variables.  I get what you are doing, I just need to learn how to apply it.  Thanks.

[Barand]

Why are you messing around with string concatenation and then worrying about removing trailing commas or UNION keywords.

 

Use arrays with join().

 

You will also find it a lot simpler with PDO. Its parameter binding is far more streamlined.

[Barand]

Using your current method (but with arrays and PDO) it becomes

$databases = [ 'database1', 'database2', 'database3', 'database4', 'database5' ];

$baseSQL = "SELECT
            FirstName, LastName, Email, Mobile, PromoCode
            FROM <DB>.tablename
            WHERE (Email LIKE ?)
                OR (Mobile LIKE ?)
                OR (PromoCode LIKE ?) ";
$queries = [];
$params  = [];

$srch = isset($_GET['search']) ? '%' . $_GET['search'] . '%' : '%';


foreach ($databases as $dbname) {
    $queries[] = str_replace('<DB>', $dbname, $baseSQL );
    array_push($params, $srch, $srch, $srch);
}

$sql = join("\nUNION\n", $queries);  // build set of UNION queries

/********** DEBUG ONLY _ DISPLAY QUERY **************/
echo '<pre>', $sql, '</pre>';
/****************************************************/

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

[TapeGun007]

Why are you messing around with string concatenation and then worrying about removing trailing commas or UNION keywords.

 

Use arrays with join().

 

You will also find it a lot simpler with PDO. Its parameter binding is far more streamlined.

 

Ignorance.  I just didn't know the proper method of going about that.

 

But when I post on here, not only do I get the answer I am looking for, but I also get an education on how to do things the RIGHT way.