create temporary tables mariadb 10.1.21 from php 5 and be able to make a selection of them?

Question

create temporary tables mariadb 10.1.21 from php 5 and be able to make a selection of them?

Navigation

#1 by (1 votes)

1

I am trying to create a temporary table from php but when doing the select it tells me that: mysqli_fetch_assoc () expects parameter 1 to be mysqli_result, boolean given in The code is as follows:

mysqli_query($db,"DROP TABLE IF EXISTS T");

$temp="CREATE TEMPORARY TABLE T AS 
(SELECT  @rownum := @rownum + 1 'posicion',
nombre,
dificultad as fallos,
palabra,
fecha,
id 
FROM palabras p,(SELECT @rownum := 0) r 
where nombre IS NOT NULL ORDER BY dificultad, fecha DESC )";

mysqli_query($db,$temp);

$usuarios=" SELECT  * FROM T
 WHERE posicion <= 3 OR id = (SELECT MAX(id) FROM T) ORDER BY posicion";
$res=mysqli_query($db,$usuarios);
$fila=mysqli_fetch_assoc($res);

I get the impression that the temporary table is created but before it can show it is destroyed, which may be failing? The user has all the privileges on the database Thanks

sql php mariadb

asked by javier 04.01.2019 в 20:42

source

1 answer

cancel execution element Why have several sites recently requested permission to use cookies? [closed]

score 1 · Accepted Answer

The error mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given occurs when that function passes a Boolean value, and not a set of results that is what you expect. That means that $res is a Boolean value ( FALSE very likely) because there was a failure in mysqli_query .

If you control the code, doing something like this:

if ( $res=mysqli_query($db,$usuarios) ) { 
    $fila=mysqli_fetch_assoc($res); 
} else { 
   echo "Error: ".mysqli_error($db); 
}

You will get the error message:

Can't reopen table: 'T'

Indeed, your query violates at least one of the restrictions of temporary tables: you can not use a temporary table twice in the same query, as you do here:

SELECT * FROM T ...

And then here:

... OR id = (SELECT MAX(id) FROM T) ...

If it is still failing once corrected, verify that your table or environment is not violating any of the other restrictions indicated in the Reference Manual :

TEMPORARY tables are only compatible with storage engines InnoDB , MEMORY , MyISAM and MERGE .



Temporary tables are not compatible with NDB Cluster.



The SHOW TABLES instruction does not list the TEMPORARY tables.
To change the name of the TEMPORARY tables, RENAME TABLE does not work. Use ALTER TABLE instead:
        ALTER TABLE old_name RENAME new_name;
You can not reference a TEMPORARY table more than once in the same query. For example, the following does not work:
        SELECT * FROM temp_table JOIN temp_table AS t2;
If a TEMPORARY table is created with the same name as an existing non-TEMPORARY table, the non-TEMPORARY table is hidden until it is   discards the TEMPORARY table, even if the tables use   different storage.



There are known problems in the use of temporary tables with replication. See the Section 17.4.1.31, "Replication and tables   temporary ", for more information.