SQL search on multiple fields problem...

[jerome merly]

example of songbook database : i have a mysql database and php search request in three fields at the same time if i search "soprano clown" i found it ... if i search "clown soprano" i have no result ... how to solve this problem by a search whatever the order .... many thanks if you can help ...

here is my php request :


function searchSongs($rowNum, $orderBy, $sortBy, $page, $search, $theme) {
//Create sql request
$search = str_replace(' ', '%', $search);
$sql = "";
$sqlCount = "";
if ($search != null) {

$sql = 'SELECT * FROM catalog WHERE (idc = "' . $search . '" OR file LIKE "%' . $search . '%" OR lang LIKE "%' . $search . '%" OR title LIKE "%' . $search . '%" OR artist LIKE "%' . $search . '%")';

// $sql = 'SELECT * FROM `catalog` WHERE (`idc` = "' . $search . '" OR `title` LIKE "%' . $search . '%" OR `lang`LIKE "%' . $search . '% "OR `file`LIKE "%' . $search . '%" OR `artist`LIKE "%' . $search . '%")';
$sqlCount = 'SELECT count(*) FROM `catalog` WHERE (`idc` LIKE "%' . $search . '%" OR `title` LIKE "%' . $search . '%" OR `lang`LIKE "%' . $search . '%" OR `file`LIKE "%' . $search . '%" OR `artist`LIKE "%' . $search . '%")';
}else{
$sql = 'SELECT * FROM `catalog`';
$sqlCount = 'SELECT count(*) FROM `catalog`';
}
if ($theme != null && $search != null && $theme != "theme" && $theme != "Theme") {
$sql = $sql . " AND `theme` = '" . $theme . "'";
$sqlCount = $sqlCount . " AND `theme` = '" . $theme . "'";
}
elseif ($theme != null && $search == null && $theme != "theme" && $theme != "Theme") {
$sql = $sql . " WHERE `theme` = '" . $theme . "'";
$sqlCount = $sqlCount . " WHERE `theme` = '" . $theme . "'";
}
if ($orderBy != null) {
$sql = $sql . " ORDER BY " . $orderBy;
$sqlCount = $sqlCount . " ORDER BY " . $orderBy;
}
if ($sortBy != null) {
$sql = $sql . " " . $sortBy.", title ASC";
$sqlCount = $sqlCount . " " . $sortBy;
}

$result1 = mysql_query($sqlCount);
$countAll = mysql_fetch_row($result1);

//Set Limit per page
$start = ($page-1)*$rowNum;
$sql1 = $sql.' LIMIT '.$start.' ,'.($rowNum+1);
$sql = $sql.' LIMIT '.$start.' ,'.$rowNum;
$result = mysql_query($sql);

$result1 = mysql_query($sql1);

$data = array();
$count = mysql_num_rows($result1);
while ($row = mysql_fetch_assoc($result)){
$song = array();
//Get the song info
//
$song['count'] = $count;
$song['file'] = $row['file'];
$song['artist'] = $row['artist'];
$song['title'] = $row['title'];
$song['format'] = $row['format'];
$song['theme'] = $row['theme'];
$song['idc'] = $row['idc'];
$song['etat'] = $row['etat'];
$song['lang'] = $row['lang'];

array_push($data,$song);
}
array_push($data,$count);
$response['song']=$data;
$response['countAll']=$countAll;
return json_encode($response);
}