insertion issue

[deepM]

Hello,

I am creating recent visitors module. here is my data structure

CREATE TABLE `recent_visitor` (
`profile_owner` int(11) NOT NULL,
`visitor_id` int(11) NOT NULL,
`first_visited` timestamp NOT NULL default '0000-00-00 00:00:00',
`recent_visited` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`visiting_count` int(11) NOT NULL default '1',
PRIMARY KEY (`profile_owner`,`visitor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

records are inserting properly like this

profile_owner visitor_id first_visited recent_visited visiting_count
7426 9 2009-05-01 03:52:14 2009-05-01 03:52:14 1
7426 10 2009-05-01 03:52:14 2009-05-01 03:52:14 1
7426 7932 2009-05-01 03:52:14 2009-05-11 16:46:34 2

but the only issue is if now the logged in user(visitor_id) =8 than it should get inserted at the end of the database,but its getting inserted before visitor_id=9 like this:

profile_owner visitor_id first_visited recent_visited visiting_count
7426 9 2009-05-01 03:52:14 2009-05-01 03:52:14 1
7426 8 2009-05-01 03:52:14 2009-05-01 03:52:14 1
7426 10 2009-05-01 03:52:14 2009-05-01 03:52:14 1
7426 7932 2009-05-01 03:52:14 2009-05-11 16:46:34 2

why its happenig? because i want to fetch most recent user, because of this i am not able to do so.

can anyone help me?

thanks in advance.

[r937]

there is no such thing as position within a table

any sequence that you wish to see can be provided only by using the ORDER BY clause of a SELECT statement

[deepM]

there is no such thing as position within a table

any sequence that you wish to see can be provided only by using the ORDER BY clause of a SELECT statement

thanks for your reply r937, but i think issue is with insertion query only, because yet i am not selecting anything. just checking my table. just now add
this

7426 11 2009-05-01 03:52:14 2009-05-01 03:52:14 1

but its got added after visitor_id=10 only.

how is it so? please help me.

[deepM]

and the same problem is with profile_owner also

if i add

6457 74 2009-05-01 03:52:14 2009-05-01 03:52:14 1

then record is getting inserted before 7426, like this

profile_owner visitor_id first_visited recent_visited visiting_count
6457 74 2009-05-01 03:52:14 2009-05-01 03:52:14 1
7426 9 2009-05-01 03:52:14 2009-05-01 03:52:14 1
7426 8 2009-05-01 03:52:14 2009-05-01 03:52:14 1
7426 10 2009-05-01 03:52:14 2009-05-01 03:52:14 1
7426 7932 2009-05-01 03:52:14 2009-05-11 16:46:34 2

It means, it checks first that whtver data is there in the database(profile_owner,visitor_id), it checks their respective values and according inserting record into database.

is it because of PRIMARY KEY (`profile_owner`,`visitor_id`) ?

and if i removed these primary keys and add new one like this
PRIMARY KEY (`id`)
then there is no use of visitor_count.getting multiple row. that i dont want.

I think problem is with the primary key.

can anyone help me?

[r937]

but its got added after visitor_id=10 only.

no, that's not quite true

there is no "before" or "after" in a table

any sequence that you wish to see can be provided only by using the ORDER BY clause of a SELECT statement

[deepM]

no, that's not quite true

there is no "before" or "after" in a table

any sequence that you wish to see can be provided only by using the ORDER BY clause of a SELECT statement

but it quite true with my case

have you seen my recent post?

[r937]

have you tried the ORDER BY clause?

[oddz]

Store the creation date or the row and use a order by clause as r937 suggested.


SELECT x,y,z FROM whatever ORDER by created DESC LIMIT 1

[deepM]

have you tried the ORDER BY clause?

good morning r937,

i am talking about insertion here not selection. yeah.for selection i am using order by clause


good morning oddz,

Store the creation date or the row and use a order by clause as r937 suggested.


SELECT x,y,z FROM whatever ORDER by created DESC LIMIT 1

I wanted to show recently visited user, so as per your suggestion tried this

"SELECT  a.username,a.avatar,r.visitor_id FROM recent_visitor as r ,author as a WHERE (r.visitor_id = a.id) AND r.profile_owner = '$uid' ORDER BY r.recent_visited DESC  LIMIT $start, $limit_value

so earlier my o/p was

meera nihira

when i tried with new user(nanda) it should come something like this

nanda meera nihira

but its comimng like

meera nihira nanda.

because its not getting inserted in last row. so how can get a proper selsetion? firstly insertion should be done properly right?
any suggestion?

[Kevin Yank]

deepson2,

i think issue is with insertion query only, because yet i am not selecting anything. just checking my table.

Can you explain what you mean by this in greater detail? As far as I know, there is no way to “check your table” except by performing a SELECT query.

If you are using a tool like phpMyAdmin to view the contents of your table after performing a series of INSERTs, then phpMyAdmin is itself issuing a SELECT query to display those contents. A SELECT query is the only way to see what is stored inside your table after performing an INSERT.

As the other posters in this thread have explained, when you perform a SELECT query without an ORDER BY clause (or when you use a tool like phpMyAdmin, which performs a SELECT behind the scenes), the order of the records is not significant.

In order to optimize performance, MySQL stores records in whatever order it deems most efficient. Sometimes it will add records to the “end” of a table, sometimes it will add them in the “middle”. You should not expect to have any control over this, and indeed under some conditions you will find that MySQL changes the order of the records in a table on its own. Again, this is how MySQL is designed to work. The only way you should ever expect to see the contents of a table in a particular order is by performing a SELECT query with an ORDER BY clause (or a GROUP BY clause).

[deepM]

thanks Kevin Yank, I really appreciated that you replied here.

Can you explain what you mean by this in greater detail? As far as I know, there is no way to “check your table” except by performing a SELECT query.

when i said this it means only that once i logged in with id and visited someone's profile insertion query inserted record so i just checked my that particular record that where its getting stored.because i am knowing both visitors_id and the logged in user's id. so i can see in my database where the record is being stored

In order to optimize performance, MySQL stores records in whatever order it deems most efficient. Sometimes it will add records to the “end” of a table, sometimes it will add them in the “middle”. You should not expect to have any control over this, and indeed under some conditions you will find that MySQL changes the order of the records in a table on its own. Again, this is how MySQL is designed to work. The only way you should ever expect to see the contents of a table in a particular order is by performing a SELECT query with an ORDER BY clause (or a GROUP BY clause).

this is something i really don't know. if it so then

Sometimes it will add records to the “end” of a table, sometimes it will add them in the “middle”. You should not expect to have any control over this, and indeed under some conditions you will find that MySQL changes the order of the records in a table on its own. Again, this is how MySQL is designed to work.

how can anyone find the most recent rows (visitors?) if my recent most record is getting stored in between the middle of my database. how can i get that particular record as recent one then?

any suggestion?

[Kevin Yank]

Hi deepson2,

Your SELECT query looks correct. Looking at the inserted data you listed in your first post, I see that the TIMESTAMP field in new records in your table appear to be getting assigned an old date/time value, instead of the date/time at which the record was inserted.

My best guess for why this might be happening is if your web server is running an old version of MySQL. In versions of MySQL earlier than 4.1, TIMESTAMP fields worked differently. Can you check which version of MySQL your web server is running?

[Kevin Yank]

how can anyone find the most recent rows (visitors?)

As explained above by other posters, the only way to identify recently-added rows is to have a column of your table devoted to storing the date/time at which the record was created. It looks like you are already doing this with the 'recent_visited' column.

[deepM]

Now i am clear about that it is not necessary that data always gets stored in to database as last row. the only thing is how can we select the data.

Your SELECT query looks correct. Looking at the inserted data you listed in your first post, I see that the TIMESTAMP field in new records in your table appear to be getting assigned an old date/time value, instead of the date/time at which the record was inserted.

yeah, even i was wondering the timestamps is not working properly.
firstly i have inserted record like this

7932 18 2009-05-01 03:52:14 2009-05-12 12:02:18 1

and the very next records comes like this
7932 14 2009-05-01 03:52:14 2009-05-01 03:52:14 1

can you tell me why is it so?

My best guess for why this might be happening is if your web server is running an old version of MySQL. In versions of MySQL earlier than 4.1, TIMESTAMP fields worked differently. Can you check which version of MySQL your web server is running?

ok, my version is 5.2

[Kevin Yank]

Hmm. How are you inserting records into the table? What does your INSERT query look like?

[deepM]

$query              = $op->insert( "INSERT INTO recent_visitor (profile_owner, visitor_id, first_visited, recent_visited, visiting_count) VALUES
('".$blogdata->author."', '".$to."', '2009-05-01 03:52:14', '2009-05-01 03:52:14', 1)
  ON DUPLICATE KEY UPDATE `visiting_count`=`visiting_count`+1");

ok, i am such dumb my first visit value is not changing only. I can see the problem here.this is the wrong method i guess. could you please tell me how can i change this with recent or even today's date?

[Kevin Yank]

Your INSERT and UPDATE queries should never specify a value for recent_visited; MySQL will set its value automatically whenever you perform an INSERT or UPDATE (as described in your CREATE TABLE.

As for the first_visited, you should set this to CURRENT_TIMESTAMP in your INSERT query (to record the visitor’s first visit), but UPDATE queries should leave it alone (since you don’t want to overwrite the existing value).

[deepM]

so, my table structure ll remain the same or i have to make changes there as well?

As for the first_visited, you should set this to CURRENT_TIMESTAMP in your INSERT query (to record the visitor’s first visit), but UPDATE queries should leave it alone (since you don’t want to overwrite the existing value).

if i have to set my first_visited column as CURRENT_TIMESTAMP than what can i put as value in recent_visited column? could you please show me the proper query? mean while i am trying but could you please show me the query.

[deepM]

$first_visited           = date('Y-m-d H:i:s');
 
 
$query              = $op->insert( "INSERT INTO recent_visitor (profile_owner, visitor_id, first_visited, recent_visited, visiting_count) VALUES
('".$blogdata->author."', '".$to."', '".$first_visited."', '', 1)
  ON DUPLICATE KEY UPDATE `visiting_count`=`visiting_count`+1");

changed my first_visited's type form timestamps to date(i am trying my hand with this). but its not working now.

any suggestion?

[Kevin Yank]

I’d keep your table structure the same and change your INSERT to this:

$query              = $op->insert( "INSERT INTO recent_visitor (profile_owner, visitor_id, first_visited, visiting_count) VALUES
('".$blogdata->author."', '".$to."', CURRENT_TIMESTAMP, 1)
  ON DUPLICATE KEY UPDATE `visiting_count`=`visiting_count`+1"); 

If you decide to generate your 'first_visited' value in PHP as you have tried, you will need the column type to be DATETIME, not DATE.

[deepM]

Kevin Yank,

If you decide to generate your 'first_visited' value in PHP as you have tried, you will need the column type to be DATETIME, not DATE.

yes, later i thought about the same. guess what i got my query

$query              = $op->insert( "INSERT INTO recent_visitor (profile_owner, visitor_id, first_visited, recent_visited, visiting_count) VALUES
('".$blogdata->author."', '".$to."', NOW(), NOW(), 1)
  ON DUPLICATE KEY UPDATE `visiting_count`=`visiting_count`+1");

and i am getting proper results as well like this

7932  	3  	2009-05-12 14:38:49  	2009-05-12 14:38:49  	1
7932 	      9 	2009-05-12 14:39:32 	2009-05-12 14:39:47 	2

thanks a lot for being patient with me. i have learnt new things today. you were really helpful. ll try your query as well.

sitepoint rocks