Member Avatar for ROTC89

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
  • 108 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by madCoder

Dani AI

Generated

A few focused fixes will make this reliable and fast. As already noted, stop using the event date as the table primary key — add a numeric id (AUTO_INCREMENT) and keep the event datetime in its own column (rename EventID to something like event_dt). In 's code there are also a few clear problems: the SELECT uses invalid identifiers, the query uses comma-style joins instead of an explicit JOIN ... ON, and filtering with MONTH(...)/DAYOFMONTH(...)/YEAR(...) prevents use of an index and forces a full table scan. Also validate and parameterize all $_GET/$_POST inputs to avoid SQL injection.

A safe migration and a cleaner daily query (example assumes renamed tables events and timecards) — add an id, move the datetime, copy timecard links to the new id, then drop the old column:

ALTER TABLE events CHANGE COLUMN EventID event_dt DATETIME NOT NULL;
ALTER TABLE events ADD COLUMN id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

ALTER TABLE timecards ADD COLUMN event_id INT UNSIGNED;
UPDATE timecards tc JOIN events e ON tc.EventID = e.event_dt
  SET tc.event_id = e.id;
ALTER TABLE timecards DROP COLUMN EventID;
ALTER TABLE timecards ADD FOREIGN KEY (event_id) REFERENCES events(id);

To fetch events for a day and sum hours from timecards, use a range scan (this lets MySQL use an index) and an explicit join:

SELECT e.id, e.title, TIME(e.event_dt) AS start_time,
       COALESCE(SUM(tc.hours),0) AS total_hours
FROM events e
LEFT JOIN timecards tc ON tc.event_id = e.id
WHERE e.event_dt >= ? AND e.event_dt < ?
GROUP BY e.id
ORDER BY e.event_dt;

Bind the two ? parameters (start-of-day and start-of-next-day) with prepared statements. Add indexes on events(event_dt) and timecards(event_id), avoid reserved names like event, and validate date parts (use PHP's DateTime to build the timestamps). These changes will make joins simple, fast, and secure while giving you the per-event, per-day hour totals you need.

Member Avatar for madCoder

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.