SitePoint Sponsor

User Tag List

Results 1 to 21 of 21

Thread: insertion issue

  1. #1
    SitePoint Guru deepM's Avatar
    Join Date
    Dec 2007
    Location
    India
    Posts
    705
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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;
    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.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru deepM's Avatar
    Join Date
    Dec 2007
    Location
    India
    Posts
    705
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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.

  4. #4
    SitePoint Guru deepM's Avatar
    Join Date
    Dec 2007
    Location
    India
    Posts
    705
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by deepson2 View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Guru deepM's Avatar
    Join Date
    Dec 2007
    Location
    India
    Posts
    705
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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?

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    have you tried the ORDER BY clause?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,147
    Mentioned
    16 Post(s)
    Tagged
    3 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

  9. #9
    SitePoint Guru deepM's Avatar
    Join Date
    Dec 2007
    Location
    India
    Posts
    705
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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

    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?

  10. #10
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    deepson2,
    Quote Originally Posted by deepson2 View Post
    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).
    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

  11. #11
    SitePoint Guru deepM's Avatar
    Join Date
    Dec 2007
    Location
    India
    Posts
    705
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  12. #12
    SitePoint Author Kevin Yank's Avatar
    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

  13. #13
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by deepson2 View Post
    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.
    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

  14. #14
    SitePoint Guru deepM's Avatar
    Join Date
    Dec 2007
    Location
    India
    Posts
    705
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  15. #15
    SitePoint Author Kevin Yank's Avatar
    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

  16. #16
    SitePoint Guru deepM's Avatar
    Join Date
    Dec 2007
    Location
    India
    Posts
    705
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code 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 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?

  17. #17
    SitePoint Author Kevin Yank's Avatar
    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

  18. #18
    SitePoint Guru deepM's Avatar
    Join Date
    Dec 2007
    Location
    India
    Posts
    705
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  19. #19
    SitePoint Guru deepM's Avatar
    Join Date
    Dec 2007
    Location
    India
    Posts
    705
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code MySQL:
    $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?

  20. #20
    SitePoint Author Kevin Yank's Avatar
    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"
    ); 
    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.
    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

  21. #21
    SitePoint Guru deepM's Avatar
    Join Date
    Dec 2007
    Location
    India
    Posts
    705
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

    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
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •