CODEWITHSUNDEEP
phpmysqlsql
user2955526
user2955526

Reputation: 13

PHP SQL Temporary table won't work

I'm trying to understand how the temporary tables work. But even when I copy and paste it from my tutorials, I don't get the expected result.

See the code underneath for more info. At the moment the .php file returns a blank page (no errors), while i would expect it to be:

1 Row inserted.

I looked for the error code but found out that temporary tables don't have an error code.

Does someone know what I'm doing wrong?

<?php
    $link = mysqli_connect("localhost","xxx","xxx","xxx");

    /* check connection */
    if (mysqli_connect_errno()) 
    {
        printf("Connect failed: %s\n", mysqli_connect_error());
        exit();
    }

    mysqli_query($link, "CREATE TEMPORARY TABLE myCity (Name CHAR(30)");

    $city = "'s Hertogenbosch";
    $city = mysqli_real_escape_string($link, $city);

    /* this query with escaped $city will work */
    if (mysqli_query($link, "INSERT INTO myCity (Name) VALUES ('$city')")) 
    {
        printf("%d Row inserted.\n", mysqli_affected_rows($link));
    }

    mysqli_close($link);
?>

Upvotes: 0

Views: 3288

Answers (2)

rernesto
rernesto

Reputation: 552

this works for me

<?php
ini_set("display_errors",1);
error_reporting(E_ALL);

$mysqli = new mysqli("localhost", "root", "admin123", "test");

if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}
$mysqli->query("CREATE TEMPORARY TABLE CITIES (
  id int(11) NOT NULL,
  name varchar(64) NOT NULL
)");

//$mysqli->autocommit(FALSE);

$mysqli->query("INSERT INTO CITIES VALUES ('1', 'Bavaria')");
$mysqli->query("INSERT INTO CITIES VALUES ('2', 'Havana')");

//$mysqli->commit();

if ($result = $mysqli->query("SELECT * FROM CITIES LIMIT 10")) {
    printf("Select returned %d rows.\n", $result->num_rows);
    /* free result set */
    $result->close();
}

//$mysqli->query("DROP TABLE CITIES");

$mysqli->close();

Remember Temporary tables only exist by session, sometimes we need create conventional table and truncate later... You would must think use DB lib like Doctrine DBAL or ADODb or something like that...

Upvotes: 0

Adrien M.
Adrien M.

Reputation: 186

Looks like you have an error in your sql creation. You are missing a closing parenthesis: ")"

CREATE TEMPORARY TABLE myCity (Name CHAR(30)

Adrien.

Upvotes: 3

Related Questions