Need some php help here and some sql
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
Code:
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
.tables:(vote_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?:
Code:
<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
Re: Need some php help here and some sql
PHP Code:
<?
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>");
PHP Code:
<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.
Re: Need some php help here and some sql
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?"?.
Re: Need some php help here and some sql
Yes, because the creator wanted it to say that. :P
HTML Code:
<form method="POST" action="vote.php" onsubmit="return confirm('Are you sure?');">
Re: Need some php help here and some sql
Quote:
Originally Posted by
rossco
Yes, because the creator wanted it to say that. :P
HTML Code:
<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
Code:
// 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:
Code:
f($context['user']['is_logged'])
{
if($context['already_voted'])
{
echo '<b>',$context['past_rate'],'</b> <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
Re: Need some php help here and some sql
I notice you're using commas instead of dots, for example here:
PHP Code:
echo '<b>',$context['past_rate'],'</
Is this how it works for your language or is it a mistake?
Re: Need some php help here and some sql
Quote:
Originally Posted by
rossco
I notice you're using commas instead of dots, for example here:
PHP Code:
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
Re: Need some php help here and some sql
I think commas are most widely used
