Count number of searches with with database/flat file

[im1]

Ok I got a new multimedia search engine that im hoping to launch pretty soon. ( temporary link here )

I all ready got the number of mp3 downloads counter set up there using flat files, but I didnt do that. It was all ready done when I got the script. Now I wanna get another counter that counts the number of total searches.
Can anyone help?

(Either flat file or db is cool with me, which ever is easier)

[hooray]

I would guess you're using PHP since you're in the MySQL forum, so I would suggest simply making a new table in your database with all the things people have searched for (from the look of your page it looks like you already have that table) and use this code on that table:

$query=mysql_query("SELECT * FROM search_terms_table");
echo mysql_num_rows($query);

replace the "search_terms_table" bit with whatever the table name is and your done :)

[im1]

This probably would have worked but my database records slightly differently.
Instead of recording the search terms all in one table its split into 3 tables. This is because theres 3 different types of searches: pictures, vidoes and music.

and thats not all, in all the tables theres another column for the number of hits that word has had. So this means if I count the number of records in a table it wont count a word more than once.
So heres the really tricky bit...
Is there a way I could count up all the search terms and how many times they've been used in all 3 tables and then add them up?

[vl4kn0]

I don't know if I understand correct but you can try something like this

$sql = "SELECT COUNT(t1.id) AS music, COUNT(t2.id) AS videos, COUNT(t3.id) AS something_else FROM table_name1 t1, table_name2 t2, table_name3 t3 WHERE ...";
$result = mysql_query($sql);
$row = mysql_fetch_object($result);

and now you can call $row->video, $row->music etc as num of records in table...

[mwasif]

You can get the count of all these 3 in one query i.e.

SELECT SUM(hits) FROM (
SELECT SUM(hits_col) as hits FROM pictures
UNION ALL 
SELECT SUM(hits_col) as hits FROM vidoes
UNION ALL 
SELECT SUM(hits_col) as hits FROM music
) as temp

I have not tested this query, but it should work.