SitePoint Sponsor |
|
User Tag List
Results 1 to 21 of 21
Thread: insertion issue
-
May 11, 2009, 04:42 #1
insertion issue
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;
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
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
can anyone help me?
thanks in advance.
-
May 11, 2009, 04:49 #2
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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
-
May 11, 2009, 04:58 #3
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
how is it so? please help me.
-
May 11, 2009, 06:24 #4
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
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?
-
May 11, 2009, 06:30 #5
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
-
May 11, 2009, 06:34 #6
-
May 11, 2009, 06:46 #7
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
have you tried the ORDER BY clause?
-
May 11, 2009, 06:57 #8
- Join Date
- Jul 2006
- Location
- Augusta, Georgia, United States
- Posts
- 4,194
- Mentioned
- 17 Post(s)
- Tagged
- 5 Thread(s)
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
-
May 11, 2009, 21:34 #9
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
Code SQL:"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?
-
May 11, 2009, 23:10 #10
- Join Date
- Apr 2000
- Location
- Melbourne, Australia
- Posts
- 2,571
- Mentioned
- 2 Post(s)
- Tagged
- 0 Thread(s)
deepson2,
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).Kevin Yank
CTO, sitepoint.com
I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
Baby’s got back—a hard back, that is: The Ultimate CSS Reference
-
May 11, 2009, 23:22 #11
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.
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).
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.
any suggestion?
-
May 11, 2009, 23:23 #12
- Join Date
- Apr 2000
- Location
- Melbourne, Australia
- Posts
- 2,571
- Mentioned
- 2 Post(s)
- Tagged
- 0 Thread(s)
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
CTO, sitepoint.com
I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
Baby’s got back—a hard back, that is: The Ultimate CSS Reference
-
May 11, 2009, 23:25 #13
- Join Date
- Apr 2000
- Location
- Melbourne, Australia
- Posts
- 2,571
- Mentioned
- 2 Post(s)
- Tagged
- 0 Thread(s)
Kevin Yank
CTO, sitepoint.com
I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
Baby’s got back—a hard back, that is: The Ultimate CSS Reference
-
May 11, 2009, 23:48 #14
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.
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?
-
May 12, 2009, 00:00 #15
- Join Date
- Apr 2000
- Location
- Melbourne, Australia
- Posts
- 2,571
- Mentioned
- 2 Post(s)
- Tagged
- 0 Thread(s)
Hmm. How are you inserting records into the table? What does your INSERT query look like?
Kevin Yank
CTO, sitepoint.com
I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
Baby’s got back—a hard back, that is: The Ultimate CSS Reference
-
May 12, 2009, 00:10 #16Code MySQL:
$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 dumbmy 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?
-
May 12, 2009, 00:45 #17
- Join Date
- Apr 2000
- Location
- Melbourne, Australia
- Posts
- 2,571
- Mentioned
- 2 Post(s)
- Tagged
- 0 Thread(s)
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).Kevin Yank
CTO, sitepoint.com
I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
Baby’s got back—a hard back, that is: The Ultimate CSS Reference
-
May 12, 2009, 00:58 #18
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).
-
May 12, 2009, 01:57 #19Code MySQL:
changed my first_visited's type form timestamps to date(i am trying my hand with this). but its not working now.
any suggestion?
-
May 12, 2009, 02:08 #20
- Join Date
- Apr 2000
- Location
- Melbourne, Australia
- Posts
- 2,571
- Mentioned
- 2 Post(s)
- Tagged
- 0 Thread(s)
I’d keep your table structure the same and change your INSERT to this:
PHP Code:$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");
Kevin Yank
CTO, sitepoint.com
I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
Baby’s got back—a hard back, that is: The Ultimate CSS Reference
-
May 12, 2009, 02:14 #21
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.
Code MySQL:$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
Code: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
sitepoint rocks
Bookmarks