Beginner SQL Question

I’m very inexperienced in SQL and am trying to setup a search for a site (the input is posted from a form) based on several template variables (ie retrieve all the page id’s where $town = ’new york’ and $location = ’city’).

It must be dead obvious, but I can’t figure out how to setup an AND query for multiple fields in the ’value’ column.

It works for ’$town’ OR ’$location’, but not ’$town’ AND ’$location’. This is what I have -

$sql="SELECT DISTINCT contentid FROM vs5090_1_site_tmplvar_contentvalues WHERE (value=’" . $town . "’) AND (value=’" . $location . "’)";

Can anyone point me in the right direction?

Quote from: c.brown at Nov 22, 2006, 08:15 PM

It works for ’$town’ OR ’$location’, but not ’$town’ AND ’$location’. This is what I have -

$sql="SELECT DISTINCT contentid FROM vs5090_1_site_tmplvar_contentvalues WHERE (value=’" . $town . "’) AND (value=’" . $location . "’)";

In this statement you are asking for rows where the "value" column is equal to $town AND equal to $location... each row will be evaluated against this criterion, and will necessarily fail because each row can have only one entry in the "value" column (a single value cannot simultaneously be equal to both ’New York’ AND ’city’).

Are you sure that "value" is actually the NAME of your database table column? I would normally expect a search like yours to be something like

SELECT contentid FROM contentvalues WHERE town = ’New York’ AND location = ’city’;

although I can’t be sure without knowing the schema (ie list of column names and their types) for the table vs5090_1_site_tmplvar_contentvalue...

Cheers

Gordon

Unless this is a "settings" kind of table, with a column "name" and a column "value", like the MODx system_settings table.

Quote from: sottwell at Nov 23, 2006, 01:07 AM

Unless this is a "settings" kind of table, with a column "name" and a column "value", like the MODx system_settings table.

A single row still can’t have two different entries for "value" simultaneously

But suppose you are right and that this is a sort of settings table.. then presumably that would mean that each "contentid" would occur in multiple rows, one for each relevant setting?

contentid name value
1            town  New York
1            location city
2            town Paris
2            location city
...

Then we’d be searching for a COMBINATION of two rows with the same id, but specified values for the town setting and location setting... if we are looking for two rows, then we need two "copies" of the table in the FROM clause...



Or use a nested query..

Gordon

I knew that!