Creating A Row when I dont want it to.

**Paradizzle** · Aug 22, 2004 19:13

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>

**devised** · Aug 22, 2004 21:52

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.

**Paradizzle** · Aug 23, 2004 00:07

Tried this

PHP Code:


$query3 = "UPDATE pictures SET votes='$newvotes' , total='$newtotal' , avg='$newavg' WHERE filename ='$filename'";

It didnt insert the Data

**devised** · Aug 23, 2004 09:50

PHP Code:


$row["filename"] = $filename;

Try swapping the above around like this:

PHP Code:


$filename = $row["filename"];

**Paradizzle** · Aug 23, 2004 11:52

Ehh that inserts the data and everything but its not keeping what used to be there just inserting the values over the old ones.

**devised** · Aug 23, 2004 15:10

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.

**Paradizzle** · Aug 23, 2004 18:22

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?

**devised** · Aug 23, 2004 19:23

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?

**Paradizzle** · Aug 24, 2004 05:24

Its not the entire page but its the entire update part. The entire page is in my first post.

**devised** · Aug 24, 2004 22:04

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

?>

**Paradizzle** · Aug 26, 2004 19:49

Wow thats Amazing! Only one problem its not updating the new votes field. Its still staying at one.

**devised** · Aug 26, 2004 23:27

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?

**Paradizzle** · Aug 27, 2004 05:15

votes int(11)

Yup its set as an integer.

**devised** · Aug 27, 2004 08:02

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?

**Paradizzle** · Aug 27, 2004 11:48

picture id: 23old votes: 1old total: 6old avg: 6new votes: 2new total: 12new avg: 6

**devised** · Aug 27, 2004 12:15

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?

**Paradizzle** · Aug 27, 2004 19:01

Yeah I dont think its updating them still.

**devised** · Aug 27, 2004 19:42

Do all the other fields update correctly? Try echoing the sql statement to see what it's set to.

PHP Code:


echo $rsUpdateSQL;

**Paradizzle** · Aug 28, 2004 10:05

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.

**devised** · Aug 28, 2004 10:10

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.

**Paradizzle** · Aug 28, 2004 20:42

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?

**devised** · Aug 28, 2004 23:09

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.

**Paradizzle** · Aug 31, 2004 20:59

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;

?>

User Tag List

Thread: Creating A Row when I dont want it to.

Thread Tools

Display

Creating A Row when I dont want it to.

Bookmarks

Bookmarks

Posting Permissions