MYSQL :: Post Stats For Every File Looked Up?

'localhost Could Not Be Looked Up......'
I ran mysql_install_db and I get that 'localhost could not be looked up with ../resolveip'. I looked in my hosts file and the entry 127.0.0.1 is there for localhost and if i do resolveip localhost it spits that number back out.

I have linux suse, and I am installing the i586 rpm . There was already some parts of mysql on, but no server and the suse disc said bad package when I tried to install just the server part. So I downloaded the whole rpm and istalled.

Stats Query - If You Are Pro, Help Me :D
I've taken over developing a statistics page for a client.
The statistics are recorded in a table named 'visitors'.
This table is about 35,000+ records at the moment.
I need to be able to display total site hits and new visitor hits for periods such as today, this week, month and year.

It creates a new record on every page thats visited on the website.
The table has 7 fields:
-----------------------------
ID
REMOTE_ADDR - logs visitors ip address
HTTP_USER_AGENT - logs browser
HTTP_REFERER - logs referal if exists
SESSION_ID - logs visitors session id
PAGE - logs page visited
DATE_ADDED - date record created - timestamp
-----------------------------

Currently the ID fields are used to test if the current remote_addr has a previous record. This is very resource intensive to test each 35,000 rows against each other. Can anyone suggest a better system or a solution?

Thanks in advanced!

I've added an example of the code the previous developer was working on.
The sql below is meant to split up the day by hour showing total hits and new visitors for a specific day.
I've tested the SQL and it times out on the new visitor 'if function'.

SELECT
CONCAT(
DATE_FORMAT(date_added, '%k'),
':00 - ',
DATE_FORMAT(date_added, '%k'),
':59'
) as `day`,

DATE_FORMAT(date_added, '%W %e %M %Y') AS act_day,

COUNT( DISTINCT REMOTE_ADDR ) AS un_total,
COUNT( DISTINCT session_id) AS site_hits,

COUNT(IF(
v1.REMOTE_ADDR IN (
SELECT v2.REMOTE_ADDR
FROM visitors AS v2
WHERE v2.id < v1.id
GROUP BY REMOTE_ADDR
),
NULL,
1
) ) AS un_new,

FROM visitors AS v1
WHERE DATE(date_added) = DATE('".$_GET['day']."')
AND page != 'REFERAL'
GROUP BY HOUR(date_added)
ORDER BY date_added DESC;

Stats Not Logging
My stats arnt being logged. Page loads with no errors..

$update = "UPDATE `log` SET hits='hits + 1' WHERE ip='$ip' AND date='$date'";
if (mysql_query($update) or die(mysql_error()) ) {
} elseif($logged[username]) {
mysql_query("INSERT into log (ip, hits, date, user) VALUES ('$ip', '1', '$date', '$logged[username]'" or die(mysql_error()) );
}
else{
mysql_query("INSERT into log (ip, hits, date, user) VALUES ('$ip', '1', '$date', '$user'" or die(mysql_error()) );
}?

Index Use Stats
Is there some way to obtain some sort of statistics of how often, or how much is any given index used for queries? What I want to know is if an index is not used so it can be erased or changed, because it is being of no help, and just wastes space and slows down insertions.

Running Update Stats
I am trying to run an update stats command but keep getting a syntax error:
Looking at the doco it looks like I can use:

update stats <table_name>;

but when I use:

mysql> update stats x_balance;
ERROR 1064: You have an error in your SQL syntax near 'x_balance' at line 1

What am I doing wrong.

Running Stats On Data
I have a table (artist_nodes) that refers to a number of keywords (around 100) (nodes table) associated with a number of artists (artists table) i.e.

artist_nodes table

col 1 - id_artist_node (primary key)

col 2 nodeID_artist_nodes

col 3 artistID_artist_nodes

the content of each column is a number code that relates to the primary keys of content in the artists and nodes tables.

Is there a method for counting the number of distinct values in the nodeID_artist_nodes column, summing these values, and calculating how often values occur with each other?

So for example, if 1 = installation, and 45=interior and 46= exterior

i am looking for the number of instances of 1, 45, and 46, and also how often each occurs with the others when associated with the same artistID (as a percentage)

so the output i am looking for in php would be something like

installation (1) = 35 instances (3% of sample)

interior (45) = 23 instances (1% of sample)

exterior (46) = 30 instances (2%)

installation with interior = 10 instances (x %)
installation with exterior = 20 instances (y %)
installation with exterior and interior = 4 instances (z %)

is there a way of doing the inverse of this, i.e seeing which keywords never occur together?

Trying To Conceptualize Table Design For Stats
I have a stats page my users can go to so they can view how much traffic their listing has received.

Creating this for the total views is straight forward and I have that working. But I want to add two more features to this:

1. See the total views for just today
2. See a history for the listing over the duration of the post (ie. 30 days).

I am at a lost how I would create a table or several tables to accomplish this. I am looking for some guidance to get me thinking about the best way to do this.

Conceptualize Table Design For Stats
I have a stats page my users can go to so they can view how much traffic their listing has received.Creating this for the total view stat is straight forward and I have that working. But I want to also add two more features to this:

CODE1. See the total views for just today
2.See a history for the listing over the duration of the post (ie. 30 days).

How To Setup A MySQL Table To Keep Track Of Stats?
I have a new system Im building that stores entries of what people are
searching for on my sites.

I want to be able to keep records of how many times a keyword was
searched for daily, and from that I can calculate weekly and monthly.

At this point I have one entry per search phrase with the number of
hits the search phrase has gotten, and the last time it was updated.

As I start to take the program out of testing and move in more into a
used tool Im getting worried that my idea of switching to mutable
entries for the same search phrase would be a bad idea as within the
last 15 days I have stored more then 3 million unique search phrases,
and a unknown number of hits.

So should I make a rolling database that stores each search then rolls
that data over to another database once a week that users can use, or
is their a better way of doing it where I only keep one entry per
search phrase and am still able to keep records how daily search
amounts so that I can track trends, etc in search phases on my sites?

Table Design For Storing Item Stats
I've created quite the database for my app, and I'm familiar with normalization and general db design. However, I'm ready to design my tables for recording all sorts of stats for my db items.

I want to be able to view stats for each item, such as number of small views, number of large views, number of downloads, ratings and so on. I also want to be able to view these items by periods of time, such as by day, week, month, by multiple months, by year, etc. So, I'm trying to figure out what the most efficient way of recording this data, so that I can keep my tables as small as possible. I'm wondering if I will have to insert a row for every stat, or if I should enter a row for every day, or only a day if there is activity, etc.

My Post Of 9/10
I am really wrapped around the axle on this, and I'd appreciate some insights

Post Counters
I am new here. Stumbled along the forum whilst trying to find a help file in regards to Post Counters for my Forum.
I was looking for statistics on my Highest Poster for the month and also highest New-member Poster for the month was.
Am hoping that this kind of information is available through the MySQL somewhere.

Post New Record
I have a table with orders, and i'm using the field OrderID (auto_increment) to store an ID for each order. (every new order, gets an ID +1 of previous).

I use a php script to register the orders, and here is the big problem: I want the OrderID of the new order record to be shown to the user, right after he posted the order. Basicly, i want to know what value the OrderID (auto increment) will get.

I assume theres no function to predict that, but are there any functions that will bring me the ID value as soon as the record is added?

Viewing Post Trends
What are some techniques for viewing long-term trends of posted data?

i.e. In a forum, what would be one way to examine how many posts have been made each day for the past month? Assume a data structure similar to this:

table_posts
-----------
post_id - unique integer id
post_time - integer unix timestamp of the time the post was made
message_title
...

Table Name From Post Variable.
Why doesn't this work - I can't figure it out.

$txt1 = $_POST[Title];

$sql2 = "CREATE TABLE $txt1 (
ThreadTitle varchar(9999),
Date varchar(12),
Username varchar(12)
)";
mysql_query($sql2,$con);

echo "Your Forum Was Successfully Created. Please Return <a href='index.php'>Here</a>.";
mysql_close($con);
?>

Post Install Of 4.0 Complications
I just installed mySql 4.0.14-0 onto my redhat 9.0 based laptop. Everything works fine except that there are ten copies of mysqld running. I have removed the /etc/my.cnf file, changed the file, restored the file and nothing changes the number of daemons running. Another guy I worked with installed the same rpms from Mysql.org and ther is only one daemon running.

Test Post Please Ignore
this is just a test post to see if my newsreader is working.

POST Submit And MySQL
Probably something stupid, but I don't get this..
When I put a value in the database I first use addslashes (PHP):

$lastname = addslashes($lastname);

In my MySQL database I see this for example: D'Hoore
When I do a search on D'Hoore through a POST form and echo the value submitted I get this: D'Hoore
But when I do a SELECT * FROM contact WHERE lastname LIKE '%$lastname%'; it doesn't return anything!Should I use addslashes() on the search term? Stripslashes? Nothing seems to work?

POST Method And Empty Fields
I am using the POST method and Update to edit my database records with an HTML form.

I can successfully update the fields I want, but I don't always want to update all fields. However, when the form is sent the empty fields are updated with a value of zero.

How can I 'ignore' the empty fields so I can just simply edit or update the fields I want to?

Radio Button Values Won't Post To SQL DB
I have a form with radio buttons. I typically don't use them and thought I would take the time to figure them out this time. The values won't post to the database. Please let me know what I'm doing wrong here. The joint field is a yes/no question with 2 radio buttons: Code:

How Do I Post My Tables For Viewing Via My Website?
I can view all my tables on my server/host site, but can't figure out how to post the table I want to be able to view and enter data into via my website.

My website under "my admin" feature was set up to let me enter a persons username, password and email address. However, I can only view "username" on my website under admin. I want to be able to add new data, like the date to my website so that I can enter the date and view it on my website. Do I need to add php or html code to my existing webpage that lets me view my database?

How Do I Change The Date Of A Post On My Forum Site?
Hi, I have started a forum site with a couple of friends. The forum runs on xoops using php 4 with a mysql (4) database. I accidently deleted a few posts. No problem there because I had a copy of the text and posted it again. But the problem is the date and time - obviously it shows the date of the new post and not the date of the deleted one. As I am a newbie still experimenting with mysql and php, I'd sure appreciate your help.

Summary:
=======
I need to change the date of a post in the mysql database. I access the database using phpmyadmin.

POST Fields From Dynamic Table List
Scenario:
I'm connecting to "points" table in a Records posting form.

SELECT pointCode, pointamt, pointcount, amt_type FROM points
Now I do the select list and tell it to only display "pointName" in the form

So far so good. Then I get lost lol. I don't know where to go from here to get those values of the pointCode, pointamt, pointcount, and amt_type set up for POST once the user has made their selection. I'm thinking they are locked up nice and snug in my $row var but not sure and if so, how do I get them OUT?

Post Comment To Documents With Code Highlighting
I was just curious, but is there a way to post comments to the website documentation with code highlighting.

Making A Column The Primary Key Post Facto
I've created a table for which I intended the email column to be the primary key,
but didn't specify it at creation. Now, I'm trying to figure out how
to specify it post facto.

Keeping Track Of Post Views Every Day (Table Structure)
I was thinking of having a table to keep track of post count every day.

For example, i have the following table structure:

CREATE TABLE `wp_views` (
`post_id` bigint(20) NOT NULL,
`year` tinyint(4) NOT NULL,
`month` tinyint(2) NOT NULL,
`month_hits` int(7) NOT NULL default Ɔ',
`day_1` int(6) NOT NULL default Ɔ',
`day_2` int(6) NOT NULL default Ɔ',
`day_3` int(6) NOT NULL default Ɔ',
`day_4` int(6) NOT NULL default Ɔ',
`day_5` int(6) NOT NULL default Ɔ',
`day_6` int(6) NOT NULL default Ɔ',
`day_7` int(6) NOT NULL default Ɔ',
`day_8` int(6) NOT NULL default Ɔ',
`day_9` int(6) NOT NULL default Ɔ',
`day_10` int(6) NOT NULL default Ɔ',
`day_11` int(6) NOT NULL default Ɔ',
`day_12` int(6) NOT NULL default Ɔ',
`day_13` int(6) NOT NULL default Ɔ',
`day_14` int(6) NOT NULL default Ɔ',
`day_15` int(6) NOT NULL default Ɔ',
`day_16` int(6) NOT NULL default Ɔ',
`day_17` int(6) NOT NULL default Ɔ',
`day_18` int(6) NOT NULL default Ɔ',
`day_19` int(6) NOT NULL default Ɔ',
`day_20` int(6) NOT NULL default Ɔ',
`day_21` int(6) NOT NULL default Ɔ',
`day_22` int(6) NOT NULL default Ɔ',
`day_23` int(6) NOT NULL default Ɔ',
`day_24` int(6) NOT NULL default Ɔ',
`day_25` int(6) NOT NULL default Ɔ',
`day_26` int(6) NOT NULL default Ɔ',
`day_27` int(6) NOT NULL default Ɔ',
`day_28` int(6) NOT NULL default Ɔ',
`day_29` int(6) NOT NULL default Ɔ',
`day_30` int(6) NOT NULL default Ɔ',
`day_31` int(6) NOT NULL default Ɔ',
UNIQUE KEY `post_id` (`post_id`),
UNIQUE KEY `year` (`year`),
UNIQUE KEY `month` (`month`)
)
Let say, today is the 20th July 2007, so my insert query will be like

INSERT INTO wp_views (post_id, year, month, month_hits) VALUES (1, 2007, 7, 1) ON DUPLICATE KEY UPDATE month_hits += 1, day_20 +=1;
Is my table structure alright? or is there a more efficient way of doing this?

Return Result Set From Procedure 5.0- Cross Post With Internals
I was looking into returning a result set from a MYSQL 5.0 stored
procedure to php. Unfortunately I do not know what type to return nor
can I find any types that would allow me to do this. Is this possible
under the 5.0 implementation?

Creat Mysql In Unix And Pull/post Data From A WindowsXP Machine With A UserDSN?
Can I creat mysql in Unix and pull/post data from a windowsXP machine with a UserDSN?

Is there an Administrative Tool (User DSN) that I can utilize to pull/post data to a Unix account without having to through the long process of "FTP" and "XTerm windows" ?

Return Results Even If No Matching Row In Stats (was "Hmm, How To Get Around This?")
I have a MySQL query which works fine. This query references 3 tables, one of them (stats) may not have any entries for the particular user if they havent played any games. My issue is, if the user has no entry in the stats table the query doesnt work, and no results are returned. What would be the best way to get around this in MySQL so I get all the other information even if there is no entry for the stats table?

SELECT p.id
, p.player
, p.team
, p.mugshot
, sum(s.goals) as goals
, t.teamname
, t.logo
FROM league as p
JOIN (SELECT goals
, player_id
FROM stats) as s
on s.player_id = p.id
JOIN (SELECT id
,teamname
,logo
FROM teams) as t
on t.id = p.team
WHERE
p.player LIKE '$playerselect'
GROUP
BY p.id
, p.player;

How Do I Upload A File To MySQL Database And View That File
I do not know how to upload a file to MySQL database and view the posting file on the webpage. I followed the code on-line that show me how to upload, but when I click "browse" to open my file in local drive and click "upload". Nothing happen ! How can I view it on the web page to make sure it was saved in data base.

Log-error File 'not Found' But This Same File Being Written To?!
I recently put up a message board and I get around 150+ hits a day so far. I've recently had some people sign up with my board but unfortunately, no one seems to be posting (they're just lurking around). I'm currently doing some promotions in the hope that others will post (like giving away a Lord of the Rings poster to all users who has 20 posts or more).

Of course, my board has only been up for less than a month (3 weeks to be exact) so I know I need to be patient but I would appreciate any advice you can give me on how to turn lurkers into posters.

.db.bk File
Need some file help. I had a host create a dump file of a mySQL database for me and the file is a .db file with a .bk extension (database file with a backup extension); i.e. filename.db.bk. I am moving a site to a new server and am used to having a .sql file for the database backup. How do I convert this file to a .sql so I can upload it to the new database?

Log File
I'm running mysql 4.1 with logging enabled. The master server produces a log file which is used for replication. I was wondering if there is a way to make the logfile turn over without stopping the database. Sometimes the file gets rather large and I'd like to turn it over without having to shut down mysql.

Where Is The .MYD File
I installed the MySQL server 5.0 and created a table with the default storage engine in the "test" database. But I cannot find the .MYD and .MYI file in the data directory. Only a .frm file resides there. Where are the .MYD and .MYI files?

What To Do With A Dmp.gz File ?
I've a site hosted at ipower.com and I recently upgraded my vDeck control panel from version 2 to 3 as they suggested.

Before that, I'd an option to backup my databases and they were just a .gz which on extraction gave me all .myd,.myi and .frm for each and every table so as to replace any table or entire DB in my local server.

Now, on clicking download, it gives a dmp.gz, and on extraction a dmp file.
How can I use this file in phpmyadmin or local file system to see the data ?

File Fox
I've been using a document management system called FileFox on a
Windows 95 platform and need to migrate the data from FileFox
(apparently stored in .dat files) to a more current database package
that will run on a Windows XP platform. Has anyone else ever used
FileFox or is there some way to migrate the data from these .dat files?

No Log File
mysql Ver 11.18 Distrib 3.23.52, for pc-linux-gnu (i686)

The doc page on mysql.com says "...start it with the
--log[=file_name]...". When I put --log in the safe_mysqld line and
restart - it doesn't start. Take it out, starts right up.
And I can't tell if logging has any thing to do with /etc/my.cnf

Bottom line of what I'm after - violations of a unique index. That
10,000 inserts worked just fine - don't care - that 5 lines were already
there, yes.

How Do I Fix A .frm File?
I am getting:

Incorrect information in file: './nitewise/newsletters.frm'

Error

SQL-query :

SHOW COLUMNS FROM `newsletters` FROM `nitewise`

MySQL said:

Incorrect information in file: './nitewise/newsletters.frm'

I have tried myisamchk -r newsletters.frm

I get :

- recovering (with sort) MyISAM-table 'newsletters.frm'
Data records: 1
- Fixing index 1

But still get the Incorrect information in file: './nitewise/newsletters.frm'

Can anyone tell me how to repair this file?

A DDL File
Hello, I'm new to this community but have heard nothing but good things about it.
I have a .DDL file that I need to run on mySQL to build a database for me. I was wondering if someone could explain to me how I am to do this, I've looked everywhere for help but have been unable to find out how.

I'm guessing I would do it from the command line client, but am unsure what to do after I'm loged into root.

the file is called "buildDB.DDL" .

My.cnf File
I want to create a database replication and for that i have to work on my.cnf file of my sql. Ihave tried to search this file by using the following command
find / -name my.cnf -print

but it did not give any result. I have mysql 3.23.36 running on FreeBSD 4.3 Version.

About My.ini File
can anybody share your my.ini file with me? I don't know what to write on it....
I copied one from web, but after paste it to my own .ini file, the whole sever shutdown.

and alway comes out with
ERROR 2003: Can't connect to MySQL server on 'localhost' (10061)

I tried to type
C:mysqlin>mysqladmin -u root status
mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to MySQL server on 'localhost' (10061)'
Check that mysqld is running on localhost and that the port is 3306.
You can check this by doing 'telnet localhost 3306'

C:mysqlin>

Run A .sql File
I know very little about MySQL, enough to get into trouble.

I downloaded a script and the first instruction is to
Quote: Use phpmyadmin to run site.sql against your database
I have phpmyadmin 2.5.1 and have used it before, but have never used a .sql file.

CSV File
I haven't got a clue what I need to be doing at this stage but basically i want to upload content from my excel to my db that is a MySQL db on my server.
BUT, the excel document only has one feild which is names. How do I upload that into my names tables on the server???
And one more was that, I also have the same file in a .txt format, the names are on seprate lines. How would I upload that into the db

Post Stats For Every File Looked Up?

I have 909,000 plus files in my database. Is it possible to post the amount of look ups , stats, for every file looked up? Example: People searching my database via my web site. If 700 people looked at a file, while viewing that file, it also tells you 700 look ups as part of the results? Code:

I have 909,000 plus files in my database. Is it possible to post the amount of look ups , stats, for every file looked up?

Example:
People searching my database via my web site.

If 700 people looked at a file, while viewing that file, it also tells you 700 look ups as part of the results? Code: