1

I've been running this code for about a week now, and did not get this problem until today. When I first load my web page, the database is initialized and the tables are created (if any of them do not already exist). However, on the same HTML page, I am calling a Javascript function to create a table of data from the database. This function call generates an error saying that the table does not exist in the database, but I logged onto WAMP and the table exists, and on subsequent loads (where the database already existed), the function works fine. What is going wrong here?

actions.js:

$(document).ready(function() {

    $.ajax({
        url: "action.php",
        type: "GET",
        data: { param1: "INITIALIZE"}
    });

    var json = [];

    //Function for getting decks from database
    function getDecks(hide) {
        $.ajax({
            url: "get_decks.php",
            type: "GET",
            data: {hideDecks : hide},
            success: function(data) {
                alert(data);
                json = JSON.parse(data);
                if(hide == "TRUE") {
                    createTable();
                } else {
                    createHideDeckTable();
                }
            }
        });
    }
}

action.php:

define("SERVER_NAME", "localhost");
define("USERNAME", "root");
define("PASSWORD", "");
define("DATABASE", "flashcards");
define("CHAPTERS_TABLE", "chapters");
define("DECKS_TABLE", "decks");
define("CARDS_TABLE", "cards");

//Function to execute database queries
function executeQuery($sql_query) {
    //Connecting to database
    $mysqli = mysqli_connect(SERVER_NAME, USERNAME, PASSWORD, DATABASE);

    //Check database connection
    if($mysqli === false) {
        die ("\nCould not connect:  " . mysqli_connect_error());
    }

    $result = mysqli_query($mysqli, $sql_query);

    if($result === false){
        echo nl2br("\n\nERROR: Could not able to execute $sql_query. " . mysqli_error($mysqli));
    }

    //Close database connection
    mysqli_close($mysqli);
}

//Function to execute database queries and return the result
function executeQueryWithResult($sql_query) {
    //Connecting to database
    $mysqli = mysqli_connect(SERVER_NAME, USERNAME, PASSWORD, DATABASE);

    //Check database connection
    if($mysqli === false) {
        die ("\nCould not connect:  " . mysqli_connect_error());
    }

    $rows = [];
    $result = mysqli_query($mysqli, $sql_query);

    if($result === false){
        echo nl2br("\n\nERROR: Could not able to execute $sql_query. " . mysqli_error($mysqli));
    } else {
        while($row = mysqli_fetch_assoc($result)) {
            $rows[] = $row;
        }
        return $rows;
    }

    //Close database connection
    mysqli_close($mysqli);
}

//This function initializes the database and all tables in the database
function initializeDatabase() {
    $mysqli = mysqli_connect(SERVER_NAME, USERNAME, PASSWORD);

    //Check database connection
    if($mysqli === false) {
        die ("\nCould not connect:  " . mysqli_connect_error());
    }

    //Query to create flashcards database
    $sql = "CREATE DATABASE IF NOT EXISTS " . DATABASE;
    mysqli_query($mysqli, $sql);

    //Set flashcards database as default database
    mysqli_select_db($mysqli, DATABASE);

    //Create the "Chapters" table
    $sql_create_chapters_table = "CREATE TABLE IF NOT EXISTS " . CHAPTERS_TABLE . "(
        chapter_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(255) NOT NULL UNIQUE
        )";
    executeQuery($sql_create_chapters_table);

    //Create the "Decks" table
    $sql_create_decks_table = "CREATE TABLE IF NOT EXISTS " . DECKS_TABLE . "(
        deck_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
        chapter_ID INT NOT NULL,
        deck_description VARCHAR(255) NOT NULL,
        faculty BOOLEAN NOT NULL,
        hidden BOOLEAN NOT NULL,
        FOREIGN KEY (chapter_ID) REFERENCES chapters(chapter_ID)
        )";
    executeQuery($sql_create_decks_table);

    //Create the "Cards" table
    $sql_create_cards_table = "CREATE TABLE IF NOT EXISTS " . CARDS_TABLE . "(
        card_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
        deck_ID INT NOT NULL,
        front_text VARCHAR(255) NOT NULL,
        front_media VARCHAR(255),
        back_text VARCHAR(255) NOT NULL,
        back_media VARCHAR(255),
        FOREIGN KEY (deck_ID) REFERENCES decks(deck_ID)
        )";
    executeQuery($sql_create_cards_table);

    //Close database connection
    mysqli_close($mysqli);
}

//Process GET request
if(isset($_GET["param1"])) {
    $arg = $_GET["param1"];
    //"INITIALIZE" means database and tables should be created
    if($arg == "INITIALIZE") {
        initializeDatabase();
    }
}

get_decks.php:

include "action.php";

$sql_query = "SELECT * FROM " . DECKS_TABLE . " JOIN " . CHAPTERS_TABLE . " ON " . CHAPTERS_TABLE . ".chapter_ID=" . DECKS_TABLE . ".chapter_ID";

//Check whether or not hidden decks should be returned by the query
if(isset($_GET["hideDecks"])) {
    if(strcmp($_GET["hideDecks"], "TRUE")==0) {
        $sql_query .= " WHERE hidden=\"0\"";
    }
}

$rows = executeQueryWithResult($sql_query);
echo json_encode($rows);

index.html:

<!DOCTYPE html>
<html>
<head>
    <title>My Application</title>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>
    <script src="actions.js" type="text/javascript"></script>
</head>
<body>
    <h1 align="center">WELCOME TO MY APPLICATION</h1>
    <script>getDecks("TRUE");</script>
</body>
</html>
lillemap
  • 139
  • 2
  • 12
  • Do try and get out of the habit of cluttering up your code with needless things like `=== false`. Many functions are designed to return values that evaluate as logically true or false so that's redundant. – tadman Mar 19 '17 at 22:29
  • @tadman thanks, I will definitely work on that once I get this database situation resolved! – lillemap Mar 19 '17 at 22:30
  • It's also a bad habit to wrap the `mysqli` interface inside another layer of abstraction with things like `executeQuery`. It's absolutely critical to use prepared statements with placeholder values, and that function doesn't seem to support those. If you think the `mysqli` interface is incomplete, try PDO, or even better, use an ORM like [Doctrine](http://www.doctrine-project.org/), [Propel](http://propelorm.org/) or [Eloquent](https://laravel.com/docs/5.4/eloquent). – tadman Mar 19 '17 at 22:30
  • You're also connecting for every single query, something that's extremely expensive in terms of time. Normally you connect once, execute as many queries as necessary, and then return that active connection to a pool so it can be re-used by other requests. Is it possible you're connecting to the wrong database for some of these queries? – tadman Mar 19 '17 at 22:32
  • @tadman I use the same exact methods, queries for each load, so the only difference in executing it the second time is that the database already exists – lillemap Mar 19 '17 at 22:37
  • It's not like the PHP driver is going to lie to you. It's only going to do what it's told. Either the database didn't exist, or it's connected to the wrong one. Enable [exceptions](http://stackoverflow.com/questions/14578243/turning-query-errors-to-exceptions-in-mysqli) to make sure you're not missing something in terms of errors. One trick is to make it log which database it was trying to connect to. The name or host might be wrong. – tadman Mar 19 '17 at 22:45
  • It says "flashcards" does not exist, which is the name of the database that does exist. Is it possible it takes a small amount of time for it to be "live" after the query to create it is executed? – lillemap Mar 19 '17 at 22:57
  • There's no query in here that creates anything, just a query that does a `SELECT`. If that part is relevant, please edit your question and include it. – tadman Mar 19 '17 at 23:02
  • Another thing to note is to embrace using booleans in JSON, not having your own quirky string versions. `getDecks(true)` should be sufficient, and it avoids all that ugly `strcmp` stuff. – tadman Mar 19 '17 at 23:03
  • @tadman in the initializeDatabase() method, I have the queries that create the database and the tables of the database – lillemap Mar 19 '17 at 23:05
  • Although I see what you're trying to do here, you've really over-complicated things. Normally databases and tables are prepared prior to starting your application. Any ORM like [Doctrine](http://www.doctrine-project.org/), [Propel](http://propelorm.org/), or [Eloquent](https://laravel.com/docs/5.4/eloquent) comes with a migration manager to help facilitate this, plus allow you to organize changes to the schema over time. Doing it "live" is a really bad plan for reasons you've discovered, the sequencing is very difficult to get right. – tadman Mar 19 '17 at 23:11
  • I'd strongly encourage you to look at existing [development frameworks](http://codegeekz.com/best-php-frameworks-for-developers/) and see if you can find one that fits your style and needs. For example, [Laravel](http://laravel.com/) is very beginner friendly, gives you a lot of support out of the box, and handles this with a well-defined procedure you can follow to get predictable results. – tadman Mar 19 '17 at 23:12

0 Answers0