[SOLVED] Need help with a query.

[JSHINER]

With the following:

$state = "CA";

$page['listings_state'] = RListing::getResults($db, $state, ($pageNum - 1) * $perPage, $perPage);

function getResults($db, $state, $offset, $limit) {
	$query = 'SELECT listing.id AS id, listing.views AS views, listing.price AS price, listing.type AS type, town.name AS town, town.state AS state, listing.town_id AS town_id FROM listing, town WHERE listing.town_id = town.id AND state = ' . $db->escape($state) . ' ORDER BY views DESC LIMIT 9';
	return $db->getArray($query);
}

 

I get the following error:

 

"Error in database query. Query was [sELECT listing.id AS id, listing.views AS views, listing.price AS price, listing.type AS type, town.name AS town, town.state AS state, listing.town_id AS town_id FROM listing, town WHERE listing.town_id = town.id AND state = CA ORDER BY views DESC LIMIT 9] and the error returned was [unknown column 'CA' in 'where clause']"

 

Any ideas why?

[adam291086]

are you sure the column state has CA in it?

[pocobueno1388]

Lets look at this part of the WHERE clause

AND state = ' . $db->escape($state) . '

 

First off you don't have the variable in quotes. Second you are using aliasing throughout the rest of the query, so why are you not using it for the column "state"?

 

So your query should be changed to

$query = "SELECT listing.id AS id, listing.views AS views, listing.price AS price, listing.type AS type, town.name AS town, town.state AS state, listing.town_id AS town_id FROM listing, town WHERE listing.town_id = town.id AND town.state = '".$db->escape($state)."' ORDER BY views DESC LIMIT 9";

[revraz]

Yep, it thinks you are looking for Column name CA and not data in the column.

[JSHINER]

When I put '".$db->escape($state)."' it now just brings up a blank white page .

[revraz]

Did you just copy and paste that one item or his whole line of code?

 

You need the double quotes around the query for it to work.

 

When I put '".$db->escape($state)."' it now just brings up a blank white page .

[JSHINER]

And those are exactly what I forgot  All set. Thanks!