SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Addict
    Join Date
    Dec 2004
    Location
    San Francisco
    Posts
    263
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help on tracking PPC campaigns

    I am currently bidding in Overture, Google and Ask Pay-Per-Click campaigns for one of my websites. And I am also receiving some decent number of click-thrus for my affiliate program. But the problem is I am not receiving enough commissions to those clicks, which means that not all of the click-thrus are generating sales.

    I am trying to track my campaigns and sales, by which I can control my spending. So far I have done the following:

    PHP Code:
    include 'includes/config.php'

    $date date("Y-m-d"); //date of visit
    $time date("H:i:s"); //time of visit
    $program $_GET['program']; //PPC program
    $keyword $_GET['keyword']; //keyword
    $bid $_GET['bid']; //bid
    $ip   getenv('REMOTE_ADDR'); //ip address of the visitor
    $referer $HTTP_REFERER//referring site

    $sql "INSERT INTO `visits` SET
    date     = '
    $date',
    time    = '
    $time',
    ip    = '
    $ip',
    program  = '
    $program',
    keyword    = '
    $keyword',
    bid    = '
    $bid', 
    ip           = '
    $ip',
    referer    = '
    $referer'" ;

    $results = @mysql_query($sql); 
    But in order to set the cookie on the visitors (Users) computer, I need to retreive the associated ID (which is an auto_increment) from the visits table. How can I retreive the ID that corresponds to the above INSERT?

    Once I have the ID, I would like to set the cookie with the following command, and use it later while tracking the sales.

    setcookie("VisitorID", $id, time()+86400, "/", "");

    Any help would be appreciated.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    use the last_insert_id function
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Dec 2004
    Location
    San Francisco
    Posts
    263
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Can you please eloborate on how to use the last_insert_id function. Also what if some other user visits the site between the insert command based on the first visitor and retreiving the last_insert_id, will I still get the ID that corresponds to the first visitor insert information.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Dec 2004
    Location
    San Francisco
    Posts
    263
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I did the same as explained in the link, but everytime the number it is retreiving is one number less than the actual inserted id, if the inserted id is 10, by last_insert_id function it is retreiving 9.

    Any explanation would be helpful.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    my advice is to post your actual code in the php forum

    or i can move this thread over there if you wish
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict
    Join Date
    Dec 2004
    Location
    San Francisco
    Posts
    263
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That would help.
    Thank you.

    BTW: Last problem in getting one number less, isn't it related to MySQL?

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i really doubt that you've found a mysql problem -- if last_insert_id consistently returned one number less, don't you think at least one of the several other million mysql users would have found it by now?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Addict
    Join Date
    Dec 2004
    Location
    San Francisco
    Posts
    263
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am not saying that the last_insert_id function itself is wrong. I was just wordering if I need to make any changes in the id field settings. I am novice when it comes to programming, so I will certainly doubt if I can point out some problems in a program.

    Anyways, finally I found the problem, it is in cookie settings.
    Thanks for your continious support.

  10. #10
    SitePoint Zealot Seriph's Avatar
    Join Date
    Oct 2005
    Location
    San Diego, CA
    Posts
    143
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    For reference purposes ... the PHP call is mysql_insert_id()


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
  •