MySQL

Count number of searches with with database/flat file

Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Mar 2008

Posts: 2

Reputation: im1 is an unknown quantity at this point

Solved Threads: 0

im1

Offline

Newbie Poster

Count number of searches with with database/flat file

Mar 10th, 2008

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)

•

Join Date: Jan 2008

Posts: 62

Reputation: hooray is an unknown quantity at this point

Solved Threads: 6

hooray

Offline

Junior Poster in Training

Re: Count number of searches with with database/flat file

Mar 10th, 2008

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:

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
$query=mysql_query("SELECT * FROM search_terms_table");
echo mysql_num_rows($query);
$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

•

Join Date: Mar 2008

Posts: 2

Reputation: im1 is an unknown quantity at this point

Solved Threads: 0

im1

Offline

Newbie Poster

Re: Count number of searches with with database/flat file

Mar 10th, 2008

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?

•

Join Date: Mar 2008

Posts: 26

Reputation: vl4kn0 is an unknown quantity at this point

Solved Threads: 4

vl4kn0

Offline

Light Poster

Re: Count number of searches with with database/flat file

Mar 12th, 2008

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

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
$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);
$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...

Last edited by vl4kn0; Mar 12th, 2008 at 1:16 pm.

Keep in mind that I'm not native english speaker....

•

Join Date: Dec 2007

Posts: 192

Reputation: mwasif is an unknown quantity at this point

Solved Threads: 26

mwasif

Offline

Junior Poster

Re: Count number of searches with with database/flat file

Mar 13th, 2008

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

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
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
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