sbourdon

Thank you, I'll look into this!

Thanks for the info; I didn't know about that! So I guess we're back at post #4! Anything wrong with that query? Thank you!

By the way, this query is being executed inside a foreach ($points as $point) { (...) } where p.userId = " . $point['userId'] . " Therefore, since we already have "p.userId", what should the query look like to get this user's missing predictions? I can get the number of missing predictions by doing the following: $missingPreds = $totalGames - $totalPreds; but I guess I could achieve the same thing in one query, right? Again, thank you for your time!

Hello and thank you for your support! Regarding my first question (which is to combine my two queries into one), here's what the new query looks like: $query = "SELECT p.userid , COUNT(*) as predictions , totgames - COUNT(*) as missing , totgames FROM " . SPORTS_BOL_PredictionDao::getInstance()->getTableName() . " p CROSS JOIN ( SELECT COUNT(*) as totgames FROM " . SPORTS_BOL_GameDao::getInstance()->getTableName() . " g ) tot GROUP BY p.userid ORDER BY g.gametime DESC"; $missingPreds = " " . $this->dbo->queryForColumn($query) . ")"; Unfortunately, this returns an error 500! Is there something I haven't done correctly here? Thank you!

Hello, I am doing my best to learn mysql but I still have a lot to learn when queries involve loops or complex syntax. Here's what I would like to achieve: Table 1 includes games played since the beginning of the season (where homeTeamScore or awayTeamScore > 0): Table 2 includes predictions made by users as to which team will win each game (gameId in T2 = id in T1): The following query lets me know how many games have been played since the beginning of the season: $query = "SELECT COUNT(`id`) FROM " . SPORTS_BOL_GameDao::getInstance()->getTableName() . " WHERE SeasonID = " . $seasonId . " AND (homeTeamScore > 0 OR awayTeamScore > 0) ORDER BY gametime DESC"; $totalGames = $this->dbo->queryForColumn($query); And this one how many predictions each user has registered: $query = "SELECT COUNT(`g`.`id`) FROM " . SPORTS_BOL_GameDao::getInstance()->getTableName() . " g INNER JOIN " . SPORTS_BOL_PredictionDao::getInstance()->getTableName() . " p ON g.id = p.gameId WHERE g.SeasonID = " . $seasonId . " AND (g.homeTeamScore > 0 OR g.awayTeamScore > 0) AND p.userId = " . $point['userId'] . " ORDER BY g.gametime DESC"; $totalPreds = $this->dbo->queryForColumn($query); First question: how could I combine these two queries into one and get the total number of missing predictions for each user? As an example, if 89 games have been played and user has registered 70 predictions, I would like to get "19" out of the query. Second question, more complex (at least to me!): what would the query look like if I wanted to know how many consecutive games has each user missed since their last prediction? As an example, if my last prediction was registered on gameId "460" and 4 more games have been played since then (id 468, 469, 470 and 471), I would like to get "4" out of the query. Hope this makes sense and is easy to understand! Thank you very much for your support!

Many, many thanks! I always learn a lot when I come around here! Best regards, Sylvain

Excellent, thanks! Here's the template file: <!-- BEGIN switch_cal_rem --> <form name="cal_reminder" method="post" action="{ACTION}"> <table width="100%" cellspacing="2" border="0" align="center" cellpadding="2"> <tr> <td align="left"> </td> <td align="right" valign="middle" nowrap="nowrap"><span class="gensmall">{L_CAL_REM_HEADER}<br /><br /> <!-- BEGIN forums --> <input type="hidden" name="create_new" value="{switch_cal_rem.forums.CAL_REM_CREATE_NEW_VALUE}" /> {switch_cal_rem.forums.FORUM_NAME}: <input type="radio" name="cal_rem_{switch_cal_rem.forums.FORUM_ID}" id="calremnone" value="NONE" {switch_cal_rem.forums.CAL_REM_NONE_CHECKED} /><label for="calremnone">{L_NONE}</label> <input type="radio" name="cal_rem_{switch_cal_rem.forums.FORUM_ID}" id="calrem24" value="1" {switch_cal_rem.forums.CAL_REM_24_CHECKED} /><label for="calrem24">{L_24}</label> <input type="radio" name="cal_rem_{switch_cal_rem.forums.FORUM_ID}" id="calrem48" value="0" {switch_cal_rem.forums.CAL_REM_48_CHECKED} /><label for="calrem48">{L_48}</label><br /> <!-- END forums --> <br /><input type="submit" class="liteoption" value="{L_SUBMIT}" name="submit" /></span></td> </tr> </table> </form> <!-- END switch_cal_rem --> <!-- BEGIN switch_cal_rem_suspended --> <table width="100%" cellspacing="2" border="0" align="center" cellpadding="2"> <tr> <td align="left"> </td> <td align="right" valign="middle" nowrap="nowrap"><span class="gensmall">{L_CAL_REM_SUSPENDED}<br /> </tr> </table> <!-- END switch_cal_rem_suspended --> As for the returned values, here they are: create_new - 0 cal_rem_8 - 0 cal_rem_9 - 0 cal_rem_5 - 1 submit - Envoyer Again, many thanks for your help! This is highly appreciated!

Thank you very much for your quick response! I guess it would be easier to understand if you had access to the complete code... So here's the code that does correctly retrieve actual subscription values from the database: // Calendar Reminders // get auth read if ( $userdata['session_logged_in'] ) { $is_auth = auth(AUTH_ALL, AUTH_LIST_ALL, $userdata); $forum_ids = array(); while ( list($forum_id, $auth) = each($is_auth) ) { if ($auth['auth_read'] && $auth['auth_view']) { $forum_ids[] = $forum_id; } } // list of forums authed $s_forum_list = implode(', ', $forum_ids); $sql = "SELECT f.forum_id, f.forum_name FROM " . FORUMS_TABLE . " AS f, " . CATEGORIES_TABLE . " AS c WHERE c.cat_id = f.cat_id AND f.forum_id IN ($s_forum_list) AND f.forum_remind = 1 ORDER BY cat_order, cat_title, forum_order, forum_name"; if ( !$result = $db->sql_query($sql) ) { message_die(GENERAL_ERROR, 'Could not obtain forums/categories informations', '', __LINE__, __FILE__, $sql); } $found_forums = mysql_num_rows( $result ); if ( $found_forums != 0 ) { $sql1 = 'SELECT user_popup_pm FROM ' . USERS_TABLE . ' WHERE user_id = ' . $userdata['user_id']; if ( !($result1 = $db->sql_query($sql1))) { message_die(GENERAL_ERROR, 'Could not get reminder status from ' . USERS_TABLE . ' table', '', __LINE__, __FILE__, $sql1); } $row1 = $db->sql_fetchrow($result1); $cal_reminder_suspended = (trim($row1['user_popup_pm']) == 0) ? true : false; if ( $cal_reminder_suspended != true ) { $template->assign_block_vars('switch_cal_rem', array()); $forum_rem_name = array(); $forum_rem_id = array(); $found_status = array(); $create_new = array(); $remind_status = array(); $i=0; while ($row = $db->sql_fetchrow($result)) { $forum_rem_name [$i] = $row['forum_name']; $forum_rem_id [$i] = $row['forum_id']; $sql2 = "SELECT remind_status FROM " . FORUMS_REMIND_TABLE . " WHERE forum_id = " . $forum_rem_id [$i] . " AND user_id = " . $userdata['user_id']; if ( !($result2 = $db->sql_query($sql2)) ) { message_die(GENERAL_ERROR, "Couldn't obtain forum reminding information", "", __LINE__, __FILE__, $sql2); } $row2 = $db->sql_fetchrow($result2); $found_status [$i] = mysql_num_rows( $result2 ); $create_new [$i] = ($found_status [$i] == 0) ? true: false; if( $create_new [$i] != true ) { $remind_status [$i] = intval($row2['remind_status']); } else { $remind_status [$i] = -1; } $template->assign_block_vars('switch_cal_rem.forums', array( 'FORUM_NAME' => $forum_rem_name [$i], 'FORUM_ID' => $forum_rem_id [$i], 'CAL_REM_CREATE_NEW_VALUE' => ($create_new [$i]) ? '1' : '0', 'CAL_REM_NONE_CHECKED' => ($remind_status [$i] == -1) ? 'checked="checked"' : '', 'CAL_REM_24_CHECKED' => ($remind_status [$i] == 1) ? 'checked="checked"' : '', 'CAL_REM_48_CHECKED' => ($remind_status [$i] == 0) ? 'checked="checked"' : '')); $i++; } } else { $template->assign_block_vars('switch_cal_rem_suspended', array()); } } } This results in the image posted in my previous post. Now, all I want is to save the submitted values for each Forum. Therefore, when you write: ..."it would appear that you want to loop around the code you posted for all the possible values of $forum_rem_id", you are totally right! For example, if I select 24 hours (instead of the actual 48 hours) for the first forum, and 48 for the following 2 forums, I want that to be saved in the database. The values posted by $HTTP_POST_VARS are as follow: cal_rem_8 = 1 (which refers to Forum ID 8 -- the first forum, with a value of 1 -- which corresponds to 48 hours) cal_rem_9 (second forum, ID 9), value of 0 (24 hours) and so on... A value of NONE means "no subscription". Is this any more helpful? Best regards, Sylvain

Hello, On my phpBB 2 board, I have the Topic Calendar MOD installed (http://rpgnet.clanmckeen.com/demo/index.php?f=29). I've also added the Remind Topic MOD, so that reminders can be send out to users 24 or 48 hours in advance (http://www.chathamkiwanis.com/BB/dload.php?action=file&file_id=14) I've created a simple form below the Calendar where users can select, for every available Forum, if they want: No reminder, a reminder 24 hours in advance, or a reminder 48 hours in advance. Here's a screenshot: Currently, the form is able to correctly retrieve actual values from the database, for all forums. The only problem I'm having is when saving the values submitted by the user. I simply don't understand how to loop through each $HTTP_POST_VARS values (the actual code only saves the values entered for the first forum)... I know the code works for every possible situation (delete, insert or update the database) because I've tried every option with the first forum. Now how could I save the values enterred for each forum? I guess a glimpse at the actual code could help! // Form submitted if ($HTTP_POST_VARS['cal_rem_' . $forum_rem_id [$i] . ''] == 'NONE') { $sql_priority = (SQL_LAYER == "mysql") ? "LOW_PRIORITY" : ""; $sql = "DELETE $sql_priority FROM " . FORUMS_REMIND_TABLE . " WHERE forum_id = " . $forum_rem_id [$i] . " AND user_id = " . $userdata['user_id']; if( !$result = $db->sql_query($sql) ) { message_die(GENERAL_ERROR, "Couldn't delete forum reminding information", "", __LINE__, __FILE__, $sql); } $message = $lang['reminder_updated'] . '<br /><br />' . sprintf($lang['Click_return_calendar'], '<a href="' . append_sid("calendar.$phpEx") . '">', '</a>') . '<br /><br />' . sprintf($lang['Click_return_index'], '<a href="' . append_sid("index.$phpEx") . '">', '</a>'); message_die(GENERAL_MESSAGE, $message); } else if ((intval($HTTP_POST_VARS['cal_rem_' . $forum_rem_id [$i] . ''] == '0')) || (intval($HTTP_POST_VARS['cal_rem_' . $forum_rem_id [$i] . ''] == '1'))) { if ($create_new [$i] == true) { $now = mktime( date('H'), 0, 0, date("m") , date("d"), date("Y") ); $now = mktime( 0,0,1, date("m", $now) , date("d", $now)-1, date("Y", $now) ); $sql_priority = (SQL_LAYER == "mysql") ? "LOW_PRIORITY" : ""; $sql = "INSERT $sql_priority INTO " . FORUMS_REMIND_TABLE . " (user_id, forum_id, remind_status, remind_calendar) VALUES (" . $userdata['user_id'] . ", " . $forum_rem_id [$i] . ", " . intval($HTTP_POST_VARS['cal_rem_' . $forum_rem_id [$i] . '']) . ", $now )"; if( !$result = $db->sql_query($sql) ) { message_die(GENERAL_ERROR, "Couldn't insert forum reminding information", "", __LINE__, __FILE__, $sql); } $message = $lang['reminder_updated'] . '<br /><br />' . sprintf($lang['Click_return_calendar'], '<a href="' . append_sid("calendar.$phpEx") . '">', '</a>') . '<br /><br />' . sprintf($lang['Click_return_index'], '<a href="' . append_sid("index.$phpEx") . '">', '</a>'); message_die(GENERAL_MESSAGE, $message); } else { $now = mktime( date('H'), 0, 0, date("m") , date("d"), date("Y") ); $now = mktime( 0,0,1, date("m", $now) , date("d", $now)-1, date("Y", $now) ); $sql_priority = (SQL_LAYER == "mysql") ? "LOW_PRIORITY" : ""; $sql = "UPDATE $sql_priority " . FORUMS_REMIND_TABLE . " SET remind_status = ".intval($HTTP_POST_VARS['cal_rem_' . $forum_rem_id [$i] . '']).", remind_calendar = ".$now." WHERE forum_id = " . $forum_rem_id [$i] . " AND user_id = " . $userdata['user_id']; if( !$result = $db->sql_query($sql) ) { message_die(GENERAL_ERROR, "Couldn't update forum reminding information", "", __LINE__, __FILE__, $sql); } $message = $lang['reminder_updated'] . '<br /><br />' . sprintf($lang['Click_return_calendar'], '<a href="' . append_sid("calendar.$phpEx") . '">', '</a>') . '<br /><br />' . sprintf($lang['Click_return_index'], '<a href="' . append_sid("index.$phpEx") . '">', '</a>'); message_die(GENERAL_MESSAGE, $message); } } I'm a total beginner when it comes to loops so don't hesitate to give as much details as possible! Thank you very much for any help you could provide me with!