CODEWITHSUNDEEP
phpmysqlmysqli
EpicJoker
EpicJoker

Reputation: 349

How to keep temporary mysqli table available in php during statement execution?

I am busy trying to execute a set of statements that involve the use of a temporary table.

My goal is to create the temporary table, insert values to it and then do a like comparison of the temporary tables contents to another table.

These statements are working perfectly in phpmyadmin when executed from RAW SQL, but I'm assuming that the table is not available when I try to insert the data.

Below is the code for my php function + mysqli execution:

function SearchArticles($Tags){
    global $DBConn, $StatusCode;
    $count = 0;
    $tagCount = count($Tags);
    $selectText = "";
    $result_array = array();
    $article_array = array();

    foreach($Tags as $tag){
        if($count == 0){
            $selectText .= "('%".$tag."%')";
        }else {
            $selectText .= ", ('%".$tag."%')";
        }
        $count++;
    }

    $query = "CREATE TEMPORARY TABLE tags (tag VARCHAR(20));";    
    $stmt = $DBConn->prepare($query);
    if($stmt->execute()){

        $query2 = "INSERT INTO tags VALUES ?;";
        $stmt = $DBConn->prepare($query2);
        $stmt->bind_param("s", $selectText);
        if($stmt->execute()){

            $query3 = "SELECT DISTINCT art.ArticleID FROM article as art JOIN tags as t ON (art.Tags LIKE t.tag);";
            $stmt = $DBConn->prepare($query3);
            if($stmt->execute()){
                $stmt->store_result();
                $stmt->bind_result($ArticleID);
                if($stmt->num_rows() > 0){
                    while($stmt->fetch()){
                        array_push($article_array, array("ArticleID"=>$ArticelID));
                    }
                    array_push($result_array, array("Response"=>$article_array));
                }else{
                    array_push($result_array, array("Response"=>$StatusCode->Empty));
                }
            }else{
                array_push($result_array, array("Response"=>$StatusCode->SQLError));   
            }
        }else{
            array_push($result_array, array("Response"=>$StatusCode->SQLError));      
        }
    }else{
        array_push($result_array, array("Response"=>$StatusCode->SQLError));
    }
    $stmt->close();
    return json_encode($result_array);
}

The first statement executes perfectly, however the second statement gives me the error of:

PHP Fatal error:  Call to a member function bind_param() on a non-object

If this is an error to do with the Temp table not existing, how do i preserve this table long enough to run the rest of the statements?

I have tried to use:

$stmt = $DBConn->multi_query(query);

with all the queries in one, but i need to insert data to one query and get data from the SELECT query.

Any help will be appreciated, thank you!

Upvotes: 3

Views: 823

Answers (2)

SOFe
SOFe

Reputation: 8224

This is not an issue with the temporary table. It should remain throughout the same connection (unless it resets with timeout, not sure about this part).

The error is that $stmt is a non-object. This means that your query was invalid (syntax error), so mysqli refused to create an instance of mysqli_stmt and returned a boolean instead.

Use var_dump($DBConn->error) to see if there are any errors.

Edit: I just noticed that your query $query2 is INSERT INTO tags VALUES ? (the ; is redundant anyway). If this becomes a string "text", this would become INSERT INTO tags VALUES "text". This is a SQL syntax error. You should wrap the ? with (), so it becomes INSERT INTO tags VALUES (?).

In conclusion, change this line:

$query2 = "INSERT INTO tags VALUES ?;";

to:

$query2 = "INSERT INTO tags VALUES (?);";

also note that you don't need the ; to terminate SQL statements passed into mysqli::prepare.

Upvotes: 2

RiggsFolly
RiggsFolly

Reputation: 94682

You have a simple syntax error use the brackets around the parameters like this 

INSERT INTO tags VALUES (?)

Upvotes: 3

Related Questions