SQL Question

Not sure if .htaccess file is correct. Also some fetch problems from mySQL

I have som problems with getting data from my mySQL database after i implemented a more clean URL path.

Instead of: www.example.com/index.php?id=headline

I would have this path: www.example.com/article/headline

My .htaccess file is looking like this, and the path seems to work:

RewriteEngine On

RewriteCond %{REQUEST_FILENAME} !-d
RewriteCond %{REQUEST_FILENAME} !-f

RewriteCond %{THE_REQUEST} (\s|%20)
RewriteRule ^([^\s%20]+)(?:\s|%20)+([^\s%20]+)((?:\s|%20)+.*)$ $1-$2$3 [N,DPI]
RewriteRule ^([^\s%20]+)(?:\s|%20)+(.*)$ /$1-$2 [L,R=301,DPI]

RewriteRule ^article/(.*)/?$ index.php?id=$1 [NC,L]

RewriteCond %{REQUEST_URI} !\.(gif|jpg|png|ico|css|js)$

Then this is the link you have to click at, for coming to the article-page:

<a href="article/'. urldecode($row["overskrift"]) .'">

And the fetching from this $row is going smoothly.

The problem is when i then have to $_GET the id, it is like it won't fetch the next step properbly:

if(isset($_GET['id'])) {
$id = $_GET['id'];
$DB = "SELECT * FROM post WHERE overskrift = " . $id;
$blogpost = $fetch->fetch_assoc();

An error appear on the last line, and i have struggled with this in about 3 hours now, but i really can't find the area where there is something wrong!
I can click on the link on the article on my frontpage, however i get this error when i am trying to enter an article:

Fatal error: Call to a member function fetch_assoc() on a non-object in /var/www/web/xxxxxxx/xxxxx.com/index.php on line 198

I hope someone can help.

Answer Source

Add a leading / to the rewrite rule match:

RewriteRule ^/article/(.*)/?$ index.php?id=$1 [NC,L]

Note that this would rewrite www.example.com/article/headline/something/else/ to www.example.com/index.php?id=headline/something/else, if that's not desired, use more restrictive character sets (like [A-z0-9_\- \.]) instead of .*

As for SQL, definitely suggest using PDO or similar to parameterize anything coming from request variables. Also worth doing some filter/sanitize on the variables before using them.

Worth outputting your SQL before running it so you can see it is as you expect. Also try copying that output and running it directly in mysql command line or via PHPMyAdmin (or another similar tool) to see if you get the data you expect.

if(isset($_GET['id'])) { 
   $id = $_GET['id'];
   $DB = "SELECT * FROM post WHERE overskrift = " . $id;
   echo '<pre>' . $DB . </pre>;
   $blogpost = $fetch->fetch_assoc();


Saw your response above. the issue with your SQL is that you are passing a string, but not encapsulating it in the SQL (note PDO paramterization would to this automagially for you). Your SQL line should read:

$DB = "SELECT * FROM post WHERE overskrift = '" . $id . "'";

But seriously.. don't do this. Parameterize it!

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download