Strange query behavior

[ludo1960]

Hi guys,

In my connection.php I have:

$db->query("DROP TABLE IF EXISTS mydata") ;

$db->query("CREATE TABLE mydata
(
ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
guid INT,
title VARCHAR(100),
body LONGTEXT,
term VARCHAR(100)
)"); 

and my query code :

$myarray = array (
    guid =>  100,
    title => "title test",
    body => "just a test",
    term => "term test",
);

$myplaceholders[] = '(' . implode (", ", array_fill(0, count($myarray), '?')) . ')'; //also tried '(?,?,?,?)'

$mykeys = implode(', ', array_keys($myarray));

array_push($values, ...array_values($myarray)); //also tried $values = array_values($myarray) ;

$res = $db->prepare("INSERT INTO mydata ($mykeys) VALUES " . join(', ', $myplaceholders)) ;

if ($res->execute($values)) {
    echo 'data inserted';
} else {
    echo 'error in query';
}

After executing the code, the table is created but no data is inserted. The strange thing is when I leave the create table statement  out of the connection.php and run the code the data is inserted. Any ideas where I'm going wrong?

[requinix]

Before you do anything else, find your php.ini, find the error_reporting and display_errors settings in it, and change them to

error_reporting = -1
display_errors = on

Then restart PHP, run your code, and see what happens.

[ludo1960]

Ok

changed to display_errors = On,

changed

error_reporting = E_ALL & ~E_DEPRECATED & ~E_STRICT 

to error_reporting = -1

added quotation marks to $myarray keys. No errors show, database still not updated

[requinix]

You should have seen either "data inserted" or "error in query". If you didn't see either then your code didn't even run at all.

[ludo1960]

oops should of said, it prints out "data inserted"

[requinix]

Then it worked. Are you sure you're looking in the right database and right table? Maybe some cached version of what was in the table?

[ludo1960]

Changed the name of the database table in the code to make sure the database was ok and tried again, restarted mysql, still says 'data inserted' but database is empty?

[mac_gyver]

your code is probably executing the connection code again, which is recreating the table. it's generally not a good idea to have table deletion/creation queries being unconditionally executed.

[ludo1960]

That would explain it, if I remove the drop and create from the connection.php, it starts working. 

IF (!EXISTS mydata) {
CREATE TABLE ......

};

Is the above code the way to go?

[ludo1960]

This works:

if ($db->query ( "DESCRIBE mydata" )) {
    
} else {

    $db->query("CREATE TABLE drupaldata
    (
    ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    guid INT,
    title VARCHAR(100),
    body LONGTEXT,
    term VARCHAR(100)
    
)"); 

}

oops, no it doesn't, how to create table if it does not exist?

Edited February 5, 2019 by ludo1960

[requinix]

Don't create tables like this in your code. They should all be set up before your code gets to run at all.

If you want/need to create them using code, that's one thing, but that's something that should be a process completely separate from everything else. Something you run yourself because you need to set up the database. After that your code shouldn't have to consider the possibility that the table doesn't exist/exist in the state it's expecting.

[ludo1960]

Yeah that makes sense, stick all the create table stuff in an install file

[ginerjm]

Or if it's a one-time process to create the table, just use the host's phpmyadmin tool to create the table and ensure that it is done correctly right in front of you.  THEN write your script.  Perhaps use cut/paste to save the input you use in that process in a notepad window until you get it correct or while experimenting or even to save it as a txt file once you are done if you think you'll need to re-create it.