Jump to content
phppup

Best approach guidance

Recommended Posts

I'm looking for some direction as I approach a new challenge.

I have a table named friends_and_family and it contains name, email, and age fields.

I'm planning a party and want to invite 10 of the people that are between 20 and 35 years old.

The format I am considering would query the table and provide a list of those members that fit the age requirement.

I would like to generate that result (which I should be capable of handling) so that each person listed has a checkbox next to their name.

I imagine I will be creating a resultant form, so that I can then evaluate the result and select the checkboxes for those specific people that I want to invite.

Upon submission, an email will be sent to the people with selected checkboxes.

How should I approach this best?

A for each loop? Implode an array? 

I'm not very good with AJAX, so I'm thinking along these lines.

 

 

Share this post


Link to post
Share on other sites

I would suggest you take this a step at a time. Start with the database. When you have the DB done, show us what you did and we will review it and guide you to any changes/improvements. After you implement the final DB design then we can move to the next step.

You already have the overall summary of the project which was the first step.

* Just a heads up. You would not have an "age" column. You would store the birth date and calculate the age from that.

Edited by benanamen
  • Like 1

Share this post


Link to post
Share on other sites

Yes, you are correct on birthdate.  It was a silly typing error.

The DB is pretty well established.  My thought process now is how best to implement having a query response that creates a form with usable checkboxes.

Share this post


Link to post
Share on other sites

PDO is my least favorite method. It is too abbreviated for me, and I get lost in its application.

Also, the link does not address the initial question: what is the best way to achieve a resulting form with usable checkboxes next to each valid response to the query?

 

Share this post


Link to post
Share on other sites

Your asking about the most basics of HTML now. Your going to have to put in at least a little effort. Are you telling us you can create a database but you do not know how to make a form or what the syntax is for check boxes?

Share this post


Link to post
Share on other sites
Query the table to get your friends' namea and ids

for each  result
	output checkbox whose name is "friend[]", label is the friend name and value is the friend id
end for

To process the posted data

foreach ($_POST['friend] as $id)
	send email to friend whose id = $id
end foreach

 

Share this post


Link to post
Share on other sites

Did WE read the very first post.  Maybe are-read with comprehension would help.

To clarify: The SQL query would deliver it's result(s) in a format that not only provides a list of people, but ALSO ads functioning checkboxes for use as it's own form.

 

Share this post


Link to post
Share on other sites

Thank you Brand to the rescue.

Glad to see I was on the right track again.  Now let's see how far I can take it.

 

Share this post


Link to post
Share on other sites

Yes - our reading comprehension is quite adequate.  But perhaps you understanding of what MySQL does for you and what HTML does needs some understanding.  As Barand has shown you, a query gathers the data and the HTML will then format its usage as you desire.

PS - You find PDO to be 'too abbreviated' for you?  I've not heard of that as an excuse ever.  Simple is usually the better choice for anything IMHO.  I think perhaps you had your own trouble with "are-read with comprehension " and a little more reading would help you adopt the use of PDO over any other database interface that your PHP install offers.

  • Like 1

Share this post


Link to post
Share on other sites

The trickiest bit will be determining age from date of birth, so here's easiest way

SELECT ... WHERE timestampdiff(YEAR, date_of_birth, curdate()) BETWEEN 20 AND 35

 

Share this post


Link to post
Share on other sites

Thanks Barand.  I was considering separating YYYY and mm/dd into separate fields.  I will try your method. Obviously, I'll be doing more reading on MySQL and handling dates.

 

Ginerjim: I'm glad I was able to enlighten you by being the first to offer a new explanation for not choosing PDO as my favorite option.  My needs do not require me to integrate with other db formats, and the 'shorthand' it utilizes is less easily understood than other choices.  It is not an excuse, it is my opinion; just as you have your own opinion, which appears NOT to be so humble, although you stated "IMHO".

Perhaps "a re-read with comprehension" will change my mind in the future, but not this week.

 

 

Share this post


Link to post
Share on other sites

Off topic but I am curious as to what you consider the use of "shorthand" in the PDO extension?

Share this post


Link to post
Share on other sites

If you don't like the object notation (eg $result=$conn->query("SELECT foo FROM bar");  )  you can always write yourelf a set of procedural PDO functions so you call $result = pdo_query($conn, "SELECT foo FROM bar") instead. After all , that is all that MySqli does, and, like mysqli procedural versions, you will always have that overhead of the intermediate function call.

function pdo_query(PDO $conn, $querystring)
{
    return ($conn->query($querystring));
}

 

Share this post


Link to post
Share on other sites

Hey ginerjim: The "shorthand" are these little -> type of items that seem to make jumps to eliminate cumbersome steps.

When I first started to code, I thought there should be an easier way, but then I got 'wired' into the step by step procedures.  I'm still not as proficient as I'd like to be, but i think I'm improving.  Perhaps one day I'll go for PDO, but for now, I need every road sign and pathway to be visible.  PS: I have the same problem with JQuery.

Share this post


Link to post
Share on other sites

Can I use the timestampdiff to adapt the the script to display when someone's birthday occurs?

Would it work as this? Our is there a better implementation?

SELECT ... WHERE timestampdiff(YEAR, date_of_birth, curdate()) = 0

Or do I need to separate mm-dd and use YYYY to determine age?

 

Share this post


Link to post
Share on other sites

Back to the PDO portion....

Now that you have identified what you were referring to as using the Object-oriented style of PDO, dare I point out that MysqlI ALSO uses what you so cutely call "shortcuts"?

I too do not like the OO method and do very little OO programming.  I much prefer the procedural style which I believe you are also referring to when you say 'step by step procedures'.   Using the OO method is not a requirement for using PDO.  The reason to go with PDO is because it could possibly be the only db interface you will ever have to learn since it was designed to be able to handle many different types of databases and therefore can be used without concerns about the future.  Those who used to use the MySQL interface of php have had to go thru struggles to modify their code to switch to either mysqlI or PDO in the last few years.  If you are just beginning with your db knowledge-building I highly suggest you go back to the manual and read up on any one of the many functions that PDO provides and notice how each one describes in detail both an OO style and the Procedural style.  Just find one good example of using PDO with prepared statements in the procedural style and I think you will see how much simpler it is than mysqlI.

Share this post


Link to post
Share on other sites

We'll see.  Thanks for the input.

Meanwhile, I hope my questions about code for birthdays doesn't get lost in this thread.

Share this post


Link to post
Share on other sites
2 hours ago, phppup said:

SELECT ... WHERE timestampdiff(YEAR, date_of_birth, curdate()) = 0

That will find everyone who is less than 1 year old.

 

2 hours ago, phppup said:

Would it work as this? Our is there a better implementation?

Tell us what you are trying to do and we can tell you if there is a better implementation (which given what I just wrote about your suggested query, is almost certainly yes)

Share this post


Link to post
Share on other sites

Barand has already provided an answer but here's some more contributions, courtesy of a google search:

If the value is stored as a DATETIME data type:
 


SELECT YEAR(CURRENT_TIMESTAMP) - YEAR(dob) - (RIGHT(CURRENT_TIMESTAMP, 5) < RIGHT(dob, 5)) as age 
  FROM YOUR_TABLE

There is another method that tries to handle the leap years but unless that is extremely important to you it's better left alone for now.

In the above the reference to 'dob' is the field in the table.

 

Share this post


Link to post
Share on other sites

It's amazing the rubbish you can find using Google, but that's no excuse to propagate it here.

mysql> select NOW(), RIGHT(CURRENT_TIMESTAMP, 5);
+---------------------+-----------------------------+
| NOW()               | RIGHT(CURRENT_TIMESTAMP, 5) |
+---------------------+-----------------------------+
| 2019-01-23 16:00:46 | 00:46                       |
+---------------------+-----------------------------+

Why propose using the hrs:mins portion to calculate age - that's taking precision to ridiculous lengths. Try

SELECT YEAR(CURRENT_DATE) - YEAR(dob) - ( RIGHT(CURRENT_DATE,5) < RIGHT(dob,5)) as age;

 

Share this post


Link to post
Share on other sites

I was more interested (since I've already established a date_of_birth field [without hours or minutes.. LOL] ...although it is tempting) in being able to use a query to determine whether TODAY() or NOW() is the birthdate.

And, if true, then send a message saying "Happy $your_age birthday"  - - or something of the like.

Edited by phppup

Share this post


Link to post
Share on other sites

You should be able to work out from the previos reply that it is their birthday if

RIGHT(CURRENT_DATE, 5) = RIGHT(date_of_birth, 5)

or if you do it in PHP

substr($row['date_of_birth'], -5) == date('m-d')

 

Share this post


Link to post
Share on other sites

So I'm guessing that the "5" is stripping out the  -YYYY  (and thereby utilizing the separator DASH as a minus sign) and leaving only the mm-dd to be compared??

Share this post


Link to post
Share on other sites

it simply takes the rightmost 5 characters from a string value, such as a (correctly stored) date

2019-01-23

If they are the same in both dates (dob and today), then "Happy Birthday"

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.