Need some php help here and some sql

[TorrentMaster9]

I was thinking of making a rate system on my site. I already made something like this on a asp site so I need some help to translate this code. Then I'll try to add it First I made a vote.php file like this one

vote.php

if(user is NOT logged)
   show a message saying he is not logged so he can't vote or just redirect the guest to login page 
   
ID = parseInt(Request("ID"));    // ID= ID of a topic that will have a rate system
sql="SELECT *FROM Votes WHERE User='" + Username ID logged + "' AND ID=" + ID;    /* Will create a table Votes on the database that will have a Vote_ID auto incremented, ID(from the topic to be voted), User(ID from the user who made the vote, an vote( a 0 to 10 integer)*/
rs_aux3=novors(sql);   /*this thing was a recordset of the sql query, dunno how to make something like this on php*/
if(!rs_aux3.EOF)
    {   
        write "You already voted on this topic before" and a link to the topic ex" <a href=\"index.php?topic=" + ID + "\">Back to topic</a>");
        rs_aux3.Close();
        rs_aux3 = null;
    }
else
    {
        vote = String(Request.Form("vote")); 
        sql = "INSERT INTO Votes (User,ID,vote) VALUES ('" + Logged User ID  + "', '" + ID + "','" + vote + "')"; 
        execsql(sql);      /* execute the query*/
        sql="SELECT COUNT(*) AS n_votes FROM Votes WHERE ID=" + ID;
        rs_aux=novors(sql);
        sql="SELECT AVG(vote) AS average FROM Votes WHERE ID=" + ID;
        rs_aux2=novors(sql);
        op=Math.round(rs_aux2.Fields.Item("average").Value*100)/100;  /* I believe I made this bcause something related with the decimal part of the average */
        sql="UPDATE topic_rating SET n_votes="+ rs_aux.Fields.Item("n_votos").Value + ", average=" + op + " WHERE ID=" + ID; /* Will create a table topic_rating on the DB with fields ID(topic ID) as key of the table and a n_votes(number of votes) and average(rate of the topic)*/
        execsql(sql); /execute update query*/
        write " Voted inserted " <a href=\"index.php?topic=" + ID + "\">Back to topic</a>");    
        rs_aux.Close();
        rs_aux = null;
        rs_aux2.Close();
        rs_aux2 = null;
        rs_aux3.Close();
        rs_aux3 = null;
    }

I need help to translate this and maybe a suggestion to create for
example two tables like these(maybe a query through phpmyadmin):
1)name:votes
.tablesvote_ID(key),ID(of the topic to be rated),user_ID(the id of
the member who voted), vote(integer from 0 to 10)
2)name:topic_rating .tables(ID(ID of the topic)(key),N-Votes(number of votes of the topic),average(rating of the topic))

I also need help on some database vars calling:
1) call for example the fields N_Votes and Average of the current topic to 2 variables
2) The vote form should be something like this?:

<form method="POST" action="vote.asp?ID= Current topic ID
    <select size="1" name="vote">
        for(&i=1;&i<=10;&i++)
          echo'<option value=\"', &i , '\">', &i, '</option>';
    </select>
    <input type="submit" value="OK" name="OK"></p>
</form>

thanks

[tesco]

<?

function mysql_query_ ($sql)
{
    $result = mysql_query($sql) or die('Query failed: ' . mysql_error());
    return $result;
}

if(user_NOT_logged_in)
{
    die("Not logged in");
}
   
if ( !$topicid = intval($_REQUEST['id']) ) # (If it's not an integer)
{
    die("Invalid Topic ID");
}

$sql="
        SELECT * FROM Votes
        WHERE User='" . $userid . "'
        AND ID=" . $topicid;
/* Will create a table Votes on the database that will have a Vote_ID auto incremented, ID(from the topic to be voted), User(ID from the user who made the vote, an vote( a 0 to 10 integer)*/

$result = mysql_query_($sql);


if( mysql_num_rows($result) > 0 )
{   
    die("You already voted on this topic before <a href=\"index.php?topic=" . $topicid . "\">Back to topic</a>");
}

$vote = intval($_REQUEST['vote']); 
$sql = "
        INSERT INTO Votes
        (User,ID,vote)
        VALUES ('" . $userid . "', '" . $topicid . "','" . $vote . "')"; 
mysql_query_($sql);

// Count Votes
$sql="
        SELECT COUNT(*) AS n_votes
        FROM Votes
        WHERE ID=" . $topicid;
$result = mysql_query_($sql);
$number_votes = mysql_fetch_array($result);
$number_votes = $number_votes['n_votes'];

// Find Average of all votes
$sql="
        SELECT AVG(vote) AS average
        FROM Votes
        WHERE ID=" . $topicid;
$result = mysql_query_($sql);
$average = mysql_fetch_array($result);
$average = round($average['average']);

// Insert rating to topic
$sql="
        UPDATE topic_rating
        SET n_votes=" . $number_votes . ", average=" . $average . " WHERE ID=" . $topicid; 
/* Will create a table topic_rating on the DB with fields ID(topic ID) as key of the table and a n_votes(number of votes) and average(rate of the topic)*/
mysql_query_($sql);

echo " Voted inserted <a href=\"index.php?topic=" . $topicid . "\">Back to topic</a>");

<form method="POST" action="vote.php">
    <select size="1" name="vote">
        <?php
        for($i=1;$i<=10;$i++)
        {
            echo '<option value="' . $i . '">' . $i . '</option>';
        }
        ?>
    </select>
    <input type="hidden" value="IDGOESHERE" name="id" />
    <input type="submit" value="OK" name="OK" />
</form>

To create those tables just use phpmyadmin to do it through the GUI.

[TorrentMaster9]

big thanks mate.
do you know the reason sometimes on a form when you click on the submit button a popup message appear with something like "Are you sure you wanna continue?"?.

[tesco]

Yes, because the creator wanted it to say that.

 <form method="POST" action="vote.php" onsubmit="return confirm('Are you sure?');">

[TorrentMaster9]

Yes, because the creator wanted it to say that.

 <form method="POST" action="vote.php" onsubmit="return confirm('Are you sure?');">

hehe I'm not so idiot hehe. well the code that I made(taking in consideration the board I used) was this:
the board works this way, loads first a xxxx.php page to load variables, make sql queries etc, then use the variables in a xxx.template.php page.
this is what I wrote on the xxx.php page

// movie rate system
	if (isset($_REQUEST['Reset']) && !$user_info['is_guest'])
	{	
		db_query("
			DELETE *FROM {$db_prefix}MoviesVotes WHERE userID = $ID_MEMBER AND movieID=$topic", __FILE__, __LINE__);
		
	}
	
	$request = db_query("
			SELECT *FROM {$db_prefix}MoviesVotes WHERE userID = $ID_MEMBER AND movieID=$topic", __FILE__, __LINE__);
	$row = mysql_fetch_assoc($request);
	if($row)  
	{
		$context['already_voted']=true;
		$context['past_rate']=$row['rating'];
	}
	else
	   $context['already_voted']=false;
	
	if (isset($_REQUEST['Rate']) && !$user_info['is_guest'])
	{
		$rating = (int) $_POST['vote'];
		if ($rating >= 1 && $rating <= 10)
		db_query("
				REPLACE INTO {$db_prefix}MoviesVotes
					(userID, movieID, rating)
				VALUES
					($ID_MEMBER, $topic, $rating)", __FILE__, __LINE__);
		$request = db_query("SELECT COUNT(*) AS n_votes FROM {$db_prefix}MoviesVotes WHERE movieID=$topic", __FILE__, __LINE__);
		$row = mysql_fetch_assoc($request);
		$request = db_query("SELECT AVG(vote) AS average FROM {$db_prefix}MoviesVotes WHERE movieID=$topic", __FILE__, __LINE__);
		$row2 = mysql_fetch_assoc($request);
		$rating = number_format($row2['average'], 2, ‘.’, ”);
		db_query("UPDATE {$db_prefix}Movies SET n_votes='$row['n_votes']', average='$rating' WHERE movieID=$topic", __FILE__, __LINE__);		
	}

and this on the xxx.template.php:

f($context['user']['is_logged'])
		{
				if($context['already_voted'])
				{
				
				
				echo '<b>',$context['past_rate'],'</b>&nbsp;<form action="', $scripturl, '?topic=', $context['current_topic'], ' method="POST" style="margin: 0;">
							<input type="hidden" name="sc" value="', $context['session_id'], '" />
							<input type="submit" name="Reset" value="Reset" />
						</form>';
				}
				else
				{
					echo '						
							
					<form action="', $scripturl, '?topic=', $context['current_topic'], ' method="POST" style="margin: 0;">
						<select name="vote">';
					for ($i = 1; $i <= 10; $i++)
						echo '
								<option value="', $i, '">', $i, '</option>';
					echo '
							</select>
							<input type="hidden" name="sc" value="', $context['session_id'], '" />
							<input type="submit" name="Rate" value="Rate" />
					</form>';
				}
		}
		else
		{
			echo'You need to be loged in order to rate this movie';
		 }

the variables relative to the board are correct so the problem is not there, I made some testing and noticed that for example the forms seems that are not sending values and I can't understand why :p and also that popup message appear . The IFs on the xxx.template file are working at 100% btw so there's not a problem there either

[tesco]

I notice you're using commas instead of dots, for example here:

echo '<b>',$context['past_rate'],'</  

Is this how it works for your language or is it a mistake?

[TorrentMaster9]

I notice you're using commas instead of dots, for example here:

echo '<b>',$context['past_rate'],'</  

Is this how it works for your language or is it a mistake?

There, I think, commas and dots work the same, well I'll make some tests(also on the web server) on the code when I finish my exams then I'll give news ,thanks

[dvd4alll]

I think commas are most widely used