Databases
StackOverflow2013
Postsdbo
POsql and php database entry 'lagging behind'
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POsql and php database entry 'lagging behind'
    primarykey
    data
    text
    <p>I have a ratings system implemented but can't get it post the correct up to date rating in one of my database tables, it's always behind by one step, i.e. it always stores the last rating just before the current one:</p> <p>The table 'post_rating' contains a row for every single vote cast on anything, and the table 'posts_rat_main' is where I'm having trouble. I want it to store an overall rating made up of all the individual votes on an item on the site, and keep queries to a minimum, but as said it's always one vote behind the current running total.</p> <p>Main page </p> <pre><code>// set up the ratings $votes = mysql_query("SELECT rat_rating FROM post_rating WHERE rat_ID = $post_ID"); $votesnr = 0; $totalvotes = 0; while($vote = mysql_fetch_array($votes)){ $votesnr++; $totalvotes += $vote['rat_rating']; } if($votesnr == 0){ $rating = 0; } else { $rating = $totalvotes/$votesnr; } $roundedrating = floor($rating) + round($rating - floor($rating)) / 2 ; ?&gt; &lt;div&gt;Total Rating&lt;/div&gt; &lt;div class="star-rating" id="rating1result&lt;?php echo $roundedrating; ?&gt; "style="background-position:0 -&lt;?php echo $roundedrating * 32; ?&gt;px;"&gt; &lt;div class="star"&gt;&lt;/div&gt; &lt;div class="star"&gt;&lt;/div&gt; &lt;div class="star"&gt;&lt;/div&gt; &lt;div class="star"&gt;&lt;/div&gt; &lt;div class="star"&gt;&lt;/div&gt; &lt;/div&gt; &lt;div class="result"&gt; &lt;span style="color:green"&gt;&lt;?php echo round($rating,2); ?&gt;&lt;/span&gt; (&lt;?php echo $votesnr; ?&gt; votes) &lt;/div&gt; &lt;script type="text/javascript"&gt; $(function(){ $('.star').mouseover(function (){ var star = $(this).index()+1; var x =(32 * star); $(this).parent().css('backgroundPosition... ' +(-x)+ 'px'); }); $('.star-rating').mouseout(function (){ var originalresult = $(this).attr('id').split('result')[1]; var y =(32 * originalresult); $(this).css('background-position','0%' +(-y)+ 'px'); }); }); $('.star').click(function (){ var id = $(this).parent().attr('id').split('ratin... var vote = $(this).index() +1; $.ajax({ type: "POST", url:"save_vote.php", data: 'id='+ id + '&amp;vote='+ vote + '&amp;stid=&lt;?php echo $post_ID ?&gt;' + '&amp;totv=&lt;?php echo $totalvotes ?&gt;' + '&amp;votes=&lt;?php echo $votesnr ?&gt;' }); $(this).parent().removeAttr("id"); $(this).parent().html(" "); window.location.href = "posts/post_view.php?id=&lt;?php echo $post_ID ?&gt;"; }); &lt;/script&gt;` </code></pre> <p>This all works well with the 'post_rating' table, the handler scrip where I suspect the problem is reads: </p> <pre><code>$id = intval($_POST['id']); $vote = intval($_POST['vote']); $post_ID = intval($_POST['stid']); $votesnr = $_POST['votes']; $tot_v = $_POST['totv']; // set up the ratings $votesnr = $votesnr; $totalvotes = $tot_v; if($votesnr == 0){ $rating = 0; } else { $rating = $totalvotes/$votesnr; } $new_rat = round($rating,2); // Sling in the data $insert_rat = "INSERT INTO post_rating"; $insert_rat.= " (rat_ID,"; $insert_rat.= " rat_rating,"; $insert_rat.= " rat_user_ID,"; $insert_rat.= " rat_user_name,"; $insert_rat.= " rat_date)"; $insert_rat.= " VALUES"; $insert_rat.= " ('$post_ID',"; $insert_rat.= " '$vote',"; $insert_rat.= " '$member_ID',"; $insert_rat.= " '$member_name',"; $insert_rat.= " NOW())"; $story_update = "UPDATE posts_rat_main"; $story_update.= " SET rating = '$new_rat'"; $story_update.= " WHERE st_ID = '$post_ID'"; mysql_query($insert_rat) or die(mysql_error()); mysql_query($story_update) or die(mysql_error()); </code></pre> <p>So whenever I update the table 'posts_rat_main' it updates it with the figure one vote behind the current vote!? I've got half of this from a tutorial and messed about it with to no avail. I'm lousy with javascript and just can't figure it out but suspect the problem lays in my 'save_vote' handler script which seems very simple. </p> <p>My latest attempt was to nick the script commented '// set up the ratings', from the 'main page' and paste it into the save_vote handler script as shown here, but to no avail. I'm fairly stumped, any ideas?</p> <p>Also as a secondary note I've found this script to be a little unresponsive at times, requiring sometimes 2 or 3 clicks on the stars before it will register the vote, and I can't imagine why that is?</p> <p>Any help much appreciated, cheers.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USPaul Lo
    1. USMr C
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POsql and php database entry 'lagging behind'
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload