asked on

PHP Create Temporary Table Then Insert

I'd like to create a temporary table that has random numbers in the name, then insert records to it. Once I'm done, I'll delete the temporary table. Creating the table works fine, but when I go to insert, I can't use a variable for the table name. Here's a code snipit...

			$query = $qry_reg_insert->prepare('INSERT INTO {$temp_table} (
				id_family_member, 
				id_housing_with

Open in new window

I've also tried 'INSERT INTO $temp_table(

I'm creating the temporary table with random numbers so another user doesn't come along at the same time and insert more records into the same table. I don't see any way to do it without using a variable inside the Insert statement.

Any thoughts?

Thanks!

Steve

Jeff Darling

You can build the string for the prepare to put the table name in there.

$temp_table = "temp0001";

$sql = "INSERT INTO ".$temp_table."  (id_family_member, id_housing_with) VALUES (?, ?)";

$query = $qry_reg_insert->prepare($sql);

$qry_reg_insert->bind_param("sss", $id_family_member, $id_housing_with);

Open in new window

stkoontz

ASKER

Thanks for the quick reply. I'm incorporating the code into my application and am getting this error message..

Fatal error: Call to undefined method PDO::bind_param() in ...

Here's the full code;

$temp_table = "temp_box_report_general_12345";

try {
		$qry_reg_insert = new PDO("mysql:host=...);


		$sql = "INSERT INTO ".$temp_table."  (id_family_member, id_housing_with) VALUES (?, ?)";

		$query = $qry_reg_insert->prepare($sql);

		$qry_reg_insert->bind_param('ss', $id_family_member, $id_housing_with);
				$qry_reg_insert = null;
				} catch (PDOException $e) {
					//echo $e;
					//echo "Error!  Please email steve@cenational.org";
					die();
				}

Open in new window

Jeff Darling

sorry, my mistake. The bind_param is part of the $stmt after the bind.

Model it after this..

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$MyTempTable = "temp0001";

$SQL = "INSERT INTO ".$MyTempTable." (firstname, lastname, email) VALUES (?, ?, ?)";

// prepare and bind
$stmt = $conn->prepare($SQL);
$stmt->bind_param("sss", $firstname, $lastname, $email);

Open in new window

Ray Paseur

Hi, Steve. Is the question about creating a random table name or creating a variable in a query statement? A random table name is usually available in PHP $_SERVER["UNIQUE_ID"].

This article shows the CREATE and INSERT examples in MySQL, MySQLi, and PDO in parallel construction.
https://www.experts-exchange.com/articles/11177/Why-PHP-Removed-Support-for-the-MySQL-API.html

Look for CREATE TABLE Query Strings and then look for Escape the Data and Load the Table.

ASKER CERTIFIED SOLUTION

Julian Hansen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

stkoontz

ASKER

Thanks, Ray, for jumping in. I am using PDO and pulled this snipit from your extensive article, but how is the record actually inserted?

// PREPARE THE QUERY TO LOAD THE NAMES ("PREPARE" ONLY NEEDED ONCE)
$pdo = new PDO("mysql:host=...);
$sql
=
"INSERT INTO temp_table
(  id_family_member,  id_housing_with ) VALUES
( :id_family_member, :id_housing_with )
"
;
try 
{ 
    $pdos = $pdo->prepare($sql); 
} 
catch(PDOException $exc) 
{ 
    var_dump($exc);
    trigger_error($exc->getMessage(), E_USER_ERROR);
}

Open in new window

stkoontz

ASKER

Julian, you win the prize. I went back to my original code, changed the single quotes to double quotes and it worked like a charm. Thanks!

Steve

Ray Paseur

After the prepare() method, the next step would be PDOStatement::execute()

Ray Paseur

And if the question is about how quotation marks work, here's a reference point...
https://www.experts-exchange.com/articles/12241/Quotation-Marks-in-PHP.html

Julian Hansen

You are welcome.