0

Hi I coded a calendar pop up with events and I wanted to be able to join the event table with a timecard table but im not sure if im doing this correctly. What it is suppose to do is when they add stuff to the calendar the person can click on it an it will show how many hours a person put in on that day alond with what event and stuff. Here is what I have done. Any advice would be helpful

<html>
<head>
<title>Show/Add Events</title>
<head>
<body>
<h1>Show/Add Events</h1>
<?php
$mysqli = mysqli_connect("localhost", "root", "", "testdb");

if(mysqli_connect_errno()){
	printf("connect failed: %s\n", mysqli_connect_error());
	exit();
}

//Add any new event
if($_POST){
	$m = $_POST["m"];
	$d = $_POST["d"];
	$y = $_POST["y"];
	
	$event_date = $y."-".$m."-".$d." ".$_POST["event_time_hh"].":
	".$_POST["event_time_mm"].":00";
	$insEvent_sql = "INSERT INTO event(Name, Description, EventID) VALUES('".$_POST["Name"]."',
	'".$_POST["Description"]."', '$event_date')";
	$insEvent_res = mysqli_query($mysqli, $insEvent_sql)
					or die(mysqli_error($mysqli));
}else{
	$m = $_GET["m"];
	$d = $_GET["d"];
	$y = $_GET["y"];
}

//Show events for this day
$getEvent_sql = "SELECT Name.event, Description.event,
				 date_format(EventID, '%l:%i %p') as fmt_date FROM
				 Event , timecard WHERE month(EventID) = '".$m."'
				 AND dayofmonth(EventID) = '".$d."' AND
				 year(EventID)= '".$y."' and event.EventID = timecard.EventID ORDER BY EventID";
$getEvent_res = mysqli_query($mysqli, $getEvent_sql)
				or die(mysqli_error($mysqli));
			   
if(mysqli_num_rows($getEvent_res) > 0){
	$event_txt = "<ul>";
	while($ev = @mysqli_fetch_array($getEvent_res)){
		$name = stripslashes($ev["Name"]);
		$Description = stripslashes($ev["Description"]);
		$fmt_date = $ev["fmt_date"];
		$event_txt .= "<li><strong>".$fmt_date."</strong>:
		".$name."<br/>".$Description."</li>";
	}
	$event_txt .= "</ul>";
	mysqli_free_result($getEvent_res);
}else{
	$event_txt = "";
}

mysqli_close($mysqli);

if($event_txt != ""){
	echo "<p><strong>Today's Events:</strong></p>
	$event_txt
	<hr/>";
}

//Show form for the adding event
echo "
<form method=\"post\" action=\"".$_SERVER["PHP_SELF"]."\">
<p><strong>Would you like to add an event?</strong><br/>
Complete the form below and press the submit button to add
the event and refresh this window.</p>
<p><strong>Event Title:</strong><br/>
<input type=\"text\" name=\"Name\" size=\"25\"
	maxlenght=\"25\"/>
<p><strong>Event Discription:</strong><br/>
<input type=\"text\" name=\"Description\" size=\"25\"
	maxlength=\"255\"/>
<p><strong>Event Time (hh:mm):</strong><br/>
<select name=\"event_time_hh\">";
for($x=1; $x <= 24; $x++){
	echo "<option value=\"$x\">$x</option>";
}
echo "</select> :
<select name=\"event_time_mm\">
<option value=\"00\">00</option>
<option value=\"15\">15</option>
<option value=\"30\">30</option>
<option value=\"45\">45</option>
</select>
<input type=\"hidden\" name=\"m\" value=\"".$m."\">
<input type=\"hidden\" name=\"d\" value=\"".$d."\">
<input type=\"hidden\" name=\"y\" value=\"".$y."\">
<br/><br/>
<input type=\"submit\" name=\"submit\" value=\"Add Event\">
</form>";
?>
</body>
</html>
2
Contributors
1
Reply
2
Views
6 Years
Discussion Span
Last Post by madCoder
0

I notice your event table doesn't have a numeric primary key; instead eventid is a date. If you add a primary key to this table (rename current eventid to eventDate or something and add an 'id' column that is primary key, auto_increment.)

Having a numeric primary key makes joining tables MUCH easier and is also much faster at the sql level. Your timecard table can then contain a column eventid that refers to the unique numeric id in events.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.