Need Help on a Few Things

[secretply]

Please try to bear with me, I'm a newbie at PHP. I have a form here that will be submitted to a PHP page and from there submitted to a table in a database and finally shows the submitted information in a table. I need help sorting the table by columns and filling in one field.

Here's my form:

<?php

$tournamenttype = array (1 => 'Point Tournament','Bounty Tournament','Jacks Tournament','Team Tournament');
$numberofseats = array (1 => '4 Seats','5 Seats','6 Seats','7 Seats','8 Seats','9 Seats','10 Seats','11 Seats');
$buyin = array (1 => '$500','$1,000','$1,500','$2,000','$2,500','$3,000','$3,500','$4,500','$5,000','$6,000','$7,000','$8,000','$9,000','$10,000','$15,000','$20,000','$25,000','$30,000','$40,000','$50,000','$60,000','$70,000','$80,000','$90,000','$100,000','$125,000','$150,000','$175,000','$200,000');
$month = array (1 => 'January','February','March','April','May','June','July','August','September','October','November','December');
$day = array (1 => '1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31');
$year = array (1 => '2010', '2011');
$hour = array (1 => '1','2','3','4','5','6','7','8','9','10','11','12');
$minute = array (1 => '00','01','02','03','04','05','06','07','08','09','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50','51','52','53','54','55','56','57','58','59','60');
$ampm = array (1 => 'A.M.', 'P.M.');

echo '<div><form method="post" action="newevent.php"><input type="hidden" name="submitted" value="true" /><fieldset><legend>Setup</legend><select name="tournamenttype"><optgroup label="Tournament Type">';
foreach ($tournamenttype as $value) { echo '<option value="'.$value.'">'.$value.'</option>'; }
echo '</optgroup></select>';
echo '<select name="numberofseats"><optgroup label="Number of Seats">';
foreach ($numberofseats as $value) { echo '<option value="'.$value.'">'.$value.'</option>'; }
echo '</optgroup></select>';
echo '<select name="buyin"><optgroup label="Buyin">';
foreach ($buyin as $value) { echo '<option value="'.$value.'">'.$value.'</option>'; }
echo '</optgroup></select></fieldset>';
echo '<fieldset><legend>Date</legend><select name="month"><optgroup label="Month">';
foreach ($month as $value) { echo '<option value="'.$value.'">'.$value.'</option>'; }
echo '</optgroup></select>';
echo '<select name="day"><optgroup label="Day">';
foreach ($day as $value) { echo '<option value="'.$value.'">'.$value.'</option>'; }
echo '</optgroup></select>';
echo '<select name="year"><optgroup label="Year">';
foreach ($year as $value) { echo '<option value="'.$value.'">'.$value.'</option>'; }
echo '</optgroup></select></fieldset>';
echo '<fieldset><legend>Time</legend><select name="hour"><optgroup label="Hour">';
foreach ($hour as $value) { echo '<option value="'.$value.'">'.$value.'</option>'; }
echo '</optgroup></select>';
echo '<select name="minute"><optgroup label="Minute">';
foreach ($minute as $value) { echo '<option value="'.$value.'">'.$value.'</option>'; }
echo '</optgroup></select>';
echo '<select name="ampm"><optgroup label="A.M. or P.M.">';
foreach ($ampm as $value) { echo '<option value="'.$value.'">'.$value.'</option>'; }
echo '</optgroup></select>'; 
echo '<input type="submit" value="Create Event" /> <input type="reset" value="Reset Fields" />';
echo '</fieldset></form></div>';

?>

What I want is when the user submits the form, convert the time selected by the user, based on user's current time, to Central Time. (Example: If 5:00 P.M. is selected by user and the user is Eastern Time, then the converted time would be 4:00 P.M.) Since this form will be used around the world, it's better to have just 1 time zone rather than 24 different time zones.

Here is the form submission data:

<?php

if (isset($_POST['submitted']))
{
include('shared/mysqlconnect.php');

$setup = $_POST['tournamenttype'] . "," . " " .$_POST['numberofseats'] . "," . " " . $_POST['buyin'];
$date = $_POST['month'] . " " . $_POST['day'] . "," . " " . $_POST['year'];
$time = $_POST['hour'] . ":" . $_POST['minute'] . " " . $_POST['ampm'];

$sqlinsert = "INSERT INTO schedule (setup, date, time) VALUES ('$setup', '$date', '$time')";

if (!mysqli_query($dbcon, $sqlinsert))
{
die("Event could not be added. Click the back button and try again.");
}
else
{
header("location:http://www.x10hosting.com/");
}
}

?>

(The header location is not the one I'm using but I rather not reveal the location.) This will insert information into the "schedule" table in my database. There is a 4th field that is not shown, countdown, in the table.

Here is the table database:

<?php

include('shared/mysqlconnect.php');

$sqlget = "SELECT * FROM schedule ORDER BY date, time";
$sqldata = mysqli_query($dbcon, $sqlget) or die('Database Connection Failed.');

echo '<table class="innertable"><tr><th class="scheduleheader">Setup</th><th class="scheduleheader">Date</th><th class="scheduleheader">Time</th><th class="scheduleheader">Countdown</th></tr>';
while($row = mysqli_fetch_array($sqldata, MYSQLI_ASSOC)) { echo '<tr><td class="data">';
echo $row['setup'];
echo '</td><td class="data">';
echo $row['date'];
echo '</td><td class="data">';
echo $row['time'];
echo '</td><td class="data">';
echo $row['countdown'];
echo '</td></tr>'; }
echo '</table>';

?>

I want the countdown field to be automatically calculated by taking the difference of the user's time by the time in the table. (Example: 3 days, 8 hours, 4 minutes left) Also, I want the table to be sorted when the <th> text is clicked. Any help would be greatly appreciated. Thanks.

 

[bhupendra2895]

You can sort table data using sql

select * from table orderby id DESC

ASC= ascending order.
DESC= descending order.This sql code sorts the table data according to id coloumn.
For doing this when user clicks column name you can use get method ($_GET).
As far as timezone issue is concerned I am looking for a solution too, I am a newbie too.

Next time please post only necessary segment of the program to explain the problem.

 

[secretply]

Thanks for the reply. Unfortunately, that is not what I wanted to do. I want the viewer to be able to sort the data when the viewer clicks the header text. The SQL sort only sorts one column by default when the table is loaded. Can you show an example with the $_GET variable, please? (I've been looking at Google and it seems that the page refreshes because an anchor tag is introduced, which is not what I would prefer.) The reason I posted all the code is because when some people post only certain segments, some people want the whole code to see what the other code is doing.

 

[bhupendra2895]

I don't have experience with java script or ajax, but they will be suitable if you want to do things without refreshing the page.

Example code for get request:-

<?php
$order = 'DESC';
if($_GET['order']==DESC)
{
$order = 'ASC';
}
if ($_GET['q']==1)
{
$sql = 'select * from table orderby id '.$order;
}
?>
//table header with get functionality
<th><a href=<?php echo $_SERVER['PHP_SELF'];?>?q=1</th>

You can use xmlhttprequest instead of get i.e. Ajax.

Disclamer :- Code written above is unsecure.You should do necessary clean up before processing user data.Read Php documentation about the security for that.

 

[misson]

I want the countdown field to be automatically calculated by taking the difference of the user's time by the time in the table. (Example: 3 days, 8 hours, 4 minutes left)

When PHP 5.3 is widespread, you can use DateInterval. Until then, you can use Unix timestamps internally. Rather than having separate date and time colums, store both in a single "when" column with type INT. If the dates will never be more than one month in advance (that is, the countdown will never include the number of months) or you don't care to use months, figuring out the interval in weeks, days, hours, minutes and seconds (WDHMS) is simple. Subtract the current date-time from the date-time of the event, which gives you the number of seconds until the event. You can convert this to WDHMS by using div & mod. Timezones won't affect the countdown calculation, as long as both timestamps are in the same one. For example:

function interval2array($interval) {
  $coundown = array();
  foreach( array('second' => 60, 'minute' => 60, 'hour' => 24, 'day' => 7) as $unit => $per ) {
    $countdown[$unit] = $interval % $per;
    $interval = (int)($interval / $per);
  }
  $countdown['week'] = $interval;
  return $countdown
}
function date_interval($from, $to) {
  return interval2array($to - $from);
}

Or:

// if you don't use UTC, you have to adjust $from-$to by the timezone offset
date_default_timezone_set('UTC');
[...]
function date_interval($from, $to) {
  return getdate($to-$from);
}

Converting from an array representation of a date-time interval to a string is left as an exersize.

If you want to include months in a countdown, it's trickier. We'll come back to that, if that's what you want.

Also, I want the table to be sorted when the <th> text is clicked.

Read "How to sort a html table." Alternatively, you can use a JS library and find a good plugin to sort the table (e.g. jQuery table sorters).

<?php

$tournamenttype = array (1 => [...]);
[...]

Since the indices don't matter, starting at 1 won't hurt, but it does look odd. There's no good reason to do so.

echo '<div><form method="post" action="newevent.php"><input type="hidden" name="submitted" value="true" /><fieldset><legend>Setup</legend><select name="tournamenttype"><optgroup label="Tournament Type">';
foreach ($tournamenttype as $value) { echo '<option value="'.$value.'">'.$value.'</option>'; }

Switching between PHP and HTML (rather than using echo) can be more readable:

<?php
$tournamenttype = array ('Point', 'Bounty', 'Jacks', 'Team');
[...]
?>
  <form method="post" action="newevent.php">
    <input type="hidden" name="submitted" value="true" />
    <fieldset>
      <legend>Setup</legend>
      <select name="tournamenttype">
        <optgroup label="Tournament Type">
          <?php foreach ($tournamentTypes as $type) { ?>
            <option value="<?php echo $type ?>"><?php echo $type ?> Tournament</option>
          <?php } ?>
        </optgroup>
      </select>

Also, use descriptive variable names; $value isn't as descriptive as the context allows. If a variable is composed of multiple words, the words should be capitalized or separated by underscores. It doesn't matter which approach you pick, as long as you're consistent.

$numberofseats = array (1 => '4 Seats','5 Seats','6 Seats','7 Seats','8 Seats','9 Seats','10 Seats','11 Seats');
[...]
foreach ($numberofseats as $value) { echo '<option value="'.$value.'">'.$value.'</option>'; }
[...]

for loops make more sense than foreach in most of these, since they loop over a range of integers.

      <select name="numberofseats">
        <optgroup label="Number of Seats">
	      <?php for ($seats=$minseats; $seats <= $maxseats; ++$seats) { ?>
            <option value="<?php echo $seats ?>"><?php echo $seats ?> Seats</option>
		  <?php } ?>
		</optgroup>
	  </select>
[...]
      <select name="day">
        <optgroup label="Day">
          <?php for ($day=1; $day <= 31; ++$day){ ?>
            <option value="<?php echo $day ?>"><?php echo $day ?></option>
          <?php } ?>
        </optgroup>
      </select>

If you want to get fancy, you can write functions to output the select elements:

<?php
$tournamenttype = array ('point' => 'Point Tournament', 'bounty' => 'Bounty Tournament', 'jacks' => 'Jacks Tournament', 'team' => 'Team Tournament');
$buyin = array(
  500 => '$500', 1000 => '$1,000', 1500 => '$1,500', 2000 => '$2,000', 2500 => '$2,500', 3000 => '$3,000', 3500 => '$3,500', 4500 => '$4,500', 
  5000 => '$5,000', 6000 => '$6,000', 7000 => '$7,000', 8000 => '$8,000', 9000 => '$9,000',
  10000 => '$10,000', 15000 => '$15,000', 20000 => '$20,000', 25000 => '$25,000', 
  30000 => '$30,000', 40000 => '$40,000', 50000 => '$50,000', 60000 => '$60,000', 70000 => '$70,000', 80000 => '$80,000', 90000 => '$90,000', 
  100000 => '$100,000', 125000 => '$125,000', 150000 => '$150,000', 175000 => '$175,000', 200000 => '$200,000');

function printRangedSelect($name, $label, $min, $max, $fmt='%d') {
  ?>
  <select name="<?php echo $name ?>">
    <optgroup label="<?php echo $label ?>">
       <?php for ($i=$min; $i <= $max; ++$i){ ?>
         <option value="<?php echo $i ?>"><?php printf $fmt, $i ?></option>
       <?php } ?>
    </optgroup>
  </select>
  <?php
}

function printArrayedSelect($label, $values) {
  ?>
  <select name="<?php echo tolower(preg_replace('/\W+/', '', $name)) ?>">
    <optgroup label="<?php echo $label ?>">
       <?php foreach ($values as $value) { ?>
         <option value="<?php echo $value ?>"><?php echo $value ?></option>
       <?php } ?>
    </optgroup>
  </select>
  <?php
}
[...]
?>
  <form method="post" action="newevent.php">
    <input type="hidden" name="submitted" value="true" />
    <fieldset>
      <legend>Setup</legend>
      <?php
        printArrayedSelect('Tournament Type', $tournamentTypes);
        printRangedSelect('Number of Seats', 4, 11, '%d Seats');
        printArrayedSelect('Buyin', $buyins);
      ?>
    </fieldset>
    <fieldset>
      <legend>Date</legend>
      <?php
        printArrayedSelect('Month', $months);
        printRangedSelect('Day', 1, 31);
        [...]

What I want is when the user submits the form, convert the time selected by the user, based on user's current time, to Central Time.

Do you want to do this automatically, or have users select their timezone?

Rather than using Central Time, I recommend using UTC. You'll have to make fewer adjustments when performing date-time calculations.

<?php

if (isset($_POST['submitted']))
{
include('shared/mysqlconnect.php');

$setup = $_POST['tournamenttype'] . "," . " " .$_POST['numberofseats'] . "," . " " . $_POST['buyin'];

Don't denormalize these fields; stick to first normal form. Keep tournament type, number of seats and buyin in separate fields.

Indent your code for readability.

$date = $_POST['month'] . " " . $_POST['day'] . "," . " " . $_POST['year'];
$time = $_POST['hour'] . ":" . $_POST['minute'] . " " . $_POST['ampm'];

$sqlinsert = "INSERT INTO schedule (setup, date, time) VALUES ('$setup', '$date', '$time')";

This is very bad. This is open to SQL injection. The mysqli driver supports prepared statements; use them. You can also switch to PDO, which is more usable.

if ('A' == strtoupper($_POST['ampm'][0])) {
    $_POST['hour'] += 12;
}
$when = mktime($_POST['hour'] + (strtoupper($_POST['ampm'][0]) = 'A' ? 0 : 12), $_POST['minute'], $_POST['second'], $_POST['month'], $_POST['day']);

$insertGame = $db->prepare('INSERT INTO schedule (type, seats, buyin, FROM_UNIXTIME(`when`))
    VALUES (:type, :seats, :buyin, :when)');
$insertGame->execute(array(':type' => $tournamentType, ':seats' => $seats, ':buyin' => $buyin, ':when' => $when));

Read "Writing MySQL Scripts with PHP and PDO" for a PDO tutorial.

You should validate the date (using e.g. checkdate) and time.

if (!mysqli_query($dbcon, $sqlinsert))
{
die("Event could not be added. Click the back button and try again.");

Don't use or die.

<?php

include('shared/mysqlconnect.php');

$sqlget = "SELECT * FROM schedule ORDER BY date, time";

Don't use SELECT *.

echo '<table class="innertable"><tr><th class="scheduleheader">Setup</th><th class="scheduleheader">Date</th><th class="scheduleheader">Time</th><th class="scheduleheader">Countdown</th></tr>';

Put the header row in a <thead> element, and the table contents in a <tbody>.

 

[misson]

Here's an example rewrite of your script to display the tournament table. LocalDB.php defines a class that manages database connections; for example implementations, see "Display all that would be secret while Mysql is broken", "MySQL and PHP", "Redirect when sql server fails".

<?php

include('LocalDB.php');
date_default_timezone_set('UTC');

[...]
// whitelist of fields that can be sorted by
$sortableFields = array('setup'=>1, 'date'=>1, 'time'=>1, 'countdown'=>1, 'when'=>1, 'type'=>1, 'seats'=>1, 'buyin'=>1));
if (isset($_POST['sortby']) && isset($sortableFields[strtolower($_POST['sortby'])])) {
  $orderby = $_POST['sortby'];
} else {
  $orderby = 'setup';
}
try {
  $db = LocalDB::connect();
  $games = $db->query("SELECT CONCAT(type, ' Tournament, ', seats, ' seats, $', FORMAT(buyin, 0)) AS `setup`, 
        DATE(`when`) AS `date`, TIME(`when`) AS `time`, UNIX_TIMESTAMP(`when`) - UNIX_TIMESTAMP(UTC_TIMESTAMP()) AS countdown 
      FROM schedule 
      ORDER BY `$orderby`");
  $games->setFetchMode(PDO::FETCH_ASSOC);
  ?>
  <table id="games" class="innertable">
    <thead>
      <tr><th class="scheduleheader" id="setup">Setup</th><th class="scheduleheader" id="date">Date</th><th class="scheduleheader" id="time">Time</th><th class="scheduleheader" id="countdown">Countdown</th></tr>
    </thead>
    <tbody>
      <?php foreach ($games as $game) { 
        $game['countdown'] = interval2string($game['countdown']);
        ?>
        <tr><td class="data"><?php echo implode('</td><td class="data">', $game); ?></td></tr>
      <?php } ?>
    </tbody>
  </table>
  <script type="text/javascript">
    $('#games th').click(sortby); 
  </script>
  <?php
} catch (PDOException $exc) {
  echo 'I had an internal error when searching for games. It's been logged, and we'll look into it.';
  // now log the error
  error_log($exc);
  [...]
}

(Note: Personally, it makes more sense to me to keep the tournament type, number of seats and buy-in as separate columns, but I kept your table structure to illustrate how to combine columns in a result.)

Define sortby as a JS function that sorts the table by a column. You could sort the table client-side (see sorttable or try jQuery or Prototype-based table sorting), using AJAX, or embed the table in a form. Here's a JS free version of the last option:

<form ...>
  <table>
    <thead>
      <tr><th><input type="submit" name="sortby" value="Setup" /></th><th><input type="submit" name="sortby" value="Date" /></th><th><input type="submit" name="sortby" value="Time" /></th><th><input type="submit" name="sortby" value="Countdown" /></th></tr>
    </thead>
    ...
  </table>
</form>

Use CSS so the column headers don't look like buttons. You could combine the form and AJAX techniques, so it will degrade gracefully when JS is disabled or not supported. If you do this, get the form version to work first, then add AJAX functionality.

There are still quite a few improvements that can be made. The example code couples the data model (i.e. the DB table structure), data access (the SQL queries) and the view of the data (the HTML table) far too tightly. Each should be handled by a separate component. These are more advanced topics; you can come back to them at a later time. One advantage of this approach is it vastly simplifies your scripts. For example, the above might look like:

try {
  $games = Game::fetch(.../* specify which games to get and in which order */);
  $doc->append(new GridView($games));
} catch (PDOException $exc) {
  $doc->append(new ErrorMessage("I had an internal error while searching for games. It's been logged, and we'll look into it"));
}