SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,738
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    $update - help with syntax basic

    Hi all

    I need help with a basic update in my code:

    Code PHP:
    $update = mysql_query("UPDATE dancers SET views = views+1 WHERE name = ".((int)$_GET['name'])) or die(mysql_error());
    Seem to work ok no errors, but instead of updating each dancer separately, it updates every dancer no matter what page/dancer I view?

    Also how do I change .((int)$_GET['name'])), its not a int but char, this is from a old project thanks.


    Any suggestions?
    The more you learn.... the more you learn there is more to learn.

  2. #2
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,738
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Update. If I remove (int) like below, it now says Unknown column 'tracy' in 'where clause' tracy is the dancers name
    Code PHP:
    $update = mysql_query("UPDATE dancers SET views = views+1 WHERE name = ".($_GET['name'])) or die(mysql_error());

    Thanks
    The more you learn.... the more you learn there is more to learn.

  3. #3
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    OK, (int) should only be used on integers. Obviously name isn't an integer, so to escape it for the database you need to use MySQL_Real_Escape_String() and put quotes around it:
    PHP Code:
    $name MySQL_Real_Escape_String($_GET['name']);
    $update mysql_query("UPDATE dancers SET views = views+1 WHERE name = '{$name}'") or die(mysql_error()); 
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  4. #4
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,738
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, should of shown my full code:

    Code PHP:
    $sql1 = "SELECT name, age, FROM dancers WHERE name='" .mysql_real_escape_string($_GET['name'])."'"; 
    $result1 = @mysql_query($sql1) or die('Error: ' . mysql_error());
    $row1 = mysql_fetch_array($result1, MYSQL_ASSOC);
     
    $update = mysql_query("UPDATE dancers SET views = views+1 WHERE name=".($_GET['name'])) or die(mysql_error());

    What do you suggest?
    The more you learn.... the more you learn there is more to learn.

  5. #5
    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)
    put quotes around the name string

    ... WHERE name= '".($_GET['name'])."'"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,738
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks a lot r937 works great!

    Cheers Guys

    Update: Would it be possible to only allow the db.views to update only once from that ip in a set time frame?, stopping a user from repeating the refresh button and gaining hits?

    Cheers
    The more you learn.... the more you learn there is more to learn.

  7. #7
    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)
    if you are recording the data in a table, then yes, just declare a unique constraint on { user, ip }
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,738
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    cont.. from above (add some extra columns to dancers db table, 'time' 'ip')

    Then add something like this:

    $time = $_POST['time'];
    $ip = $_SERVER['REMOTE_ADDR'];
    .....
    $insert = "INSERT INTO dancers (time, ip) - when the page is first viewed, and don't up date the views column until 24 hrs, I don't want an errors showing to the user just sorts itself out in the background..

    Is this possible? If so, how can I add it to my code?

    Thanks

    update, posted the same time r937.
    if you are recording the data in a table
    I'm displaying content from the table, the content is manually inserted and updated from time to time and can be viewed by anyone. I need to track this user and store their ip and disallow any more recorded hits to this dancer for 24hrs.

    But at the same time still allow this ip/user to view other dancers but only record these viewings once aswell, not a major problem but would be very handy to see how I can set this up
    Last edited by computerbarry; Jul 19, 2009 at 17:22.
    The more you learn.... the more you learn there is more to learn.

  9. #9
    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)
    a unique index on { user, ip } assumes that you are recording each { user, ip } as it happens

    disallowing all duplicates is a lot easier than disallowing duplicates within a time period
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You could create a new table with columns for dancer, ip and date viewed.
    Then something like this would probably work:
    PHP Code:
    $name mysql_real_escape_string($_GET['name']);
    $ip mysql_real_escape_string($_SERVER['REMOTE_ADDR']);

    $query "
    UPDATE dancers 
    SET views = views+1 
    WHERE name='
    $name' AND 
    NOT EXISTS (
        SELECT *
        FROM dancer_views
        WHERE 
        ip = '
    $ip' AND 
        name='
    $name' AND 
        date_viewed < DATE_SUB(NOW(), INTERVAL 24 HOUR)
    )"

    Please note - you are starting to use dancer name like an ID. This is a bad idea, because as soon as you get two dancers with the same name your app is going to break. You should be passing the ID around in $_GET and using it in your queries to identify the correct dancer. The query I've just posted should also be modified to use dancer_id instead of the name which is less reliable.


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
  •