Your Ad Here Your Ad Here
Results 1 to 8 of 8

Thread: Need some php help here and some sql

  1. #1
    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
    .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?:
    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
    http://filesharingtalk.com/boardrules/

  2. Internet, Programming and Graphics   -   #2
    tesco's Avatar woowoo
    Join Date
    Aug 2003
    Location
    Canadia
    Posts
    24,069
    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.

  3. Internet, Programming and Graphics   -   #3
    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?"?.
    http://filesharingtalk.com/boardrules/

  4. Internet, Programming and Graphics   -   #4
    tesco's Avatar woowoo
    Join Date
    Aug 2003
    Location
    Canadia
    Posts
    24,069
    Yes, because the creator wanted it to say that.

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

  5. Internet, Programming and Graphics   -   #5
    Quote Originally Posted by rossco View Post
    Yes, because the creator wanted it to say that.

    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>&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
    http://filesharingtalk.com/boardrules/

  6. Internet, Programming and Graphics   -   #6
    tesco's Avatar woowoo
    Join Date
    Aug 2003
    Location
    Canadia
    Posts
    24,069
    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?

  7. Internet, Programming and Graphics   -   #7
    Quote Originally Posted by rossco View Post
    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
    http://filesharingtalk.com/boardrules/

  8. Internet, Programming and Graphics   -   #8
    dvd4alll's Avatar BANNED BT Rep: +13BT Rep +13BT Rep +13
    Join Date
    Oct 2007
    Location
    Helioplis
    Posts
    275
    I think commas are most widely used

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
  •