Jump to content
ludo1960

Strange query behavior

Recommended Posts

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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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 by ludo1960

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.