Performing a MYSQL query based off of $_GET results

Question

When a user clicks an item on my items page, it takes them to blank page template using $_GET to pass the item brand and model through.

I'd like to perform another MYSQL query when that user clicks through to populate the blank page with the product details from my database. I'd like to retrieve the single row using the model number (unique ID) to populate the page with the information. I've tried a couple of things but am having a little difficulty.

On my blank item page, I have

                $brand = $_GET['Brand'];
                $modelnumber = $_GET['ModelNumber'];

                $query = mysql_query("SELECT * FROM items WHERE `Model Number` = '$modelnumber'");
                $results = mysql_fetch_row($query);

                echo $results;

I think having ''s around Model Number is causing troubles, but without them, I get a Warning: mysql_fetch_row() expects parameter 1 to be resource, boolean given error.

My database columns looks like

Brand | Model Number | Price | Description | Image

A few other things I have tried include

$query = mysql_query("SELECT * FROM item WHERE Model Number = $_GET['ModelNumber']");

Which gave me a syntax error. I've also tried concatenating the $_GET which gives me a mysql_fetch_row() expects parameter 1 to be resource, boolean given error

Which leads me to believe that I'm also going about displaying the results incorrectly. I'm not sure if I need to put it in a where loop like I have with my previous page which displays all items in the database because this is just displaying one.

**WARNING!** Your code contains an [SQL injection vulnerability](http://en.wikipedia.org/wiki/SQL_injection) -- you're passing unfiltered user input ($_GET) directly into an SQL string. Please [switch to PDO](http://php.net/book.pdo) or [mysqli](http://php.net/book.mysqli) so you can use [prepared statements with parameterized queries](http://en.wikipedia.org/wiki/Prepared_statement). — Charles, Dec 11 '12 at 22:54
Also, you're totally not doing any error checking. `mysql_query` returns false (hint: it's the boolean value being whined about) when there was an error. Check the result of `mysql_error` and see what it says. Oh, and for goodness' sake, *don't* just `mysql_query(...) or die ...`, that's a massive anti-pattern and code smell. — Charles, Dec 11 '12 at 22:55
@Charles, Would a mysql_escape_string (I think it was) help? — , Dec 11 '12 at 22:59
no, a switching to PDO or mysqli would help. Both have sanitizing solutions that make learning about mysql_ functions pointless — Kai Qing, Dec 11 '12 at 23:01
Combining [`mysql_real_escape_string`](http://php.net/mysql_real_escape_string) and [`mysql_set_charset`](http://php.net/mysql_set_charset) will be adequate, yes. Yes, `_real_`. It matters. However, using prepared statements makes it *much easier*, thus the recommendation to switch to PDO or mysqli. (Watch out, mysqli's prepared statement API is ugly.) — Charles, Dec 11 '12 at 23:01
It seems that [mysql_escape_string does not do unicode properly](http://ilia.ws/archives/103-mysql_real_escape_string-versus-Prepared-Statements.html) — hd1, Dec 11 '12 at 23:02
@hd1, [that's been corrected in newer MySQL versions](http://stackoverflow.com/a/5139286/168868). The linked article is from 2006. — Charles, Dec 11 '12 at 23:02

score 0 · Answer 1 · answered Dec 11 '12 at 23:00

0

First, notice the first comment on your question. You definitely want to add some sort of sanitation, mysql_real_escape_string at the very least, although PDO or Mysqli would be preferred.

Second, before getting a real answer to your question, let's get some more information about your error.

Try the following:

$brand = mysql_real_escape_string($_GET['Brand']);
$modelnumber = mysql_real_escape_string($_GET['ModelNumber']);

$query = mysql_query("SELECT * FROM items WHERE `Model Number` = '$modelnumber'")OR die(mysql_error());
$results = mysql_fetch_row($query);

var_dump($results);

mysql_error tell us what is going on behind the scenes.

answered Dec 11 '12 at 23:00

kmoney12

4,413
5
37
59

Running that, I seem to get information about the database result like so. array (size=7) 0 => string '23501' (length=5) 1 => string 'SMEG' (length=4) 2 => string 'Smeg' (length=4) 3 => string 'SA35MX' (length=6) 4 => string '445.45' (length=6) 5 => string ' SMEG MICROWAVE OVEN SA35MX 34L S/STEEL' (length=39) 6 => string '' (length=54) – Dec 11 '12 at 23:04
That's alright. Do you recognize the information in the var_dump as values from your table? If so, your query seems to have executed successfully. – kmoney12 Dec 11 '12 at 23:07
I do! I think combining this knowledge with ntgCleaners answer will help me resolve this problem. – Dec 11 '12 at 23:10
Okay, also, as Yves suggested, I would recommend changing mysql_fetch_row to mysql_fetch_array – kmoney12 Dec 11 '12 at 23:11

score 0 · Accepted Answer · answered Dec 11 '12 at 23:01

It seems that your "result" is pulling in too much information for a single variable. Also, I don't think your table should have column names with spaces, I would suggest filling them all with dashes or underscores.

Here's my suggestion:

$qry = mysql_query("SELECT * FROM items WHERE Model_Number = '$modelnumber'"); //REMEMBER TO MAKE THE CHANGE "Model Number" -> "Model_Number"
while($row = mysql_fetch_array($qry)){
    $brand = $row['Brand'];
    $modelnumber = $row['Model_Number'];
    $price = $row['Price'];
    $description = $row['Description'];
    $image = $row['Image'];
}

This will populate all of these variables with whatever you have in your tables.

Ah yes, I see. I have something similar on my previous page, but wasn't sure if a single result was handled in a similar fashion. — , Dec 11 '12 at 23:15

score 0 · Answer 3 · answered Dec 11 '12 at 23:09

If you are using mysql_fetch_row it will only returns a numerical array of strings that corresponds to the fetched row, or FALSE if there are no more rows http://php.net/mysql_fetch_row

If you need to retrieve the data inside that row. You need to use mysql_fetch_array()

also try using mysql_error();

 $results = mysql_fetch_row($query) or die(mysql_error());

you will see the error output produce by your code

Performing a MYSQL query based off of $_GET results

3 Answers3