MYSQL query select from column with multiple options.

General PHP Help
#1

I’m having trouble getting my head round this problem so the title may be misunderstood!

I have an art gallery website with a mysql database.

everything is fairly straightforward, a subset of column names are

Title, Artist, Medium, Price,…etc

All this works fine. I can display pictures by selecting from any of the columns.

Now I want to add another search based upon the pictures on display at a particular exhibition.

All the present columns have a single entry but the exhibitions column will have many entries. The list of exhibitions will grow at about 15 per year.

My search code is currently:
[php]$data = mysql_query("SELECT * FROM dbase WHERE type=’$type’);[/php]

What I’m looking for is a way to extend this to say
[php] ‘WHERE type=’$type’ & contains = “exhibition name X”’ [/php]
I could use a shorthand name for the exhibitions based upon “year+number” and some character to act as a list delimiter.

Or maybe there is another way, I’m open to suggestions, well, actually I’m getting desperate!

#2

Check this out… http://www.tutorialspoint.com/mysql/mysql-where-clause.htm your code should be something like

Blah Blah Blah WHERE condition 1 AND condition 2

The short answer is use AND not &

#3

Thanks for the suggestion, Andrew, but I don’t see how you can use <=> to get the answer. It needs a function like subnetting an ip address. If you assign each exhibition a number (a power of 2) then sum all the exhibitions a picture has been shown at ( like using the chmod shorthand notation), then you just have to see which picture has the matching exhibition number but I don’t know a mysql function that would do it.

I might be able to do it by having a table for each exhibition but that is going to get rather unwieldy.

Back to the drawing board!

#4

Without seeing some table structure and data it’s hard to understand the problem you are having. Perhaps you are looking for LIKE?

[php]
SELECT * FROM dbase WHERE type LIKE ‘%exhibition name X%’
[/php]

#5

In the end I used a diferent approach. I was trying to simplify the code but id doesn’t look like mysql has a string function that can spot a pattern in a long word or sentence.

So I built a 2 table for each exhibition one has a list of pictures the other has all the exhibition info. I then uses a mysql join query to select the picture data from the main table.

you can see the results at:
http://www.watermeadowstudio.co.uk/gallery.php?type=Ex&name=ex_12_sm_sfa&page=1

I’ve only got one exhibition running at present but the system works even if it is a bit clunky

#6

Of course it does.

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

Sponsor our Newsletter | Privacy Policy | Terms of Service