LIKE %s with comma-separated entry

Question

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);

David_Powers · Accepted Answer

%'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.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"));

bregent · Answer

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.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded