Ad:

MySQL Discussion Thread
Unsolved
Views: 1286

Mar 10th, 2008

Count number of searches with with database/flat file

Expand Post »

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)

Similar Threads

memory management in wndows 2000 in Windows NT / 2000 / XP

im1

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

2 posts
since Mar 2008

Permalink

Mar 10th, 2008

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

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:

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

hooray

Reputation Points: 11

Solved Threads: 6

Junior Poster in Training

Offline

62 posts
since Jan 2008

Permalink

Mar 10th, 2008

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

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?

im1

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

2 posts
since Mar 2008

Permalink

Mar 12th, 2008

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

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

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.

vl4kn0

Reputation Points: 27

Solved Threads: 4

Light Poster

Offline

26 posts
since Mar 2008

Permalink

Mar 13th, 2008

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

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

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

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

mwasif

Reputation Points: 29

Solved Threads: 47

Posting Whiz

Offline

312 posts
since Dec 2007

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MySQL Forum Timeline: Query syntax not working

Next Thread in MySQL Forum Timeline: How to create a trigger to UPDATE

DaniWeb Message

Cancel Changes

User Name
Password