PHP cannot show tables from MySqli Query

Question

I am trying to figure out why I can connect to a database, but cannot access the data in it.

Here's my configuration:

//config.php
<?php
define("HOST", "MYSERVERNAMEISHERE");
define("DATABASE", "users");
?>

My user logs in, and their information is passed to be checked:

//login.php
<?php
if ($_POST) {
    if ($_POST["user"] && $_POST["password"]) {
        include_once "config.php";
        define("USER", $_POST["user"]);
        define("PASSWORD", $_POST["password"]);
        $link = new mysqli(HOST, USER, PASSWORD, DATABASE);
        if ($link) {
            $link->close();
            if ($_SESSION) {
                session_destroy();
            }
            session_start();
            $_SESSION["user"] = $_POST["user"];
            $_SESSION["password"] = $_POST["password"];
        }
    }
}
if ($_SESSION) {
    header('Location: profile.php');
}
else {
    header('Location: login.html');
}
?>

When they pass, they get to see their profile page.

//profile.php
<?php
session_start();
if (!$_SESSION["user"] || !$_SESSION["password"]) {
    session_destroy();
    header("Location: login.html");
}
else {
    include_once "config.php";
}
$link = new mysqli(HOST, USER, PASSWORD, DATABASE) or die("Unable to connect to database");
$result = $link->query("SHOW TABLES") or die("Unable to show tables");
...
ADDITIONAL PHP AND HTML CODE AFTER THIS POINT

The problem is that the process dies when I try to query the mysqli link. (I get Unable to show tables) Right now, the SHOW TABLES is just filler for debugging; I will actually have useful mysqli queries when I figure out the issue.

Please help me determine where my bug is. If you find a typo or a reference link for me, sorry for wasting your time. I've been researching and debugging for hours now.

Thanks very much in advance.

PS: If you have some good advice for changes I should make, I appreciate those too. It's my first time making a user login.

Please use PHP's [built-in functions](http://jayblanchard.net/proper_password_hashing_with_PHP.html) to handle password security. If you're using a PHP version less than 5.5 you can use the `password_hash()` [compatibility pack](https://github.com/ircmaxell/password_compat). Make sure that you [don't escape passwords](http://stackoverflow.com/q/36628418/1011527) or use any other cleansing mechanism on them before hashing. Doing so *changes* the password and causes unnecessary additional coding. — Jay Blanchard, Apr 25 '16 at 22:28
I do not see where you're querying the database. You appear to be allowing every user access to your database....which is not likely. — Jay Blanchard, Apr 25 '16 at 22:28
instead of using that measly `unable to show tables` message, use `->error` property instead — Kevin, Apr 25 '16 at 22:38
@JayBlanchard I guess I was right about a comment I deleted in regards to USER and PASSWORD not being defined, *hah*. — Funk Forty Niner, Apr 25 '16 at 22:48
possible duplicate of [PHP: Notice: Undefined variable and Notice: Undefined index](http://stackoverflow.com/questions/4261133/php-notice-undefined-variable-and-notice-undefined-index) — Funk Forty Niner, Apr 25 '16 at 22:48
You need to put `exit()` after you redirect to `login.html`. Otherwise, it will still try to do the `SHOW TABLES` query. — Barmar, Apr 25 '16 at 22:50
@JayBlanchard and RodrigoDuterte Thank you for the advice. Like I said, I am a total noob, and very much appreciate your insights. — GarrettML, Apr 25 '16 at 23:00

score 1 · Accepted Answer · answered Apr 25 '16 at 22:42

1

Your query in profile.php is failing because USER and PASSWORD are not defined. When the person logs in, they are defined in login.php. When redirected to profile.php, USER AND PASSWORD do not have values since they are not in config.php.

In profile.php, change

$link = new mysqli(HOST, USER, PASSWORD, DATABASE) or die("Unable to connect to database");

to

$link = new mysqli(HOST, $_SESSION["user"], $_SESSION["password"], DATABASE) or die("Unable to connect to database");

answered Apr 25 '16 at 22:42

fislerdata

290
1
4
8

I just found it myself. I marked as correct. Would upvote if I could. I also found that I was not properly vetting the login information. Thanks for the quick response. – GarrettML Apr 25 '16 at 22:47

PHP cannot show tables from MySqli Query

1 Answers1