separate db connection.php from db queries

Question

I want to improve my non-existing PHP knowledge. To do so I created a MySQL DB and a connection.php file with an SQL query (please ignore SQL injection comments for now, I am aware of it). I trying to figure out, how I can split the connection from the actual query.

<?php

header("Access-Control-Allow-Origin: *");

$username = "root"; 
$password = "root";   
$host = "localhost";
$database="test";

$connection = mysqli_connect($host, $username, $password, $database);

$myNodesQuery = "
SELECT * FROM nodes";

$query = mysqli_query($connection, $myNodesQuery);

if ( ! $query ) {
    echo mysqli_error();
    die;
}

$data = array();

for ($x = 0; $x < mysqli_num_rows($query); $x++) {
    $data[] = mysqli_fetch_assoc($query);
}

//echo json_encode($data, JSON_FORCE_OBJECT);     
echo json_encode($data);

mysqli_close($connection);

My thoughts were to create another PHP file and add $connection = mysqli_connect (require('connection.php')) to receive the connection string. Unfortunately, I receive a path error.

🐘If you're just getting started with PHP and want to build applications, I'd strongly recommend looking at various development frameworks to see if you can find one that fits your style and needs. They come in various flavours from lightweight like Fat-Free Framework to far more comprehensive like Laravel. These give you concrete examples to work from and guidance on how to write your code and organize your project's files. — tadman
– tadman, Commented May 31, 2021 at 8:15
Note: The object-oriented interface to mysqli is significantly less verbose, making code easier to read and audit, and is not easily confused with the obsolete mysql_query interface where missing a single i can cause trouble. Use this style: $db = new mysqli(…) and $db->prepare("…") The procedural interface is an artifact from the PHP 4 era and looks clunky and out of place when used in new code. — tadman
– tadman, Commented May 31, 2021 at 8:16
You can't just jam a require inside of things like that. You require and it imports functions you can call. Inside your database inclusion you'd have a function that returns a valid handle, presumably from a connection pool to be efficient. — tadman
– tadman, Commented May 31, 2021 at 8:16
Unfortunately I receive an path error - it would be useful to see what you have tried to see where it goes wrong. — Nigel Ren
– Nigel Ren, Commented May 31, 2021 at 8:22
If you are only starting to learn PHP then you should learn PDO instead of mysqli. PDO is much easier and more suitable for beginners. Start here phpdelusions.net/pdo & websitebeaver.com/… — Dharman
– Dharman ♦, Commented May 31, 2021 at 10:36

Dharman · Accepted Answer · 2021-05-31 10:37:51Z

2

Keeping your code as is then:

File connection.php:

<?php
$username = "root"; 
$password = "root";   
$host = "localhost";
$database="test";
$connection = mysqli_connect($host, $username, $password, $database);

The main file

<?php
header("Access-Control-Allow-Origin: *");
require 'connection.php';

$myNodesQuery = "
SELECT * FROM nodes";
// whatever follows
...

Please note that - unless you use a framework - it would be much better if you build your reusable connection class or connection-returning function. And BTW consider using the far superior PDO.

edited May 31, 2021 at 10:37

Dharman♦

33.9k27 gold badges106 silver badges157 bronze badges

answered May 31, 2021 at 8:19

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

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

1 Comment

tadman Over a year ago

PDO really is immeasurably better.

Collectives™ on Stack Overflow

separate db connection.php from db queries

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related