Problems with SELECT COUNT UNION

[PlecPicker]

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:

    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.

SELECT f.*, u.*
     FROM FRIENDS AS f
     JOIN USERS AS u ON u.uid = f.fid2
    WHERE f.fid1 = 1 
      AND f.fid2 > 1
UNION 
   SELECT f.*, u.*
     FROM FRIENDS AS f
     JOIN USERS AS u ON u.uid = f.fid1
    WHERE f.fid2  = 1 
      AND f.fid1 < 1
ORDER BY RAND()
LIMIT 6; 

I dont know SQL well enough :-/

[PlecPicker]

Nevermind

I made

        $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.

[WyCnet]

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.