www.webdeveloper.com
Results 1 to 3 of 3

Thread: Problems with SELECT COUNT UNION

  1. #1
    Join Date
    May 2012
    Posts
    60

    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 '%"
    .$searchword."%'";
            
    $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.*
         
    FROM FRIENDS AS f
         JOIN USERS 
    AS u ON u.uid f.fid2
        WHERE f
    .fid1 
          
    AND f.fid2 1
    UNION 
       SELECT f
    .*, u.*
         
    FROM FRIENDS AS f
         JOIN USERS 
    AS u ON u.uid f.fid1
        WHERE f
    .fid2  
          
    AND f.fid1 1
    ORDER BY RAND
    ()
    LIMIT 6
    I dont know SQL well enough :-/

  2. #2
    Join Date
    May 2012
    Posts
    60
    Nevermind

    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
    Location
    youTUBE
    Posts
    234

    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:
    12
    45
    83
    0
    80
    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.

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