Searching for needle in haystack (php/mysql)

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
cent
Forum Newbie
Posts: 16
Joined: Wed Nov 16, 2005 2:23 pm

Searching for needle in haystack (php/mysql)

Post by cent »

Hi All,

In a field called "savedata" in a table (MySQL), we store data like:
1|3|5|10

It's basically a list of numbers that correspond to data from an array set.

I'm trying to search it with PHP and not having much luck.

Currently using:

PHP Code:

Code: Select all

$query = "SELECT * FROM database.table WHERE savedata LIKE '1'";

if record A has savedata = 1
and
record B has savedata = 12|14|7|1|2

then the above PHP code only finds record A even though record B has a 1 as well. I tried replacing the pipe character with a space so the numbers appears as words rather than a single string. No luck as well.

I tried LIKE '%1%' with no luck.

This seems more a MySQL problem than a PHP one.

Is there a way to do this or perhaps store the data differently in the field so that its searchable?

Best,
Cent
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

how many values can be in this field? If it's at or under 64 a SET field type will work. Otherwise, I think you may need to use a REGEXP expression statement.
cent
Forum Newbie
Posts: 16
Joined: Wed Nov 16, 2005 2:23 pm

Post by cent »

thanks feyd.

i'll try that and see how it works.

best,
cent
Post Reply