Update database with Implode

[NICON]

Essentially what I am trying to do here is update my database table where the column open_day contains 25 0's and 25 commas like this 0,0,0,0,0,0,0, etc...

 

I want to update the array position where $_GET('day') - 1 is the correct position for that day.

if ($_GET['day']) {
	$time = date("d", time());
	if ($_GET['day'] > 25 || $_GET['day'] < 1) {
		$msg = 'Nice try there is no gift available for that day!';
	}
	elseif ($time < $_GET['day']) {
		$msg = 'To soon you can wait to open this day!';
	}	
	elseif ($time > $_GET['day']) {
		$msg = 'Sorry you missed this day try again next year!';
	}
	else {
		$db->query("SELECT * FROM adventcal_2016 WHERE uid = ?");
		$db->execute(array(
			$user_class->id
		));
		$entry = $db->fetch_row(true);
		$open_day = explode(",", $entry[open_day]);
	        $opened = $open_day[$_GET['day'] - 1];
		$update = implode(",", $opened);
		$db->query("UPDATE adventcal_2016 SET open_day = ? WHERE uid = ?");
                $db->execute(array(
                    $update,
                    $user_class->id
                ));
		$msg = 'You have opened day ' . $_GET['day'] . ' and received x points and $x!';
        }
}

[benanamen]

Before we get to your question, Why do you want to insert 25 zeros and commas?

[NICON]

it simplifies the database table. Its a temporary table for an advent calendar only used in December and I didn't want to make each day into a separate column. If I showed you the rest of the script you would understand better why I have done it this way.

Edited November 29, 2016 by NICON

[benanamen]

To give you the proper advice it would be a good idea to be able to see what you're working with and what the real problem is you are trying to solve.

[NICON]

Okay I stepped away from the office to go home as soon as I get there is will post the entire script along with the database structure.

[NICON]

Okay below is the script I have built in its entirety. It is nowhere near done because I have only got to this last and final point of the functionality. I know I am needing a foreach loop but not 100% on how to go about making it work properly.

<?php
include "header.php";
genHead("Advent Calendar");

$days = array(
    1,
    2,
    3,
    4,
    5,
    6,
    7,
    8,
    9,
    10,
    11,
    12,
    13,
    14,
    15,
    16,
    17,
    18,
    19,
    20,
    21,
    22,
    23,
    24,
    25
);
$box = array(
    1,
    2,
    3,
    4,
    5,
    6,
    7,
    8,
    9,
    10,
    11,
    12,
    13,
    14,
    15,
    16,
    17,
    18,
    19,
    20,
    21,
    22,
    23,
    24,
    25
);
if ($_GET['day']) {
    $time = date("d", time());
    if ($_GET['day'] > 25 || $_GET['day'] < 1) {
	$msg = 'Nice try there is no gift available for that day!';
    }
    elseif ($time < $_GET['day']) {
	$msg = 'To soon you can wait to open this day!';
    }	
    elseif ($time > $_GET['day']) {
	$msg = 'Sorry you missed this day try again next year!';
    }
    else {
	$db->query("SELECT * FROM adventcal_2016 WHERE uid = ?");
	$db->execute(array(
		$user_class->id
	));
	$entry = $db->fetch_row(true);
	$open_day = explode(",", $entry[open_day]);
	$opened = $open_day[$_GET['day'] - 1];
	$update = implode(",", $opened);
	$db->query("UPDATE adventcal_2016 SET open_day = ? WHERE uid = ?");
        $db->execute(array(
            $update,
            $user_class->id
        ));
		
	$msg = 'You have opened day ' . $_GET['day'] . ' and received x points and $x ' . $entry[open_day] . '!';
    }
}

$db->query("SELECT uid FROM adventcal_2016 WHERE uid = ?");
$db->execute(array(
    $user_class->id
));
$entry = ($db->fetch_single()) ? true : false;
if (empty($entry))
	$db->query("INSERT INTO adventcal_2016 (uid) VALUES (?)");
    $db->execute(array(
        $user_class->id,
    ));

echo "<div class='success'>",$msg,"<br /></div>";

if ($user_class->id != 2) {
    $mtg->error("You don't have access");
} else {
    echo '<table cellpadding="5" align="center">';
    echo '<tr>';
    echo '<td> </td>';
    echo '<td> </td>';
    echo '<td> </td>';
    echo '<td> </td>';
    echo '<td>' . showDay(1, $box) . '</td>';
    echo '<td>' . showDay(2, $box) . '</td>';
    echo '<td>' . showDay(3, $box) . '</td>';
    echo '</tr>';
    echo '<tr>';
    echo '<td>' . showDay(4, $box) . '</td>';
    echo '<td>' . showDay(5, $box) . '</td>';
    echo '<td>' . showDay(6, $box) . '</td>';
    echo '<td>' . showDay(7, $box) . '</td>';
    echo '<td>' . showDay(8, $box) . '</td>';
    echo '<td>' . showDay(9, $box) . '</td>';
    echo '<td>' . showDay(10, $box) . '</td>';
    echo '</tr>';
    echo '<tr>';
    echo '<td>' . showDay(11, $box) . '</td>';
    echo '<td>' . showDay(12, $box) . '</td>';
    echo '<td>' . showDay(13, $box) . '</td>';
    echo '<td>' . showDay(14, $box) . '</td>';
    echo '<td>' . showDay(15, $box) . '</td>';
    echo '<td>' . showDay(16, $box) . '</td>';
    echo '<td>' . showDay(17, $box) . '</td>';
    echo '</tr>';
    echo '<tr>';
    echo '<td>' . showDay(18, $box) . '</td>';
    echo '<td>' . showDay(19, $box) . '</td>';
    echo '<td>' . showDay(20, $box) . '</td>';
    echo '<td>' . showDay(21, $box) . '</td>';
    echo '<td>' . showDay(22, $box) . '</td>';
    echo '<td>' . showDay(23, $box) . '</td>';
    echo '<td>' . showDay(24, $box) . '</td>';
    echo '</tr>';
    echo '<tr>';
    echo '<td>' . showDay(25, $box) . '</td>';
    echo '<td> </td>';
    echo '<td> </td>';
    echo '<td> </td>';
    echo '<td> </td>';
    echo '<td> </td>';
    echo '<td> </td>';
    echo '</tr>';
    echo '</table>';
}

function showDay($num, $todaysbox) {
	global $user_class, $db;
	
	$db->query("SELECT open_day FROM adventcal_2016 WHERE uid = ?");
	$db->execute(array(
		$user_class->id
	));
	$entry = $db->fetch_row(true);
	$open_day = explode(",", $entry[open_day]);
	$open = $open_day[$num - 1];
	$rtn = '<table onclick=\'document.location="?day=' . $num . '"\' onmouseover="this.style.border=\'solid 2px #999\';" onmouseout="this.style.border=\'solid 2px #000\';" style="background-color:#000;border:solid 2px #000;" width="100">';
        $rtn .= '<tr style="background-color:#ddd;color:#000;">';
	if ($open == 1)
        $rtn .= '<td align="center" height="18" style="color:red;"><b>Opened</b>';
	else 
        $rtn .= '<td align="center" height="18" style="color:green;"><b>Not Opened</b>';
	$rtn .= '</td></tr>';
        $rtn .= '<tr><td align="center" style="color:#fff;font-size:18pt;">' . $num . '<br /></td></tr></table>';
	
        return $rtn;
}

include "footer.php";
?>

The user database table is adventcal_2016 with 3 columns uid, which is the users ID $user_class->id, the id which is just a unique int11 incremental ID, and finally the open_day which is a varchar50 that contains 50 characters by default all zeros like so 0,0,0,0,0,0,0,0,0,0, etc..

 

I need to update each of the zeros and change them to ones upon opening the box for each day. That is where I am in this project. Any and all help is appreciated.

[Barand]

A better way to do it is to have 1 row for each day for each user.

CREATE TABLE `advent` (
  `userid` int(11) NOT NULL,
  `day` int(11) NOT NULL,
  `opened` tinyint(4) NOT NULL DEFAULT 0 COMMENT '0 or 1',
  PRIMARY KEY (`userid`,`day`)
) ;

Then the update is simply

UPDATE advent
SET opened = 1
WHERE userid=? and day=?;

[NICON]

Yes that would be an option and a quite simple one but with almost 3000 users and 25 days would give me 75000 rows and I didn't want all that clutter.

[Jacques1]

The clutter you currently have is far, far worse. You can also reduce the number of rows if you only store the opened boxes (all other boxes are implicitly closed).

[NICON]

So is the answer that it cant be done in this manner?

[Jacques1]

A lot of things “can be done”. I can punch myself in the face while singing Ave Maria. But does it make sense? Not so much.

[NICON]

haha touche! Let me see what I can whip up. Really wanted to do it this way but I will modify it the way Barand mentioned... The easy way inst always the fun way!

[benanamen]

OP, an aside from whatever you're trying to do here, you can avoid clustmuck arrays by using the range function. I have already fried my brain on another problem to even begin deciphering what your doing although you have confirmed my keen spidey sense of an XY Problem. I couldn't tell you why at the moment, but whatever you're doing, you're doing it wrong. I am sure someone will get you proper answers before I can get back to you again.

<?php
$days = range(1,25);
print_r ($days);
?>

[NICON]

Barand's option by far was much much easier and it looks nice as well. Simple and functional is always better than sloppy and complex. Thanks!