0

The story so far

I am used to developing using the mysqli_connect() function to connect to the database like so:

<?php  
$db_host = "localhost"; 
$db_username = "root";
$db_pass = "pass";  
$db_name = "db"; 

$connect = mysqli_connect("$db_host","$db_username","$db_pass") or die ("could not connect to mysql");
mysqli_select_db($connect, "$db_name") or die ("no database");
?>

When I connect in this way I always write my queries like:

$sql = mysqli_query($connect, "INSERT INTO table (column1, column2, column3) VALUES('$value1','$value2','$value3')") or die (mysql_error());

However I have recently been working on a project with a new hosting company who won't allow this. The above connection script will give a 500 error and when I asked the hosting company they said I should connect using PDO, which I have never done before.

I am now able to connect using this script:

<?php
class DbConnect {
    const DB_HOSTNAME = 'localhost';
    const DB_USERNAME = 'root';
    const DB_PASSWORD = 'pass';
    const DB_DATABASE = 'db';
    public $error;

    public function connect() {
        try {
            $db = new \PDO("mysql:dbname=" . self::DB_DATABASE . ";host=" . self::DB_HOSTNAME, self::DB_USERNAME, self::DB_PASSWORD, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
            $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        } catch (\PDOException $e) {
            $this->error = $e->getMessage();
            return FALSE;
        }
        return $db;
    }

    public function fail($message) {
        $out = "{$message}";
        return $out;
    }

    public function success($message) {
        $out = "{$message}";  
        return $out;
    }
}

$connect = new DbConnect;
$db = $connect->connect();
if ($db === FALSE) {
    echo $connect->fail($connect->error);
} else {
    echo $connect->success("Connected to database");
}
?>

However, I am now trying to integrate this into an entire site and and am having some issues.

Question

First of all, I just replaced one connection script with the other using <?php include_once("../connect_to_sql.php"); ?> (I didn't expect it to work, I just wanted to see what kind of errors I got) and besides a page full of errors I got the following at the very top. I thought if it was connecting by itself there should be nothing on my other pages where it is included that should cause the connection to fail:

SQLSTATE[HY000] [1045] Access denied for user 'root'@'localhost' (using password: YES)

Secondly, when I do get this connecting correctly inside my other files I don't want to have to change every single query I'm making. Will the $connect variable from the second connection file work the same way for the mysqli_query() function? Or what would be the best way to go about this where I would have to make minimal changes.

And finally, what are the differences/benefits of connecting in this way or which way is better/more efficient?

halfer
  • 19,824
  • 17
  • 99
  • 186
Paddy Hallihan
  • 1,624
  • 3
  • 27
  • 76
  • given you are trying to connect as a root, it's no wonder you are getting an error – Your Common Sense Mar 07 '18 at 10:08
  • I am not actually using root, pass, db, they're just placeholders for posting here – Paddy Hallihan Mar 07 '18 at 10:10
  • I do not think this is a duplicate question, I have tried to find logs on my shared hosting but cannot. I figured the reason I was not connecting via the first method was because they had restrictions on the domain this is why I contacted them. The issue you linked https://stackoverflow.com/questions/1475297/phps-white-screen-of-death seems more to be about showing server errors and error handling, where I think my questions were more about the connection/query code/syntax itself – Paddy Hallihan Mar 07 '18 at 10:27
  • I don't disagree with you, maybe I'm not completely understanding it. I'm just asking to try get a better grip on it myself. Would this not be a specific error related to my connection: SQLSTATE[HY000] [1045] Access denied for user 'root'@'localhost' (using password: YES). – Paddy Hallihan Mar 07 '18 at 10:36
  • Thank you for your comments by the way I am just trying to get a better understanding of something I have never done before. I am not too worried about SQL injection at this time as I am only just getting set up with the backend where only the admin should be entering info. I just want to get to the point where I have an understanding of this connection method and how my queries would be affected or what way I would need to rewrite them. – Paddy Hallihan Mar 07 '18 at 10:39
  • you can google on that error message it has been asked (and answered) several thousand times already. The same you can do for the difference between mysqli and pdo as well – Your Common Sense Mar 07 '18 at 11:12

0 Answers0