eve_mf eve_mf - 1 month ago 9
MySQL Question

Mysql PDO - declaring database constants

Well, seeing the issue of screenshots, here there is my code, as some of you required to "test the code".

I am working in a project with mysql and the PDO object. Everything was working as expected until I started with the reports.php, where I need to use two of the functions from functions.php, the problem is, it displays an error as my database constants has been already defined. I know it is happening because I am including an external connection file called db_connection.php within every function that needs it.

I have tried to change the include within each function to include_once, or require_once, but then, it displays an error saying the $conn variable (it is how my connection is called) is not defined, and the execute(), prepare(), query() methods for the database statements are not defined either.

I have also tried to include the code from the db_connection.php file into a function, place it at the beginning of my functions.php and just assign to my $conn variable, the function just created.

Does anyone knows how can I declare my database connection using PDO to not declared it twice?

Thank you, I hope, the code makes everything clearer.... because it does not do it for me....

css

.required {
&:after {
content: " *";
color: red;
}
}
.addLink {
color: #645180;
margin-bottom: 10px;
display: block;
}
.submit {
border: 0;
background-color: #645180;
color: white;
width:100%;
margin: 15px 0;
padding:5px;
}
#content {
padding: 3% 0;

h1 {
text-align:center;
}
}

ul.projectList,
ul.taskList {
list-style: none;
border: .5em solid white;
padding:15px 0;
li {
background-color: white;
border-bottom: 1px solid #b2b2b2;
padding: 5 15px;

&:last-child {
border-bottom: 0;
}
}
}
table {
width: 40%;
margin:0 auto;
td {
padding: .2rem .2rem;
}
}
thead > tr> td {
font-weight: bold;
background-color: #cebece;
}
.totalReportTime > th {
background-color: #632c62;
color: white;
}


db_connection.php

<?php
define('DB_SERVER', "localhost");
define('DB_USER', "tracker_userr");
define('DB_PASSWORD', "tracker_passs");
define('DB_DATABASE', "tracker");
define('DB_DRIVER', "mysql");
?>


functions.php

<?php
function connect_to_db() {
define('DB_SERVER', "localhost");
define('DB_USER', "tracker_user");
define('DB_PASSWORD', "tracker_pass");
define('DB_DATABASE', "tracker");
define('DB_DRIVER', "mysql");

try {
$conn = new PDO(DB_DRIVER . ":dbname=" . DB_DATABASE . ";host=" . DB_SERVER, DB_USER, DB_PASSWORD);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
//echo "Connected successfully";
} catch(PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
return $conn;
}
function get_projects_list() {
// include 'db_connection.php';
$conn = connect_to_db();
try {
return $conn->query("SELECT * FROM projects");
} catch (PDOException $e) {
echo 'Error:' . $e->getMessage() . "<br />";
return array();
}
}

function get_tasks_list($filter = null) {
// include 'db_connection.php';
$conn = connect_to_db();
$sql = 'SELECT tasks.*, projects.title as project FROM tasks'
. ' JOIN projects ON tasks.project_id = projects.project_id';

$orderBy = ' ORDER BY date DESC';

if ($filter) {
$orderBy = ' ORDER BY projects.title ASC, date DESC';
}
try {
$results = $conn->prepare($sql . $orderBy);
$results->execute();
} catch (PDOException $e) {
echo 'Error:' . $e->getMessage() . "<br />";
return array();
}
return $results->fetchAll(PDO::FETCH_ASSOC);
}

function add_project($title, $category) {
// include 'db_connection.php';
$conn = connect_to_db();
try {
$sql = "INSERT INTO projects(title, category) VALUES (:title, :category)";

$results = $conn->prepare($sql);
$results->bindParam(':title', $title, PDO::PARAM_STR, 100);
$results->bindParam(':category', $category, PDO::PARAM_STR, 100);


if($results->execute()) {
echo '1 row has been inserted';
}

$conn = null;

} catch(PDOException $e) {
echo 'Error: ' . $e->getMessage() . '<br />';
return false;
}
return true;
}

function add_task($title, $date, $time, $project_id) {
// include 'db_connection.php';
$conn = connect_to_db();
try {
$sql = "INSERT INTO tasks(title, date, time, project_id) VALUES (:title, :date, :time, :project_id)";

$results = $conn->prepare($sql);
$results->bindParam(':title', $title, PDO::PARAM_STR, 100);
$results->bindParam(':date', $date, PDO::PARAM_STR, 100);
$results->bindParam(':time', $time, PDO::PARAM_STR, 100);
$results->bindParam(':project_id', $project_id, PDO::PARAM_INT);


if($results->execute()) {
echo '1 row has been inserted';
}

$conn = null;

} catch(PDOException $e) {
echo 'Error: ' . $e->getMessage() . '<br />';
return false;
}
return true;
}


try {
$conn = new PDO(DB_DRIVER . ":dbname=" . DB_DATABASE . ";host=" . DB_SERVER, DB_USER, DB_PASSWORD);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
//echo "Connected successfully";
} catch(PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}


project_list.php

<?php
$pageTitle = "Project | Time Tracker";
$page = 'projects';

include('includes/functions.php');
include 'includes/header.php'; ?>

<div class="col-container">
<h1>Project List</h1>
<a class="addLink" href="project.php">Add project</a>
<ul class="projectList">
<?php
foreach (get_projects_list() as $item) {
echo '<li>' . $item['title'] . '</li>';
}
?>
</ul>
</div>

<?php include 'includes/footer.php';


header.php

<!DOCTYPE html>
<html lang="en">
<head>
<title><?php echo $pageTitle; ?></title>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=Edge">
<meta name="description" content="project list">
<meta name="keywords" content="">
<meta name="author" content="Evelia Molina">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link href='https://fonts.googleapis.com/css?family=Roboto+Slab:400,700' rel='stylesheet' type='text/css'>
<link rel="stylesheet" href="css/pre_style.css" type="text/css">
<link href="css/style.css" rel="stylesheet" type="text/css"/>
</head>
<body>

<svg style="display: none;">
<defs>
<svg id="logo_icon" viewBox="0 0 64 64" preserveAspectRatio="xMinYMin meet">
<rect x="19.5" y="41.4" transform="matrix(0.7071 -0.7071 0.7071 0.7071 -24.2442 27.4475)" width="3" height="3.3"/>
<rect x="19.4" y="19.5" transform="matrix(0.7071 -0.7071 0.7071 0.7071 -8.7041 21.0111)" width="3.3" height="3"/>
<rect x="15" y="30.4" width="3" height="3.3"/>
<rect x="30.4" y="46.1" width="3.3" height="3"/>
<path d="M32,0C14.3,0,0,14.3,0,32c0,17.7,14.3,32,32,32s32-14.3,32-32C64,14.3,49.7,0,32,0z M32,55.6C19,55.6,8.4,45,8.4,32
C8.4,19,19,8.4,32,8.4S55.6,19,55.6,32C55.6,45,45,55.6,32,55.6z"/>
<path d="M48.9,30.4c0-0.2,0-0.4-0.1-0.6c0-0.2-0.1-0.4-0.1-0.6c-0.1-0.3-0.1-0.7-0.2-1c0-0.1-0.1-0.2-0.1-0.3
c-0.4-1.6-1-3.2-1.9-4.6c-0.2-0.4-0.5-0.7-0.7-1.1c0,0-0.1-0.1-0.1-0.1c-0.3-0.4-0.6-0.8-1-1.2c0,0,0,0,0,0c0,0-0.1-0.1-0.1-0.1
c-0.4-0.4-0.8-0.8-1.2-1.2c-0.1-0.1-0.3-0.3-0.5-0.4c-0.3-0.3-0.6-0.5-0.9-0.7c-0.2-0.2-0.4-0.3-0.7-0.5c-0.3-0.2-0.5-0.3-0.8-0.5
c-0.3-0.2-0.5-0.3-0.8-0.5c-0.1-0.1-0.2-0.1-0.3-0.2c-0.1-0.1-0.3-0.1-0.4-0.2c-0.2-0.1-0.4-0.2-0.6-0.3c0,0,0,0,0,0
C36.4,15.4,34.2,15,32,15c0,7,0,11.1,0,13.5c-0.6,0-1.2,0.2-1.8,0.5c0,0-0.1,0-0.1,0c-0.1,0.1-0.2,0.1-0.2,0.2
c-0.1,0-0.1,0.1-0.2,0.1c0,0,0,0-0.1,0c0,0,0,0,0,0c-0.3,0.3-0.6,0.7-0.8,1.1c-0.9,1.8-0.1,3.9,1.6,4.7c1.1,0.5,2.3,0.5,3.3-0.1
c0,0,0.1,0,0.1,0c0.1-0.1,0.2-0.1,0.2-0.2c0.1,0,0.1-0.1,0.2-0.1c0,0,0,0,0.1,0c0,0,0,0,0,0l0,0c0,0,0,0,0,0
c3.8,4.3,7.2,8.2,8.9,10.1c1.7-1.5,3.1-3.3,4-5.2c0,0,0,0,0,0c0,0,0,0,0,0c0.2-0.5,0.4-0.9,0.6-1.4c0-0.1,0-0.1,0.1-0.2
c0.1-0.4,0.3-0.8,0.4-1.2c0.5-1.7,0.7-3.3,0.7-5c0-0.1,0-0.2,0-0.3C49,31.1,49,30.7,48.9,30.4z"/>
</svg>
<svg id="task_icon" viewBox="0 0 64 64" preserveAspectRatio="xMinYMin meet">
<path d="M64,53.3l-10,9.6c-0.7,0.7-1.8,1.1-2.5,1.1l0,0l0,0c-1.1,0-1.8-0.4-2.5-1.1l-10-9.6l0,0l0,0l0,0l0,0V3.6c0-2.1,1.4-3.6,3.6-3.6h17.8C62.6,0,64,1.4,64,3.6V53.3L64,53.3L64,53.3z M56.9,7.1H46.2v7.1h10.7V7.1z M56.9,49.1V17.8H46.2v31.3l5.3,4.3l0,0L56.9,49.1z M21.3,64H3.6C1.4,64,0,62.6,0,60.4V3.6C0,1.4,1.4,0,3.6,0h17.8c2.1,0,3.6,1.4,3.6,3.6v56.9C24.9,62.6,23.5,64,21.3,64z M17.8,7.1H7.1v7.1h5.3c1.1,0,1.8,0.7,1.8,1.8s-0.7,1.8-1.8,1.8H7.1v7.1h1.8c1.1,0,1.8,0.7,1.8,1.8s-0.7,1.8-1.8,1.8H7.1v7.1h5.3c1.1,0,1.8,0.7,1.8,1.8c0,1.1-0.7,1.8-1.8,1.8H7.1v7.1h1.8c1.1,0,1.8,0.7,1.8,1.8s-0.7,1.8-1.8,1.8H7.1v7.1h10.7V7.1z"/>
</svg>
<svg id="project_icon" viewBox="0 0 64 64" preserveAspectRatio="xMinYMin meet">
<path d="M60.4,64H3.6C1.4,64,0,62.6,0,60.4V3.6C0,1.4,1.4,0,3.6,0h24.9c1.4,0,2.5,0.7,3.2,2.1l5.7,12.1h23.1c2.1,0,3.6,1.4,3.6,3.6v42.7C64,62.6,62.6,64,60.4,64z M7.1,56.9h49.8V21.3h-22c-1.4,0-2.5-0.7-3.2-2.1L26.3,7.1H7.1V56.9z"/>
<path d="M60.4,7.1h-16c-2.1,0-3.6-1.4-3.6-3.6S42.3,0,44.4,0h16C62.6,0,64,1.4,64,3.6S62.6,7.1,60.4,7.1z"/>
</svg>
<svg id="report_icon" viewBox="0 0 64 64" preserveAspectRatio="xMinYMin meet">
<path d="M60.4,0H3.6C1.4,0,0,1.4,0,3.6v56.9C0,62.6,1.4,64,3.6,64h56.9c2.1,0,3.6-1.4,3.6-3.6V3.6C64,1.4,62.6,0,60.4,0zM56.9,56.9H7.1V7.1h49.8V56.9z"/>
<path d="M16.5,21.3h30.9c2.1,0,3.6-1.4,3.6-3.6c0-2.1-1.4-3.6-3.6-3.6H16.5c-2.1,0-3.6,1.4-3.6,3.6C13,19.9,14.4,21.3,16.5,21.3z" />
<path d="M16.5,35.6h30.9c2.1,0,3.6-1.4,3.6-3.6s-1.4-3.6-3.6-3.6H16.5c-2.1,0-3.6,1.4-3.6,3.6S14.4,35.6,16.5,35.6z"/>
<path d="M16.5,49.8h30.9c2.1,0,3.6-1.4,3.6-3.6s-1.4-3.6-3.6-3.6H16.5c-2.1,0-3.6,1.4-3.6,3.6S14.4,49.8,16.5,49.8z"/>
</svg>
</defs>
</svg>

<header class="header">
<div class="col-container">
<h1>
<a class="logo" href="./">
<svg viewbox="0 0 64 64" class="logo-icon"><use xlink:href="#logo_icon"></use></svg>
<span class="logo-name">Time Tracker</span>
</a>
</h1>

<ul class="nav">
<li class="nav-item tasks<?php if ($page == "tasks") { echo " on"; } ?>"><a class="nav-link" href="task_list.php">Tasks</a></li>
<li class="nav-item projects<?php if ($page == "projects") { echo " on"; } ?>"><a class="nav-link" href="project_list.php">Projects</a></li>
<li class="nav-item reports<?php if ($page == "reports") { echo " on"; } ?>"><a class="nav-link" href="reports.php">Reports</a></li>
</ul>
</div>
</header>
<div id="content">


footer.php

</div><!-- end content -->

<footer class="footer">
<div class="col-container">
<svg viewbox="0 0 64 64" class="logo-icon"><use xlink:href="#logo_icon"></use></svg>
<p class="footer-copy">&copy; <?php echo date("Y"); ?> Time Tracker by Eve</p>
</div>
</footer>

</body>
</html>


project.php

<?php require 'includes/functions.php';
$pageTitle = "Project | Time Tracker";
$page = 'projects';


if ($_SERVER['REQUEST_METHOD'] == 'POST') {
$title = filter_input(INPUT_POST, 'title', FILTER_SANITIZE_STRING);
$category = filter_input(INPUT_POST, 'category', FILTER_SANITIZE_STRING);

if(empty($title) || empty($category)) {
$error_message = "Title and category can not be empty";
} else {
if(add_project($title, $category)) {
header('Location: project_list.php');
exit;
} else {
$error_message = "Could not add project";
}
}
}
include 'includes/header.php';?>
<div class="col-container">
<h1>Add project</h1>
<?php
if (isset($error_message)) {
echo '<p class="message">' . $error_message . '</p>';
}
?>
<form method="POST" action="project.php">
<div>
<label for="title" class="required">Title</label>
<input name="title" id="title" type="text" value="" />
</div>
<div>
<label for="category" class="required">Category</label>
<select id="category" name="category">
<option value="">Select one</option>
<option value="Charity">charity</option>
<option value="Science">science</option>
<option value="Art">art</option>
</select>
</div>
<button class="submit" type="submit" name="submit">Submit</button>
</form>
</div>

<?php include 'includes/footer.php';


task.php

<?php require 'includes/functions.php';

$pageTitle = "Task | Time Tracker";
$page = 'tasks';
$project_id = $title = $date = $time = '';


if ($_SERVER['REQUEST_METHOD'] == 'POST') {
$project_id = filter_input(INPUT_POST, 'project_id', FILTER_SANITIZE_NUMBER_INT);
$title = filter_input(INPUT_POST, 'title', FILTER_SANITIZE_STRING);
$date = filter_input(INPUT_POST, 'date', FILTER_SANITIZE_STRING);
$time = filter_input(INPUT_POST, 'time', FILTER_SANITIZE_STRING);

$dateMatch = explode('/', $date);

if(empty($project_id) || empty($title) || empty($date) || empty($time)) {
$error_message = "Required fields can not be empty";
} elseif (count($dateMatch) != 3
|| strlen($dateMatch[0]) != 2
|| strlen($dateMatch[1]) != 2
|| strlen($dateMatch[2]) != 4
|| !checkdate($dateMatch[1], $dateMatch[0], $dateMatch[2])
) {
$error_message = "Please, introduce a valid date format";
} else {
if(add_task($title, $date, $time, $project_id)) {
header('Location: task_list.php');
exit;
} else {
$error_message = "Could not add task";
}
}
}
include 'includes/header.php';?>
<div class="col-container">
<h1>Add task</h1>
<?php
if (isset($error_message)) {
echo '<p class="message">' . $error_message . '</p>';
}
?>
<form method="POST" action="task.php">
<div>
<label for="project_id" class="required">Project</label>
<select name="project_id">
<option value="">Select one</option>
<?php foreach (get_projects_list() as $item) {
echo '<option value="' . $item['project_id'] . '"';
if ($project_id == $item['project_id']) {
echo 'selected';
}
echo '>' . $item['title'] . "</option>";
} ?>
</select>
</div>
<div>
<label for="title" class="required">Title</label>
<input name="title" type="text" value="<?php echo htmlspecialchars($title); ?>" />
</div>
<div>
<label for="date" class="required">Date</label>
<input name="date" type="text" value="<?php echo htmlspecialchars($date); ?>" />
</div>
<div>
<label for="time" class="required">Time</label>
<input name="time" type="text" value="<?php echo htmlspecialchars($time); ?>" />
</div>
<button class="submit" type="submit" name="submit">Submit</button>
</form>
</div>

<?php include 'includes/footer.php';


task_list.php

<?php
$pageTitle = "Task | Time Tracker";
$page = 'taks';

include('includes/functions.php');
include 'includes/header.php'; ?>

<div class="col-container">
<h1>Task List</h1>
<a class="addLink" href="task.php">Add task</a>
<ul class="taskList">
<?php
foreach (get_tasks_list() as $item) {
echo '<li>' . $item['title'] .' - In project: ' . $item['project'] . '</li>';
}
?>
</ul>
</div>

<?php include 'includes/footer.php';


reports.php

<?php require_once 'includes/functions.php';

$pageTitle = "Reports | Time Tracker";
$page = 'reports';
$filter = 'all';

include 'includes/header.php';?>
<div class="col-container">
<h1>Reports</h1>
<form method="GET" action="reports.php">
<label for="filter-by-project">Filter:</label>
<select name="filter-by-project">
<option value="">Select a project</option>
<?php
foreach(get_projects_list() as $item) {
echo '<option value="' . $item['project_id'] . '">' . $item['title'] . '</option>';
}
?>
</select>
<input class="button" type="submit" value="Run" />
</form>
<table>
<?php
$total = $project_id = $project_total = 0;
$tasks = get_tasks_list($filter);
foreach ($tasks as $item) {
if ($project_id != $item['project_id']) {

$project_id = $item['project_id'];
echo '<thead>';
echo '<tr>';
echo '<td>' . $item['project'] . '</td>';
echo '<td> Date </td>';
echo '<td> Time </td>';
echo '</tr></thead>';
}

$project_total += $item['time'];
$total += $item['time'];
echo '<tr>';
echo '<td>' . $item['title'] . '</td>';
echo '<td>' . $item['date'] . '</td>';
echo '<td>' . $item['time'] . '</td>';
echo '</tr>';

if (next($tasks)['project_id'] != $item['project_id']) {
echo '<tr>';
echo '<th colspan="2">Project Total</th>';
echo '<th>' . $project_total . '</th>';
echo '</tr>';
$project_total = 0;
}
}
?>
<tr class="totalReportTime">
<th colspan="2">Total</th>
<th><?php echo $total; ?></th>
</tr>
</table>
</div>

<?php include 'includes/footer.php';


Errors if I include the db_connection.php file in every function or the same errors if I create a function with the connection:

Notice: Constant DB_SERVER already defined in /html/my_tracker/includes/functions.php on line 3

Notice: Constant DB_USER already defined in /html/my_tracker/includes/functions.php on line 4

Notice: Constant DB_PASSWORD already defined in /html/my_tracker/includes/functions.php on line 5

Notice: Constant DB_DATABASE already defined in /html/my_tracker/includes/functions.php on line 6

Notice: Constant DB_DRIVER already defined in /html/my_tracker/includes/functions.php on line 7


Errors if I require_once or include_once:

Notice: Undefined variable: conn in /html/my_tracker/includes/functions.php on line 44

Fatal error: Call to a member function prepare() on a non-object in /html/my_tracker/includes/functions.php on line 44

Fatal error: Call to a member function query() on a non-object in /html/my_tracker/includes/functions.php on line 52

Fatal error: Call to a member function execute() on a non-object in /html/my_tracker/includes/functions.php on line 66

Answer

Okay... the code is horrible (sorry to say, but it is. Database login and information should NEVER be defined in constants). However, this is very simple to fix. Instead of redefining, and/or re-including everywhere, just do this where you have defined the constants:

if (!defined('DB_SERVER')) {
    define('DB_SERVER', "localhost");
}
if (!defined('DB_USER')) {
    define('DB_USER', "tracker_user");
}   
if (!defined('DB_PASSWORD')) {
    define('DB_PASSWORD', "tracker_pass");
}
if (!defined('DB_DATABASE')) {
    define('DB_DATABASE', "tracker");
}
if (!defined('DB_DRIVER')) {
    define('DB_DRIVER', "mysql");
}

This should fix the pesky "already defined" messages, without having to rewrite the whole code - which you should do.

PDO should be used in a OOP-state, not procedural, and defining a $db-class will normally prevent all of these problems. Also, putting vital information in defined constants is a major security problem, and should be avoided. You should have some kind of config-file where you define all of these variables, and load this into the classes that need them. However, this would mean a complete redesign of quite a bit of code.