Search code examples
phpmysqlsqlforum

Accessing MySQL DB row data using PHP

I have the following PHP to access a DB for a simple forum I'm building:

$sql = "
    SELECT
        categories.cat_id,
        categories.cat_name,
        categories.cat_description
    FROM
        categories
    WHERE
        categories.cat_id = '" . mysql_real_escape_string($_GET['id']) ."'
";                        
$result = mysql_query($sql);

//If data is unable to be served...
if (!$result) { 
    echo 'Category could not be displayed! Please try again later' . mysql_error();
} else {
    if(mysql_num_rows($result) == 0) {
        //This is the error code being displayed
        echo 'Category does not exist!';
    }
}

Here is the code for this section of the DB:

CREATE TABLE categories(
    cat_id          INT(8) NOT NULL AUTO_INCREMENT,
    cat_name        VARCHAR(255) NOT NULL,
    cat_description VARCHAR(255) NOT NULL,
    UNIQUE INDEX
    cat_name_unique (cat_name),
    PRIMARY KEY(cat_id)
)
TYPE=INNODB;

The problem: For some reason the query does not seem to be pulling the row data from the DB correctly, I'm at a loss as to why it's not working. That said, I'm also new to MySQL and SQL in general. Does anyone have any ideas as to what's going on? Simply stated, why is the MySQL query not accessing data for the PHP code?

Any help will be much appreciated!

Thanks!

Update:

After changing the SQL query to:

categories.cat_id = " . mysql_real_escape_string($_GET['id']);

That solved the original problem, only to make another problem come to the fore.

I am now getting the error code:

echo 'Category could not be displayed! Please try again later' . mysql_error();

With SQL outputting: 

You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near '' at line 8

I actually encountered this problem before, but in my attempts to fix it came up with the original code I posted. I have similar code throughout my PHP files that works, which leads me to believe it's an error I've made in the DB code.

Solution

  • Yet to see what error you're returning, but in the mean time according to your table def, cat_id is an int.

    But you're passing it in surrounded by single quotes, meaning if it's a string you won't get an error in PHP, you'll get it from mySQL.

    Good practice is to change this

    categories.cat_id = '" . mysql_real_escape_string($_GET['id']) . "'";

    to

    categories.cat_id = " . intval( mysql_real_escape_string($_GET['id']) );

    and you'll be less likely to have DBMS errors if $_GET['id'] returns a string, or if the DBMS is set to strict_mode

    Ideally you might test your values taken from $_GET, and handle incorrect types gracefully before passing them in, thus avoiding either PHP or mySql generating an error.