LIKE %s with comma-separated entry

[Guest]

I am trying out to get a recordeset WHERE the column nameArtist 'contains' the URL parameter "artist". For example if "Picasso" is in the parameter and then it should find all projects which Picasso worked on. The problem is that often the column nameArtist contains more artist e.g. "Mondrian,Picasso,Chagall". it still should filter this project.

I tried with LIKE, but this does not give the projects when there are more artists.

I hope someone can give me a tip what I do wrong here.. Thankx.

$colname_rsAllprojects = "-1";
if (isset($_GET['artist'])) {
  $colname_rsAllprojects = $_GET['artist'];
}
mysql_select_db($database_connCasco, $connCasco);
$query_rsAllprojects = sprintf("SELECT * FROM `2008_content` WHERE nameArtist LIKE %s ORDER BY projectyear DESC", GetSQLValueString($colname_rsAllprojects, "text"));
$rsAllprojects = mysql_query($query_rsAllprojects, $connCasco) or die(mysql_error());
$row_rsAllprojects = mysql_fetch_assoc($rsAllprojects);
$totalRows_rsAllprojects = mysql_num_rows($rsAllprojects);

[bregent]

Depending on where the search term is located, you might need to put the wildcard character both before and after. In other words, if you are looking for Picasso in "Mondrian,Picasso,Chagall", then %Picasso won't find it. You need to use %Picasso% because other characters appear both before and after it.

And I'll again mention, putting more than one piece of data into a character field like this is a bad idea. It seems easier at first, but it always causes problems in the long run.

[Guest]

If I put WHERE nameArtist LIKE %s% I get the error message: "Too few arguments in ...Query was empty"

Now I already have more characters in a data field, but how is this normally solved if there ARE more artists working on one project?

[bregent]

>If I put WHERE nameArtist LIKE %s%

OK, %s is a php replaceable parameter. The % here is not the sql wildcard. In order to use the sql like predicate, you need to use the SQL % wildcard, so you need to wrap the wildcard(s) around your parameter value, not around the parameter itself.

>Now I already have more characters in a data field,

>but how is this normally solved if there ARE more

>artists working on one project?

You create a separate table to store the project/artist relationships.

I believe that MySQL has a non standard datatype that allows you to store and search for values in comma seperated strings, but this is the wrong way to store such relationships.

[Guest]

%'Picasso'% this is a wildcard? It is also putting the same error.

Is there not something like "LIKE" but more going searching inside the comma-separated entries? something like "CONTAINS"?

If I make the a project/artist table, it will also have one artist on one row and in the column "project" it will be comma-separated entries, because the artist could work on endless projects. And the way round: one project could have more artist working on it.

[David_Powers · Accepted solution]

Bregent is correct.You need to put % before and after the value you're searching for. However, just putting it like that in the SQL query won't work, because Dreamweaver uses sprintf() to pass the value to a custom function called GetSQLValueString() to prevent SQL injection. The % needs to be added to the value passed to GetSQLValueString().

Dreamweaver normally does this automatically when you add the % to the variable in the SQL field of the Advanced Recordset dialog box. However, I seem to remember that the way you did this in the dialog box changed in CS3 or CS4. Regardless of which version you're using, it should work in all versions if you edit the PHP code like this:

$query_rsAllprojects = sprintf("SELECT * FROM `2008_content`
WHERE nameArtist LIKE %s ORDER BY projectyear DESC", 
GetSQLValueString("%" . $colname_rsAllprojects . "%", "text"));