Need help with this date script please

[joshm101]

I am a PHP newbie and need some help.

I have a select input that requires some php date coding. Any help would be much appreciated.

PLUS, I am in Australia so I'm not sure if there is any confliction when writing the date.

 

<select style="height:100px;" name="status[]" id="status[]" multiple>

                                                                <?php

                                                                            $statusstring = implode(",", $status);
                                                                            $status = $_SESSION['status'];
                                                                            
                                                                            
                                                                                if ($dateTime = new DateTime('2019-07-19'))
                                                                                {
                                                                                    print "<option>Full Member</option>";
                                                                                    print "<option>Social Member</option>";
                                                                                    print "<option selected>Non Member</option>";
                                                                                }
                                                                            
                                                                                else if ($status == 'Full Member')
                                                                                {
                                                                                    print "<option selected>Full Member</option>";
                                                                                    print "<option>Social Member</option>";
                                                                                    print "<option>Non Member</option>";
                                                                                }
                                                                                
                                                                                else if ($status == 'Social Member')
                                                                                {
                                                                                    print "<option>Full Member</option>";
                                                                                    print "<option selected>Social Member</option>";
                                                                                    print "<option>Non Member</option>";
                                                                                }
                                                                                
                                                                                else if ($status == 'Non Member')
                                                                                {
                                                                                    print "<option>Full Member</option>";
                                                                                    print "<option>Social Member</option>";
                                                                                    print "<option selected>Non Member</option>";
                                                                                }
                                                                                
                                                                                else 
                                                                                {
                                                                                    print "<option>Full Member</option>";
                                                                                    print "<option>Social Member</option>";
                                                                                    print "<option>Non Member</option>";
                                                                                }

                                                                ?>
                                                            

                                                            </select>

[requinix]

You forgot the part of the post where you say what it is that you need help doing.

[joshm101]

Sorry about that.

I want the select input option changed in the database when the Date is equal to the date given.

[joshm101]

  Here is where I am currently at:

 

   if ($dateTime > new DateTime('2019-06-26'))
                                                                                {
                                                                                    if($stmt->rowCount() > 0)
                                                                                    {

                                                                                        $sql = "UPDATE bowlers_info SET status = 'Non Member'";

                                                                                        $stmt = $this->db->prepare($sql);
                                                                                        $result = $stmt->execute();
                                                                                    
                                                                                    }
                                                                                    print "<option>Full Member</option>";
                                                                                    print "<option>Social Member</option>";
                                                                                    print "<option selected>Non Member</option>";
                                                                                }

[requinix]

48 minutes ago, joshm101 said:

I want the select input option changed in the database when the Date is equal to the date given.

What date? Why was it July 19 before and June 26 in your latest post? What is this UPDATE query about?

Remember that you're the only one who really knows what you're working with. We need more to be able to catch up enough to help. Context. Background information.

[joshm101]

My background is 1 year study of PHP, MySQL and I am fairly new with the date functions.

I am building a free project for a Lawn Bowling Club.

 

I have a Table that shows the input data from the form fields and this is the area of the paid membership status.

So when the date gets to (Sometime next year, just a dummy date was used) the date provided, I would like the status value to go back to Non Member, instead of still being financial in the database.

 

 

[Barand]

Is the date the same "Year End" for all players or is it different for each player depending on their renewal date?

[joshm101]

18 minutes ago, Barand said:

Is the date the same "Year End" for all players or is it different for each player depending on their renewal date?

It resets for all members back to non member on the renewal date. There is no counting of days.

[MPM]

I for one still have no idea what you are talking about.  I did notice that this:

if ($dateTime = new DateTime('2019-07-19'))

will always be true as there is only one equals so it assigns the new date (which seems fair) so why is it an if?

A general tip is to try to use SQL for dates as it is much easier.

[mac_gyver]

in the most general purpose and simple implementation, the membership status value for a user is derived data and should not specifically be updated/reset, just queried for when needed.

you would query to get the membership status value by querying to find if there is an 'active' (the current date or date in question is between the start and end dates) membership record for the user in question. if there is a matching record, retrieve the membership status value. if not, use the default Non Member value.

[Barand]

1 hour ago, joshm101 said:

It resets for all members back to non member on the renewal date. There is no counting of days.

Why are you permitting multiple selections? How many memberships does a person want?

What if a member renewed his subscription on or prior to that date - you would still show them as a non-member.

I you run it with that same renewal date on the day after that renewal date then they will be back to their original status.

5 hours ago, joshm101 said:

 $sql = "UPDATE bowlers_info SET status = 'Non Member'";

                                                                                        $stmt = $this->db->prepare($sql);
                                                                                        $result = $stmt->execute();

Why are you preparing a statement with no parameter placeholders?

Your code is very repetetive - use a function EG

function membershipOptions ($renewal, $currentStatus)
{
    $renew = new DateTime($renewal);
    $today = (new DateTime())->setTime(0,0,0);
    
    if ($today == $renew) $currentStatus = 'Non-member';
    
    $memberStati = ['Full Member', 'Social Member', 'Non-member'];
    $opts = '';
    foreach ($memberStati as $stat) {
        $sel = $stat==$currentStatus ? 'selected' : '';
        $opts .= "<option $sel>$stat</option>"; 
    }
    return $opts;
}

 

[joshm101]

30 minutes ago, Barand said:

Why are you permitting multiple selections? How many memberships does a person want?

oops. 

What if a member renewed his subscription on or prior to that date - you would still show them as a non-member.
I'm still new to programming.

If you run it with that same renewal date on the day after that renewal date then they will be back to their original status.

Could you kindly point me in the right direction?

Why are you preparing a statement with no parameter placeholders?

I am new to OOP also.

Your code is very repetetive - use a function EG

that seems alien to me, maybe I have to brush up on my OOP.

function membershipOptions ($renewal, $currentStatus)
{
    $renew = new DateTime($renewal);
    $today = (new DateTime())->setTime(0,0,0);
    
    if ($today == $renew) $currentStatus = 'Non-member';
    
    $memberStati = ['Full Member', 'Social Member', 'Non-member'];
    $opts = '';
    foreach ($memberStati as $stat) {
        $sel = $stat==$currentStatus ? 'selected' : '';
        $opts .= "<option $sel>$stat</option>"; 
    }
    return $opts;
}

Could you please go through it with me?

 

[Barand]

3 hours ago, joshm101 said:

Could you kindly point me in the right direction?

That would require us knowing where you want to go.

We can help you more if you explain exactly what this process should accomplish and what your database tables look like.

[joshm101]

Please see the files I am using.

https://www.dropbox.com/s/5kk18az68ic402v/Bowls Program.zip?dl=0

1. There is an exported MySQL file in there to use in phpmyadmin.
2. Database will need to be created in phpmyadmin called bowls_program
3. The navigation isn't properly coded as of yet. It just has dummy data.
4. If you goto the bowlersinfo folder, my scripts are in there to use.

My aim:

To create a form that can input records into the database and can create, view, edit and delete data.

I want an automated function for the Membership Status. I don't have the exact date as of yet so just anything in the future will do for testing purposes.

When the date gets to e.g. 2019-07-20 the membership will reset back to non member and then once the bowlers purchase the new membership, we can update as we go. I would ideally like the SQL query to be automated to the same date every year.

 

 Hope this helps.

 

[mac_gyver]

i reviewed your code. i notice there's an event table. if you are going to allow members to participate in events, your design needs to be able to determine the membership status on any date, past/present/future, such as the date(s) of an event. by only storing the current status in a column and updating it to manage the membership expire and renewal, you will not be able to do anything like this. also, to 'update' the values, you will need to actually execute a query with great enough frequency to keep the values in all the rows up to date, so that someone visiting the site doesn't ever see wrong information.

if you do what i posted in my reply above in this thread, you will have a simple solution that can determine the membership status on any date, doesn't require any update queries at all, and will always return current, correct and accurate membership status information.

[joshm101]

On 6/29/2019 at 3:22 PM, mac_gyver said:

i reviewed your code. i notice there's an event table.

The events table is separate. It is connected to the Calendar which is for just adding notes to the calendar, nothing special.

[MPM]

Mac's point is still valid though.  You do not need to store whether someone is a member.  You just query the current date against their/the membership date when you need to know.  It is trivial that way and MUCH more useful and flexible.  It allows you to later add queries for automated reminders as memberships dates come closer and more.

It is one of the fundamental principles of database design rather than a programming issue.

[joshm101]

Can anyone help me re-write this?

Thanks

[mac_gyver]

the following is an sql query and the pdo based code needed to retrieve a user's membership status once it has been stored with the information needed to manage and track the data  -

$sql = "SELECT type, start_date, end_date FROM membership WHERE user_id = ? AND ? BETWEEN start_date AND end_date";
$stmt = $pdo->prepare($sql);
$stmt->execute([$user_id,$date]);
$user_membership = $stmt->fetch();

the membership table would have columns for -  id (integer auto-increment primary index), user_id (integer user/bowler id) , type (integer membership type id that is mapped elsewhere to the membership status names), start_date (date), and end_date (date).

the above query is SELECTing the start and end dates, in case you want to display them. remove them from the query if this information is not needed.

example code allowing the selection of a made up user and selecting a date to demonstrate how the above query could be used -

<?php

// retrieve and display a selected user's membership status on any date (default is current date)

// define mapping of membership id values to labels/names
$membership_map = [3=>'Full Member', 2=>'Social Member', 1=>'Non-member'];

require 'pdo_connection.php';

// condition inputs
$date = $_GET['date'] ?? date('Y-m-d');
$user_id = $_GET['user_id'] ?? 0;

// if a user has been selected, retrieve membership status
if($user_id)
{
	$sql = "SELECT type, start_date, end_date FROM membership WHERE user_id = ? AND ? BETWEEN start_date AND end_date";
	$stmt = $pdo->prepare($sql);
	$stmt->execute([$user_id,$date]);
	$user_membership = $stmt->fetch();
}

// make up sample users data - you would query your database table instead
$users = [];
$users[1] = ['first_name'=>'fn1', 'last_name'=>'ln1'];
$users[2] = ['first_name'=>'fn2', 'last_name'=>'ln2'];
$users[3] = ['first_name'=>'fn3', 'last_name'=>'ln3'];


// display date selection input
?>
<form>
<input type='date' name='date' value='<?php echo $date;?>'><br>
<?php
// display user selection input
?>
<select name='user_id'>
<option value=''>Select a User</option>
<?php
foreach($users as $id=>$arr)
{
	$sel = $user_id == $id ? ' selected' : '';
	echo "<option value='$id'$sel>{$arr['first_name']} {$arr['last_name']}</option>";
}
?>
</select><br>
<input type='submit'></form>


<?php
// display the membership status
if($user_id)
{
	$user = $users[$user_id]; // 'retrieve' the specific user data from the made up sample data - you would query your database table instead
	echo "The user - {$user['first_name']} {$user['last_name']},";

	if(empty($user_membership))
	{
		//there was no matching row in the membership table
		echo " has no active membership matching the date - $date, and is therefore a $membership_map[1] on that date.";
	} else {
		// there was a matching row in the membership table
		echo " has an active membership, from: {$user_membership['start_date']}, to: {$user_membership['end_date']}, matching the date - $date, and has a membership type of - {$membership_map[$user_membership['type']]}";
	}
}