Jump to content

Variable mySQLi prepared statement


TapeGun007
Go to solution Solved by Barand,

Recommended Posts

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.

Link to comment
Share on other sites

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 by Barand
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • Solution

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);
Link to comment
Share on other sites

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.  :)

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.