Some kind of sorting a database table?

@pkngApr 22.2010

Hi again!

I’m working with a logging script that collect ip-number and some other things from a user at first time. Later back in time I saved all this info i simple txt-files, but now I’m going to use a database. So I guess the simpliest an perhaps best way is just to write all info from several user and use a ip-number as a unique id. At each time when the visitor visit a page(using a $_SESSION variable), the info will be written in the database, so there will be a lot of mixed information with diffrent ip-numbers from row to row if there are several user at the same time.

So my question is how to sort all this ip-numbers from a table and create a unique single visitor. Later I want to read the info and perhaps use sql and the “where” and the unique ip-number to get the info. I hope my question is clear? Thanks! ?

to post a comment

PHP

7 Comments(s) _↴

@JonaApr 22.2010 — #[font=arial]Hi,

You should be able to configure the "ip-number" field as a unique, indexed database field. Doing so will allow the database system to automatically understand that there may be no duplicates of the specified field. The way to accomplish this will be contingent on what type of database you use.[/font]

@pkngauthorApr 22.2010 — #Hmm, interesting! I use MySQL. But I would like to write info from one user several times. The table could have these fields id, ip-number, time, date and page. At each visit to a page info will be written in the database to this fields even if the user visit a page several times. The point is to track and log which pages the user has visited and at which time and date. So I guess the ip-number has to be written several times?!

And because info will be written several times from different users the ip-number field will be full of different numbers from different users. Thats my problem how "read" this and divide them all into uniqe users!?

@JonaApr 22.2010 — #[font=arial]How you choose to design your database schema is entirely up to you. Your rows must have fields whose data collides with other fields if you want to track multiple rows for a given field. You can accomplish this several ways.

Since you mentioned you have an "id" field, you might make [i]that[/i] field unique and indexed, and allow the ip-number field to have duplicate values. You could then search for all rows whose ip-number field is equal to a given value, for example the current user's IP address. This would return multiple rows of data with all of the available fields for a given IP.[/font]

@pkngauthorApr 22.2010 — #Thanks for the reply. How you describe it in the second part is just what I was planning. But my problem is when I'm going to check all visitors. I will have a lot of ip-numbers. My goal was to pick an ip-number of all visitor and present them in a list. When I have this list of all different ip-numbers I can pick one and then search for all rows in fields that has just that ip-numbers.

I want to create some code, with the help from this forum, to first find out how many different ip-numbers I have in the ip-number field, not the total ip-numbers because there will be several numbers that are the same. Second I will gather them in a array or something to create a list of all different ip-numbers. I would preciate som help or proposal which and how to use the PHP or SQL code. Thanks! ? I'm eager to get this to work!

If this is a bad design of the database or the hole idea, then please tell me a better way. I'm curious and eager to learn.

@OctoberWindApr 22.2010 — #I would take a good long look at sessions, and in particular session_id(). You can use session_id() to create a unique "combination" PK in your table (link id+session_id; id being unique PK, session_id being the "unique visitor" id). This will allow you to [code=php]select * from table where session_id = X[/code], and track the page views of each "visitor".

You can store whatever info you need (ip, date-time, page, ect) for each view, and still be able to track concurrent users that might share an IP.

IP's change way too often to be a reliable source to track people, especially "uniquely" as two people under the same network/firewall/proxy will have the same IP.

@ChipzzzApr 22.2010 — #If I understand what you're trying to do, this might be something like the code you might want:

<i>
 </i>&lt;?php
 // Create the table
 $con = mysql_connect($host, $user, $password);
 $sql = "CREATE TABLE log ( ";
 $sql .=  "id INT NOT NULL AUTO_INCRMENT, ";
 $sql .=  "  PRIMARY KEY(id), ";
 $sql .=  "  when TIMESTAMP, ";
 $sql .=  "  ip VARCHAR(16) ";
 $sql .=  ")";
 $result = mysql_query($sql, $con);
 mysql_close($con);
 ?&gt;
 
 &lt;?php
 function logit($w, $i) {
 // Make a log entry at TIMESTAMP $w from ip $i
 $con = mysql_connect($host, $user, $password);
 $sql = "INSERT INTO log VALUES ($w, $i)";
 $result = mysql_query($sql, $con);
 mysql_close($con);
 return $result;
 }
 function getall() {
 // Get a table of all ips that visited and how many times for each
 $con = mysql_connect($host, $user, $password);
 $sql = "SELECT (when, ip, COUNT(id)) FROM log ";
 $sql .= "GROUP by ip";
 $result = mysql_query($sql, $con);
 mysql_close($con);
 return $result;
 }
 function getoneip($i) {
 // Get a list of all TIMESTAMPs a given ip $ visited
 $con = mysql_connect($host, $user, $password);
 $sql = "SELECT (when, ip) FROM log ";
 $sql .= "WHERE ip = '$i'";
 $result = mysql_query($sql, $con);
 mysql_close($con);
 return $result;
 }
 ?&gt;

It's untested and almost certainly has errors (it's more like pseudo-code, actually), but I think it will give you a general idea of how to go about it.

Good luck with your project.

@criterion9Apr 22.2010 — #This might also be a good candidate for normalized tables. Foreign keys could help you quickly and efficiently select only the data you wish without extra complex processing inside the query.

Also in #PHP _↴

[RESOLVED] Two form change mysql query:obtaining array key from a value How to Bypass login restriction on my download links

Success!

Help @pkng spread the word by sharing this article on Twitter...

Tweet This

Some kind of sorting a database table?

7 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

Some kind of sorting a database table?

7 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

7 Comments(s) _↴

Also in #PHP _↴