get month > display table. hmmmm

[studvicious]

Hello everyone, I have a little PHP/MySQL question for you guys. I'm fairly new to this so please bear with me! 😃

What I want:
I would like to have a place on my site that has a listing of people's birthdays. So if it were January it would read:

JANUARY

John Doe 02
Jane Doe 14
Fred Man 23

What I've done so far:
I've created a database called, "birthdays" and inside that db are 12 tables. Each table represents each month as in, "jan","feb","mar", etc.. Inside each table I have 2 columns, "name" and "date". And this is pretty much where I'm stuck.

I have the basic knowledge down, being that I can script the PHP to connect to the db, and all of that but I'm stumped now. :bemused: I guess what I'm thinking is to have a code that asks what the current month is and then once it finds that to pull up that table, and then to sort the "date" column from the earliest date to the lastest.

If anybody could help me out with this I would greatly appreciate it.

[cahva]

You have to rethink your table structure. Just put the guys and gals to the table with their birthday (date field). So when you want to fetch people who has birthday in January, you would fetch the result with this query:

$query="SELECT name , DAY(birthday) as birthday FROM tablename WHERE MONTH(birthday)=1";
$result=mysql_query($query);

while ($row=mysql_fetch_array($result)) {
echo $row['name'].' '.$row['birthday'].'<br>';
}

Read mysql documentation about different date and time functions:
Date and time functions

[studvicious]

that makes sense, thank you! One more question though, what would be the best way to input their birthdate if I only have the month and the day?

[Roger_Ramjet]

Ask then for the year as well. 😃

Seriously. If you only have the month and day and intend to continue with that then seperate columns for each would be simplest and most eficient. I'd make the month a char field and the day column tinyint.

[studvicious]

I'll give that a shot, thanks.

[studvicious]

Originally posted by cahva
You have to rethink your table structure. Just put the guys and gals to the table with their birthday (date field). So when you want to fetch people who has birthday in January, you would fetch the result with this query:

$query="SELECT name , DAY(birthday) as birthday FROM tablename WHERE MONTH(birthday)=1";
$result=mysql_query($query);

while ($row=mysql_fetch_array($result)) {
echo $row['name'].' '.$row['birthday'].'<br>';
}

Hello again, I've just started to dig through this and I've got another question.. how will this tell me what the current month is? The page that I need this for is:

http://www.destinyrevivalcenter.com/events.php

near the bottom on the right hand side you'll see what I'm talking about. Right now that's hand coded every month to update the birthday's and anniversaries.

I'm going to change my table structure to where it will be, "name", "month", "day" as recommended.

[Roger_Ramjet]

mysql date and time functions

SELECT * FROM table
WHERE month=date_format(current_date, '%M')

php date and time functions

date("F") returns the current month in full eg January, February.

[studvicious]

perfect! thank you 😃

[studvicious]

ok. I've added a table, "birthdays" to a database, "birthdays" with 3 columns, "name","month",and "day" I filled the table with 2 months worth of names so far (january, and february) and decided to try it out. Nothing shows up.. I'm sure that there must be a problem in how I put the code together:

<?php

$db_host = "localhost";
$db_user = "username";
$db_pass = "password";
$db_name = "destiny_birthdays";

$ac_connect = mysql_connect($db_host, $db_user, $db_pass) or die(mysql_error());
mysql_select_db($db_name) or die(mysql_error());

$query="SELECT * FROM birthdays
WHERE month=date_format(current_date, '%M')
$result=mysql_query($query)";

while ($row=mysql_fetch_array($result)) {
echo $row['name'].' '.$row['day'].'<br>';
}

?>

[Roger_Ramjet]

date_format(current_date, '%M') should return 'January' since that is what today is. Change it to

date_format(Now(), '%M')

Remeber, we are doing a string match so your month Column must contain 'January' as well, not 01 or some such

[studvicious]

hmm... still blank. Would it matter if I had "january" instead of "January"?

[Roger_Ramjet]

Depends on your server settings. Read the manual about that, some settings are case sensitive others are not. Default for string comparison is non-sensitive.

[cahva]

I dont see the point to store month as string. Put them in as number. Queries will be faster and it wont consume space that much. Hell, I'll make an example 🙂

$month=date("n"); // = month 1-12. No leading zeros
$month_text=date("F"); // January through December

$query="SELECT name, day FROM birthdays WHERE month=".$month;
$result=mysql_query($query);
if (mysql_num_rows($result)>0) {
	echo '<p>'.$month_text.' birthdays</p>';
	echo '<table width="100%" border="0">';
	while ($row=mysql_fetch_array($result)) {
		echo '<tr><td>'.$row['name'].'</td><td align="right">'.$row['day'].'</td></tr>';
	}
	echo '</table>';
}

[studvicious]

after some tinkering here is the final code, it works perfectly. Thank you both! 🙂

<?php

$location = "localhost";
$username = "username";
$password = "password";
$database = "database_name";

$conn = mysql_connect("$location","$username","$password");
if (!$conn) die ("Could not connect MySQL");
mysql_select_db($database,$conn) or die ("Could not open database");

$query = "SELECT * FROM birthdays
WHERE month=date_format(current_date, '%M')";
$result = mysql_query($query);
$numrows = mysql_num_rows($result);

while($row = mysql_fetch_array($result))
{
	echo "$row[name]";
	echo "$row[day]<br />";
}

?>

[cahva]

And I forgot to put quotes around the string that date_format returns. That is why it was returning nothing.

Ah.. 😃 Good point :p

[studvicious]

Ok, with my CSS applied this is my final product, it seems pretty redundant though, is there a better way to achieve this? You can see what this looks like at:

http://www.destinyrevivalcenter.com/events2.php

<div class="adboxcontainer">
				<div class="adboxleft">
					<?php

				$location = "localhost";
				$username = "username";
				$password = "password";
				$database = "database";
				$conn = mysql_connect("$location","$username","$password");

				if (!$conn) die ("Could not connect MySQL");
				mysql_select_db($database,$conn) or die ("Could not open database");

				$query = "SELECT * FROM birthdays
				WHERE month=date_format(current_date, '%M')";
				$result = mysql_query($query);
				$numrows = mysql_num_rows($result);

				while($row = mysql_fetch_array($result))
				{
					echo "$row[name]<br />";
				}

				?>
			</div>
			<div class="adboxright">
				<?php

				$location = "localhost";
				$username = "username";
				$password = "password";
				$database = "database";
				$conn = mysql_connect("$location","$username","$password");

				if (!$conn) die ("Could not connect MySQL");
				mysql_select_db($database,$conn) or die ("Could not open database");

				$query = "SELECT * FROM birthdays
				WHERE month=date_format(current_date, '%M')";
				$result = mysql_query($query);
				$numrows = mysql_num_rows($result);

				while($row = mysql_fetch_array($result))
				{
					echo "$row[day]<br />";
				}

				?>
			</div>
		</div>

I think the formatting is a little off

[Roger_Ramjet]

Oh dear. I should have spotted that you forgot to terminate the query string correctly, the next line being the same colour was a dead giveaway.

Cahva, I doubt that storage is an issue here, studvicious will not be storing millions of people's birthdays. I recomended text storage for the months as it simplifies so much for studvicious, from his select on the input page to the query and presentation on the birthdays page, no transformation is required.
Input January, query for January and then display January. Yes I know integer indexes are faster, but 01 is not an integer is it?

[studvicious]

hmmm... confused now. What should I do?

[Roger_Ramjet]

Looks good to me, very nice layout.

The main thing you need to take out is the continual opening of the db connection. The connection remains open as long as the thread that opened it is running. And the thread is running while your script is still executing. So you only ever need to run connect once in each script.

The usual way to process the result set multiple times would be to do this

$names = "";
$dates = ""

$query = "SELECT * FROM birthdays
                    WHERE month=date_format(current_date, '%M')";
                    $result = mysql_query($query);
                    $numrows = mysql_num_rows($result);

while($row = mysql_fetch_array($result))
                    {
   $names .= $row['name'].'<br />';
   $dates .= $row['day'].'<br />';
} 

echo '<div class="adboxleft">'.$names.'</div.';
echo '<div class="adboxright"> '.$dates.'</div.';

[studvicious]

I had to change it a wee little bit but that works great.. thanks again!