I'm stuck aswell, about php loop and mysql

[Zephyris]

I'll start off by saying hi and thanks in advance.

 

This is a poll script, in my mysql I have a table named

weekly_polls( id,author, question, date, time, option1, option2 ~ upto option10 )

 

Options are possible answers to the poll. What I'm trying to do is a loop that get's all those options(answers) and display them on my page only if the field of the table is not empty. So I came up with this:

// Weekly Poll
$result2 = mysql_query("SELECT * FROM `weekly_polls` ORDER BY `date`, `time` DESC LIMIT 1");
$row2 = mysql_fetch_array($result2)

// Check Options / Answers
		  $oavail = 0;
		  if(!empty($rows2['option1'])){ $oavail += 1; }
		  if(!empty($rows2['option2'])){ $oavail += 1; }
		  if(!empty($rows2['option3'])){ $oavail += 1; }
		  if(!empty($rows2['option4'])){ $oavail += 1; }
		  if(!empty($rows2['option5'])){ $oavail += 1; }
		  if(!empty($rows2['option6'])){ $oavail += 1; }
		  if(!empty($rows2['option7'])){ $oavail += 1; }
		  if(!empty($rows2['option8'])){ $oavail += 1; }
		  if(!empty($rows2['option9'])){ $oavail += 1; }
		  if(!empty($rows2['option10'])){ $oavail += 1; }
		  
for( $num=1; $num < $oavail; $num += 1 ){
echo('<tr><td bgcolor="#000000"> <input name="" type="radio" value="" />'.($row2['option'.$num]).'</td></tr>');
} 

 

But the thing is, it isnt workign yet... it's not displaying anything.

[aebstract]

I'm not sure if it will help or not, but why do you have ( ) around your echo'd section? Not sure if you can do that or not.

[trq]

First things first. Stroing all the otions in there own field is probably a poor db design in the first place. Basically any time you see opt1 opt2 opt3 etc etc as field names, something is wrong.

 

What exactly is this table meant to be storing?

[cooldude832]

turn up your error reporting off a first glance you missed a semi colon after $row2 = mysql_fetch

 

also the query could be error so add an Or die(mysql_error()); to all queries

[revraz]

Wouldn't hurt to use a DATETIME field also instead of a DATE and a TIME field, or use Unix time.

[Zephyris]

Thanks for the replies,

 

the thing is I'm not getting any error and I just added the "or die.." and fixed some of the things.. at first it was displaying all 10 options... until I added in if empty... options contain text.. it's basicly all the answers possible.. up to 10 answers max. I'm doing this check to see which is empty... and calculating the total of options that aren't empty so I can then echo the answers up to the last none empty one.

 

I'm using date,time separatly because sometimes I just want the date and sometimes just the time...

[Zephyris]

I fixed it myself, it happens it just didn't like my code. I optimized it and now it works... awesome uh

 

for those interested:

// Check Options / Answers
for( $num=1; $num < 10; $num += 1 ){
if( !empty($row2['option'.$num]) ) {
              	echo '<tr><td bgcolor="#000000"> <input name="" type="radio" value="" />'.($row2['option'.$num]).'</td></tr>';
}
} 

[trq]

I would still look at a different db design. I didn't have time to post before, but IMO this would be a better design which allows limitless options for each poll.

 

CREATE TABLE polls (
  id INT AUTO_INCREMENT,
  question TEXT,
  stamp TIMESTAMP
);

CREATE TABLE poll_options (
  id INT AUTO_INCREMENT,
  poll_id INT,
  option TEXT,
  votes INT
);

 

Then, to create a new poll you might use a function something like...

 

<?php

  function create_poll($question,$options) { 
    // connect to db.
    $sql = "INSERT INTO polls (question,stamp) VALUES ('$question',NOW());";
    if (mysql_query($sql)) {
      $poll_id = mysql_insert_id();
      foreach ($options as $option) {
        $sql = "INSERT INTO poll_options (poll_id, option) VALUES ($poll_id,'$option');";
        if (!mysql_query($sql)) {
          return false;
        }
      }
    } else {
      return false;
    }
    return true;
  }

?>

 

Just an idea.

[Zephyris]

i was thinking of doing this for my forum polls db, but id need a vote table to check account id and ips. thats what im doing for my weekly poll.. adding a vote db with account id and ip so u can check for double votes.