SitePoint Sponsor

User Tag List

Results 1 to 23 of 23
  1. #1
    SitePoint Enthusiast
    Join Date
    Apr 2003
    Location
    NJ
    Posts
    44
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Creating A Row when I dont want it to.

    I'm making a script like hotornot.com where you rate things. Only problem is that instead of inserting the rating values onto the specific picture its inserting new rows. I'm a complete noob at php and most of this script besides the rating part was written by a friend of mine. If anyone could please explain how I could get the ratings to go into the specific row I would apprciate it.

    PHP Code:
    <html>
    <head>
    </title></title>
    </head>
    <body>

    <form name="image" method="post" action="<? echo $_SERVER['PHP_SELF']; ?>">
    <select name="Gender" onchange="image.submit()">
    <option value="male">Men
    <option value="female">Women
    </select>

    <select name="Age" onchange="image.submit()">
    <option value="1">18-25
    <option value="2">26-32
    <option value="3">33-40
    <option value="4">Above 40
    </select>

    <select name="type" onchange="image.submit()">
    <option value="***">***
    <option Value="boobs">Boobs
    </select>
    </form>

    <?php
    $db 
    mysql_connect("localhost""","") or die(mysql_error());
        
    mysql_select_db("dugreg_assntitties",$db) or die(mysql_error());
        
    $query "select * from pictures order by rand() limit 1";  
        
    $sql mysql_query($query) or die(mysql_error());
        
    $row mysql_fetch_assoc($sql);
    ?>

    <img src="/images/<?=$row["filename"]?>"><br>

    <form name="rating" method="post" action="<? echo $_SERVER['PHP_SELF']; ?>">
    <input type="radio" name="rate" value="1" onchange="rating.submit()"> 1
    <input type="radio" name="rate" value="2" onchange="rating.submit()"> 2
    <input type="radio" name="rate" value="3" onchange="rating.submit()"> 3
    <input type="radio" name="rate" value="4" onchange="rating.submit()"> 4
    <input type="radio" name="rate" value="5" onchange="rating.submit()"> 5
    <input type="radio" name="rate" value="6" onchange="rating.submit()"> 6
    <input type="radio" name="rate" value="7" onchange="rating.submit()"> 7
    <input type="radio" name="rate" value="8" onchange="rating.submit()"> 8
    <input type="radio" name="rate" value="1" onchange="rating.submit()"> 9
    <input type="radio" name="rate" value="10" onchange="rating.submit()"> 10
    </form>

    <?

    $query2 
    "select * from pictures where filename = '" $row['filename'] . "' and votes = '$votes' and total = '$total' and avg = '$avg'";  
        
    $sql mysql_query($query2) or die(mysql_error());
        
    $row mysql_fetch_assoc($sql);
         print 
    $row["id"];
        print 
    $row["votes"];
        print 
    $row["total"];
        print 
    $row["avg"];

    $newvotes $votes 1;
    $newtotal $total $rate;
    $newavg = ($newtotal) / ($newvotes);
    $row["filename"] = $filename;

    $query3 "INSERT INTO `pictures` (filename,votes,total,avg) VALUES ('$filename','$newvotes','$newtotal','$newavg')";  
        
    $sql mysql_query($query3) or die(mysql_error());
        

    ?>


    </body>
    </html>

  2. #2
    3MTA3
    Join Date
    Jul 2003
    Location
    Florida
    Posts
    1,016
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    For the SQL string that inserts the new data, you should use "UPDATE" instead of "INSERT INTO". You will also need to add a "WHERE" statement in it so it knows which row to update.

  3. #3
    SitePoint Enthusiast
    Join Date
    Apr 2003
    Location
    NJ
    Posts
    44
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Tried this
    PHP Code:
    $query3 "UPDATE pictures SET votes='$newvotes' , total='$newtotal' , avg='$newavg' WHERE filename ='$filename'"
    It didnt insert the Data

  4. #4
    3MTA3
    Join Date
    Jul 2003
    Location
    Florida
    Posts
    1,016
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $row["filename"] = $filename
    Try swapping the above around like this:
    PHP Code:
    $filename $row["filename"]; 

  5. #5
    SitePoint Enthusiast
    Join Date
    Apr 2003
    Location
    NJ
    Posts
    44
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ehh that inserts the data and everything but its not keeping what used to be there just inserting the values over the old ones.

  6. #6
    3MTA3
    Join Date
    Jul 2003
    Location
    Florida
    Posts
    1,016
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try setting the variables before you perform calculations with them.

    PHP Code:
    $votes $row["votes"];  //set the $votes variable with the old value
    $total $row["total"];  //set the $total variable with the old value
    $rate $_POST['rate']; //set the $rate variable to the value from the form

    $newvotes $votes 1
    $newtotal $total $rate
    $newavg = ($newtotal) / ($newvotes); 
    Also, I think the fields in the database should be an integer so that you can perform calculations with them. It looks like your SQL string has them set as strings (in single quotes). Someone please correct me if I'm wrong!

    If you post your updated code, then I'll give it a shot.

  7. #7
    SitePoint Enthusiast
    Join Date
    Apr 2003
    Location
    NJ
    Posts
    44
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The fields are set as int(11) so they are integers. This is the code I have now for the updating.

    PHP Code:
    $query2 "select * from pictures where filename = '" $row['filename'] . "' and votes = '$votes' and total = '$total' and avg = '$avg'";  
        
    $sql mysql_query($query2) or die(mysql_error());
        
    $row mysql_fetch_assoc($sql);
         print 
    $row["id"];
        print 
    $row["votes"];
        print 
    $row["total"];
        print 
    $row["avg"];

    $votes $row["votes"];  //set the $votes variable with the old value
    $total $row["total"];  //set the $total variable with the old value
    $rate $_POST['rate']; //set the $rate variable to the value from the form

    $newvotes $votes 1;
    $newtotal $total $rate;
    $newavg = ($newtotal) / ($newvotes);
    $filename $row["filename"];

    echo 
    $newvotes;
    echo 
    $newtotal;
    echo 
    $newavg;

    $query3 "UPDATE pictures SET votes='$newvotes' , total='$newtotal' , avg='$newavg' WHERE filename ='$filename'";  
        
    $sql mysql_query($query3) or die(mysql_error());
        

    ?> 

    Are you sure this can be done with the update value, and dosent need to be an insert?

  8. #8
    3MTA3
    Join Date
    Jul 2003
    Location
    Florida
    Posts
    1,016
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you are wanting to insert a new row in the database then use INSERT. If you want to update an existing row in the database, then use UPDATE.

    Other than your database connection info, it that the entire section of code?

  9. #9
    SitePoint Enthusiast
    Join Date
    Apr 2003
    Location
    NJ
    Posts
    44
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Its not the entire page but its the entire update part. The entire page is in my first post.

  10. #10
    3MTA3
    Join Date
    Jul 2003
    Location
    Florida
    Posts
    1,016
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I modified the code to a style that I am used to doing and took out a few parts that didn't look relevant to the block of code. I haven't tested it out it so if you try it, please let me know how it works. Or if this isn't what you are trying to achieve, then please explain what you want it to do in greater detail.


    PHP Code:
    <?php 
    //Database connection
    $db mysql_connect("localhost""","") or die(mysql_error()); 

    //This is the recordset that displays the random picture
    mysql_select_db("dugreg_assntitties",$db) or die(mysql_error()); 
    $rsPicsSQL "select * from pictures order by rand() limit 1";   
    $rsPics mysql_query($rsPicsSQL) or die(mysql_error()); 
    $row_rsPics mysql_fetch_assoc($rsPics); 

    if (
    $_GET['action'] == "rate"){
        
    $getFilename $_POST['pictureFilename']; //get the filename of the picture that was rated
        
    $rate $_POST['rate']; //get the rate value that was selected on the form 

        //This is the recordset for the picture that was rated
        
    $rsPictureSQL "select * from pictures where filename = '$getFilename'";   
        
    $rsPicture mysql_query($rsPictureSQL) or die(mysql_error()); 
        
    $row_rsPicture mysql_fetch_assoc($rsPicture); 
        
        
    $votes $row_rsPicture['votes'];  //set the $votes variable with the old value 
        
    $total $row_rsPicture['total'];  //set the $total variable with the old value     

        //perform calculations
        
    $newvotes $votes 1
        
    $newtotal $total $rate;  
        
    $newavg = ($newtotal) / ($newvotes); 

        
    //update the picture's rating
        
    $rsUpdateSQL "UPDATE pictures SET votes=$newvotes, total=$newtotal, avg=$newavg WHERE filename ='$filename'";   
        
    $rsUpdateQUERY mysql_query($rsUpdateSQL) or die(mysql_error()); 

        
    //display values
        
    echo "picture id: " $row_rsPicture['id']; 
        echo 
    "old votes: " $row_rsPicture['votes']; 
        echo 
    "old total: " $row_rsPicture['total']; 
        echo 
    "old avg: " $row_rsPicture['avg']; 
        echo 
    "new votes: $newvotes"
        echo 
    "new total: $newtotal"
        echo 
    "new avg: $newavg"
        
        
    mysql_free_result($rsPicture);  //Close recordset    
    }   

    ?> 

    <img src="/images/<?php echo $row_rsPics['filename'];?>"><br> 

    <form name="rating" method="post" action="<?php $_SERVER['PHP_SELF']; ?>?action=rate"> 
    <input type="radio" name="rate" value="1" onchange="rating.submit()"> 1 
    <input type="radio" name="rate" value="2" onchange="rating.submit()"> 2 
    <input type="radio" name="rate" value="3" onchange="rating.submit()"> 3 
    <input type="radio" name="rate" value="4" onchange="rating.submit()"> 4 
    <input type="radio" name="rate" value="5" onchange="rating.submit()"> 5 
    <input type="radio" name="rate" value="6" onchange="rating.submit()"> 6 
    <input type="radio" name="rate" value="7" onchange="rating.submit()"> 7 
    <input type="radio" name="rate" value="8" onchange="rating.submit()"> 8 
    <input type="radio" name="rate" value="1" onchange="rating.submit()"> 9 
    <input type="radio" name="rate" value="10" onchange="rating.submit()"> 10 
    <input name="pictureFilename" type="hidden" value="<?php echo $row_rsPics['filename']; ?>">
    </form> 

    <?php
    mysql_free_result
    ($rsPics);  //Close recordset
    mysql_close($db);  //Close connection
    ?>

  11. #11
    SitePoint Enthusiast
    Join Date
    Apr 2003
    Location
    NJ
    Posts
    44
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow thats Amazing! Only one problem its not updating the new votes field. Its still staying at one.

  12. #12
    3MTA3
    Join Date
    Jul 2003
    Location
    Florida
    Posts
    1,016
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Paradizzle
    Wow thats Amazing! Only one problem its not updating the new votes field. Its still staying at one.
    Well, at least we're almost there.

    Are you sure that the 'votes' field in the database is set as an integer?

  13. #13
    SitePoint Enthusiast
    Join Date
    Apr 2003
    Location
    NJ
    Posts
    44
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    votes int(11)

    Yup its set as an integer.

  14. #14
    3MTA3
    Join Date
    Jul 2003
    Location
    Florida
    Posts
    1,016
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    On the section where it displays the values, what do the "old values" and "new values" display? Do they both display a value or are any of them blank?

  15. #15
    SitePoint Enthusiast
    Join Date
    Apr 2003
    Location
    NJ
    Posts
    44
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    picture id: 23old votes: 1old total: 6old avg: 6new votes: 2new total: 12new avg: 6

  16. #16
    3MTA3
    Join Date
    Jul 2003
    Location
    Florida
    Posts
    1,016
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Paradizzle
    picture id: 23old votes: 1old total: 6old avg: 6new votes: 2new total: 12new avg: 6
    Going by the values that are being displayed, the votes were increased (old votes: 1, new votes: 2). Are they not reflecting that in the database?

  17. #17
    SitePoint Enthusiast
    Join Date
    Apr 2003
    Location
    NJ
    Posts
    44
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah I dont think its updating them still.

  18. #18
    3MTA3
    Join Date
    Jul 2003
    Location
    Florida
    Posts
    1,016
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Do all the other fields update correctly? Try echoing the sql statement to see what it's set to.

    PHP Code:
    echo $rsUpdateSQL

  19. #19
    SitePoint Enthusiast
    Join Date
    Apr 2003
    Location
    NJ
    Posts
    44
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ahhhh doing that little debug thing helped me figure it out. You had where filename=$filename and it should have been where filename=$getFilename. Thanks alot its working wonderfully now.

  20. #20
    3MTA3
    Join Date
    Jul 2003
    Location
    Florida
    Posts
    1,016
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Paradizzle
    Ahhhh doing that little debug thing helped me figure it out. You had where filename=$filename and it should have been where filename=$getFilename. Thanks alot its working wonderfully now.
    Glad you got it working now.

  21. #21
    SitePoint Enthusiast
    Join Date
    Apr 2003
    Location
    NJ
    Posts
    44
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks alot for making that work, but one more thing if you don't mind. If you look at the original script you can see I had a select box for specific options. How could I make that work. I allready have the values set in the database as 1-4 or or how ever many options there are. But what would the query be to select from the specific rows and yet still be random based on them?

  22. #22
    3MTA3
    Join Date
    Jul 2003
    Location
    Florida
    Posts
    1,016
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Paradizzle
    Thanks alot for making that work, but one more thing if you don't mind. If you look at the original script you can see I had a select box for specific options. How could I make that work. I allready have the values set in the database as 1-4 or or how ever many options there are. But what would the query be to select from the specific rows and yet still be random based on them?
    I would set each of the form elements to a variable. Next, I would add some WHERE statements for each form element in the SQL string for the pictures.

    Give it a try yourself and if needed, I'll try to coach you through the process.

  23. #23
    SitePoint Enthusiast
    Join Date
    Apr 2003
    Location
    NJ
    Posts
    44
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think I got it to work. If you see any errors or something I might not be noticing let me know.

    PHP Code:
    <?php
    //Database connection
    $db mysql_connect("localhost""","") or die(mysql_error());

    //This is the recordset that displays the random picture
    mysql_select_db("dugreg_assntitties",$db) or die(mysql_error());
    $rsPicsSQL "select * from pictures order by rand() limit 1";   
    $rsPics mysql_query($rsPicsSQL) or die(mysql_error());
    $row_rsPics mysql_fetch_assoc($rsPics);

    if (
    $_GET['action'] == "rate"){
        
    $getFilename $_POST['pictureFilename']; //get the filename of the picture that was rated
        
    $rate $_POST['rate']; //get the rate value that was selected on the form

        //This is the recordset for the picture that was rated
        
    $rsPictureSQL "select * from pictures where filename = '$getFilename'";   
        
    $rsPicture mysql_query($rsPictureSQL) or die(mysql_error());
        
    $row_rsPicture mysql_fetch_assoc($rsPicture);
        
        
    $votes $row_rsPicture['votes'];  //set the $votes variable with the old value
        
    $total $row_rsPicture['total'];  //set the $total variable with the old value     

        //perform calculations
        
    $newvotes $votes 1;
        
    $newtotal $total $rate;  
        
    $newavg = ($newtotal) / ($newvotes);

        
    //update the picture's rating
        
    $rsUpdateSQL "UPDATE pictures SET votes=$newvotes, total=$newtotal, avg=$newavg WHERE filename ='$getFilename'";   
        
    $rsUpdateQUERY mysql_query($rsUpdateSQL) or die(mysql_error());

        
    //display values
        
    echo "picture id: " $row_rsPicture['id'];
        echo 
    "old votes: " $row_rsPicture['votes'];
        echo 
    "old total: " $row_rsPicture['total'];
        echo 
    "old avg: " $row_rsPicture['avg'];
        echo 
    "new votes: $newvotes";
        echo 
    "new total: $newtotal";
        echo 
    "new avg: $newavg";
        
        
    mysql_free_result($rsPicture);  //Close recordset    
    }   
    elseif (
    $_GET['action'] == "select"){

        
    //This is the recordset that displays the type of picture
        
    mysql_select_db("dugreg_assntitties",$db) or die(mysql_error());
        
    $rsPicsSQL2 "select * from pictures WHERE gender='$Gender' AND age='$Age' AND type='$type' order by rand() limit 1";   
        
    $rsPics2 mysql_query($rsPicsSQL2) or die(mysql_error());
        
    $row_rsPics2 mysql_fetch_assoc($rsPics2);
    }

    ?>

    <form name="image" method="post" action="<? echo $_SERVER['PHP_SELF']; ?>?action=select"">
    <select name="Gender" onchange="image.submit()">
    <option value="1">Men
    <option value="2">Women
    </select>

    <select name="Age" onchange="image.submit()">
    <option value="1">18-25
    <option value="2">26-32
    <option value="3">33-40
    <option value="4">Above 40
    </select>

    <select name="type" onchange="image.submit()">
    <option value="1">***
    <option Value="2">Boobs
    </select>
    </form>

    <img src="/boobs/images/<?php echo $row_rsPics['filename'];?>"><br>

    <form name="rating" method="post" action="<?php $_SERVER['PHP_SELF']; ?>?action=rate">
    <input type="radio" name="rate" value="1" onchange="rating.submit()"> 1
    <input type="radio" name="rate" value="2" onchange="rating.submit()"> 2
    <input type="radio" name="rate" value="3" onchange="rating.submit()"> 3
    <input type="radio" name="rate" value="4" onchange="rating.submit()"> 4
    <input type="radio" name="rate" value="5" onchange="rating.submit()"> 5
    <input type="radio" name="rate" value="6" onchange="rating.submit()"> 6
    <input type="radio" name="rate" value="7" onchange="rating.submit()"> 7
    <input type="radio" name="rate" value="8" onchange="rating.submit()"> 8
    <input type="radio" name="rate" value="1" onchange="rating.submit()"> 9
    <input type="radio" name="rate" value="10" onchange="rating.submit()"> 10
    <input name="pictureFilename" type="hidden" value="<?php echo $row_rsPics['filename']; ?>">
    </form>

    <?php
    mysql_free_result
    ($rsPics);  //Close recordset
    mysql_close($db);  //Close connection

    echo $rsUpdateSQL
    echo 
    $rsPicsSQL2;
    ?>
    Last edited by Paradizzle; Aug 31, 2004 at 21:31.


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
  •