PHP PDO Create Temporary Table Error

[shades]

Hi,

 

I don't know why when i run the below Query in MySQL WorkBench it executes. But when I PDO prepare and execute it and later select It gives no such table exists.

$stmt = $dbconnect -> prepare ("CREATE TEMPORARY TABLE randomsample (
                  `id` int(11) NOT NULL AUTO_INCREMENT,
                  `groupid` int(11) NOT NULL,
                  `groupname` varchar(100),
                  `text` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
                  PRIMARY KEY (`id`)
                ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;")
$stmt -> execute();

Right now I have created a permanent table and it works fine, so if someone could help me in pointing my mistake it will be really helpful.

 

Thanks.

[requinix]

Temporary tables are per-connection. Are you using multiple connections in your code? Perhaps accidentally?

[shades]

Temporary tables are per-connection. Are you using multiple connections in your code? Perhaps accidentally?

 

 

Hmm yes you are right in the first .php file I am creating the table and then inserting data into it. In the next php file I am selecting the data inserted. So, In both the php files I am creating a new connection. 

 

Note: In another php file i need to partition the rows and export into excel file. So basically, I don't have the need to store them in database.

 

Meh... So what would be a proper solution to this ? 

[requinix]

Depends what you're doing...

[shades]

Depends what you're doing...

 

For example:

 

1. I have a permanent table with 1000 rows. 

2. Next i randomly select a fixed(provided in an input form) number of rows and insert into a temporary table

3. Select and display the inserted data from the temporary table

4. Finally i group few of the rows in the temporary table and export into an excel file.

 

So, is there an option to have persistent connect till the final step ? I have read about cookies and session. But I haven't used them so far.

[Sepodati]

Why not just display and export the randomly selected rows in one step? I don't see the need for a temporary table at all.

 

What's the larger problem you're trying to solve? Is it really just "give me X random rows from the table as a CSV" ?

Edited August 30, 2017 by Sepodati

[shades]

Why not just display and export the randomly selected rows in one step? I don't see the need for a temporary table at all.

 

What's the larger problem you're trying to solve? Is it really just "give me X random rows from the table as a CSV" ?

 

The flow is : I got a bigger set (1000 rows) --> get a subset (around 300 rows) randomly ---> select again random rows from these rows and then partition these rows with separate partition id (say like 10 partitions each having 30 rows (this also is randomly selected ). And finally export each partition as an excel file. So I will have 10 excel file.

 

I was thinking about using two temporary files. First to select 300 rows and display. Second to insert randomly selected rows with partition id. So finally i can export it to the excel files with partition id and other columns. I hope i explained it well :/

[Sepodati]

If you know you want X partitions of Y rows each, then just get X*Y random rows. As you're displaying them, every Y rows, save that as a new CSV file. I still don't see why this can't be done all in a single processing step.

Edited August 30, 2017 by Sepodati

[kicken]

If you're dealing with a relatively small number of rows like you say (300-ish that you need to keep track of) then just store the row ID's into either your session variable or a hidden form input and re-select them in the second step.

 

If you're dealing with larger data sets you can create a persistent table to use as scratch space. You can create it as a memory table so it functions like a temporary table.

 

CREATE TABLE randomsample (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `groupid` int(11) NOT NULL,
    `groupname` varchar(100),
    `text` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=MEMORY

You only need to make the table once, so you'd take the create out of your script. Instead just dump your data into it at the first step, then delete it after you're done at the last step.

[shades]

If you're dealing with a relatively small number of rows like you say (300-ish that you need to keep track of) then just store the row ID's into either your session variable or a hidden form input and re-select them in the second step.

 

If you're dealing with larger data sets you can create a persistent table to use as scratch space. You can create it as a memory table so it functions like a temporary table.

 

CREATE TABLE randomsample (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `groupid` int(11) NOT NULL,
    `groupname` varchar(100),
    `text` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=MEMORY

You only need to make the table once, so you'd take the create out of your script. Instead just dump your data into it at the first step, then delete it after you're done at the last step.

 

 

Wow that's an interesting technique, never thought in that way. Yea delete or truncate is better ? I read Truncate is very fast.

[kicken]

Truncate would remove everything. If you might have multiple sessions using the table at the same time then you would be removing their data as well.

[shades]

Truncate would remove everything. If you might have multiple sessions using the table at the same time then you would be removing their data as well.

 

 

Ahh got it thanks for the Information. I will try your solution and will update if things are working fine.

[shades]

If you're dealing with a relatively small number of rows like you say (300-ish that you need to keep track of) then just store the row ID's into either your session variable or a hidden form input and re-select them in the second step.

 

If you're dealing with larger data sets you can create a persistent table to use as scratch space. You can create it as a memory table so it functions like a temporary table.

 

CREATE TABLE randomsample (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `groupid` int(11) NOT NULL,
    `groupname` varchar(100),
    `text` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=MEMORY

You only need to make the table once, so you'd take the create out of your script. Instead just dump your data into it at the first step, then delete it after you're done at the last step.

 

 

It is working fine but only issue was that the MEMORY does not support BLOB/TEXT so had to manually specify varchar(16000).