I'm trying to convert the following php code into a prepared statements.
The code below querys for all games that has not started yet, delete all picks and then insert new picks.
$sql = "SELECT gameID, weekNum, gameTimeEastern FROM htb_schedule
WHERE weekNum = " . $week . "
AND (DATE_ADD(NOW(), INTERVAL " . SERVER_TIMEZONE_OFFSET . " HOUR) < gameTimeEastern
AND DATE_ADD(NOW(), INTERVAL " . SERVER_TIMEZONE_OFFSET . " HOUR) < '" . $cutoffDateTime . "') ";
$query = $mysqli->query($sql);
if ($query->num_rows > 0) {
while ($row = $query->fetch_assoc()) {
$sql = "DELETE FROM htb_picks WHERE userID = " . $user->userID . " AND gameID = " . $row['gameID'];
$mysqli->query($sql);
if (!empty($_POST['game' . $row['gameID']])) {
$sql = "INSERT INTO htb_picks (userID, gameID, pickID, weekN, timestamp) VALUES (" . $user->userID . ", " . $row['gameID'] . ", '" . $_POST['game' . $row['gameID']] . "', " . $week . ", NOW() )";
$mysqli->query($sql);
}
}
}
The code below is my attempt at prepared statements.
$sql = "SELECT gameID, weekNum, gameTimeEastern FROM htb_schedule
WHERE weekNum = ?
AND (DATE_ADD(NOW(), INTERVAL " . SERVER_TIMEZONE_OFFSET . " HOUR) < gameTimeEastern
AND DATE_ADD(NOW(), INTERVAL " . SERVER_TIMEZONE_OFFSET . " HOUR) < '" . $cutoffDateTime . "') ";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param("i", $week);
$stmt->execute();
$stmt->bind_result($gameID, $weekNum, $gameTimeEastern);
if ($stmt->num_rows > 0) {
while ($stmt->fetch()) {
$sql = "DELETE FROM htb_picks WHERE userID = ? AND gameID = ? ";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param("ii", $user->userID, $gameID);
$stmt->execute();
$stmt->close();
if (!empty($_POST['game' . $row['gameID']])) {
$sql = " INSERT INTO htb_picks (userID, gameID, pickID, weekN, timestamp) VALUES (?, ?, ?, ?, NOW()) ";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param("iisi", $user->userID, $gameID, $_POST['game' . $row['gameID']], $week);
$stmt->execute();
$stmt->close();
}
}
}
$stmt->free_result();
The code is suppose to DELETE and INSERT results but it's not doing that. What am I doing wrong?
If I use the following code below it will work but the first sql is not a prepared statment.
$sql = "SELECT * FROM htb_schedule
WHERE weekNum = " . $week . "
AND (DATE_ADD(NOW(), INTERVAL " . SERVER_TIMEZONE_OFFSET . " HOUR) < gameTimeEastern
AND DATE_ADD(NOW(), INTERVAL " . SERVER_TIMEZONE_OFFSET . " HOUR) < '" . $cutoffDateTime . "') ";
$query = $mysqli->query($sql);
if ($query->num_rows > 0) {
while ($row = $query->fetch_assoc()) {
$sql = "DELETE FROM htb_picks WHERE userID = ? AND gameID = ? ";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param("ii", $user->userID, $row['gameID']);
$stmt->execute();
$stmt->close();
if (!empty($_POST['game' . $row['gameID']])) {
$sql = " INSERT INTO htb_picks (userID, gameID, pickID, weekN, timestamp) VALUES (?, ?, ?, ?, NOW()) ";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param("iisi", $user->userID, $row['gameID'], $_POST['game' . $row['gameID']], $week);
$stmt->execute();
$stmt->close();
}
}
}
$query->free;