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!

Thanks; everything's running perfectly now!

Yes, the code you posted (straight copy / paste in a php file)...

Unfortunately, it returns: Any ideas? ??? Thanks for your patience!

Many thanks for these explanations! To make things clearer, here's a screenshot of the phpbb_fsports_config table in phpmyadmin: I need to get the value that's next to "default_league" (which is "9"). What should the SQL query look like? P.S.: I couldn't tell you what sql_query() should return; I got that code from one of phpBB's file (it's a trial and error process, but I'm learning!)

Thanks for your reply. Since I'm totally new at this, I've been trying to create a functionnal query by looking at existing lines in php files, but I didn't succeed... Could you simply tell me how I could get the value in the config_value column that is corresponding to the default_league in the config_name column? Not sure if this is clear... Let me know if it ain't; I'll included a screenshot from phpmyadmin! Regards, sbourdon

Hello, I'm new at this and I'm trying to insert the default_league value in the users' table when a new account is being created. I've added this code to get its value from the DB, but it doesn't work: $sql = "SELECT config_value FROM " . FSPORTS_TABLE . " WHERE config_name = default_league"; if (!$result = $db->sql_query($sql)) { message_die(GENERAL_ERROR,'Error getting fsports defaults','',__LINE__,__FILE__,$sql); } $default_league = $row['default_league']; Do you see what's wrong in there? I'm totally lost... ??? Thanks a lot for your help and support!

I've noticed that Options from Poll1 appear in Poll2, and Options from Poll2 appear in Poll3 and so on... Example: [ Poll ] Poll 1 Question - Poll1 Option A - Poll1 Option B [ Poll ] Poll2 Question - Poll1 Option A - Poll1 Option B - Poll2 Option A - Poll2 Option B Any ideas? Thanks!

Hello, I would like to include poll details in the emails sent by phpBB Digests. FYI, this phpBB MOD grabs new postings and their content since your last visit to the Forum, and include them in an email. Therefore, I need to grad poll questions and options from the database... I thought I had it all worked out, but the code goes through the loop too many times. Sometimes, options will repeat two or three times; it looks like it all depends on the number of posts available in the Forum! Here's the code I've created: $vote_text = ''; if ($row2['topic_vote'] != 0 && ($row2['topic_first_post_id'] == $row2['post_id'])) { $sql3 = "SELECT vd.vote_text, vr.vote_option_text FROM " . VOTE_DESC_TABLE . " vd, " . VOTE_RESULTS_TABLE . " vr WHERE vd.topic_id = $row2[topic_id] AND vr.vote_id = vd.vote_id GROUP BY vd.vote_text, vr.vote_option_text ORDER BY vr.vote_option_id ASC"; if ( !($result3 = $db->sql_query($sql3))) { message_die(GENERAL_ERROR, 'Could not obtain vote data for this topic', '', __LINE__, __FILE__, $sql); } while ($row3 = $db->sql_fetchrow($result3)) { if ($html) { $vote_text = $row3['vote_text'] . '<br />'; $vote_option .= '- ' . $row3['vote_option_text'] . '<br />'; } else { $vote_text = $row3['vote_text'] . "\n"; $vote_option .= '- ' . $row3['vote_option_text'] . "\n"; } } } And here's how it appears in the emails: [ Poll ] Question - Option 1 - Option 2 - Option 1 - Option 2 Options 1 and 2 should only appear once! :-\ Does anyone see what's wrong or missing here? Thanks a lot for your help and support!

Got it! In bbcode.php: replaced // [img] $text = preg_replace("#\[img\]((http|ftp|https|ftps)://)([^ \?&=\#\"\n\r\t<]*?(\.(jpg|jpeg|gif|png)))\[/img\]#sie", "'[img:$uid]\\1' . str_replace(' ', '%20', '\\3') . '[/img:$uid]'", $text); with // [img=image_url_here] code.. if ((strstr($text, 'servername')) == FALSE) { $text = preg_replace("#\[img\]((http|ftp|https|ftps)://)([^ \?&=\#\"\n\r\t<]*?(\.(jpg|jpeg|gif|png)))\[/img\]#sie", "'SORRY - LINKED EXTERNAL IMAGE REMOVED'", $text); } else { $text = preg_replace("#\[img\]((http|ftp|https|ftps)://)([^ \?&=\#\"\n\r\t<]*?(\.(jpg|jpeg|gif|png)))\[/img\]#sie", "'[img:$uid]\\1' . str_replace(' ', '%20', '\\3') . '[/img:$uid]'", $text); } Simply replace 'servername' with your... server name! Thanks, Minc!

Hello, I want to modify the bbcode tag so that only images available on the local server are allowed. I've installed a little modification to upload images and therefore, I want to make sure that users do not try to bypass this system and include links to external images in their posts using the tags... What I would need to do is the following: 1. check if the bbcode is used in the message ($message); 2. check if the server address appears between the tags ($board_config['server_name']); 3. if YES, execute code; if NO, display an error message. Unfortunately, I'm new to this... Would someone be kind enough to help me with this? Thank you very much!

Got it! Simply had to change body to post_text! Newbie mistake... Thanks to all for your help!

Thanks a LOT! We're almost there! Only 2 problems remain: 1. ALL images get deleted when this script is executed; 2. The following error message shows up each time the script is executed: I've inserted the code in the page_header.php file, and here's what it looks like as of now: // Delete old IMG $files = ""; $dir = "images/thumbs/"; //no trailing slash $path = opendir($dir); while (false !== ($file = readdir($path))) { if($file!="." && $file!="..") { if(is_file($dir."/".$file)) $files[]=$file; else $dirs[]=$dir."/".$file; } } if($files) { //Output information natcasesort($files); $x = 0; foreach ($files as $file){ //This is where you want to run your query $sql = "SELECT * FROM " . POSTS_TEXT_TABLE . " WHERE body LIKE \"%$file%\""; $result = mysql_query($sql); $count = mysql_num_rows($result); if($count == 0){ unlink($dir.$file); } } } closedir($path); // Delete old IMG I had to include a trailing slash in the code, even though it said not to... Any ideas on how to solve these remaining issues? Regards, sbourdon

Thanks a lot for these quick answers! Unfortunately, I'm a complete newbie at this so I'm gonna need a little bit more details... How do I count the number of rows in the query? And how do I move on to the next image? Sorry if this seems ridiculous but we all have to start somewhere...

Hello everyone! Not being too comfortable with SQL queries, I would like your help to accomplish the following... SQL Query must do the following: 1. Look for pictures in the /images/IMG directory 2. Look for [img:xxxxxxxxxx][yyy][/img:xxxxxxxxxx] tags in the phpbb_posts_text, phpbb_privmsgs_text, phpbb_users and phpbb_shout tables 3. Compare the 2 and delete pics from the IMG directory that no longer appear in those tables Example: A post has been created using the following image: [img:3e25fd8174][images/IMG/sbourdon_logo_phpBB.gif][/img:3e25fd8174] In the IMG directory, the image sbourdon_logo_phpBB.gif has been copied. If the post is ever edited or deleted and that picture is no longer being used, I want it to be removed from the IMG directory. What should the SQL query look like? Thanks!

Thanks! I'm new at this so I guess I'll have to read some more... If you or anyone else has other hints and tips, don't hesitate to share them!

Hello, I've just installed a modification on my board to resize uploaded pics and the only problem I have is that GIF images lose their transparency... How can I fix this? Here's part of the code where images get resized: // iMinimize_MOD - start function getsize($url) { global $userdata; // Maximum width and height for inpost images $iWidth = 640; $iHeight = 640; // JPEG Quality $quality = 80; $base = basename($url); $base_ext = substr($base, 0, -4); $id = $userdata['username'] . "_" . $base_ext; // broken_link $broken_link = $phpbb_root_path . "images/broken_link.gif"; if(@!$size = getimagesize($url)) { return $broken_link; } $img_size = getimagesize($url); $real_width = $img_size[0]; $real_height = $img_size[1]; // Find the height or width if 0 is the value. if($iHeight == 0) { $percent = $size[1] / $size[0] * 100; $iHeight = round($iWidth / 100 * $percent, 0); } if($iWidth == 0) { $percent = $size[0] / $size[1] * 100; $iWidth = round($iHeight / 100 * $percent, 0); } // Look up the extension of the image and use the right functions. $cut_url = strlen($url) - 3; $ext = substr($url, $cut_url); // .jpg if($ext == "jpg") { $im = imagecreatefromjpeg($url); $ow = imagesx( $im ); $oh = imagesy( $im ); if($real_width > $iWidth OR $real_height > $iHeight) { $wscale = $iWidth / $ow; $hscale = $iHeight / $oh; } else { $wscale = 1; $hscale = 1; } $scale = ( $hscale < $wscale ? $hscale : $wscale ); $nw = round( $ow * $scale, 0 ); $nh = round( $oh * $scale, 0 ); $dstim = imagecreatetruecolor( $nw, $nh ); imagecopyresampled( $dstim, $im, 0, 0, 0, 0, $nw ,$nh ,$ow ,$oh ); imagejpeg( $dstim, $phpbb_root_path . "images/IMG/" . $id . ".jpg", $quality); imagedestroy( $dstim ); $thumb = $id . ".jpg"; // .gif } elseif($ext == "gif") { $im = imagecreatefromgif($url); $ow = imagesx( $im ); $oh = imagesy( $im ); if($real_width > $iWidth OR $real_height > $iHeight) { $wscale = $iWidth / $ow; $hscale = $iHeight / $oh; } else { $wscale = 1; $hscale = 1; } $scale = ( $hscale < $wscale ? $hscale : $wscale ); $nw = round( $ow * $scale, 0 ); $nh = round( $oh * $scale, 0 ); $dstim = imagecreatetruecolor( $nw, $nh ); imagecopyresampled( $dstim, $im, 0, 0, 0, 0, $nw ,$nh ,$ow ,$oh ); imagegif( $dstim, $phpbb_root_path . "images/IMG/" . $id . ".gif"); imagedestroy( $dstim ); $thumb = $id . ".gif"; // .png } elseif($ext == "png") { $im = imagecreatefrompng($url); $ow = imagesx( $im ); $oh = imagesy( $im ); if($real_width > $iWidth OR $real_height > $iHeight) { $wscale = $iWidth / $ow; $hscale = $iHeight / $oh; } else { $wscale = 1; $hscale = 1; } $scale = ( $hscale < $wscale ? $hscale : $wscale ); $nw = round( $ow * $scale, 0 ); $nh = round( $oh * $scale, 0 ); $dstim = imagecreatetruecolor( $nw, $nh ); imagecopyresampled( $dstim, $im, 0, 0, 0, 0, $nw ,$nh ,$ow ,$oh ); imagepng( $dstim, $phpbb_root_path . "images/IMG/" . $id . ".png"); imagedestroy( $dstim ); $thumb = $id . ".png"; // broken_link } else { return $broken_link; } $iUrl = "[" . $phpbb_root_path . "images/IMG/" . $thumb . "]" . $url; return $iUrl; } // iMinimize_MOD - end Would someone know how to keep transparency for resized GIF images? Thanks a lot!