function card_search($start, $end, $searchtype, $searchstring, $colors, $colortype, $set_id, $searchbyset)
{
global $db;
if (!empty($searchstring)) {
if (in_array('searchexactly', $searchtype)) {
$words = array($searchstring);
} else {
$words = explode(' ', $searchstring);
//this splits the search string into seperate words in an array
}
} else {
$words = NULL;
}
if (is_array($words)) {
foreach ($words as $word) {
if (preg_match("/ae/i", $word)) {
$new_word = add_accent($word);
array_push($words, $new_word);
}
}
}
$bindcount = 0;
$bindvariables = array();
$query = '';
//setup the basic query without any filters
$query1 = ' SELECT DISTINCT ' . ' card_name ' . ' FROM cards ' . ' WHERE 1=1';
//WHERE 1=1 is there so I dont have to work a WHERE clause into the following logic, I simply have to add AND and OR clauses
//add content filtering based on search string and searchtype
$first = TRUE;
if (is_array($words) && is_array($searchtype)) {
if (in_array('searchexactly', $searchtype)) {
$logic = 'OR';
} else {
$logic = 'OR';
}
foreach ($words as $word) {
//echo 'inside Foreach loop';
//Starts the loop by adding AND on front of the added search options only the first time the loop runs
if ($first) {
$query = $query . ' AND(';
}
if (in_array('name', $searchtype)) {
//echo 'inside name if';
if ($first) {
$query = $query . ' card_name LIKE :' . $bindcount;
$first = FALSE;
} else {
$query = $query . ' ' . $logic . ' card_name LIKE :' . $bindcount;
}
}
if (in_array('text', $searchtype)) {
if ($first) {
$query = $query . ' ability_text LIKE :' . $bindcount;
$first = FALSE;
} else {
$query = $query . ' ' . $logic . ' ability_text LIKE :' . $bindcount;
}
}
if (in_array('flavor', $searchtype)) {
if ($first) {
$query = $query . ' flavor_text LIKE :' . $bindcount;
$first = FALSE;
} else {
$query = $query . ' ' . $logic . ' flavor_text LIKE :' . $bindcount;
}
}
if (in_array('types', $searchtype)) {
if ($first) {
$query = $query . ' card_type LIKE :' . $bindcount;
$first = FALSE;
} else {
$query = $query . ' ' . $logic . ' card_type LIKE :' . $bindcount;
}
}
//this should close the AND added on the first loop cycle by adding the closing bracket
if ($bindcount == count($words) - 1) {
$query = $query . ' )';
}
$bindcount += 1;
//increment the bind value placeholder to place in the query
array_push($bindvariables, '%' . $word . '%');
//store the value of word and wrap it in %..% to make it work with the bind value placeholders
//these will have the same index ie :1 in the query will $bindvariables[1]
//do it this way so prepared statements can be used for security
}
}
//this loop will do much the same as the last, but for colors instead
$first = TRUE;
$colorcount = 0;
if (isset($colors)) {
foreach ($colors as $color) {
//Starts the loop by adding AND on front of the added search options only the first time the loop runs
if ($first) {
$query = $query . ' AND(';
}
//if its the first time through , done use OR or AND
//otherwise check to see if we should use OR or AND and use the correct one
if ($first) {
$query = $query . ' color LIKE :' . $bindcount;
$first = FALSE;
} elseif ($colortype == 1) {
$query = $query . ' AND color LIKE :' . $bindcount;
} else {
$query = $query . ' OR color LIKE :' . $bindcount;
}
//this should close the AND added on the first loop cycle by adding the closing bracket
if ($colorcount == count($colors) - 1) {
$query = $query . ' )';
}
$colorcount += 1;
$bindcount += 1;
//increment the bind value placeholder to place in the query
array_push($bindvariables, '%' . $color . '%');
//store the value of color and wrap it in %..% to make it work with the bind value placeholders
//these will have the same index ie :1 in the query will $bindvariables[1]
//do it this way so prepared statements can be used for security
}
}
//lastly check if they want to search by set, and if so, add that filter
if (isset($searchbyset)) {
if ($searchbyset == 1) {
$query = $query . ' AND set_id = :' . $bindcount;
$bindcount += 1;
array_push($bindvariables, $set_id);
}
}
//final touches
$query1 .= $query . ' ORDER BY card_name ASC, set_id DESC , variation_number ASC';
$query1 .= ' LIMIT :start, 50';
//run the query and prepare the bind variables;
$statement = $db->prepare($query1);
$count = 0;
foreach ($bindvariables as $variable) {
$statement->bindValue(':' . $count, $variable);
$count++;
}
$statement->bindValue(':start', $start, PDO::PARAM_INT);
//$statement->bindValue(':end', $end, PDO::PARAM_INT);
try {
$statement->execute();
} catch (PDOException $ex) {
//var_dump($query1);
//echo $ex;
return $ex;
}
//run a query to get how many total rows there are to populate page numbers, send it back to a global variable
//$statement2 = $db->prepare('SELECT FOUND_ROWS()');
//$statement2->execute();
global $row_count;
//var_dump($query1);
$row_count = get_row_count($query, $bindvariables);
//var_dump($row_count);
//$statement2->closeCursor();
$results = $statement->fetchAll();
//var_dump($bindvariables);
//var_dump($query);
//var_dump($results);
//var_dump($row_count);
return $results;
}
function card_search($start, $end, $searchtype, $searchstring, $colors, $colortype, $set_id, $searchbyset)
{
global $db;
if (!empty($searchstring)) {
if (in_array('searchexactly', $searchtype)) {
$words = array($searchstring);
} else {
$words = explode(' ', $searchstring);
//this splits the search string into seperate words in an array
}
} else {
$words = NULL;
}
//var_dump($words);
$bindcount = 0;
$bindvariables = array();
//setup the basic query without any filters
$base_query = ' SELECT DISTINCT ' . ' card_name ' . ' FROM cards ' . ' WHERE 1=1';
$filters = '';
//WHERE 1=1 is there so I dont have to work a WHERE clause into the following logic, I simply have to add AND and OR clauses
//add content filtering based on search string and searchtype
// add Filters for the words
$first = true;
$notfirst = false;
//if there are words
if (is_array($words)) {
//var_dump($words);
//var_dump($searchtype);
$filters = ' AND( ';
//if there are types selected
if (is_array($searchtype)) {
//if collectionname was selected
if (in_array('name', $searchtype)) {
//loop through as many times as there are words
if ($notfirst) {
$filters .= ' OR ';
}
foreach ($words as $word) {
if ($first) {
$filters .= '( card_name LIKE :' . $bindcount;
$bindcount++;
$bindvariables[] = '%' . $word . '%';
$first = false;
$notfirst = true;
} else {
$filters .= ' AND card_name LIKE :' . $bindcount;
$bindcount++;
$bindvariables[] = '%' . $word . '%';
}
if (preg_match("/ae/i", $word)) {
//echo 'Match Found';
$new_word = add_accent($word);
$filters .= ' OR card_name LIKE :' . $bindcount;
$bindcount++;
$bindvariables[] = '%' . $new_word . '%';
}
//
if (preg_match("/'/", $word)) {
//echo 'Match Found';
$new_word = str_replace(''', "'", $word);
$filters .= ' OR card_name LIKE :' . $bindcount;
$bindcount++;
$bindvariables[] = '%' . $new_word . '%';
}
}
$filters .= ')';
$first = true;
}
//if collectiondesc was selected
if (in_array('text', $searchtype)) {
//loop through as many times as there are words
if ($notfirst) {
$filters .= ' OR ';
}
foreach ($words as $word) {
if ($first) {
$filters .= '( ability_text LIKE :' . $bindcount;
$bindcount++;
$bindvariables[] = '%' . $word . '%';
$first = false;
$notfirst = true;
} else {
$filters .= ' AND ability_text LIKE :' . $bindcount;
$bindcount++;
$bindvariables[] = '%' . $word . '%';
}
if (preg_match("/ae/i", $word)) {
//echo 'Match Found';
$new_word = add_accent($word);
$filters .= ' OR ability_text LIKE :' . $bindcount;
$bindcount++;
$bindvariables[] = '%' . $new_word . '%';
}
//
if (preg_match("/'/", $word)) {
//echo 'Match Found';
$new_word = str_replace(''', "'", $word);
$filters .= ' OR ability_text LIKE :' . $bindcount;
$bindcount++;
$bindvariables[] = '%' . $new_word . '%';
}
}
$filters .= ')';
$first = true;
}
//if searchbyusername was selected
if (in_array('types', $searchtype)) {
//loop through as many times as there are words
if ($notfirst) {
$filters .= ' OR ';
}
foreach ($words as $word) {
if ($first) {
$filters .= '( card_type LIKE :' . $bindcount;
$bindcount++;
$bindvariables[] = '%' . $word . '%';
$first = false;
$notfirst = true;
} else {
$filters .= ' AND card_type LIKE :' . $bindcount;
$bindcount++;
$bindvariables[] = '%' . $word . '%';
}
if (preg_match("/ae/i", $word)) {
//echo 'Match Found';
$new_word = add_accent($word);
$filters .= ' OR card_type LIKE :' . $bindcount;
$bindcount++;
$bindvariables[] = '%' . $new_word . '%';
}
//
if (preg_match("/'/", $word)) {
//echo 'Match Found';
$new_word = str_replace(''', "'", $word);
$filters .= ' OR card_type LIKE :' . $bindcount;
$bindcount++;
$bindvariables[] = '%' . $new_word . '%';
}
}
$filters .= ')';
$first = true;
}
//if searchbycard was selected
if (in_array('flavor', $searchtype)) {
//loop through as many times as there are words
if ($notfirst) {
$filters .= ' OR ';
}
foreach ($words as $word) {
if ($first) {
$filters .= '( flavor_text LIKE :' . $bindcount;
$bindcount++;
$bindvariables[] = '%' . $word . '%';
$first = false;
$notfirst = true;
} else {
$filters .= ' AND flavor_text LIKE :' . $bindcount;
$bindcount++;
$bindvariables[] = '%' . $word . '%';
}
if (preg_match("/ae/i", $word)) {
//echo 'Match Found';
$new_word = add_accent($word);
$filters .= ' OR flavor_text LIKE :' . $bindcount;
$bindcount++;
$bindvariables[] = '%' . $new_word . '%';
}
//
if (preg_match("/'/", $word)) {
//echo 'Match Found';
$new_word = str_replace(''', "'", $word);
$filters .= ' OR flavor_text LIKE :' . $bindcount;
$bindcount++;
$bindvariables[] = '%' . $new_word . '%';
}
}
$filters .= ')';
$first = true;
}
}
$filters .= ')';
}
//add the closing bracket
//this loop will do much the same as the last, but for colors instead
$first = TRUE;
$colorcount = 0;
if (isset($colors)) {
foreach ($colors as $color) {
//Starts the loop by adding AND on front of the added search options only the first time the loop runs
if ($first) {
$filters = $filters . ' AND(';
}
//if its the first time through , done use OR or AND
//otherwise check to see if we should use OR or AND and use the correct one
if ($first) {
$filters = $filters . ' color LIKE :' . $bindcount;
$first = FALSE;
} elseif ($colortype == 1) {
$filters = $filters . ' AND color LIKE :' . $bindcount;
} else {
$filters = $filters . ' OR color LIKE :' . $bindcount;
}
//this should close the AND added on the first loop cycle by adding the closing bracket
if ($colorcount == count($colors) - 1) {
$filters = $filters . ' )';
}
$colorcount += 1;
$bindcount += 1;
//increment the bind value placeholder to place in the query
array_push($bindvariables, '%' . $color . '%');
//store the value of color and wrap it in %..% to make it work with the bind value placeholders
//these will have the same index ie :1 in the query will $bindvariables[1]
//do it this way so prepared statements can be used for security
}
}
//exclude unselected colors
$first = TRUE;
$colorcount = 0;
if (in_array('excludecolors', $searchtype)) {
$uncolors = array('W', 'U', 'B', 'R', 'G');
if (isset($colors)) {
//var_dump($uncolors);
for ($i = 0; $i < count($uncolors); $i++) {
foreach ($colors as $color) {
if ($uncolors[$i] == $color) {
//echo 'match found';
$uncolors[$i] = '';
}
}
}
$uncolors = array_filter($uncolors);
}
//var_dump($uncolors);
//remove the element in uncolors if they match
foreach ($uncolors as $uncolor) {
//Starts the loop by adding AND on front of the added search options only the first time the loop runs
if ($first) {
$filters = $filters . ' AND(';
}
//if its the first time through , done use OR or AND
//otherwise check to see if we should use OR or AND and use the correct one
if ($first) {
$filters = $filters . ' color NOT LIKE :' . $bindcount;
$first = FALSE;
} else {
$filters = $filters . ' AND color NOT LIKE :' . $bindcount;
}
//this should close the AND added on the first loop cycle by adding the closing bracket
if ($colorcount == count($uncolors) - 1) {
$filters = $filters . ' )';
}
$colorcount += 1;
$bindcount += 1;
//increment the bind value placeholder to place in the query
array_push($bindvariables, '%' . $uncolor . '%');
//store the value of color and wrap it in %..% to make it work with the bind value placeholders
//these will have the same index ie :1 in the query will $bindvariables[1]
//do it this way so prepared statements can be used for security
}
}
//lastly check if they want to search by set, and if so, add that filter
if (isset($searchbyset)) {
if ($searchbyset == 1) {
$filters = $filters . ' AND set_id = :' . $bindcount;
$bindcount += 1;
array_push($bindvariables, $set_id);
}
}
//final touches
if (count($bindvariables) > 0) {
$query = $base_query . $filters;
} else {
$query = $base_query;
}
$query .= ' ORDER BY card_name ASC, set_id DESC , variation_number ASC';
$query .= ' LIMIT :start, 50';
//run the query and prepare the bind variables;
$statement = $db->prepare($query);
$count = 0;
foreach ($bindvariables as $variable) {
$statement->bindValue(':' . $count, $variable);
$count++;
}
$statement->bindValue(':start', $start, PDO::PARAM_INT);
//$statement->bindValue(':end', $end, PDO::PARAM_INT);
try {
$statement->execute();
} catch (PDOException $ex) {
//var_dump($query);
//echo $ex;
return 'error';
}
$results = $statement->fetchAll();
//run a query to get how many total rows there are to populate page numbers, send it back to a global variable
//$statement2 = $db->prepare('SELECT FOUND_ROWS()');
//$statement2->execute();
global $row_count;
$row_count = get_row_count($filters, $bindvariables);
//var_dump($bindvariables);
//echo $query;
return $results;
}
function collection_search($text, $types)
{
global $db;
if (!empty($text)) {
$words = explode(' ', $text);
} else {
$words = NULL;
}
//var_dump($text);
//var_dump($words);
$bindcount = 0;
$bindvariables = array();
//Select the unique collections that can be filtered by the the desired criteria
$base_query = 'SELECT DISTINCT
c.collection_id
FROM
users u INNER JOIN collections c on u.user_id = c.user_id
INNER JOIN collection_items ci on ci.collection_id = c.collection_id
INNER JOIN cards ca on ci.card_id = ca.card_id
WHERE c.is_public = 1';
$filters = ' AND(';
// add Filters for the words
$first = true;
$notfirst = false;
//if there are words
if (is_array($words)) {
//if there are types selected
if (is_array($types)) {
//if collectionname was selected
if (in_array('collectionname', $types)) {
//loop through as many times as there are words
if ($notfirst) {
$filters .= ' OR ';
}
foreach ($words as $word) {
if ($first) {
$filters .= '( c.collection_name LIKE :' . $bindcount;
$bindcount++;
$bindvariables[] = '%' . $word . '%';
$first = false;
$notfirst = true;
} else {
$filters .= ' AND c.collection_name LIKE :' . $bindcount;
$bindcount++;
$bindvariables[] = '%' . $word . '%';
}
}
$filters .= ')';
$first = true;
}
//if collectiondesc was selected
if (in_array('collectiondesc', $types)) {
//loop through as many times as there are words
if ($notfirst) {
$filters .= ' OR ';
}
foreach ($words as $word) {
if ($first) {
$filters .= '( c.collection_description LIKE :' . $bindcount;
$bindcount++;
$bindvariables[] = '%' . $word . '%';
$first = false;
$notfirst = true;
} else {
$filters .= ' AND c.collection_description LIKE :' . $bindcount;
$bindcount++;
$bindvariables[] = '%' . $word . '%';
}
}
$filters .= ')';
$first = true;
}
//if searchbyusername was selected
if (in_array('searchbyusername', $types)) {
//loop through as many times as there are words
if ($notfirst) {
$filters .= ' OR ';
}
foreach ($words as $word) {
if ($first) {
$filters .= '( u.user_name LIKE :' . $bindcount;
$bindcount++;
$bindvariables[] = '%' . $word . '%';
$first = false;
$notfirst = true;
} else {
$filters .= ' AND u.user_name LIKE :' . $bindcount;
$bindcount++;
$bindvariables[] = '%' . $word . '%';
}
}
$filters .= ')';
$first = true;
}
//if searchbycard was selected
if (in_array('searchbycard', $types)) {
//loop through as many times as there are words
if ($notfirst) {
$filters .= ' OR ';
}
foreach ($words as $word) {
if ($first) {
$filters .= '( ca.card_name LIKE :' . $bindcount;
$bindcount++;
$bindvariables[] = '%' . $word . '%';
$first = false;
$notfirst = true;
} else {
$filters .= ' AND ca.card_name LIKE :' . $bindcount;
$bindcount++;
$bindvariables[] = '%' . $word . '%';
}
if (preg_match("/ae/i", $word)) {
//echo 'Match Found';
$new_word = add_accent($word);
$filters .= ' OR ca.card_name LIKE :' . $bindcount;
$bindcount++;
$bindvariables[] = '%' . $new_word . '%';
}
//
if (preg_match("/'/", $word)) {
//echo 'Match Found';
$new_word = str_replace(''', "'", $word);
$filters .= ' OR ca.card_name LIKE :' . $bindcount;
$bindcount++;
$bindvariables[] = '%' . $new_word . '%';
}
}
$filters .= ')';
$first = true;
}
}
}
//add the closing bracket
$filters .= ')';
if (count($bindvariables) > 0) {
$query = $base_query . $filters;
} else {
$query = $base_query;
}
$query .= ' ORDER BY c.collection_id ';
$statement = $db->prepare($query);
$count = 0;
foreach ($bindvariables as $variable) {
$statement->bindValue(':' . $count, $variable);
$count++;
}
try {
$statement->execute();
} catch (PDOException $ex) {
//var_dump($query);
//echo $ex;
return $ex;
}
$results = $statement->fetchAll();
return $results;
}
