Kevin Powell - Community•9mo ago

PHP todo app database setup question

I have a some far a table in mind for username and passwords but not sure how to link their "tasks" to my database in order to save them for when they logout and later re login. Any tips?

316 Replies

ἔρως•9mo ago

by id create a table with the user_id you can set it up to automatically update and/or delete all tasks when the user is deleted

Jochem•9mo ago

I'd do something like this users

+--------+----------+-------------+
| userid | username |  password   |
+--------+----------+-------------+
|      1 | md       | [hash here] |
|      2 | jochem   | [hash here] |
+--------+----------+-------------+

+--------+----------+-------------+
| userid | username |  password   |
+--------+----------+-------------+
|      1 | md       | [hash here] |
|      2 | jochem   | [hash here] |
+--------+----------+-------------+

tasks

+--------+--------+-------------------+-------+
| taskid | userid |    description    | done  |
+--------+--------+-------------------+-------+
|      1 |      1 | Make an app       | FALSE |
|      2 |      1 | Do something else | FALSE |
+--------+--------+-------------------+-------+

+--------+--------+-------------------+-------+
| taskid | userid |    description    | done  |
+--------+--------+-------------------+-------+
|      1 |      1 | Make an app       | FALSE |
|      2 |      1 | Do something else | FALSE |
+--------+--------+-------------------+-------+

ἔρως•9mo ago

by using a foreign relationship

MD•9mo ago

good point man im rusty tasks should be a separate table

ἔρως•9mo ago

hi rusty, i though you were md yes, because it is an n:n relationship

MD•9mo ago

how you link two different sqlite files?

ἔρως•9mo ago

you don't

Jochem•9mo ago

it's just two tables in a single sqlite file

ἔρως•9mo ago

an sqlite file isn't a single table it's an entire database

MD•9mo ago

wild

ἔρως•9mo ago

that's why we told you to use sqlite

MD•9mo ago

do you have the php create the tables separately or write the code in the file?

ἔρως•9mo ago

you can do both you can probably use phynx to do the migrations for you

Jochem•9mo ago

I'd personally use an external SQLITE tool to set up the database

ἔρως•9mo ago

i think it supports sqlite

Jochem•9mo ago

much easier than doing it with code then you just open the file and the tables already exist migrations are a thing to worry about later

ἔρως•9mo ago

there's another easy way: when the file doesn't exist, create it from an sql file

MD•9mo ago

ive done with it code before on mysql but that was like near 18 years ago

ἔρως•9mo ago

so, to upgrade the database, just delete the sqlite3 file

MD•9mo ago

what tools do you suggest?

ἔρως•9mo ago

vscode there are extensions for it, to read sqlite files you have vscode

Jochem•9mo ago

I usually use the cli to create the file I think

ἔρως•9mo ago

i make it in php i use the method i said

MD•9mo ago

i added the cli tool for sqllite but gotta wait for the system to do a rebuild to add it i did make a database folder in my project structure with a tododb.db file but I guess I should delete it and start over

ἔρως•9mo ago

don't call it .db use .sqlite3 it's easier for you to know what it is

MD•9mo ago

should i have the cli create the whole file? or create it before hand then connect to it in the cli and add the tables?

ἔρως•9mo ago

i think that that's too much work i just use the method i said don't create it before make the tool create it

Jochem•9mo ago

this is what I do. Just create the file, open it with the cli, run the CREATE TABLE commands, and close it again for me, migrations are for databases it would take me more than 30 seconds to recreate from scratch

MD•9mo ago

this should work because migrations by hand can be tedious

ἔρως•9mo ago

yes, but for this, since he's going to do lots of changes, just having an sql file with all the create statements, then delete the file and make php create it automatically is easier you want to add a column? edit the sql file, delete the database ... profit!

MD•9mo ago

i think with PDO it's just new PDO ("sqlite://database/tododb.sqlite3"); right?

ἔρως•9mo ago

yes

MD•9mo ago

what you think jochem about epic input ?

ἔρως•9mo ago

i used that for a project, for very quick prototyping which is what you're doing now you can add data to the sql file as well, by the way

MD•9mo ago

explain how you do your method epic with detail if you can since php can handle it

Jochem•9mo ago

It's what I'd do for something bigger than a todo app.

MD•9mo ago

true im still gonna learn that way for other projects but this seems a bit much

Jochem•9mo ago

like, you can just use ALTER statements to make one or two tweaks to the database file. There's nothing wrong with the concept though, and it can be handy to keep the sql file anyway just for reference if you ever do delete the database file

ἔρως•9mo ago

dude, it's literally this:

$create = !file_exists(...sqlite3);
$pdo = new pdo(...);

if($create) {
  $pdo->query(file_get_contents(...sql));
}

$create = !file_exists(...sqlite3);
$pdo = new pdo(...);

if($create) {
  $pdo->query(file_get_contents(...sql));
}

this

Jochem•9mo ago

also remember db files don't go in git in general, so it can be valuable to have a .sql version of the structure so you don't have to recreate the structure from your code later

ἔρως•9mo ago

exactly! and, you can take the sql file, run the code anywhere and the database is created automatically this is awful pseudocode, by the way

MD•9mo ago

change my sqlite3 into a sql file for github?

ἔρως•9mo ago

nonono sqlite3 = the database file sql = the file with the sql code to re-create the database file

MD•9mo ago

oh!

ἔρως•9mo ago

if you need to change anything, you change in the sql file then just delete the database file

Jochem•9mo ago

you would, at least definitely in a larger project, keep an SQL file with SQL statements that recreates your database from scratch. Basically a structure backup, plus inserts for any rows that are required for your app to function, so that if you rebuild it from scratch six months later, you can just run the file and not have to bother to recreate from scratch. It's not a huge deal for a one-off project with two tables and less than ten columns, but it's useful to have

MD•9mo ago

sweet the update finished i got the CLI tool

MD•9mo ago

MD•9mo ago

this what I got so far

ἔρως•9mo ago

also, if he ruins the database, he can re-create it

MD•9mo ago

true

Jochem•9mo ago

we're in total agreement about the usefulness, I'm just arguing that for two tables with 3 columns each it's potentially a little bit overkill to write migrations

ἔρως•9mo ago

oh, im not talking about migrations, but quick destroy and recreate scenarios

MD•9mo ago

ok so userid is int how big? username and password text?

ἔρως•9mo ago

it's sqlite you have int or int big int, small int ... it's just int but i would use bigint unsigned

MD•9mo ago

im thinking user_id BIG INT UNSIGNED, username VARCHAR(255), password TEXT, task_id BIG INT UNSIGNED, description TEXT, done BOOLEAN? do I need to do anything with foreign key for user_id?

ἔρως•9mo ago

primary key index wait, no

Jochem•9mo ago

I don't think bcrypt produces more than 255 characters, does it? So your password can just be a varchar.

ἔρως•9mo ago

actually, you are using sqlite it has a rowid field for you just use that and yes, varchar(255) is enough for a bcrypt password and it is too for an argon2 password

Jochem•9mo ago

ah hey, actually, I think sqlite doesn't have a varchar type internally. It'll accept it, but it just translates it to TEXT

CREATE TABLE users (
    user_id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL,
    password TEXT NOT NULL
);

CREATE TABLE users (
    user_id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL,
    password TEXT NOT NULL
);

ἔρως•9mo ago

sqlite accepts the varchar declaration also, you can drop the user_id

CREATE TABLE users (
    username TEXT NOT NULL,
    password TEXT NOT NULL
);

CREATE TABLE users (
    username TEXT NOT NULL,
    password TEXT NOT NULL
);

this is enough sqlite automatically creates a rowid field

Jochem•9mo ago

that I did not know You could also primary key on the username, technically. It's unique anyway but I personally prefer to be explicit and have it named the way I like

ἔρως•9mo ago

yes, this would be a good idea for mysql a unique index for it

MD•9mo ago

Row id

ἔρως•9mo ago

rowid https://www.sqlite.org/rowidtable.html using the rowid is way better than using your own primary key you can still set an index on the username, but i doubt you will have more than 6 rows

MD•9mo ago

Would email be a better login? Or username

Jochem•9mo ago

I usually prefer email logins personally one less thing to remember

ἔρως•9mo ago

same

MD•9mo ago

convert username to email?

ἔρως•9mo ago

nah

MD•9mo ago

QLite version 3.43.2 2023-10-10 12:14:04
Enter ".help" for usage hints.
sqlite> CREATE TABLE users ( username TEXT NOT NULL, password TEXT NOT NULL);
sqlite> CREATE TABLE tasks ( taskid INTEGER PRIMARY KEY AUTOINCREMENT, rowid INTEGER, description TEXT, done BOOLEAN );

QLite version 3.43.2 2023-10-10 12:14:04
Enter ".help" for usage hints.
sqlite> CREATE TABLE users ( username TEXT NOT NULL, password TEXT NOT NULL);
sqlite> CREATE TABLE tasks ( taskid INTEGER PRIMARY KEY AUTOINCREMENT, rowid INTEGER, description TEXT, done BOOLEAN );

this what I got so far

ἔρως•9mo ago

no no no no no don't create the rowid column

MD•9mo ago

but wont tasks and user not match on row ids?

Jochem•9mo ago

this is why I prefer to be explicit with the ID columns

ἔρως•9mo ago

NEVER create a rowid column

Jochem•9mo ago

just create the user_id column INTEGER PRIMARY KEY AUTOINCREMENT on the users table, then reference it with a user_id column on tasks

ἔρως•9mo ago

unless you know what you're doing

MD•9mo ago

ill need to start over

ἔρως•9mo ago

i dont like jochem's suggestion, but it's the best option if the rowid confuses you so, just do what he said

Jochem•9mo ago

what Epic is suggesting is having a user_id column on the tasks table which then references the rowid on users

ἔρως•9mo ago

Jochem•9mo ago

you'd join with SELECT * FROM users u LEFT JOIN tasks t ON t.user_id = u.rowid I prefer being explicit, so you can do SELECT * FROM users u LEFT JOIN tasks t USING(user_id) (does that even work in sqlite?)

MD•9mo ago

both tables are created 🙂 how do we save the file from CLI?

ἔρως•9mo ago

it's close enough

Jochem•9mo ago

don't think you have to, it just writes as you go just .exit

MD•9mo ago

weird file just shows up as "todoDB" no extension

ἔρως•9mo ago

that's because you didn't add an extension?

MD•9mo ago

would userid in tasks table need an AUTOINCREMENT?

ἔρως•9mo ago

no you can't autoincrement 2 columns

MD•9mo ago

make it NOT NULL?

ἔρως•9mo ago

yes

MD•9mo ago

ok done!

ἔρως•9mo ago

what's the create sql for the tables?

MD•9mo ago

[md89@darknet database]$ sqlite3 todoDB.sqlite3
SQLite version 3.43.2 2023-10-10 12:14:04
Enter ".help" for usage hints.
sqlite> CREATE TABLE users (userid INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT NOT NULL, password NOT NULL);
sqlite> CREATE TABLE tasks (taskid INTEGER PRIMARY KEY AUTOINCREMENT, userid INTEGER NOT NULL, description TEXT, done BOOLEAN);
sqlite> .exit
[md89@darknet database]$

[md89@darknet database]$ sqlite3 todoDB.sqlite3
SQLite version 3.43.2 2023-10-10 12:14:04
Enter ".help" for usage hints.
sqlite> CREATE TABLE users (userid INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT NOT NULL, password NOT NULL);
sqlite> CREATE TABLE tasks (taskid INTEGER PRIMARY KEY AUTOINCREMENT, userid INTEGER NOT NULL, description TEXT, done BOOLEAN);
sqlite> .exit
[md89@darknet database]$

ἔρως•9mo ago

you forgot the unsigned also, you didn't set up the constraint

MD•9mo ago

:/ what am i missing?

ἔρως•9mo ago

all integers must be insigned and you should add a constraint to the tasks table

MD•9mo ago

dont know how to do that on sqlite everything gets casted to INTEGER according to SQLite

ἔρως•9mo ago

yes, but not unsigned

MD•9mo ago

https://www.sqlite.org/datatype3.html

ἔρως•9mo ago

just add UNSIGNED

MD•9mo ago

INTEGER UNSIGNED

CREATE TABLE users (userid INTEGER UNSIGNED PRIMARY KEY AUTOINCREMENT, username TEXT NOT NULL, password NOT NULL);

CREATE TABLE users (userid INTEGER UNSIGNED PRIMARY KEY AUTOINCREMENT, username TEXT NOT NULL, password NOT NULL);

before I press enter

ἔρως•9mo ago

no, the other way around also, use bigint

MD•9mo ago

CREATE TABLE users (userid UNSIGNED BIG INT PRIMARY KEY AUTOINCREMENT, username TEXT NOT NULL, password TEXT NOT NULL);

CREATE TABLE users (userid UNSIGNED BIG INT PRIMARY KEY AUTOINCREMENT, username TEXT NOT NULL, password TEXT NOT NULL);

ἔρως•9mo ago

yup, sounds good

MD•9mo ago

Parse error: AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY looks like you cant

ἔρως•9mo ago

integer then

MD•9mo ago

they changed things

ἔρως•9mo ago

🤔 i mean, when you don't use the rowid column, and try to create your own, weird stuff gets weird but, you're right, it's just integer

MD•9mo ago

CREATE TABLE tasks (taskid INTEGER PRIMARY KEY AUTOINCREMENT, userid INTEGER NOT NULL, description TEXT, done BOOLEAN);

CREATE TABLE tasks (taskid INTEGER PRIMARY KEY AUTOINCREMENT, userid INTEGER NOT NULL, description TEXT, done BOOLEAN);

ἔρως•9mo ago

yup

MD•9mo ago

how we add the constraint?

ἔρως•9mo ago

by the way, don't use a "done" use an enum for the status because the task can be in many states

MD•9mo ago

so what data type fits then?

ἔρως•9mo ago

it can be paused, done, pending, queued, running, cancelled, error ...

MD•9mo ago

Each column in an SQLite 3 database is assigned one of the following type affinities:

    TEXT
    NUMERIC
    INTEGER
    REAL
    BLOB

Each column in an SQLite 3 database is assigned one of the following type affinities:

    TEXT
    NUMERIC
    INTEGER
    REAL
    BLOB

this project is going to get more complicated

ἔρως•9mo ago

status TEXT CHECK( status IN ( ... ) )

MD•9mo ago

idk how to do that I just went by boolean because Jochem used it why not just... Done or Not Done?

ἔρως•9mo ago

you do exactly as i wrote it

MD•9mo ago

this isnt meant to be a big project just a simple todo app so i can move on to a bigger project

ἔρως•9mo ago

i know oh, then just "done" is enough

MD•9mo ago

im debating on a blog next project one that I actively update with news like a personal ok how we add the constraint?

ἔρως•9mo ago

ive never done it in sqlite 🤣 https://www.sqlite.org/foreignkeys.html

MD•9mo ago

https://www.tutorialspoint.com/sqlite/sqlite_constraints.htm

ἔρως•9mo ago

yup there should be something that lets you delete everything from the tasks when you delete an user and before you think it is overkill: data consistency is never overkill

MD•9mo ago

got it

[md89@darknet database]$ sqlite3 todoDB.sqlite3
SQLite version 3.43.2 2023-10-10 12:14:04
Enter ".help" for usage hints.
sqlite> CREATE TABLE users (userid INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT NOT NULL, password TEXT NOT NULL);
sqlite> CREATE TABLE tasks (taskid INTEGER PRIMARY KEY AUTOINCREMENT, userid INTEGER NOT NULL, description TEXT, done BOOLEAN, FOREIGN KEY(userid) REFERENCES users(userid)); 
sqlite> .exit

[md89@darknet database]$ sqlite3 todoDB.sqlite3
SQLite version 3.43.2 2023-10-10 12:14:04
Enter ".help" for usage hints.
sqlite> CREATE TABLE users (userid INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT NOT NULL, password TEXT NOT NULL);
sqlite> CREATE TABLE tasks (taskid INTEGER PRIMARY KEY AUTOINCREMENT, userid INTEGER NOT NULL, description TEXT, done BOOLEAN, FOREIGN KEY(userid) REFERENCES users(userid)); 
sqlite> .exit

now im going to eat dinner

ἔρως•9mo ago

good luck

MD•9mo ago

back

ἔρως•9mo ago

👋

MD•9mo ago

so for my models i got this

<?php

    namespace models;

    class User {
        public $username;
        public $password;

        public function __construct($username, $password)
        {
            $this->username = $username;
            $this->password = $password;
        }
    }

<?php

    namespace models;

    class User {
        public $username;
        public $password;

        public function __construct($username, $password)
        {
            $this->username = $username;
            $this->password = $password;
        }
    }

<?php

    namespace models;

    class Task
    {
        public $description;
        protected $done;

        public function isDone() {
            return $this->done;
        }

        public function done() {
            $this->done = true;
        }
    }

<?php

    namespace models;

    class Task
    {
        public $description;
        protected $done;

        public function isDone() {
            return $this->done;
        }

        public function done() {
            $this->done = true;
        }
    }

ἔρως•9mo ago

why public?

MD•9mo ago

I think these will talk to those fields? username should be public

ἔρως•9mo ago

then add a readonly

Jochem•9mo ago

also, you can shortcircuit those assignments in the constructor

ἔρως•9mo ago

yup

Jochem•9mo ago

I forget how, but you don't need to do that anymore

MD•9mo ago

make password protected or private

Jochem•9mo ago

https://www.php.net/manual/en/language.oop5.decon.php#language.oop5.decon.constructor.promotion

ἔρως•9mo ago

public function __construct(
  public string $username,
  public string $password,
) {}

public function __construct(
  public string $username,
  public string $password,
) {}

none: get it from the database and throw it away after use

MD•9mo ago

oh ok so just take password completely away from the user model?

ἔρως•9mo ago

yeah, i wouldn't save any passwords keep it in the database only

Jochem•9mo ago

yeah, you can just fetch the pw when you need to check it

ἔρως•9mo ago

MD•9mo ago

i also added this request for trimming

ἔρως•9mo ago

what?

MD•9mo ago

<?php

    namespace helpers;

    class Request {
        public static function uri() {
            return trim(
                parse_url($_SERVER['REQUEST_URI'], PHP_URL_PATH), "/"
            );
        }

        public static function method() {
            return $_SERVER['REQUEST_METHOD'];
        }
    }

<?php

    namespace helpers;

    class Request {
        public static function uri() {
            return trim(
                parse_url($_SERVER['REQUEST_URI'], PHP_URL_PATH), "/"
            );
        }

        public static function method() {
            return $_SERVER['REQUEST_METHOD'];
        }
    }

trimming paths down for the browser url

ἔρως•9mo ago

why don't you use $_SERVER['PATH_INFO']?

MD•9mo ago

https://php.watch/versions/8.1/readonly

PHP.Watch

PHP 8.1: Readonly Properties

ἔρως•9mo ago

this lets you do /index.php/same/path/here

MD•9mo ago

nice

ἔρως•9mo ago

that's what i told you make it readonly

MD•9mo ago

achieves same thing?

ἔρως•9mo ago

no, i just explained the difference here

MD•9mo ago

i changed REQUEST_URI to PATH_INFO

ἔρως•9mo ago

you can use both $_SERVER['PATH_INFO'] ?? $_SERVER['REQUEST_URI'] ?? '/'

MD•9mo ago

<?php

    namespace helpers;

    class Request {
        public static function uri() {
            return trim(
                parse_url($_SERVER['REQUEST_URI'], $_SERVER['PATH_INFO']), "/"
            );
        }

        public static function method() {
            return $_SERVER['REQUEST_METHOD'];
        }
    }

<?php

    namespace helpers;

    class Request {
        public static function uri() {
            return trim(
                parse_url($_SERVER['REQUEST_URI'], $_SERVER['PATH_INFO']), "/"
            );
        }

        public static function method() {
            return $_SERVER['REQUEST_METHOD'];
        }
    }

ἔρως•9mo ago

that's not what i wrote https://www.php.net/manual/en/function.parse-url.php that will also throw an exception

MD•9mo ago

<?php

    namespace helpers;

    class Request {
        public static function uri() {
            return trim(
                parse_url($_SERVER['PATH_INFO'] ?? $_SERVER['REQUEST_URI'] ?? "/", PHP_URL_PATH), "/"
            );
        }

        public static function method() {
            return $_SERVER['REQUEST_METHOD'];
        }
    }

<?php

    namespace helpers;

    class Request {
        public static function uri() {
            return trim(
                parse_url($_SERVER['PATH_INFO'] ?? $_SERVER['REQUEST_URI'] ?? "/", PHP_URL_PATH), "/"
            );
        }

        public static function method() {
            return $_SERVER['REQUEST_METHOD'];
        }
    }

ἔρως•9mo ago

close, yes

MD•9mo ago

updated

ἔρως•9mo ago

thats better

MD•9mo ago

very ternary are the models ok for the database?

ἔρως•9mo ago

null coalescense i think so

MD•9mo ago

great! hey trying to load this class variable in for my database it's not liking it

<?php

namespace database;

require '../Config.php'; 


    class SQLConnection {
        /**
         * PDO instance
         * @var type
         */

         private $pdo;

         /**
          * return pdo connection to sqlite
          * @return \PDO
          */
          public function connect() {
            if ($this->pdo == null) {
                $this->pdo = new \PDO("sqlite:" . Config::PATH_TO_SQLITE);
            }

            return $this->pdo;
          }
    }

<?php

namespace database;

require '../Config.php'; 


    class SQLConnection {
        /**
         * PDO instance
         * @var type
         */

         private $pdo;

         /**
          * return pdo connection to sqlite
          * @return \PDO
          */
          public function connect() {
            if ($this->pdo == null) {
                $this->pdo = new \PDO("sqlite:" . Config::PATH_TO_SQLITE);
            }

            return $this->pdo;
          }
    }

here's Config

<?php

    namespace App;

    class Config {
        const PATH_TO_SQLITE = './database/todoDB.sqlite3';
    }

<?php

    namespace App;

    class Config {
        const PATH_TO_SQLITE = './database/todoDB.sqlite3';
    }

ἔρως•9mo ago

just use dependency injection

MD•9mo ago

directory structure

Jochem•9mo ago

your ./database is refering to the path relative to Config, but the string is used in database/conn.php

MD•9mo ago

Undefined type 'database\Config' is namespace operate like Go does with thing where it goes by the folder name?

Jochem•9mo ago

try \Config? (though tbh I'm past the Balmer point and just guessing)

ἔρως•9mo ago

you're not following proper psr paths you need to use \database\Config also, the path is wrong it should be ./...sqlite3

Jochem•9mo ago

doubt that, config isn't part of the database folder or namespace

ἔρως•9mo ago

since you want it in the same directory

Jochem•9mo ago

it's either App\Config or \Config

MD•9mo ago

so namespace functions kinda like Golang

Jochem•9mo ago

https://tenor.com/bc6q4.gif

Tenor

MD•9mo ago

everything is in "app" folder

ἔρως•9mo ago

namespaces function like paths no, you put them wherever you want but there's standards so, follow them you can save so many headaches just by using composer if not, just have all the classes inside a folder, like "classes" and register a class autoload that takes the namespace path and includes the file

Jochem•9mo ago

this is what I used to do, yeah never bothered with namespaces

ἔρως•9mo ago

namespaces are important https://www.php.net/manual/en/function.spl-autoload-register.php <-- use that

MD•9mo ago

wait where do I change the path? in the "Config::" or require

ἔρως•9mo ago

config

MD•9mo ago

ok here's new

<?php

    namespace App;

    class Config {
        const PATH_TO_SQLITE = '../app/database/todoDB.sqlite3';


    }

<?php

    namespace App;

    class Config {
        const PATH_TO_SQLITE = '../app/database/todoDB.sqlite3';


    }

ἔρως•9mo ago

yikes! no! todoDB.sqlite3 that's it

MD•9mo ago

config isn;t in database folder though? is that ok?

ἔρως•9mo ago

where's the path going to be used?

MD•9mo ago

PDO for connection

ἔρως•9mo ago

and where is that file?

MD•9mo ago

inside database folder both sqlite3 file and conn.php inside database

<?php

namespace database;

require '../Config.php'; 


    class SQLConnection {
        /**
         * PDO instance
         * @var type
         */

         private $pdo;

         /**
          * return pdo connection to sqlite
          * @return \PDO
          */
          public function connect() {
            if ($this->pdo == null) {
                $this->pdo = new \PDO("sqlite:" . App/Config::PATH_TO_SQLITE);
            }

            return $this->pdo;
          }
    }

<?php

namespace database;

require '../Config.php'; 


    class SQLConnection {
        /**
         * PDO instance
         * @var type
         */

         private $pdo;

         /**
          * return pdo connection to sqlite
          * @return \PDO
          */
          public function connect() {
            if ($this->pdo == null) {
                $this->pdo = new \PDO("sqlite:" . App/Config::PATH_TO_SQLITE);
            }

            return $this->pdo;
          }
    }

ἔρως•9mo ago

if it is inside the same folder, then you can just skip the directory because it's relative to where you use it otherwise, it's just a normal string

MD•9mo ago

that's weird PDO errored without the "" forward slash

ἔρως•9mo ago

use \PDO; at the top

MD•9mo ago

<?php

namespace database;


    class SQLConnection {
        /**
         * PDO instance
         * @var type
         */

         private $pdo;

         /**
          * return pdo connection to sqlite
          * @return \PDO
          */
          public function connect() {
            if ($this->pdo == null) {
                $this->pdo = new \PDO("sqlite://todoDB.sqlite3");
            }

            return $this->pdo;
          }
    }

<?php

namespace database;


    class SQLConnection {
        /**
         * PDO instance
         * @var type
         */

         private $pdo;

         /**
          * return pdo connection to sqlite
          * @return \PDO
          */
          public function connect() {
            if ($this->pdo == null) {
                $this->pdo = new \PDO("sqlite://todoDB.sqlite3");
            }

            return $this->pdo;
          }
    }

i deleted the Config.php

ἔρως•9mo ago

that's a bad idea, but i get why you did it

MD•9mo ago

what do you recommend? i dont want "bad" code that means 0 job

ἔρως•9mo ago

that's not bad just sub-optimal

MD•9mo ago

what's optimal?

ἔρως•9mo ago

composer

MD•9mo ago

oh im not using composer for this project just vanilla php

ἔρως•9mo ago

why? you want to show you can use the tools, right?

MD•9mo ago

others suggested it

ἔρως•9mo ago

composer and a psr-2 would be the optimal for you, in my opinion

MD•9mo ago

for this small scale things like composer and laravel are not needed psr

ἔρως•9mo ago

whoever said that is bsing you composer isn't about size it's about reusable code

MD•9mo ago

how do I use composer for this project? i dont want to use laravel

ἔρως•9mo ago

dude, nobody is talking about laravel composer is composer laravel is laravel

MD•9mo ago

ok what we do with composer here?

ἔρως•9mo ago

install composer

MD•9mo ago

i have composer already on my system

ἔρως•9mo ago

alright

MD•9mo ago

im in the PHPTodoAPP directory where whole project sits

ἔρως•9mo ago

give me a second to pick a good loader

ἔρως•9mo ago

https://packagist.org/packages/env/dotenv

env/dotenv - Packagist

A simple, tiny and lightweight PHP .env loader

ἔρως•9mo ago

check this one it just parses the .env file and stores it in a variable but, you can save it into the environment variables, if you wish

MD•9mo ago

md89@darknet PHPTodoApp]$ composer require env/dotenv
./composer.json has been created
Running composer update env/dotenv
Loading composer repositories with package information
Updating dependencies
Lock file operations: 1 install, 0 updates, 0 removals
  - Locking env/dotenv (v2.0.0)
Writing lock file
Installing dependencies from lock file (including require-dev)
Package operations: 1 install, 0 updates, 0 removals
  - Downloading env/dotenv (v2.0.0)
  - Installing env/dotenv (v2.0.0): Extracting archive
Generating autoload files
No security vulnerability advisories found.
Using version ^2.0 for env/dotenv
[md89@darknet PHPTodoApp]$

md89@darknet PHPTodoApp]$ composer require env/dotenv
./composer.json has been created
Running composer update env/dotenv
Loading composer repositories with package information
Updating dependencies
Lock file operations: 1 install, 0 updates, 0 removals
  - Locking env/dotenv (v2.0.0)
Writing lock file
Installing dependencies from lock file (including require-dev)
Package operations: 1 install, 0 updates, 0 removals
  - Downloading env/dotenv (v2.0.0)
  - Installing env/dotenv (v2.0.0): Extracting archive
Generating autoload files
No security vulnerability advisories found.
Using version ^2.0 for env/dotenv
[md89@darknet PHPTodoApp]$

ἔρως•9mo ago

that's it now, just do require './vendor/autoload.php'; do it in your index.php file

MD•9mo ago

created index.php inside app folder and added require "vendor/autoload.php";

ἔρως•9mo ago

yes, but do ./vendor/autoload.php you then create a .env file that's parseable by the php_ini_parse function https://www.php.net/manual/en/function.parse-ini-file.php

MD•9mo ago

something like this?

<?php
var_dump(parse_ini_file('.env', false, INI_SCANNER_RAW));

<?php
var_dump(parse_ini_file('.env', false, INI_SCANNER_RAW));

ἔρως•9mo ago

that's what the file does but has a little bit of nuance behind https://github.com/sabroan/php-dotenv/blob/main/src/Dotenv.php this is the code

MD•9mo ago

i see so what I need in .env file just the path to sqlite ffile?

ἔρως•9mo ago

besides, showing that you know how to use composer is important too

MD•9mo ago

outside of localhost address

ἔρως•9mo ago

something like this:

PDO_DSN="sqlite3://db.sqlite3"

PDO_DSN="sqlite3://db.sqlite3"

you can actually do something cool af

define('ENV', \Env\Dotenv::toArray(
  path: '.env',
  strict: false,
));

define('ENV', \Env\Dotenv::toArray(
  path: '.env',
  strict: false,
));

MD•9mo ago

PDO_DB = "sqlite3://app/database/todoDB.sqlite3"

PDO_DB = "sqlite3://app/database/todoDB.sqlite3"

ἔρως•9mo ago

then, you can just use ENV['PDO_DSN']

MD•9mo ago

do i need to move my .env inside app folder? right now it's outside

ἔρως•9mo ago

no, leave in the same directory as the index.php file

MD•9mo ago

index.php inside app

ἔρως•9mo ago

the index.php where everything is loaded from

MD•9mo ago

technically i made a goof since index.php is in app folder then it should be

<?php
   
   require "../vendor/autoload.php";

since ./ puts it inside app still the php intellisense isn't very great on vscode

ἔρως•9mo ago

move the vendor folder to the same folder where the index.php file is actually no, you can leave it like that you can also do ../.env, if you want but that's very confusing

MD•9mo ago

ἔρως•9mo ago

what i would do is to have index.php, vendor and .env in the same folder

MD•9mo ago

this is current structure

ἔρως•9mo ago

your structure is a huge mess do this it will save you lots of headaches

MD•9mo ago

yeah everything is in app never was a directory expert they're all in app now

ἔρως•9mo ago

good

MD•9mo ago

ἔρως•9mo ago

good, good now, you can add a loader to your composer,json file which should be with the rest of the files same as the composer.lock

MD•9mo ago

yeah im still learning all these little things im just used to php without anything web is just way different now

ἔρως•9mo ago

by the way, you can use require __DIR__ . '/vendor/autoload.php'; to make sure you load it from the right place, without using relative paths well, it is the same but you were making a mess, and im helping you to clean it up for you

MD•9mo ago

<?php
   
   require __DIR__ . "/vendor/autoload.php";

<?php
   
   require __DIR__ . "/vendor/autoload.php";

ἔρως•9mo ago

yup

MD•9mo ago

awesome

ἔρως•9mo ago

now, to add a loader

MD•9mo ago

this is php ive done in case you're interested

MD•9mo ago

https://github.com/MD-2016/Anywhere-Bank-Web-Application

GitHub

GitHub - MD-2016/Anywhere-Bank-Web-Application: A web based banking...

A web based banking application that allows users to make an account and keep track of their money. - GitHub - MD-2016/Anywhere-Bank-Web-Application: A web based banking application that allows use...

ἔρως•9mo ago

move all your classes into a single folder that's a monolithic mess do this, and have that folder be in the same folder as the index.php, .env and so on

MD•9mo ago

that will mess things up model has two classes in it for those respective models

ἔρως•9mo ago

dude, just do it

MD•9mo ago

every file that has the word class in it

MD•9mo ago

ἔρως•9mo ago

open your composer.json file, and add this - replace Acme with a name that you don't mind repeating:

    "autoload": {
        "psr-4": {"Acme\\": "class/"}
    }

    "autoload": {
        "psr-4": {"Acme\\": "class/"}
    }

MD•9mo ago

{
    "require": {
        "env/dotenv": "^2.0"
    },

    "autoload" : {
        "psr-4": {"Acme\\": "class/"}
    }
}

{
    "require": {
        "env/dotenv": "^2.0"
    },

    "autoload" : {
        "psr-4": {"Acme\\": "class/"}
    }
}

ἔρως•9mo ago

are you sure you want to use the acme namespace?

MD•9mo ago

idk what im even doing so i put what you did

ἔρως•9mo ago

what that does is "when you see the namespace starting with 'Acme', it loads the file inside the class/ directory"

MD•9mo ago

my folder is called classes where they all are currently

ἔρως•9mo ago

then you have to change the name of it i mean, in the composer file

MD•9mo ago

so this means all those files need their namespace changed?

ἔρως•9mo ago

no yes if i were you, i would change "Acme" to "MD" or something like that

MD•9mo ago

renamed classes to class folder

ἔρως•9mo ago

that works

MD•9mo ago

so database namespace needs changed to MD/database or just MD/

ἔρως•9mo ago

this BUT!!!!!! the folder HAS TO BE CALLED database SAME CAPITALIZATION and the file name MUST BE THE SAME AS THE CLASS NAME

MD•9mo ago

well everything been moved to one folder and it's name is class this is gonna be a mess

ἔρως•9mo ago

now do these changes to every single file trust me, it's not

MD•9mo ago

conn.php

<?php

namespace database;


    class SQLConnection {
        /**
         * PDO instance
         * @var type
         */

         private $pdo;

         /**
          * return pdo connection to sqlite
          * @return \PDO
          */
          public function connect() {
            if ($this->pdo == null) {
                $this->pdo = new \PDO("sqlite:/todoDB.sqlite3");
            }

            return $this->pdo;
          }
    }

<?php

namespace database;


    class SQLConnection {
        /**
         * PDO instance
         * @var type
         */

         private $pdo;

         /**
          * return pdo connection to sqlite
          * @return \PDO
          */
          public function connect() {
            if ($this->pdo == null) {
                $this->pdo = new \PDO("sqlite:/todoDB.sqlite3");
            }

            return $this->pdo;
          }
    }

ἔρως•9mo ago

you have to change the name of the file to SQLConnection.php

MD•9mo ago

ok what about the namespace?

ἔρως•9mo ago

change it as well this is what happens

MD•9mo ago

to SQLConnection?

ἔρως•9mo ago

no same as you put in there

MD•9mo ago

{
    "require": {
        "env/dotenv": "^2.0"
    },

    "autoload" : {
        "psr-4": {"MD\\": "class/"}
    }
}

{
    "require": {
        "env/dotenv": "^2.0"
    },

    "autoload" : {
        "psr-4": {"MD\\": "class/"}
    }
}

ἔρως•9mo ago

yup

MD•9mo ago

namespace MD\class;

ἔρως•9mo ago

this is what happens: MD\database\SQLConnection will load the file class\database\SQLConnection.php you're mapping the namespaces to a folder

MD•9mo ago

MD\Class is ok for namespace?

ἔρως•9mo ago

that will load in class\class\ so, no, it's awful

MD•9mo ago

MD?

ἔρως•9mo ago

MD\<foldername>\ <-- namespace

MD•9mo ago

well all those files got moved out of database, helpers, and other folders so im lost now

ἔρως•9mo ago

models will be in class\models the namespace is MD\models

MD•9mo ago

model User.php

<?php

    namespace MD\models;

    class User {
        public readonly string $username;

        public function __construct($username)
        {
            $this->username = $username;
        }
    }

<?php

    namespace MD\models;

    class User {
        public readonly string $username;

        public function __construct($username)
        {
            $this->username = $username;
        }
    }

ἔρως•9mo ago

that's it

MD•9mo ago

ok just feels a little unorganized now that models is empty and such

ἔρως•9mo ago

why is it empty?

MD•9mo ago

i moved them from models to class

ἔρως•9mo ago

🤦‍♂️ no! you keep the same directory structure

MD•9mo ago

so keep same directory structure but they're all inside of class folder?

ἔρως•9mo ago

you're literally saying "hey, php, the namespace MD is inside the folder class"

MD•9mo ago

class/models/User.php

ἔρως•9mo ago

yes instead of being 5-6 folders inside app those 5-6 are inside class and class is inside app

MD•9mo ago

since I put SQLConnection inside database folder with the sqlite3 database previously move that folder inside class?

ἔρως•9mo ago

everything that has classes goes inside that folder

MD•9mo ago

ill just move the file then

ἔρως•9mo ago

don't do that keep the same directory structure or you will be making a huge salad

MD•9mo ago

ill make a different directory for DB move SQLConnection inside class

ἔρως•9mo ago

as long as it is inside the class directory, do what you want pretend that the class directory is the app directory but only classes go there

MD•9mo ago

moved SQLConnection inside a folder called dbhelper inside class folder

ἔρως•9mo ago

update the namespace

MD•9mo ago

basically we're doing the same thing Go lang does except changing folder names to match in go you name the folder based on the namespace

ἔρως•9mo ago

you are doing the same now

MD•9mo ago

then go can find the files easier

ἔρως•9mo ago

that's the idea the folders are the namespaces the files are the classes also, NO MORE INCLUDES AND REQUIRES if you have any, delete them all except the one inside the index.php that one's required to load composer

MD•9mo ago

I think this is a good stopping point

ἔρως•9mo ago

that's how it is done then, you can do use MD\dbhelper\SQLConnection; and the file is included imediatelly for you and you can just use SQLConnection right there

MD•9mo ago

Neat So autoloader

ἔρως•9mo ago

that's what the autoloader does it automatically loads the classes for you but uses composer to do it for you

MD•9mo ago

I know ill almost need to make a routes.php

ἔρως•9mo ago

thats up to how you organize things you can use klein, for example it's a very old, but really decent router

MD•9mo ago

Nice

ἔρως•9mo ago

you can handle routes super easily with it

ἔρως•9mo ago

https://packagist.org/ <-- you can search for the packages in this website

Packagist

The PHP Package Repository

ἔρως•9mo ago

https://packagist.org/packages/klein/klein <-- and this is klein

klein/klein - Packagist

A lightning fast router for PHP

Gaming

Programming

PHP todo app database setup question