Att mods: if you are reading this, I’m sorry I couldn’t find a more accurate tag.
Link to exercise:
https://www.codecademy.com/courses/learn-intermediate-php/projects/php-code-club
I’m trying to complete the optional 22. task but have been unsuccessful, i want to introduce a column for the userStatus (i’ve referred to as simply ‘status’) but how to change something on the DB boggled me for a long time until i noticed the db.sql file. But an ychanges in the db.sql doesnt seem to affect the CC environment.
I’m sure it’s something silly and small (as per my history here) but i’m been stuck on this for hours now so I’m looking for an assist.
Note: i’ve restarted and forced refreshed the pages numerous times.
My latest error on https://localhost/members.php:
SQLSTATE[42703]: Undefined column: 7 ERROR: column members.status does not exist LINE 3: members.*, members.status, tiers.title ^
My db.sql
DROP TABLE IF EXISTS tiers;
CREATE TABLE tiers (
id serial PRIMARY KEY,
title VARCHAR (255) NOT NULL,
price INT,
status BOOLEAN DEFAULT TRUE
);
INSERT INTO tiers (title, price)
VALUES
('Bronze', 20),
('Silver', 50),
('Gold', 100),
('Platinum', 250);
DROP TABLE IF EXISTS members;
CREATE TABLE members (
id serial PRIMARY KEY,
first_name VARCHAR (255) NOT NULL,
last_name VARCHAR (255),
address VARCHAR (255),
tier_id INT
);
INSERT INTO members (first_name, last_name, address, tier_id)
VALUES
('Selena', 'Heidenreich', '858 Murl Locks Lake Jonasside, TN 33085-2765' , 2),
('Theodore', 'Barnes', '994 Kunze Inlet Kemmerberg, WA 69344', 3),
('Matias', 'Larson', '12340 Kub Ridge Hailiemouth, MN 96724', 3),
('Mckayla', 'Gopinatha', '50601 Homenick Lake Suite 855 Keelingbury, WV 62917-8508', 4);
UPDATE members SET status = 1 WHERE id = 1;
members.php
<?php
require_once 'functions.php';
// Create a try/catch block to handle database exceptions
try {
// Connect to the database using the connect function
$db = connect();
// Create the $membersQuery here. Use an inner join to get the tier titles for the table
$membersQuery = $db->query('
SELECT
members.*, members.status, tiers.title
FROM
members
INNER JOIN
tiers ON members.tier_id = tiers.id'
);
// Fetch all members and assign the result to $members
$members = $membersQuery->fetchAll(PDO::FETCH_ASSOC);
} catch (Exception $e) {
// Echo the message if there was an exception
echo $e->getMessage();
}
// Close the database connection here
?>
<?php require_once '_header.php' ?>
<a href='index.php' class='btn btn-secondary m-2 active' role='button'>Home</a>
<a href='tiers.php' class='btn btn-secondary m-2 active' role='button'>Tiers</a>
<?php if (!empty($_GET['type']) && ($_GET['type'] === 'success')) : ?>
<div class='row'>
<div class='alert alert-success'>
Success! <?= $_GET['message'] ?>
</div>
</div>
<?php elseif (!empty($_GET['type']) && ($_GET['type'] === 'error')) : ?>
<div class='row'>
<div class='alert alert-danger'>
Error! <?= $_GET['message'] ?>
</div>
</div>
<?php endif; ?>
<div class='row'>
<h1 class='col-md-12 text-center border border-dark text-white bg-primary'>Members</h1>
</div>
<div class='row'>
<table class='table table-striped'>
<thead>
<tr>
<th scope='col'>#</th>
<th scope='col'>First Name</th>
<th scope='col'>Last Name</th>
<th scope='col'>Address</th>
<th scope='col'>Tier</th>
<th scope='col'>Status</th>
<th scope='col'>Actions</th>
</tr>
</thead>
<tbody>
<?php foreach ($members as $member) : ?>
<tr>
<td><?= $member['id'] ?></td>
<td><?= htmlentities($member['first_name']) ?></td>
<td><?= htmlentities($member['last_name']) ?></td>
<td><?= htmlentities($member['address']) ?></td>
<td><?= htmlentities($member['title']) ?></td>
<td><?= htmlentities($member['status'] ? 'active' : 'inactive') ?></td>
<td>
<a class='btn btn-primary' href='member-form.php?id=<?= $member['id'] ?>' role='button'>Edit</a>
<a class='btn btn-danger' href='delete-member.php?id=<?= $member['id'] ?>' role='button'>Delete</a>
</td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
</div>
<div class='row'>
<div class='col'>
<a class='btn btn-success' href='member-form.php' role='button'>Add member</a>
</div>
</div>
<?php require_once '_footer.php' ?>
Edit: Just realized this is terrible to recreate. please let me know if I should share all the projects modified files. I can post it as code snippet like above or upload as PHP files.
