How to check if table exists, if not create in SQLite/PDO?

Question

At the moment I'm having to do it manually by documenting out one of my includes, I've tried a few things but it never seems to work properly and messes up my page?

include("dbconnection.php");
include("dbresults.php");
/*$sqltable = "CREATE TABLE Devices (id, dname, dmodel, ddesc, dcountry,dprivacy)";
if ($db->query($sqltable) == TRUE) { 
 echo "table created succesfully";
} else {
    echo "error creating table: . $e->getMessage()";
}
*/

If I delete my table, I get nothing but a blank after include("dbconnection.php"); runs, when I comment out dbresults .php and uncomment the table code it works fine until the table is created, on refresh another white page until I reverse the commented out lines again (back to how they are above)

dbconnection.php

try {

$db = new PDO("sqlite:".__DIR__."/database.db");
$db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
} catch  (exception $e) {
echo "unable to connect";
echo $e->getMessage();

 exit;
}

dbresults.php

$sth = $db->prepare("SELECT * FROM Devices");
$sth->execute();
while ($result = $sth->fetch(PDO::FETCH_ASSOC)) {
$dbarray[] = $result;
}

I've tried putting the create table inbetween Dbconnection.php and Dbresults.php, or even putting it in the files themselves. I've tried multiple things but seems like whatever I do, it just breaks the page after dbconnection.php. I can get it to recognise the table doesn't exist, but once I go to create the table my page breaks. I'm sure I'm missing something really silly here, hopefully someone can push me in the right direction what I'm doing wrong

Edit: I realise my above code isn't checking for if table exists or anything, I tried a few things but nothing really worked so I just reverted it back to what's above.

user9123 · Accepted Answer · 2020-05-19 17:42:24Z

1

You have probably found the answer by now, but for anyone else looking for a solution, you can simply change

CREATE TABLE

to

CREATE TABLE IF NOT EXISTS

and not actually check if the table exists in the first place.

answered May 19, 2020 at 17:42

user9123

6512 gold badges13 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to check if table exists, if not create in SQLite/PDO?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related