PHP - Unable to pass URL query string to model for database query

Question

Using PHP 5.6 and PostgreSQL

I'm relatively new to PHP. I'm sorting results on a page using a query string, like so:

if (isset($_GET['sort'])) {

    $criteria = $_GET['sort'];
    $result = get_all_sorted_restaurants_with_limit($limit, $criteria); 

 } else {

    $result = get_all_restaurants_with_limit($limit); 

 }

where 'sort' is the url query string value. From doing a var_dump, I can see its correctly grabbing a string value.

Next, if the value is set, it calls the following method in my model:

function get_all_sorted_restaurants_with_limit($limit, $sort) {

    $conn = open_database_connection();

    $result = pg_query($conn, "SELECT * FROM restaurant r WHERE r.Type =
    $sort LIMIT $limit;");

    close_database_connection();

    return $result;

}

Here's where things get wonky: When I try doing it with $sort, in the get_all_sorted_restaurants_with_limit method, it returns nothing. However, if I set it myself (i.e. to 'sports bar', like so:

    $result = pg_query($conn, "SELECT * FROM restaurant r WHERE r.Type  = 'sports bar' LIMIT $limit;");

it works. The var dump shows that this is the exact same string that's being grabbed from the query url.

How would I fix this? Do I need to cast it, or grab a strval() (I've tried, doesn't work.... ).

_“The var dump shows that this is the exact same string that's being grabbed from the query url”_ – ah, so it includes the single quotes already …? — CBroe, Apr 11 '15 at 02:59
Learn to use http://php.net/manual/en/function.pg-last-error.php after every pg_ functions. Your query will be semantically wrong because you don't quote the variable. — LeleDumbo, Apr 11 '15 at 02:59
And btw., why do you call something “sorting”, when it is obviously actually just _filtering_ (which is what the WHERE clause does) …? — CBroe, Apr 11 '15 at 03:00
CBroe You're right, $filter would make more sense. I'll refactor. As for value, I saw that the output for $var_dump($sort) was "sports bar", because it was a string I think I assumed the quotes were included. My mistake. — Nick Dubus, Apr 11 '15 at 03:06

score 2 · Accepted Answer · answered Apr 11 '15 at 02:59

2

Looks like you're just missing the single quotes around $sort:

$result = pg_query($conn, "SELECT * FROM restaurant r WHERE r.Type = '$sort' LIMIT $limit;");

answered Apr 11 '15 at 02:59

Daniel Nugent

43,104
15
109
137

Yep that fixed it. Is there a reason why $sort takes single quotes and $limit doesn't? Is it because one is an integer and the other is a string? – Nick Dubus Apr 11 '15 at 03:01
@NickDubus it needs the surrounding single quotes because it's a string literal. See this answer, and the one below it as well: http://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks/11321508#11321508 – Daniel Nugent Apr 11 '15 at 03:33

score 2 · Answer 2 · answered Apr 11 '15 at 03:04

Seems you missed single quotes in your $sort variable but for to be sure try this,

function get_all_sorted_restaurants_with_limit($limit, $sort) {

        $conn = open_database_connection();

        $result = pg_query($conn, "SELECT * FROM restaurant r WHERE r.Type =
 '$sort' LIMIT $limit;");  // see $sort variable

     if($result)
     {
     //success, good to go
     }
    else
    {
     $error = pg_last_error($conn); 
     var_dump($error);
    }
        close_database_connection();

        return $result;     
    }

PHP - Unable to pass URL query string to model for database query

2 Answers2