Temporary MySQL Insert followed by a Select

[bululu]

If I have the following Array :

$people=array(1234 => 1, 1235 => 4, 1237 => 3, 1239 => 5);

Is it possible using PHP, to insert an array like above, temporarily such that the table is automatically destroyed at the end of the PHP session? The idea is to be able to select from the table later down the script, since that table should last as long as the session. I have read about Temporary MySQL table and looked at examples, but at my stage, I can not wrap my brain around it.

 

Is the above wish possible in PHP/ MySQL?

Edited November 3, 2013 by bululu

[Barand]

Temporary tables only last as long as the mysql connection, nothing to do with the php session. Since a connection is closed at the end of the script then the temp table will disappear then also.

 

This will create a temp table with your values

$db = new mysqli(HOST,USERNAME,PASSWORD,DATABASE);

$people=array(1234 => 1, 1235 => 4, 1237 => 3, 1239 => 5);

$sql = "CREATE TEMPORARY TABLE temp (
        `key` INT,
        `value` INT
    )";
$db->query($sql);

$rec = array();
foreach ($people as $k=>$v) {
    $rec[] = "($k, $v)";
}

$sql = "INSERT INTO temp VALUES " . join(',', $rec);
$db->query($sql);

[AdRock]

I would guess you would need to tell the script to drop the table before session_destroy() as another query

[bululu]

 

Temporary tables only last as long as the mysql connection, nothing to do with the php session. Since a connection is closed at the end of the script then the temp table will disappear then also.

 

This will create a temp table with your values

$db = new mysqli(HOST,USERNAME,PASSWORD,DATABASE);

$people=array(1234 => 1, 1235 => 4, 1237 => 3, 1239 => 5);

$sql = "CREATE TEMPORARY TABLE temp (
        `key` INT,
        `value` INT
    )";
$db->query($sql);

$rec = array();
foreach ($people as $k=>$v) {
    $rec[] = "($k, $v)";
}

$sql = "INSERT INTO temp VALUES " . join(',', $rec);
$db->query($sql);

 

 

Temporary tables only last as long as the mysql connection, nothing to do with the php session. Since a connection is closed at the end of the script then the temp table will disappear then also.

 

This will create a temp table with your values

$db = new mysqli(HOST,USERNAME,PASSWORD,DATABASE);

$people=array(1234 => 1, 1235 => 4, 1237 => 3, 1239 => 5);

$sql = "CREATE TEMPORARY TABLE temp (
        `key` INT,
        `value` INT
    )";
$db->query($sql);

$rec = array();
foreach ($people as $k=>$v) {
    $rec[] = "($k, $v)";
}

$sql = "INSERT INTO temp VALUES " . join(',', $rec);
$db->query($sql);

Thanks a lot @Barand, I have tried it. I am gettng an empty utput and have no way of verifying that the query succeeded. I am also having problems with the Pear kind of DB Connection ie with arrows as I am still a newbie, I do not even have Pear enabled, will do that when I am well vested with PHP and MySQL

$db->query($sql);

I am used to the simple one that used mysqli. I have then tried to add a select query to select from the temp table, but I get the error

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in C:\wamp\www\webuilder temp\prev5~.php on line 

See below the addition I have made, that gives the above error,

$db = new mysqli('localhost','root','somepassword','bululu_db');

    $people=array(1234 => 1, 1235 => 4, 1237 => 3, 1239 => 5);

    $sql = "CREATE TEMPORARY TABLE temp (
            `key` INT,
            `value` INT
        )";
    $db->query($sql);

    $rec = array();
    foreach ($people as $k=>$v) {
        $rec[] = "($k, $v)";
    }

    $sql = "INSERT INTO temp VALUES " . join(',', $rec);
    $db->query($sql);

$query="SELECT FROM * temp WHERE key=1235";
$result = mysqli_query($db,$query);
($row = mysqli_fetch_array($result));
 echo $row['key'];
 echo $row['value'];

Edited November 3, 2013 by bululu

[Barand]

 

 

$query="SELECT FROM * temp WHERE man_number=1235";

 

That query is failing because there is no man_number column in the temp table (as mysqli_error would have told you)

 

Either change that query to use "key" or change "key" to "man_number" when you create the temp table

[bululu]

That query is failing because there is no man_number column in the temp table (as mysqli_error would have told you)

 

Either change that query to use "key" or change "key" to "man_number" when you create the temp table

@Barand, you readit before I edited. I used it as above key instead of man_number, but the same problem.

[Barand]

What does mysqli_error($db) return after the select query?

 

It could be you do have permissions to create temporary tables, although you should have with "root".

 

run the query "SHOW PRIVILEGES" to see

[bululu]

What does mysqli_error($db) return after the select query?

 

It could be you do have permissions to create temporary tables, although you should have with "root".

 

run the query "SHOW PRIVILEGES" to see

 

What does mysqli_error($db) return after the select query?

 

It could be you do have permissions to create temporary tables, although you should have with "root".

 

run the query "SHOW PRIVILEGES" to see

 

 

Everything is fine, the root user has ALL previleges! I can't understand why it is not working!

[Barand]

What does mysqli_error($db) return after the select query?