PHP app: on new record insert show/hide content

My PHP app is an index page with a contest where a user can submit a response and become eligible to win a prize. From all the submissions, I choose a winner by inserting (on a private admin page) a new record in a MySQL ‘winners’ table. When that happens, and a new record is inserted, I would like the textarea and submit button on the index page to become hidden and to echo a message like “Thank all our visitors for their submissions. Come back on Friday (or Tuesday) for another chance to win a prize.” Is there a way to do this with SQL and PHP? The bare bones are below. The problem is the SQL logic.

<?
function isNewRecExist() {
// implement your logic and return a true or false value
}
$newRecExist = isNewRecExist();

if ( $newRecExist ) {
// code for outputting the fbml tags for textArea & buttons
} else {
// code for outtputting the “come back later” message
}

?>

Any suggestions would be greatly appreciated.

Welcome to the SP forums :slight_smile:

What is the logic to decide if ‘a new rec exists’ ?
What is the table structure?

If each question can have only one winner, you could just add a ‘winner’ column to the question table. No need for a different winner table.

Using this function
// to show/hide the answer box and submit button and show a message
//need to grab the hasWinner value of 0 or 1
function showHide($hasWinner)
{
$link = open();
$query = “select q.id, q.value question, q.end_date, (case when w.id is null then false else true end) hasWinner from question q
left outer join winner w on (w.question_id=q.id)
order by start_date desc
limit 1=$hasWinner”;
$stmt = mysqli_prepare($link, $query);
mysqli_execute($stmt);
mysqli_stmt_close($stmt);

	close($link);
}

and calling it on the index page like this

if (showHide($hasWinner) == 0)
	{
	include 'tuesday.php';
	}
else
{
	echo "Thank you to everyone who entered.";
	}

//the next line is just to see if a value is returned
<p>
<?php
echo “$hasWinner”;

?&gt;&lt;/p&gt;

This doesn’t work. I’m missing something. If I manually change the 0 to 1 in this line, if (showHide($hasWinner) == 0), the show/hide works. But it doesn’t work automatically if I change the value in the database.

Does anyone spot my mistake here?

Guido, thank you for your suggestion. That SQL query did the trick. I created a function to call it, and it appears to work. I’m doing some testing now. I appreciate your help.

I simplified and found this query does just what I want. Any comments?

									&lt;?php
//Give what day of the week it is. Returns Sunday through Saturday.
//$day = date("l");
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$query="SELECT `has_winner`

FROM question
ORDER BY start_date DESC
LIMIT 1";
$result=mysql_query($query);
$hasWinner=mysql_result($result, 0, has_winner);

if ($hasWinner == 0)
	{
	include ('tuesday.php');
	}
else
{
	echo "Thank you to everyone who entered.";
	}

echo “$hasWinner”;
?>

id question_id answer_id prize_id date_created

I was thinking of checking against the date comparing to $day = date(“l”);

Then I got another suggestion for the SQL:

select q.id, q.value question, q.end_date, (case when w.id is null then false else true end) hasWinner from question q
left outer join winner w on (w.question_id=q.id)
order by start_date desc
limit 1

If the winner value is ‘0’ then show the content, if ‘1’ then hide it.

Getting closer. I put this on the index page.
<?php
//Give what day of the week it is. Returns Sunday through Saturday.
//$day = date(“l”);
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( “Unable to select database”);

$query="select q.id, q.value question, q.end_date, (case when w.id  is null then false else true end) hasWinner from question q

left outer join winner w on (w.question_id=q.id)
order by start_date desc
limit 1";
$result=mysql_query($query);
$hasWinner=mysql_result($result,‘hasWinner’);

if ($hasWinner == 0)
	{
	include ('tuesday.php');
	}
else
{
	echo "Thank you to everyone who entered.";
	}

echo “$hasWinner”;
?>

And it returns a value from the database, but it’s not the hasWinner Boolean value, which is what I need. How do I get that value?