$_GET for MySQL query causing Unknown column error

Question

I have a series of links that pass information to a new page to run a MySQL query. This is one of the links from source code:

<a class="bloglink" href="parknews.php?tpf_news.park_id=5">

and this is the code that generates the links:

<a class="bloglink" href="parknews.php?tpf_news.park_id=<?php echo $row2['park_id'];?>">
<?php echo $row2['name']; ?>
</a>

The query that uses that info is here:

$park_id = $_GET['tpf_news.park_id'];
$sql = 'SELECT headline, story, DATE_FORMAT(date, "%d-%M-%Y") AS date, name
FROM tpf_news
INNER JOIN tpf_parks ON tpf_news.park_id = tpf_parks.park_id WHERE tpf_news.park_id = $park_id ORDER BY date DESC' ;

This causes this error to display:

Error fetching news: SQLSTATE[42S22]: Column not found: 1054 Unknown column '$park_id' in 'where clause'

I can't work out why it is not working. If in the query I replace WHERE tpf_news.park_id = $park_id with WHERE tpf_news.park_id = 6 (or any other number), it works fine.

Any ideas?

score 1 · Answer 1 · answered May 08 '13 at 22:49

You have your SQL in single quotes. That means the variable will not be displayed as you think. Use double quotes.

And for the love of GOD us prepared statements.

$sql = "SELECT headline, story, DATE_FORMAT(date, "%d-%M-%Y") AS date, name
FROM tpf_news
INNER JOIN tpf_parks ON tpf_news.park_id = tpf_parks.park_id WHERE tpf_news.park_id=$park_id ORDER BY date DESC" ;

$sql = 'SELECT headline, story, DATE_FORMAT(date, "%d-%M-%Y") AS date, name
FROM tpf_news
INNER JOIN tpf_parks ON tpf_news.park_id = tpf_parks.park_id WHERE tpf_news.park_id='.$park_id.' ORDER BY date DESC' ;

score 1 · Accepted Answer · edited May 23 '17 at 12:29

When your strings are in quotes your variables aren't interpolated. So you need to use double quotes instead:

$sql = "SELECT headline, story, DATE_FORMAT(date, '%d-%M-%Y') AS date, name
FROM tpf_news
INNER JOIN tpf_parks ON tpf_news.park_id = tpf_parks.park_id WHERE tpf_news.park_id = $park_id ORDER BY date DESC" ;

Or use concatenation:

$sql = 'SELECT headline, story, DATE_FORMAT(date, "%d-%M-%Y") AS date, name
FROM tpf_news
INNER JOIN tpf_parks ON tpf_news.park_id = tpf_parks.park_id WHERE tpf_news.park_id =' .  $park_id .' ORDER BY date DESC' ;

FYI, you also wide open to SQL injections

$_GET for MySQL query causing Unknown column error

2 Answers2