How do I update date type column in database using PHP variable?

Question

0.00/5 (No votes)

See more:

I have a function called updateCatalog($conn, $date) and with this I hope to update a column with date as type on a table in the database.
Problem I'm having is when I do, it sets the date to 0000-00-00 and show -0001-11-30 on my index.php page.
What should I do?

Index.php:

PHP

$sql = "SELECT * FROM catalog";
	$result = $conn->query($sql);

	echo '
		<table id = "catalog-table">
			<tr class = "column-name">
				<th> YEAR </th>
				<th> MONTH </th>
				<th> TITLE </th>
				<th class = "butt-column"> </th>
			</tr>
	';

	while ($row = mysqli_fetch_array($result)) {

		$month = date('F', strtotime(str_replace('.', '-', $row['dates'])));
		$year = date('Y', strtotime(str_replace('.', '-', $row['dates'])));

		echo "<tr>";
		echo '<td class = "cent-col">' . $year . '</td>';
		echo '<td class = "cent-col">' . $month . '</td>';
		echo '<td>' . $row['title'] . '</td>';
		echo '
			<td class = "cent-col">
				<form action="editcat.php" method="POST">
					<input type = "hidden" name = "id" value = "' . $row['id'] . '"/>
					<input type = "hidden" name = "date" value = "' . $row['dates'] . '"/>
					<input type = "hidden" name = "title" value = "' . $row['title'] . '"/>
					<input class = "edit-butt" type = "submit" name = "submit-butt" value = "edit">
				</form>
			</td>
		';
		echo "</tr>";

	}
	echo "</table>";

editcat.php:

PHP

<section class = "form-sec" id = "editcat-form-sec">
	<div class = "form-div">
		<form id="editcat-form" action="includes/editcat.inc.php" method="post">
			<?php
				if (isset($_POST['id'])) {

					$id = $_POST['id'];
					$date = $_POST['date'];
					$title = $_POST['title'];

					echo '<input type="hidden" name="id" value="' . $id . '"/>';
					echo '<input id = "date-in" type="date" name="date" value="' . $date . '" placeholder="' . $date . '">';
					echo '<input id = "title-in" type="text" name="title" value="' . $title . '" placeholder="' . $title . '">';
				}
			?>

			<button type="submit" name="submit">Update</button>
		</form>

	</div>
</section>

includes/editcat.inc.php:

PHP

<?php

if (isset($_POST['submit'])) {
	
	$id = $_POST['id'];
	$date = $_POST['date'];
	$title = $_POST['title'];

	require_once 'dbh.inc.php';
	require_once 'functions.inc.php';

	updateCat($conn, $id, $date, $title);
	header("location: ../index.php");
	exit();

}
else {
	header("location: ../index.php");
	exit();
}

includes/functions.inc.php:

PHP

//--------------------Edit Catalog--------------------
function updateCat($conn, $id, $date, $title) {
	$sql = "UPDATE catalog SET dates = $date, title = \"$title\" WHERE id = $id;";
	
	if ($conn -> query($sql) === TRUE) {
		header("location: ../index.php");
		exit();
	}

	else {
		header("location: ../index.php");
		exit();
	}
}

What I have tried:

I tried putting these in the sql function instead of $date
$newdate = date($date);
$newdate2 = date('Y-m-d',strtotime($row['dates']));
$newdate3 = STR_TO_DATE($row['$date'], '%m/%d/%Y');

Posted 28-Sep-22 2:40am

School Shan

Add a Solution

Comments

Richard Deeming 28-Sep-22 8:51am

Not like that! Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation/interpolation to build a SQL query. ALWAYS use a parameterized query.

PHP: SQL Injection - Manual[^]

School Shan 28-Sep-22 9:23am

I do see a lot of people conserned with SQL injection but from what I read it's what hackers/attackers do to extract data or something. I do understand that that is a big problem but for my use there will never be a hacker/attacker so is it still important for me to be concern about it? Is there other reason for me to be worry about it? Other than it is important for me to make a good practice or something. Because from a Youtube tutorial I followed to make it secure from SQL Injection, it makes the code longer and more complicated for me to understand what is happening.
This is only for my school project, I just need to show working website and that's it. It will never be published online. Well I won't and if someone from the school wants to use my code(which I highly doubt they ever will) then I think they can deal with making it more secure.

Richard Deeming 28-Sep-22 9:29am

"... there will never be a hacker/attacker ..." 🤣

Even if you can't understand why someone would want to hack your site, they will still do it "for the lolz".

Even if your site is only available inside your local network, you could have an insider threats[^] - eg: a disgruntled employee. Or you could have a computer inside your network infected with malware.

No matter what you may think, there will ALWAYS be someone trying to hack your site.

And even if this is just toy code for a school assignment, that will never be run in any real system, and either your teacher doesn't care about the security of your code, or you don't care about being marked down for writing horribly-insecure code, it's still not going to hurt you to do it the right way. You'll avoid a ton of potential data-conversion errors, as well as forming good habits for writing "real" code.

School Shan 28-Sep-22 9:44am

Hmm I guess you are right with the there will always be someone hacking just for the lolz. But as I mentioned, I really just need to show the working website not a secure website.
However, from what you said, I am curious how people can hack something only available in my local network. Does local network mean like connected to the same wifi/internet connection?
Also, I know it is a good practice/habit to always write the 'real' secure codes, but at this moment with this project it will hurt me if it will take me too long to do so since due date.

That said, I do have a code from the tutorial I mentioned. Will this fix the problem I am having right now?

$sql = "INSERT INTO users2 (facId, name, username, password) VALUES (?, ?, ?, ?);";
$stmt = mysqli_stmt_init($conn);

if (!mysqli_stmt_prepare($stmt, $sql)) {
header("location: ../signup.php?error=stmtfailed");
exit();
}

mysqli_stmt_bind_param($stmt, "isss", $facid, $name, $username, $pwd);
mysqli_stmt_execute($stmt);
mysqli_stmt_close($stmt);
header("location: ../signup.php?error=none");
exit();

And other than pointing out that my code is not secure, do you have a solution to my problem?

Richard Deeming 28-Sep-22 9:48am

Using parameters would probably fix the issue you're having at the moment. But I'd be inclined to avoid that tutorial and anything else written by its author - storing passwords in plain-text is another major security problem.

Secure Password Authentication Explained Simply[^]

PHP even provides built-in functions for doing it properly, so there's no excuse!

PHP: password_hash[^]
PHP: password_verify[^]

School Shan 28-Sep-22 9:59am

Oh yes the tutorial also used the password hashing but I didn't I actually wanted to see the passwords that I put on the phpmyadmin thingy. Well that is my excuse.

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)