Hi, all:
I am working on an application that allows Peace organizations to sign up and promote their actions to other organizations for support and participation.
Everything is working now, finally thanks to help from people here, but would like to have you MySQL gurus and PHP honchos to look this over and tell me if what I am doing can be done more efficiently/smarter...
Thanks!
<?php
// include functions
include_once('../includes/functions.php');
// make sure user is logged in
checkStatus();
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<link rel="stylesheet" type="text/css" href="../includes/global.css" />
<title>Actions Summary</title>
</head>
<body>
<div id="main">
<h1>Actions Summary Page</h1>
<?php
// call function to connect to database
$sql = dbconnect();
// ACTIONS:
// action_id,action_org_id,action_name,action_start_date,action_end_date,action_vote_end_date,action_description,action_url,action_image,action_document,action_timestamp,action_display
$query = "SELECT action_id, action_org_id, action_name, action_start_date FROM actions WHERE CURDATE() <= action_start_date AND action_display = 'yes'";
// execute query
$result = @mysql_query($query) or db_error();
// define the arrays and variables
$i = 0;
$actionsArray = array();
while($row = mysql_fetch_array($result))
{
$actionsArray[$i] = array('id'=>$row['action_id'], 'org'=>$row['action_org_id'], 'name'=>$row['action_name'], 'date'=>$row['action_start_date']);
$i++;
}
// VOTES:
// vote_id, action_id, voter_org_id, vote, vote_timestamp
$query = "SELECT action_id, voter_org_id, vote FROM votes WHERE action_id IN(";
$i = 0; // reset counter
foreach($actionsArray as $actionInfo)
{
if($i != 0)
{
$query .= ',' . $actionInfo['id'];
}
else {
$query .= $actionInfo['id'];
}
$i++;
}
$query .= ")";
// execute query
$result = @mysql_query($query) or db_error();
$votesArray = array(); // create an array
$i = 0; // reset counter
while($row = mysql_fetch_array($result))
{
$votesArray[$i] = array('actionID'=>$row['action_id'],'orgID'=>$row['voter_org_id'], 'vote'=>$row['vote']);
$i++;
}
// ORGANIZATIONS:
// org_id, org_name, org_mission, org_description, org_logo, org_url, org_timestamp, org_display
$query = "SELECT org_id, org_name, org_url FROM organizations WHERE org_display = 'yes'";
// execute query
$result = @mysql_query($query) or db_error();
$i = 0; // reset counter
$orgsArray = array(); // create an array
while($row = mysql_fetch_array($result))
{
$orgsArray[$i] = array('id'=>$row['org_id'], 'name'=>$row['org_name'], 'url'=>$row['org_url']);
$i++;
}
?>
<table cellpadding="10" cellspacing="0" border="1" style="border-collapse:collapse; width:100%;">
<tr>
<th><?php echo count($actionsArray); ?> actions <img src="../images/site/arrow-right.gif" alt="arrow" width="18" height="18" border="0" align="absmiddle">
<br /><?php echo count($orgsArray); ?> organizations <img src="../images/site/arrow-down.gif" alt="arrow" width="18" height="18" border="0" align="absmiddle"></th>
<?php
// loop through all the actions and give them their own table heading
foreach($actionsArray as $val)
{
echo '<th>';
echo '<a href="details.php?id=' . $val['id'] . '">' . $val['name'] . '</a>';
echo '<br />' . $val['date'];
echo '</th>' . "\n";
}
?>
</tr>
<?php
// loop through all the organizations and their votes for each action
foreach($orgsArray as $org)
{
echo '<tr>' . "\n";
echo '<td>' . $org['name'] . '</td>' . "\n";
// create a loop that goes through all the actions:
foreach($actionsArray as $actionInfo)
{
// if this org is the sponsor of this action
if($actionInfo['org'] == $org['id'])
{
echo '<td class="participate"><span class="sponsor">Sponsor</span></td>' . "\n";
}
else {
// create a vote variable
$voted = 0;
// for each action, loop through the votes
foreach($votesArray as $vote)
{
// if there is a vote for a particular action
if($actionInfo['id'] == $vote['actionID'])
{
// if this org placed this vote
if($org['id'] == $vote['orgID'])
{
// if they voted to support
if($vote['vote'] == 'yes')
{
$voted = 1;
}
// if they voted to participate
elseif($vote['vote'] == 'participate')
{
$voted = 2;
}
// else they voted NOT to support
else {
$voted = -1;
}
}
}
}
if($voted == 2)
{
echo '<td class="participate">Participate</td>' . "\n";
}
elseif($voted == 1)
{
echo '<td class="support">Support</td>' . "\n";
}
elseif($voted == -1)
{
echo '<td class="not_support">Not Support</td>' . "\n";
}
else {
echo '<td class="none"> </td>' . "\n";
}
}
} // .foreach
echo '</tr>' . "\n";
}
?>
</tr>
</table>
<?php
// close MySQL connection
$close = @mysql_close();
?>
<p><a href="javascript:history.back(1);">Click here</a> to go back, or <a href="../">click here</a> to return to our homepage.</p>
</div>
</body>
</html>
