Member Avatar for dyingatmidnight

Urg, I will so happy when I'm finished this project, I feel like I'm spamming you guys every couple of days.

So I have a couple date values in my tables. And I'm trying to do some calculations with them. Which are working fine. But I'm retuning the number of days between two dates and I need to insert that into a table. The value in the table is an interger and I think this is why I'm getting a syntax error. My syntax is correct if I copy the query over to the console and replace the php variables with arbitrary values so I know it the query is structured correct at least.

$curDate = new DateTime();
$query = "SELECT * FROM checkout WHERE dueDate < CURDATE() and cardNumber = '$cardNumber'";
$result = mysql_query($query) or die(mysql_error());
$lateBooks=mysql_numrows($result);
if($lateBooks == 0){
	echo "There are no fines on this account";
}
if($lateBooks > 0){
	$i=0;
	while($i<$lateBooks){
		$finePerDay = 0.15;
		$dueDate = mysql_result($result, $i, "dueDate");
		$bookDue = new DateTime($dueDate);
		$libraryID = mysql_result($result, $i, "libraryID");
		$interval = $bookDue->diff($curDate);
		$daysLate = $interval->d;
		$fines = "INSERT INTO fines VALUES('$libraryID','$cardNumber','$daysLate'";
		$result = mysql_query($fines) or die(mysql_error());
		$fine = $daysLate * $finePerDay;
		$i++;
	}
}

It craps out on the $fines query and I believe it has to do with the $daysLate variable not matching with the int(64) value it should be passed. I've tried casting $daysLate as an int but that doesn't seem to help.

  • 2 Contributors
  • 2 Replies
  • 335 Views
  • 8 Hours Discussion Span
  • Latest Post Latest Post by dyingatmidnight

Dani AI

Generated

A quick note for context: the immediate syntax error was a missing closing parenthesis in the INSERT statement — thanks to for spotting that — and once the bracket was added the query ran. The following suggestions are extra, practical hardening and correctness tips that help avoid similar problems going forward.

Always list the target columns in an INSERT (for example, INSERT INTO fines (libraryID, cardNumber, daysLate) ...). Omitting the column list makes the query brittle (it must match the table column order exactly) and can cause silent errors or unexpected value placement; also be explicit about types before inserting because MySQL will coerce values when types differ. (dev.mysql.com)

When computing the number of days between two DateTime objects, do not rely on the DateInterval day component alone. The d part is the day component (0–31) and will not reflect the total elapsed days when months or years are involved. Use the interval’s total-days value or format code instead, for example via the days property or format('%a'), and cast to integer before using in arithmetic. That avoids undercounting late days across month boundaries. (php.net)

Modernize and harden DB code: the old mysql_* API is deprecated/removed — migrate to mysqli or PDO and use prepared statements to separate data from SQL and to bind types (safer and clearer). A minimal mysqli example:

$mysqli = new mysqli($host, $user, $pass, $db);
$stmt = $mysqli->prepare('INSERT INTO fines (libraryID, cardNumber, daysLate) VALUES (?, ?, ?)');
$stmt->bind_param('ssi', $libraryID, $cardNumber, $daysLate);
$stmt->execute();

Also, store monetary values in an exact type (DECIMAL) rather than float to avoid rounding problems (or store cents as integers). These steps improve safety, correctness and maintainability. (php.net)

Recommended Answers

All 2 Replies

Member Avatar for twiss

It might be that that's not the problem, but there's a missing ) at the end of the query.
What does var_dump($daysLate); say?

commented: nice spot +15
Member Avatar for dyingatmidnight

oh lololol. Clearly I have been looking at this stuff for far too long today to not have noticed that. Having added in the offending bracket it now works fine. Thanks so much.

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.