I was doing it the other way before but it was having too many if statements because i had 3 select options, which means that i would need to perform so many ifs… and yet i can’t get the user to select all 3 options but just individual option… i understand what you are saying… my columns name are not the best and organised
Database design is not a luxury, not something to go off half-cock on. If you don’t get it right to begin with you will be beset with problems for the life of the website (assuming it doesn’t die a cot death).
1 Like
I understand where you’re at exactly. When I first started I knew PHP code better than database code. In fact I did well to get a SELECT right let alone JOINs or sub-SELECTs. Because of this my tendency was to have very simple “one big table with everything” and then use PHP to do the heavy lifting. It “worked” up to a point as long as the tables were relatively small. But once they started getting larger the PHP code needed to work with them became unmanageable. At that point I started to learn more about more complex queries so that the work could be shared between what the database can do well and what PHP can do. eg. using ORDER BY expiry_date in the query instead of sort($expiry_date) in PHP
A few nested if(){ are OK, but once they get convoluted it’s a good sign that a step back and a rethink is a good idea.
For a contrived example, if there were user and plan tables, a JOIN might look like
SELECT user.id
, plan.id
FROM user
JOIN plan
ON plan.user_id = user.id
GROUP BY user.id
without any need for complex if blocks
2 Likes
Tell you what, lets get you on track the correct way. We will all just be spinning our wheels if we don’t. Post an SQL dump of your current DB schema and we will start from the beginning and build a solid foundation to code against. You also need to get an understanding of “Database Normalization” so we dont have to do all the work for you. It is one of the easier things to understand about databases.
2 Likes
If I am using the prepared statement, do I still need mysqli_real_escape_string? I think someone said no but just thought I would confirm that…
No you don’t need it.
1 Like
The great thing about forum discussions is that it is easy to reread the thread to check things like that when you’re uncertain. And there’s also the forum search, to help you locate information from other threads.
4 Likes
The database is the foundation of the application. It is extremely important to get it right. When it comes to professional development one poor architectural decision like stuffing a bunch of values in a single column will end up costing much more money longterm then had things been properly done. This is especially true once you start needing to generate reports. Poor database design and reporting do not mix well. Also not taking into consideration the need for historical info is a major mistake. Simple having a single value in a column and updating it while simple is not robust nor facilitates many things which you might need to do in the future.
3 Likes
I am still puzzled why my memberships table is not working! I know that I am new to this and have a lot to learn but like to learn things myself at the same time but it just seems that a ghost has taken over this code! I keep getting this message! I also have another problem… it shouldn’t allow me to add another user with the same username but has done so…
This is my updated code for the signup page
<?php
if (!isset($_POST['submit'])) {
header("Location: ../signup.php");
exit();
} else {
include_once 'dbh.php';
// information from users table
$first = $_POST['first'];
$last = $_POST['last'];
$email = $_POST['email'] ;
$uid = $_POST['uid'];
$password = $_POST['pwd'];
$user_permission = 'Standard User';
$freelesson = $_POST['freelesson'];
$datejoined = date('Y-m-d H:i:s', strtotime("+1 day"));
$user_activate = 0;
$premium = 0;
// information from memberships table
// $subscriptionplan = implode(',', $subscriptionplan);
$subscriptionplan = $_POST['subscriptionplan'];
$subscriptionplan = implode(',', $subscriptionplan);
$subscriptionplandate = date('Y-m-d H:i:s', strtotime("+1 day"));
$subscriptionplandate2 = date('Y-m-d H:i:s', strtotime("+1 day"));
$subscriptionplandate3 = date('Y-m-d H:i:s', strtotime("+1 day"));
$fees = 0;
$fees2 = 0;
$fees3 = 0;
$totalfees = 0;
$paid = 0;
$paid2 = 0;
$paid3 = 0;
$expirydate = date('Y-m-d H:i:s', strtotime("+1 day"));
$expirydate2 = date('Y-m-d H:i:s', strtotime("+1 day"));
$expirydate3 = date('Y-m-d H:i:s', strtotime("+1 day"));
$paidbydate = date('Y-m-d H:i:s', strtotime("+1 day"));
$paidbydate2 = date('Y-m-d H:i:s', strtotime("+1 day"));
$paidbydate3 = date('Y-m-d H:i:s', strtotime("+1 day"));
$overdue = 0;
$overdue2 = 0;
$overdue3 = 0;
// $fees = mysqli_real_escape_string($conn, $_POST['fees']);
// $totalfees = mysqli_real_escape_string($conn, $_POST['totalfees']);
$activate = 0;
//Error handlers...
if (empty($first) || empty($last) || empty($email) || empty($uid)|| empty($password)) {
header("Location: ../signup.php?signup=empty");
exit();
} else {
//Check if input characters are valid
if (!preg_match("/^[a-zA-Z]*$/", $first) || !preg_match("/^[a-zA-Z]*$/", $last)) {
header("Location: ../signup.php?signup=invalid");
exit();
} else {
//Checking for valid emails
if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
header("Location: ../signup.php?signup=email");
exit();
} else {
if (Strlen($password) < 5) {
header("Location: ../signup.php?signup=invalidlength");
exit();
} else {
if (!preg_match('/^(?=.*\d)(?=.*[A-Za-z])[0-9A-Za-z!@#$%]{8,20}$/', $password)) {
header ("Location: ../signup.php?signup=notalphanumeric");
exit();
} else {
if($subscriptionplan == '') {
header("Location: ../signup.php?signup=mustchooseplan");
exit();
}
$sql = "SELECT * FROM users WHERE user_uid = ?;";
// Create a prepared statement
$stmt = mysqli_stmt_init($conn);
//Prepare the prepared stement
if (!mysqli_stmt_prepare($stmt, $sql)) {
echo "SQL statement failed";
} else {
//Bind parameters to the placeholder
mysqli_stmt_bind_param($stmt, "s", $uid);
$result = mysqli_stmt_prepare($stmt, $sql);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
$num_rows = mysqli_num_rows($result);
if ($num_rows > 0) {
header("Location: ../signup.php?signup=usernametaken");
exit();
} else {
// Select subscription
// Supply a random generated token for email activation
$token = 'qqewreqreqwsdfdfdafcbvcQERFGHFGHGFHRETERTDF!@#$%^^()';
$token = str_shuffle($token);
$token = substr($token, 0, 10);
$token2 = 'qqewreqreqwsdfdfdafcbvcQERFGHFGHGFHRETERTDF!@#$%^^()';
$token2 = str_shuffle($token2);
$token2 = substr($token2, 0, 10);
$paid = 0;
$activate = 0;
//Hashing the password
$hashedPwd = password_hash($password, PASSWORD_DEFAULT);
//Insert the user into the database
$sql ="INSERT INTO users (user_first, user_last, user_email, user_uid, user_password, user_permission, freelesson, datejoined, user_token, user_activate, premium) VALUES (?,?,?,?,?,?,?,?,?,?,?);";
$stmt = mysqli_stmt_init($conn);
if(!mysqli_stmt_prepare($stmt, $sql)) {
echo "SQL error";
} else {
mysqli_stmt_bind_param($stmt, "sssssssssss", $first, $last, $email, $uid, $hashedPwd, $user_permission, $freelesson,$datejoined, $token, $user_activate, $premium);
mysqli_stmt_execute($stmt);
}
// insert into user_lessonsubscription table
// $subscriptionplan = implode(',',$subscriptionplan);
$sql ="INSERT INTO memberships (user_uid, subscriptionplan, subscriptionplandate, subscriptionplandate2, subscriptionplandate3, fees, fees2, fees3, totalfees, paid, paid2, paid3, expirydate, expirydate2, expirydate3, paidbydate, paidbydate2, paidbydate3, overdue, overdue2, overdue3, token2, activate) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
$stmt = mysqli_stmt_init($conn);
if(!mysqli_stmt_prepare($stmt, $sql)) {
echo "SQL error";
} else {
mysqli_stmt_bind_param($stmt, "sssssssssssssssssssssss", $uid, $subscriptionplan, $subscriptionplandate, $subscriptionplandate2, $subscriptionplandate3, $fees, $fees2, $fees3, $totalfees, $paid, $paid2, $paid3, $expirydate, $expirydate2, $expirydate3, $paidbydate, $paidbydate2, $paidbydate3, $overdue, $overdue2, $overdue3, $token2, $activate);
mysqli_stmt_execute($stmt);
}
// Send an email to the user:
$company = "pianocourse101@hotmail.com";
$subject = "Activate your email account";
$mailTo = "piano0011@hotmail.com";
$headers = "From: ".$company;
$txt = "Thank you for registering with pianocourse101! At pianocourse101, your child can now learn how to play the piano right from the comfort of your own home! \n\nOur lessons are based from the Bastien Piano Basics series because it is both fun and educational for your child. \n\nHowever, you must activate your FREE membership account by clicking on the link below: \n\n http://localhost/loginsystem/includes/activate.php?email=".$mailTo."&activatetoken=".$token."" ;
mail($mailTo, $subject, $txt, $headers);
header("Location: ../signup.php?signup=success");
exit();
}
}
}
}
}
}
}
}
I have declared all variables at the top and checked my prepared statements…
Is that prepared-statement stuff correct, where you’re checking to see if the user exists? I don’t use mysqli as I prefer PDO, but it doesn’t seem to be the same as the documentation suggests it should be.
$sql = "SELECT * FROM users WHERE user_uid = ?;";
$stmt = mysqli_stmt_init($conn);
if (!mysqli_stmt_prepare($stmt, $sql)) {
echo "SQL statement failed";
} else {
mysqli_stmt_bind_param($stmt, "s", $uid);
$result = mysqli_stmt_prepare($stmt, $sql);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
$num_rows = mysqli_num_rows($result);
I don’t understand why you seem to call mysqli_stmt_prepare twice, the second time after you’ve called mysqli_stmt_bind_param(). Surely that won’t work?
As an aside, if you’re just checking for the existence of a user with that same username and don’t intend to use the results, there’s no need to SELECT * in the query, just select one column, or even just use SELECT COUNT to return the number.
You seem to be using the username as the primary key to link the two tables together, which to me seems wrong somehow. It’ll probably work, but if a user wants to change their username at some point, it’ll be a pain compared to using an auto-generated ID when the user is created and using that to link the tables. It also seems that in splitting out the subscription information to the memberships table, you’ve retained the clunky database layout that you had before. It’s great that you’ve split it, but the point wasn’t just to put it somewhere else, it was to improve the design.
Think of a query where you want to figure out if a user has a specific type of membership. Your database layout means the query is more complex than it needs to be as it will have to look in three different columns to see if the membership exists. It’ll then have to figure out how to get the appropriate “linked” information for that membership. Your membership table should look like this:
membership-id // unique id for the table
user-id // to link to the users table, to show the user name
subscription-plan //
subscription-plan-date //
fees
paid
expiry_date
paidbydate
overdue
token
activate
and allow multiple rows for each user. That way you can have as many subscriptions per user as you want, and it’s far easier to find stuff. Adding stuff is easier too - you don’t need to look through your three subscription columns to find an empty one, just write a simple INSERT query and you’re done.
1 Like
I am not that certain but I was checking to see if it failed first and then success… checking for errors first…
That’s great, but you then prepare it again, but don’t bind the parameter after the second prepare.
You could easily add some echo() statements to see what’s happening step by step.
Also - see edit above, written as you replied.
I read somewhere that I can’t use num_rows with prepared statement
You’re using it here with a prepared statement. Does it work? Where did you read that?
There’s nothing in the PHP documentation to specifically say that you can’t use it with a prepared statement, but there are notes about using it with buffered result sets.
If it’s really an issue, then you could use the query instead:
select count(*) as rowcount from users where user_uid = ?
then retrieve the result and look at the value of rowcount.
Is this correct? but it still allows me to register with the same username:
$sql = "select count(*) as rowcount from users where user_uid = ?;";
// Create a prepared statement
$stmt = mysqli_stmt_init($conn);
//Prepare the prepared stement
if (!mysqli_stmt_prepare($stmt, $sql)) {
echo "SQL statement failed";
} else {
//Bind parameters to the placeholder
mysqli_stmt_bind_param($stmt, "s", $uid);
mysqli_stmt_execute($stmt);
if ($rowcount > 0) {
header("Location: ../signup.php?signup=usernametaken");
exit();
} else {
Can’t be, then. You don’t define $rowcount anywhere so it’ll default to zero.
But it’s fixed in your other thread, so probably best to stop confusing matters.
Can you show the structure of your memberships table?
That also indicates that the username field in your database is missing a UNIQUE INDEX, as that should never be allowed to happen.
1 Like
But what was the typo?