Project Tracking Module - Question

mefoo · Regular Joined: Sep 03, 2010 Posts: 57

Another question on something I don't quite get.

I am trying to get in place my method of showing only "active" projects. (reason being... I want the ability to approve or deny requests prior to them showing in the project list)

Here's how I've attempted it... but it falls short.

I have created a loop that checks the db for a value (either 0 or 1) on a col in the "projects" table.

Code:

$projectresult = $db->sql_query('SELECT project_id FROM ' . $prefix . '_mpstracker_projects ORDER BY weight');

while(list($project_id) = $db->sql_fetchrow($projectresult)) {
$project = pjprojectpercent_info($project_id);

}

if($project['mps_active'] > 0) {

//// All the project listing code removed for the sake of space ///

} else {

};

The problem is that once the loop hits a value of "0".... it breaks the loop and nothing (even the project that has a value of "1" doesn't show).

I can see why it's happening... but I don't understand how to get around this. Is wrapping the entire listing in an "if/else" statement the wrong way to achieve the desired result? Again, any help or guidance will be deeply appreciated.

-----------------------

Edit... nvm... "WHERE" is the route to go. Wink

mefoo · Regular Joined: Sep 03, 2010 Posts: 57

Palbin-

I need your opinion. I'm getting really close to having a working version of what I set out to achieve, however, I've got one last real major hurdle to cross. It's the group id part.

Here's what I was thinking. (I haven't really studied how the group id works just yet... this is all pondering at this point) I was going to create a new table row in the table "projects" (named gid or something similar to how the actual group id is stored) and grab the users group id and insert that when they submit the form and from there... when the user goes to the project tracking module... it lists based on 1. if the project is active or not & 2. based on their gid.

Does this sound correct or in a correct approach or is there a better way to achieve the same results? Thanks for your input and help... it's been a live saver to me. Are there complications I'm not seeing by using this approach?

Palbin · Posted: Mon Sep 27, 2010 2:11 pm

mefoo wrote:

Palbin-

I need your opinion. I'm getting really close to having a working version of what I set out to achieve, however, I've got one last real major hurdle to cross. It's the group id part.

Here's what I was thinking. (I haven't really studied how the group id works just yet... this is all pondering at this point) I was going to create a new table row in the table "projects" (named gid or something similar to how the actual group id is stored) and grab the users group id and insert that when they submit the form and from there... when the user goes to the project tracking module... it lists based on 1. if the project is active or not & 2. based on their gid.

Does this sound correct or in a correct approach or is there a better way to achieve the same results? Thanks for your input and help... it's been a live saver to me. Are there complications I'm not seeing by using this approach?

Sounds good to me.

mefoo · Regular Joined: Sep 03, 2010 Posts: 57

Thanks for confirming. I really appreciate all your help with this and insights. It's been a real learning experience and I hope to become good enough to contribute back to the RN community.

mefoo · Regular Joined: Sep 03, 2010 Posts: 57

New question.

Back to the checkboxes. These things are just a thorn in my side.

Is it possible to Insert my data based on the checkbox "id" vs. the "name"???

I.e. I have...

Code:

If(isset($_POST['CHECKBOX_NAME'])) {

$task_name = 'Found';
$priority_id = '3';
$status_id = '3';
///// Task Insert ////
$db->sql_query('INSERT INTO ' .$prefix. '_mpstracker_tasks VALUES (NULL, "'. $project_id . '","'. $task_name. '","'.$task_description. '","'.$priority_id. '","'.$status_id. '","'.$task_percent. '","'.$date. '","'.$start_date. '","'.$finish_date .'", "' . $mps_windstorm . '")');
}else{

}

This codes works perfectly fine... but it creates a problem when validating with the position absolute engine. Reason being... the way that they group checkboxes together in order to set a "minimum" required for the group.... is to set all the checkbox names the same.

their code...

Code:

Notice that they use the same "name="data[User3][preferedColor]" for the names of the checkboxes.

Just curious if I'm able to submit the data using the id instead as it's unique. Thanks.

P.s.... I've tried just changing the CHECKBOX_NAME to the id... no dice.

----------------------------------

Nevermind... I think the "_REQUEST" method will work around that. I'll test when I get home. Wink

Guardian2003 · Posted: Fri Oct 01, 2010 3:45 pm

Try not to use $_REQUEST unless you absolutely have to as it is generally considered 'unsafe'

killing-hours · Posted: Fri Oct 01, 2010 8:17 pm

Guardian2003 wrote:

Try not to use $_REQUEST unless you absolutely have to as it is generally considered 'unsafe'

Agreed. Don't know what I was thinking but regardless... that doesn't work.

Now I'm back to my original question... is there a way to use the id to Insert data into the database?

P.s. Sorry for the name change... but since I plan on being around for quite some time... I felt the need to slip into something a bit more comfortable.

Guardian2003 · Posted: Fri Oct 01, 2010 10:54 pm

Maybe use serialise ?
$data=serialize($_POSTdata['User3']); //takes the data from a post operation...
$query=INSERT INTO table VALUES('$data');

killing-hours · Posted: Sat Oct 02, 2010 7:21 am

While I was hunting for an answer to my situation.... I saw that word "serialise" pop up in a few places. But from what I've read this morning... that doesn't seem like it fits the bill.

I guess maybe I wasn't really explaining it right now that I see how others are explaining things.

The way I see to solve this would be to get the value of the checked checkbox and store that in a variable... then use that variable in an "if" statement to trigger the sql INSERT if the value equals one of three values.

i.e.

I have an array of 3 checkboxes.

Code:

echo '<td><input type="checkbox" class="validate[minCheckbox[1]] checkbox" id="chkwind" name="check[]" value="1" /></td>'."\n";
echo '<td><input type="checkbox" class="validate[minCheckbox[1]] checkbox" id="chkframe" name="check[]" value="2" /></td>'."\n";
echo '<td><input type="checkbox" class="validate[minCheckbox[1]] checkbox" id="chkfound" name="check[]" value="3" /></td></tr>'."\n";

On the submit of the form the $_POST will hold all "values" from this array.

Code:

$_POST['check']

From there... I'm assuming there is a way to extract the "values" into separate variables for later use in the script. Not sure... can't find anything specific enough to read up on.

With your example Guardian... it would submit the values of the checkboxes into the database... but that's not my intention. My intention is to use the checkboxes as a way to trigger 3 predefined inserts based on which checkboxes are checked.

The purpose of having them in an array is for validation. (specifically... it's to ensure they choose at least 1 checkbox)

So what i'm gettng at is this...is there some way to extract and store the values of the checkboxes (array) into separate variables within the form action script?

My brain running wild...

Code:

$tempvars = $_POST['check'];

Now $tempvars is holding the array.... but I need to go one step further and split that array into single variables.

Code:

$temp1 = $tempvars['id of checkbox 1'];
$temp2 = $tempvars['id of checkbox 2'];
$temp3 = $tempvars['id of checkbox 3'];

Then I could use those 3 vars to trigger inserts if they meet a condition.

Code:

if($temp1 == 1){

//// DO INSERT

}else{

});

I hope that all made sense... The last part is just me typing what I was thinking and not some attempt at writing working code.

Guardian2003 · Posted: Sat Oct 02, 2010 9:24 am

Untested as I'm pushed for time but from what I understand, using $_POST['check'] since your form is using check[] as a multidimensional array you might use

Code:

foreach ( $_POST['check'] as $k=> $v)
{
if ($v == '1') {
// DO INSERT if $_POST['check'] is 1
}
if($v == '2') {
// DON INSERT if $_POST['check'] is 2
}

killing-hours · Posted: Sat Oct 02, 2010 12:40 pm

Guardian2003 wrote:

Untested as I'm pushed for time but from what I understand, using $_POST['check'] since your form is using check[] as a multidimensional array you might use

Code:

foreach ( $_POST['check'] as $k=> $v)
{
if ($v == '1') {
// DO INSERT if $_POST['check'] is 1
}
if($v == '2') {
// DON INSERT if $_POST['check'] is 2
}

Absolutely beautiful!! Now I know what "foreach" is used for. Thank you so much!!

killing-hours · Posted: Sat Oct 16, 2010 8:31 am

Hello again all-

Just wanted to drop a line...

I launched the modified project tracking module on Monday and so far it seems to be a success. I can't thank y'all enough for the assistance you've provided me. You are hero's to me.

----------------

I have an issue though that I need a lil help on.

I'm trying to "archive" completed projects based on their dates.

(i.e. if the project has been completed for 7 days... don't display.

So as I'm digging through this It seems pretty straight forward... write the sql query in a way that subtracts the seconds = to a week from the current time().

But with that... there is a catch. Since the "new" projects added to the db aren't completed... their finished date will be 0 which won't work with the query like this.

Code:

$today = time();
$archive = $today - 604800;

Code:

$projectresult = $db->sql_query('SELECT project_id FROM ' . $prefix . '_mpstracker_projects WHERE date_finished >' . $archive . 'ORDER BY weight');

any advice?

montego · Posted: Sat Oct 16, 2010 1:15 pm

Unfortunately I cannot tell from your posts (quick glance) what all the fields you have in this table. Do you have some kind of status field which gets updated upon closure? What values are in this field?

killing-hours · Posted: Sat Oct 16, 2010 2:34 pm

Yes.... there is a status_id... but that won't work because it will "instantly" remove the project from the upon completion.

Using 2 as "completed".

Code:

(i.e. $db->sql_query('SELECT project_id, status_id FROM' . $prefix . '_mpstracker_projects WHERE status_id!=2 ORDER BY weight');

I'm trying to give a 7 day grace period to show the completed project prior to just not showing it.

Maybe this?

Upon entering the project... set the finished date (finished date doesn't display to the end user nor the admins... only used when closing out the project) to something like 12/31/2199 (in unix timestamp) so that the value of the finished date will always be higher than what the query is looking for less the project is completed + 7 days? Sounds right to me.... I think.

montego · Posted: Sat Oct 16, 2010 3:05 pm

killing-hours, don't be too quick... Wink

So, the bottom line, in words, you want to return the following:

"Show all projects that are not completed AND show me all the projects that ARE completed but finish date is within the grace period"

Don't forget that the WHERE clause can be a bit more sophisticated than you think. How about this SELECT:

'SELECT project_id, status_id FROM' . $prefix . '_mpstracker WHERE status_id != 2 OR (status_id = 2 AND date_finished <= ' . $archive . ') ORDER BY weight'

Wink

wHiTeHaT · Posted: Sat Oct 16, 2010 3:33 pm

forget all i typed... i missed page 2 Wink

killing-hours · Posted: Sat Oct 16, 2010 4:20 pm

@ montego...

I still don't think that works. I've tried it... but no luck. I can't quite put my finger on it... but that "<=" seems wrong for some reason.

I'm not so much worried about the status of the project because the list shows the project regardless of that. The date is key.

As it stands now... this is the flow of the date.

-------------------------

1. Project enters db / Finished date value = 0

2. Project is completed / Finished date value = (current unix timestamp)

3. Project list shows all
-----------------------------------------------

In order to allow the grace period... it would have to revolve around a ">=".

1. Project enters db / Finished date value = 0

2. Project is completed / Finished date value =- (current unix timestamp)

3. Project list shows (Select) projects who's finished date values = 0 OR finished date value is >= to one week ago

-------------------------

Reason being... the "finished date" timestamp will continually be less than archive because that archives value is moving upward.

Sorry about the rambling...I think that was more for me than you. All I can see are different lines of code running through my head right now and it's tying a knot. Wink

-----------------------------------------------------------------

@whitehat...

I'm long past that... I've already got the module created how I want it and it works fine... I'm polishing it off now.

wHiTeHaT · Posted: Sat Oct 16, 2010 4:31 pm

Code:

$projectresult = $db->sql_query('SELECT project_id FROM ' . $prefix . '_mpstracker_projects WHERE date_finished >' . $archive . ' AND date_finished !=0 ORDER BY weight');

wHiTeHaT · Posted: Sat Oct 16, 2010 4:41 pm

but u use unixtime , i suggest to not use it and use the getdate function.

Only registered users can see links on this board!
Get registered or login to the forums!

it will provide you with all you need to execute your wanted mysql query.

EDIT: assuming you run php 5.X

montego · Posted: Sat Oct 16, 2010 4:50 pm

wHiTeHaT, using "AND date_finished !=0" won't work either as he wants everything without a date_finished to be presented. However, your code gives me a different idea on how to make this work (I hope):

Code:

$projectresult = $db->sql_query('SELECT project_id FROM ' . $prefix . '_mpstracker_projects WHERE date_finished >' . $archive . ' OR date_finished =0 ORDER BY weight');

Just don't change how date_finished is being set.

montego · Posted: Sat Oct 16, 2010 4:53 pm

wHiTeHaT wrote:

but u use unixtime , i suggest to not use it and use the getdate function.

I'm more a fan of using a true Unix time-stamp as I've found it much easier to ensure I can handle time-zone presentation better (and I would force it to save as GMT this way you can move the site to any server in the world and should be able to render different time-zones properly. It also makes date math straightforward. JMO.

wHiTeHaT · Posted: Sat Oct 16, 2010 4:55 pm

@montego , you probaly right... but in the past year when installed php5 , i came aware of the new date stuff for php.
I can remember (just not exactly), you could also when used an updated version of mysql (what came along appserv) , use improved sql query's to control and manipulate the dates verry efficient.even bether and witouth wissles and bells.

killing-hours · Posted: Sat Oct 16, 2010 5:59 pm

Gah... I was thinking that was it... but alas... no luck.

Can get the projects with just 0 to work fine.. it's only when I add the $archive part that it breaks.

Here's what I've got:

Code:

$today = time();
$archive = $today - 604800;

$projectresult = $db->sql_query('SELECT project_id, date_finished FROM ' . $prefix . '_mpstracker_projects WHERE date_finished= 0 OR date_finished >'. $archive . ' AND mps_active= 1 AND mpsgid=\'' . $mps_gid['gid'] . '\'ORDER BY weight');

I've tried adding ' ' around the "604800" in the $archive, I've tried writing the query like this...
date_finished= 0 (OR date_finished>' . $archive . ') etc, Have tried without the ( )... not working.

I have two test projects in the db... one with a finished date of "1287234722" (Sat, 16 Oct 2010 13:12:02 GMT) & and one with a date_finished of 0 ... which "should" show both projects given the query... but it shows neither projects once the date_finished> $archive part is added.

Maybe a syntax issue somewhere? Everything else seems logically right.

--------------------
Edit**

changed the archive to read like this

Code:

$archive = time() - 604800;

and echoed it out to ensure it was actually doing the math... it is. So the problem must be in the query syntax somewhere.

--------------------

Edit 2***

Got it... Wink

"Conditionals".

Code:

$archive = time() - 604800;

$projectresult = $db->sql_query('SELECT project_id FROM ' . $prefix . '_mpstracker_projects WHERE mps_active= 1 AND mpsgid=\'' . $mps_gid['gid'] . '\'AND date_finished>\'' . $archive . '\' || mps_active= 1 AND mpsgid=\'' . $mps_gid['gid'] . '\' AND date_finished= 0 ORDER BY weight');

Thanks for your time guys. I really appreciate it.