error in your SQL syntax - Getting this error and cannot pinpoint where the error is

Question

I am building a search function on my website and I am having some trouble locating an error in the SQL. There are four SQL statements used to return the counts of the same search using the AND and the OR keywords. The second set of SQL statements return the actual results of the AND and the OR searches limited according to the page number of the current search.

Below I will show the SQL function for the and count and and search. The code for the other two SQL statements is the exact same, only using OR instead of AND.

function _and_count($words) {

    require_once("include/conn.inc.php");

    $arraysize = count($words);

    $and_count_sql = "SELECT COUNT(DISTINCT tg_id)
                       FROM tg_keywords
                       WHERE tg_keyword=";

    if ($arraysize > 1) {
        foreach($words as $word){
            $count = 1;
            if ($count == 1){
                $and_count_sql .= "'".$word."' AND ";
            }
            elseif ($count < $arraysize){
                $and_count_sql .= "tg_keyword='".$word."' AND ";
            }
            else {
                $and_count_sql .= "'".$word."'";
            }
            $count++;
        }

    } elseif ($words == 1) {
        $and_count_sql .= "'".$words."'";
    }

    $and_count_result = mysql_query($and_count_sql)
        or die(mysql_error());

    return $and_count_result;

}


function _search_and($words, $startpos, $endpos) {

    require_once("include/conn.inc.php");

    $arraysize = count($words);

    $search_and_sql = "SELECT DISTINCT tg_id COUNT(*) AS nb
                       FROM tg_keywords
                       LEFT JOIN tg_info
                       ON tg_info.tg_id=tg_keywords.tg_id
                       WHERE tg_keyword=";

    if ($arraysize > 1){
        foreach($words as $word) {
            $count = 1;
            if ($count == 1 && $count < $arraysize) {
                $search_and_sql .= "'".$word."' AND ";
            } elseif ($count == 1 && $count == $arraysize) {
                $search_and_sql .= "'".$word."'";
            } elseif ($count < $arraysize) {
                $search_and_sql .= "tg_keyword='".$word."' AND ";
            } elseif ($arraysize == $count) {
                $search_and_sql .= "'".$word."'";
            }
            $count++;
        }
    } elseif ($arraysize == 1) {
        $search_and_sql .= "'".$words."'";
    } else {
        $search_and_sql .= "''";
    }

    $search_and_sql .= "GROUP BY tg_id
                        ORDER BY nb DESC, tg_info.date_added ASC
                        LIMIT ".$endpos." OFFSET ".$startpos;

    $search_and_result = mysql_query($search_and_sql, $link)
        or die(mysql_error());

    $and_array = mysql_fetch_array($search_and_result);

    return $and_array;


}

Any advice would be greatly appreciated. Thanks Archie

Answer 1

 $search_and_sql = "SELECT DISTINCT tg_id COUNT(*) AS nb

missing comma between tg_id and Count.

Its good practise to show generated query, ie. echo $search_and_sql; before mysql_query

Answer 2

You are setting $count = 1 at the beginning of each loop iteration and then testing if $count == 1 so your SQL probably has an extra AND at the end.

Answer 3

it looks like $count is always 1 inside the foreach loop, so you end up with a trailing AND

Answer 4

Isn't there an error here?

SELECT DISTINCT tg_id COUNT(*) AS nb

You should have a "," between tg_id and COUNT(*)

If the error doesn't come from here, could you please echo your SQL queries and give us the result?