Please help me with this database problem!

Hi,
I'm trying to create a new Module. In this module, I recorded search activity on the site in a database. For Example, when a user searches for something on the site, the keyword is recorded in a database.

Database info
Database: needbrea_ts
Table: dle_searchcloud
Row: term
Unique row (primary): id (auto_increment)

The search terms are recorded in the row term.

1. My first problem is that, when a user enters the same keywords, the database is recording it as a new value. I want it skip entering the record if the term already exists.
2. Secondly, i want to show the search terms recorded in the database as a tag cloud linked to /search/some-search-term.html

!!!Can somebody help me on this!!!

 

1. If all terms are gonna be unique, then remove auto_increment column and make term as primary key.
2. Add a column to store search count for that particular term. Then using a script, you can generate a tag cloud showing terms in different sizes depending on its search count.

 

That helps, but what is the script to generate the tag cloud. I have used the following php:

$term = array(); // create empty array
$query = mysql_query("SELECT term FROM dle_searchcloud");
while ($row = mysql_fetch_array($query))
{
$term = $row['term'];

}

But shows only 1 term.

 

nah, that is wrong way of generating tag cloud...

Refer to following links, they will be helpful..

http://scriptplayground.com/tutorials/php/Tag-Cloud/
http://www.roscripts.com/Create_tag_cloud-71.html

 

I have been successfully able to record the search terms in the database. Could you tell me how to just show all the values in the table. No need of tag cloud. Just need to skip the duplicates. See Attachment Please.

 

Share your table structure and I will give you new table structure which will take care of duplicate terms.

Mainly all you need is a primary key on terms field and you can also remove auto_increment since it is not needed.

 

See Pm for Table Structure.

 

Thanks for the New structure. I Implemented it. But how can i show the values in my site. No need of tag cloud. Just a normal table structure. if you don't mind, can you show that also.

 

values? as in you want to show this table on your website?
Well, then you need to use php and write a small script to display it on website in desired manner..

 

No. I want to show those tags as simple html instead of tag clouds etc. See attachment for a clear picture.

That image is not from my site. I attached that image just for reference.

 

Yes, for simply displaying values from mysql to browser of user you need to use php.

here is how it works:
1. php connects to mysql database, queries data.
2. php generated html to be sent to user's system.
3. user's browser on once receives html displays it..

 

Can you guide me. I used this code.

$query = "SELECT tag FROM dle_search";
$result = mysql_query($query);

while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
echo "Recent Searches :{$row['tag']} <br>";
}

 

I got it. Thanks a lot. But if there are 5000 tags (or search terms). Does the above code display all. It will become messy and also increases load time. If you can just guide me through that, i will be full and final.

 

you can do pagination to show only limited tags every time.

query will be something like this..

query = "SELECT tag FROM dle_search limit 0, 100";

to show next 100,

query = "SELECT tag FROM dle_search limit 100, 100";

you need to maintain start and end in php to pass onto query and then do the necessary programming

you can also search pagination in php on google for more detailed help on how to program it.

 

I want to ask you 1 final question. Everything is up and fine upto now. Just 1 q. I want the newest one to be at the top. i know it can be done by grouping. Probably by timestamp.

 

for newest one to be on top, you need to add datetime in database.

1. Add column with datatype as datetime in table.
2. everytime you insert a tag in table, insert current datetime as well.
3. while selecting, order by datetime field in descending order.

This will serve your purpose.

 

can i use this to add date into the table.
mysql_query("INSERT INTO dle_search (date)
VALUES ('CURDATE()')");

 

put it this way..

Keep column name as "entry_date", datatype as datetime

INSERT IGNORE INTO dle_search (news_id, tag, entry_date) VALUES (0, 'HDD Temperature', NOW())


select would be..

SELECT tag FROM dle_search ORDER BY entry_date DESC LIMIT 0, 100

 

Thank you mastermunj. I was successfully able to create this module. This module would have actually cost a lot in the market. With your help, I saved a lot of time and money. Thank you.