How can you connect a database using PHP?

[idontknowphp]

this is my code:

<?php

/*--------------------BEGINNING OF THE CONNECTION PROCESS------------------*/

//define constants for db_host, db_user, db_pass, and db_database

//adjust the values below to match your database settings

define('DB_HOST', 'localhost');

define('DB_USER', 'root');

define('DB_PASS', 'root'); //may need to set DB_PASS as 'root'

define('DB_DATABASE', 'people.sql'); //make sure to set your database

//connect to database host

$connection = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_DATABASE);

//make sure connection is good or die

if ($connection->connect_errno) 

{

  die("Failed to connect to MySQL: (" . $connection->connect_errno . ") " . $connection->connect_error);

}

/*-----------------------END OF CONNECTION PROCESS------------------------*/

/*----------------------DATABASE QUERYING FUNCTIONS-----------------------*/

//SELECT - used when expecting single OR multiple results

//returns an array that contains one or more associative arrays

function fetch_all($query)

{

  $data = array();

  global $connection;

  $result = $connection->query($query);

  while($row = mysqli_fetch_assoc($result)) 

  {

    $data[] = $row;

  }

  return $data;

}

//SELECT - used when expecting a single result

//returns an associative array

function fetch_record($query)

{

  global $connection;

  $result = $connection->query($query);

  return mysqli_fetch_assoc($result);

}

//used to run INSERT/DELETE/UPDATE, queries that don't return a value

//returns a value, the id of the most recently inserted record in your database

function run_mysql_query($query)

{

  global $connection;

  $result = $connection->query($query);

  return $connection->insert_id;

}

//returns an escaped string. EG, the string "That's crazy!" will be returned as "That\'s crazy!"

//also helps secure your database against SQL injection

function escape_this_string($string)

{

  global $connection;

  return $connection->real_escape_string($string);

}

?>

[Strider64]

What I would do is create a PHP Sandbox and use the world database https://dev.mysql.com/doc/index-other.html. That is what I do when I want to learn about PHP

I would use PDO instead of mysqli and here’s is some code from my php sandobox

<?php
include 'lib/includes/connect/connect.php';
$records = [];

$db_options = [
    /* important! use actual prepared statements (default: emulate prepared statements) */
    PDO::ATTR_EMULATE_PREPARES => false
    /* throw exceptions on errors (default: stay silent) */
    , PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
    /* fetch associative arrays (default: mixed arrays)    */
    , PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ
];
if (isset($_POST['submit'])) {

    $pdo = new PDO('mysql:host=' . DATABASE_HOST . ';dbname=world;charset=utf8', DATABASE_USERNAME, DATABASE_PASSWORD, $db_options);

    $sql = 'SELECT Name, CountryCode, District, Population FROM city WHERE CountryCode LIKE :CountryCode ORDER BY District'; // Set the Search Query:

    $stmt = $pdo->prepare($sql); // Prepare the query:

    $stmt->execute([':CountryCode' => "%" . $_POST['countryCode']]); // Execute the query with the supplied user's parameter(s):

    $records = $stmt->fetchAll();
}

the connection constants

define('DATABASE_HOST', 'localhost');
define('DATABASE_NAME', 'sandbox');
define('DATABASE_USERNAME', 'your username');
define('DATABASE_PASSWORD', 'your password');
define('DATABASE_TABLE', 'world');

[phdr]

The error you are getting has nothing to do with your php code. It’s due to the driver php is using and the database not speaking the same authentication hash-method. If you obtained php and the database server through a MAMP (stack), everything should be built to work together. If you installed separate pieces, I seem to recall in one of your threads a specific mention of installing MySql (the database server), you managed to get different versions/builds that are not using the same authentication hash-method.

@idontknowphp

this is my code:

As to the posted code, there are several problems, and in fact you should be learning the much better, modern, simpler, and more consistent PDO extension, instead of the mysqli extension. A laundry list of issues -

1. Don’t use defined constants for the connection credentials. This limits you to one single connection. Code should be general-purpose and reusable.
2. Don’t use the root user in your application. You should create a specific user that only has the permissions that are needed for a web application.
3. The database name would not have .sql as part of it.
4. The variable holding the connection should be appropriately name so that anyone reading the code knows what it is. Use a name like $mysqli or $pdo.
5. Don’t unconditionally output connection, query, prepare, and execute errors onto a live web page as that just gives hackers useful information when they intentionally trigger errors. Instead, use exceptions for errors and in most cases let php catch and handle the exception, where php will use its error related settings to control what happens with the actual error information (database statement errors will ‘automatically’ get displayed/logged the same as php errors.) You would then remove any existing database statement error handling logic, simplifying the code. BTW - you don’t have any error handling logic for the query() calls, so doing this will give you error handling that you are missing now.
6. While some of the functions you have are useful, you should actually extend the mysqli (or PDO) class with these, so that you can access all the base methods and properties, without writing a bunch of code.
7. Don’t use the global keyword to get things into functions. This limits you to a single database connection and ends up making debugging harder, since you must then keep track of where every global gets used at.
8. The run_…_query() function should NOT return the last insert id. Only insert and update queries have a last insert id. If you need either the last insert id or the number of affected rows, you should specifically make a call to get these.
9. You should use a prepared query when supplying external, unknown, dynamic values to a query when it gets executed. While only adding a single php statement per query, this eliminates any use of _escape_string() calls (which only work for string data), it provides security for all data types, and it simplifies the sql query syntax, making it easier to write error free sql statements (you won’t believe the number of problems caused by all the extra quotes, dots, and {} trying to get php variables into sql query statements.)

Edit:
10. Use a single programming method for the same thing. You are mixing mysqli OOP and procedural notation. Pick one programming method and stick to it.

[idontknowphp]

I have a downloaded database file already, should I save the database file to the root folder of PHP so that I can view the database?

[Mrwilson1]

Make sure you are not using a table name, but make sure its the database name.

Dont use people.sql, just use “people”

Defining the connection parameters is ok