Selecting data from MySQL DB using the ID in URL

Question

I'm trying to pull data in a database from the ID in the URL.

Basically someone clicks on a category link. Then the category name goes into the URL. So the URL looks like this when clicking on the "action" category: http://localhost:8888/test/category.php?id=action

I want to then grab everything in the database that equals to the category in the URL - so grab all "action" items in the database which is in a column called "category"

Here is the PHP code I'm working on below. I get this error: There was an error running the query [Unknown column 'action' in 'where clause']

PHP code:

<?php
$db = new mysqli('localhost', 'root', 'root', 'test');

if($db->connect_errno > 0){
    die('Unable to connect to database [' . $db->connect_error . ']');
}

$id = mysql_real_escape_string($_GET['id']);
$sql = <<<SQL
    SELECT *
    FROM `games_db`
    WHERE `category` = ($id)
SQL;

if(!$result = $db->query($sql)){
    die('There was an error running the query [' . $db->error . ']');
}
while($row = $result->fetch_assoc()){
    echo '<h2>' . $row['title'] . '</h2>' . ' ' . $row['description'] . '<br /><br />';
}


echo 'Total results: ' . $result->num_rows;

// Free result set
mysqli_free_result($result);

mysqli_close($db);
?>

it needs to be "$id", not ($id) so it compares against a string, otherwise it treats it as a column name. also, obligatory comment about not using mysql_*, and although it's good to see you're escaping the parameters -- prepared statements would be better again. — pala_
– pala_, Commented May 25, 2015 at 0:57
you're mixing APIs, this will never fire up mysql_real_escape_string — Funk Forty Niner
– Funk Forty Niner, Commented May 25, 2015 at 1:02

Funk Forty Niner · Accepted Answer · 2015-05-25 01:43:50Z

1

You're mixing MySQL functions with mysql_real_escape_string(), which doesn't work with any other API than its own.

Use its mysqli_ equivalent mysqli_real_escape_string(), which requires a DB connection.

Then quote the $id variable in the query, and not use brackets.

$id = mysqli_real_escape_string($db, $_GET['id']);
$sql = <<<SQL
    SELECT *
    FROM `games_db`
    WHERE `category` = '$id'
SQL;

Sidenote: "$id" will also work.

Reference(s):

http://php.net/manual/en/mysqli.real-escape-string.php

answered May 25, 2015 at 1:43

Funk Forty Niner

74.2k14 gold badges71 silver badges146 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Selecting data from MySQL DB using the ID in URL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related