Dynamic SQL Query

Discussion in 'PHP' started by TOSCS, Mar 10, 2011.

0
  1. #1
    My table 'list' is:

    `id` `name` `votes_up` `votes_down`

    I want to output this to a webpage, but instead of having votes_up and votes_down, I just want 'Votes', which would obviously be (votes_up - votes_down)

    How would I express this in PHP/SQL?
     
    TOSCS, Mar 10, 2011 IP
  2. G3n3s!s

    G3n3s!s Active Member

    Messages:
    325
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    80
    #2 
    $sql = 'SELECT id,name, votes_up -  votes_down as Votes;';
    PHP:
     
    G3n3s!s, Mar 10, 2011 IP
  3. TOSCS

    TOSCS Member

    Messages:
    79
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    41
    #3
    Cheers!

    Would you be able to tell me how I can produce a page with, say, the Top 10 voted entries? Would that require a loop?
     
    TOSCS, Mar 10, 2011 IP
  4. G3n3s!s

    G3n3s!s Active Member

    Messages:
    325
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    80
    #4
    $sql = 'SELECT id,name, votes_up - votes_down as Votes ORdeR by votes_up desc;';



    this does sort it from most votes_up to 0 votes_up
     
    G3n3s!s, Mar 10, 2011 IP
  5. ThePHPMaster

    ThePHPMaster Well-Known Member

    Messages:
    737
    Likes Received:
    52
    Best Answers:
    33
    Trophy Points:
    150
    #5
    The above query will not work if something has more votes_down than UP. You need to ORDER BY Votes DESC, not BY votes_up DESC.

    
$sql = 'SELECT id,name, votes_up - votes_down as votes ORDER BY votes DESC LIMIT 10';
    Code (markup):
     
    ThePHPMaster, Mar 10, 2011 IP
  6. TOSCS

    TOSCS Member

    Messages:
    79
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    41
    #6
    This is what's happening right now...
    http://www.ijustcantgetenough.net/topten.php

    It's looping the whole page! this is my code
    <body>
<?php
/**
Display the results from the database
**/

$q = "SELECT id, title, votes_up - votes_down as votes FROM entries ORDER BY votes DESC LIMIT 3";
$r = mysql_query($q);

if(mysql_num_rows($r)>0): //table is non-empty
	while($row = mysql_fetch_assoc($r)):
		$net_vote = $row['votes_up'] - $row['votes_down']; //this is the net result of voting up and voting down
$subdate = date('F d, Y', strtotime($row['time'])); 

?>


<div class='entry'>
<center><img src="images/logo.png" /><div class="menu"><?php include("menu.php"); ?></div></center>
	<span class='link'>
		<h2>Top Ten</h2>
<p>Soon you will be able to see the Top 10 things that visitors Just Can't Get Enough of!</p>
<h1><?php echo $row['title']; ?></h1>
	</span>
	<p>
<?php 
if($row['time']!=0){

echo "Submitted on " .$subdate.""; }


?><br />
<span class='votes_count' id='votes_count<?php echo $row['id']; ?>'><?php echo $net_vote." votes"; ?></span>
	  
	  <span class='vote_buttons' id='vote_buttons<?php echo $row['id']; ?>'>
		  <a href='javascript:;' title='Vote Up' class='vote_up' id='<?php echo $row['id']; ?>'>Vote Up!</a>
		  <a href='javascript:;' title='Vote Down' class='vote_down' id='<?php echo $row['id']; ?>'>Vote Down!</a><br />
</span>          <a href="/"><img src="images/refresh_icon.png" width="16" height="16" border="0" title="Another wan!" /></a>
          <a href="/index.php?report=<?php echo $row['id']; ?>"><img src="images/flag.gif" width="16" height="16" border="0" title="Flag for abuse" /></a></p>
    PHP:
     
    TOSCS, Mar 11, 2011 IP
  7. G3n3s!s

    G3n3s!s Active Member

    Messages:
    325
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    80
    #7
    you do not have any </div> for class 'entry' there. To have top10, you have to check also votes down, so

    $sql = 'SELECT id,name, votes_up - votes_down as Votes ORdeR by votes_up-votes_down desc;';
     
    G3n3s!s, Mar 11, 2011 IP
  8. TOSCS

    TOSCS Member

    Messages:
    79
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    41
    #8
    I also don't have a </body>... that's only a snippet of the code (the relevant part of that page)

    I'll try that query, thanks
     
    TOSCS, Mar 11, 2011 IP
  9. G3n3s!s

    G3n3s!s Active Member

    Messages:
    325
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    80
    #9
    you started while($row = fetch()):

    but you do not have endwhile;

    do you got it there?
     
    G3n3s!s, Mar 11, 2011 IP
  10. TOSCS

    TOSCS Member

    Messages:
    79
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    41
    #10
    yes I have
    	endwhile;
endif;
    PHP:
    At the end of my page
     
    TOSCS, Mar 12, 2011 IP
  11. TYPELiFE

    TYPELiFE Peon

    Messages:
    109
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #11
    Have you run

    SELECT id, title, votes_up - votes_down as votes FROM entries ORDER BY votes DESC LIMIT 3
    Code (markup):
    Directly in MySQL console or other administration tool?
     
    TYPELiFE, Mar 12, 2011 IP
  12. TOSCS

    TOSCS Member

    Messages:
    79
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    41
    #12
    Just did it there mate and it works a treat. Now I just need to figure out how to echo those results onto a PHP page
     
    TOSCS, Mar 12, 2011 IP