Accessing MySQL DB row data using PHP

Questions

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.

Any thoughts?

Thanks for all the responses!

Answers

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 ]) );

http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html

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.

Source

License : cc by-sa 3.0

http://stackoverflow.com/questions/15608101/accessing-mysql-db-row-data-using-php

Related