Results 1 to 3 of 3

Thread: Problems with SELECT COUNT UNION

  1. #1
    Join Date
    May 2012

    Problems with SELECT COUNT UNION

    Hey guys, I am having a problem with counting data returned by a user search.

    If the user searches by the menu's my search and count works fine.
    No union commands used.

    But if the user does a search, i get the results fine, i just cant count the number of results.

    the code is:
    PHP Code:
        if ( isset ( $_SESSION['search'] )){ $searchword=validate($_SESSION['search']);
    $search "FROM Items WHERE code LIKE '%".$searchword."%' UNION 
            SELECT * FROM Items WHERE type LIKE '%"
    .$searchword."%' UNION 
            SELECT * FROM Items WHERE subtype LIKE '%"
    .$searchword."%' UNION 
            SELECT * FROM Items WHERE subsubtype LIKE '%"
    .$searchword."%' UNION 
            SELECT * FROM Items WHERE text LIKE '%"
    .$searchword."%' UNION 
            SELECT * FROM Items WHERE name LIKE '%"
    .$searchword."%' UNION 
            SELECT * FROM Items WHERE thickness LIKE '%"
    .$searchword."%' UNION 
            SELECT * FROM Items WHERE colour LIKE '%"
    .$searchword."%' UNION 
            SELECT * FROM Items WHERE shape LIKE '%"
    $finalsearch="SELECT * ".$search." ORDER BY " $order " DESC";
    $count "SELECT COUNT(*) " $search;    } 

    the error is 'The used SELECT statements have a different number of columns'

    I found this following example, it looks like what i need to do, but i just cant seem to translate it to my table and get it to work.

    PHP Code:
    SELECT f.*, u.*
         JOIN USERS 
    AS u ON u.uid f.fid2
        WHERE f
    AND f.fid2 1
       SELECT f
    .*, u.*
         JOIN USERS 
    AS u ON u.uid f.fid1
        WHERE f
    AND f.fid1 1
    LIMIT 6
    I dont know SQL well enough :-/

  2. #2
    Join Date
    May 2012

    I made

    PHP Code:
            $itemcount mysql_result(mysql_query("SELECT count(*) FROM Items WHERE code LIKE '%".$searchword."%' OR type LIKE '%".$searchword."%' OR subtype LIKE '%".$searchword."%' OR subsubtype LIKE '%".$searchword."%' OR text LIKE '%".$searchword."%' OR name LIKE '%".$searchword."%' OR thickness LIKE '%".$searchword."%' OR colour LIKE '%".$searchword."%' OR shape LIKE '%".$searchword."%'"), 0); 

    Though i dare say my sql could be optimized.

  3. #3
    Join Date
    Feb 2012

    I won't mind.

    When that count is returned you would not know where the items were found. In otherwords that count would not be so useful.

    Try the original post again and do a count of each field searched. You have nothing to join because all your data is in one table, therefore the main select is a selection from sub-queries, whose results are unioned. In this case do the count on the subqueries, so you have a union of counts.

    This should look like:
    but the result rows are field implicit. To avoild this progrmatically, put the field names into an array
    var $fieldNAMES = array {"type","subtype", "subsubtype"};
    Then male your select get the column names from $fieldNAMES so when the row data count is returned the program knows row 1 count is "type", and so on and so on...

    But then again you don't have to.
    Knowledge is that which can be shown to be the case, and Intelligence is the method one uses to deploy the demonstration of what is the case, everything else is Information.

    For a stronger Text reading try:

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
HTML5 Development Center

Recent Articles