phpBB2Refugees.com Logo
Not affiliated with or endorsed by the phpBB Group

Register •  Login 

Continue the legacy...

Welcome to all phpBB2 Refugees!Wave Smilie

This site is intended to continue support for the legacy 2.x line of the phpBB2 bulletin board package. If you are a fan of phpBB2, please, by all means register, post, and help us out by offering your suggestions. We are primarily a community and support network. Our secondary goal is to provide a phpBB2 MOD Author and Styles area.

help updating sql via template form


 
Search this topic... | Search MOD Writing... | Search Box
Register or Login to Post    Index » MOD Writing  Previous TopicPrint TopicNext Topic
Author Message
guyb
Board Member



Joined: 30 Sep 2009

Posts: 11



PostPosted: Wed Sep 30, 2009 6:29 am 
Post subject: help updating sql via template form

Hi, I've been modding the "phpbb-auction" mod for some time now and managed to accomplish most of what I wanted. Unfortunately the mod had a lot of code problems and support on their site is almost non-existent... so I'm posting here - actually more for a conceptual rather than a "mod" issue:

Where I'm having a problem is here: there is an admin panel called "admin_auction_offer.php" which in its original form shows the main details of all listed auction items, and I want to change it into an interactive control panel whereby as the admin I can edit and update those fields.

I've managed to convert the .php and .tpl files so the page indeed is a form, and I also created a "mode" for editing/updating the fields which appears as a text link - if I click on it on the form page, it's supposed to UPDATE into sql all the displayed fields (and so record any changes I may have made to the displayed data).

The form fields when initially displayed are populated with the present auction data, but I'm unable to edit/update them: I receive sql errors that certain fields can't be passed (probably because of whether they are INT or VAR and I'm unsure how to pass those strings).

I also have a suspicion that the structure of the code in the .php file may be incorrect: if I want to display data and from that data make edits which I send back to sql, do I only have to UPDATE the sql table one time and only populate the template one time?

Currently this is the essence of the .php file:

In the original "admin_auction_offer.php" file, after this code:
Code:
// START Grab all the offer-data
     $sql = "SELECT t.*,
                    u.username,
                    u.user_id,
                    u2.username as maxbidder_user_name,
                    u2.user_id as maxbidder_user_id,
                    i.pic_id,
                    acc.auction_account_auction_amount,
                    acc.auction_account_amount_paid
             FROM (" . AUCTION_OFFER_TABLE . " t
             LEFT JOIN " . USERS_TABLE . " u ON u.user_id = t.FK_auction_offer_user_id
             LEFT JOIN " . USERS_TABLE . " u2 ON u2.user_id = t.FK_auction_offer_last_bid_user_id
             LEFT JOIN " . AUCTION_IMAGE_TABLE . " i ON t.pk_auction_offer_id=i.pic_auction_id
             LEFT JOIN " . AUCTION_ACCOUNT_TABLE . " acc ON t.pk_auction_offer_id=acc.fk_auction_offer_id)
             ORDER BY t.auction_offer_time_stop;";   


I created:
Code:
if ( $mode == "admin_offer_edit" )
    {
    $total_offers = 0;
     while( $row = $db->sql_fetchrow($result) )
     {
         $offer_rowset[] = $row;
         $total_offers++;
     } // while
     $db->sql_freeresult($result);
     $offer_id = ( isset($HTTP_GET_VARS[POST_AUCTION_OFFER_URL]) ) ? $HTTP_GET_VARS[POST_AUCTION_OFFER_URL] : $HTTP_POST_VARS[POST_AUCTION_OFFER_URL];


and then tried creating these conditions (if a field received data from the form, that would be the variable's value, otherwise the default would be whatever was originally entered, i.e. probably nothing):

Code:
if ( empty($HTTP_POST_VARS['auction_offer_title']))
                      {
                     $auction_offer_title = $offer_rowset[$i]['auction_offer_title'];
                      }
else
                      {
                           $auction_offer_title = $HTTP_POST_VARS['auction_offer_title'];
                      }
                 
if ( empty($HTTP_POST_VARS['auction_offer_text']))
                      {
                       $auction_offer_text = $offer_rowset[$i]['auction_offer_text'];
                      }
else
                      {
                           $auction_offer_text = $HTTP_POST_VARS['auction_offer_text'];
                      }

if ( empty($HTTP_POST_VARS['auction_offer_admins_uncensored_text']))
                      {
                       $auction_offer_admins_uncensored_text = $offer_rowset[$i]['auction_offer_admins_uncensored_text'];
                      }
else
                      {
                           $auction_offer_admins_uncensored_text = $HTTP_POST_VARS['auction_offer_admins_uncensored_text'];
                      }

if ( empty($HTTP_POST_VARS['auction_offer_comment']))
                      {
                       $auction_offer_comment = $offer_rowset[$i]['auction_offer_comment'];
                      }
else
                      {
                           $auction_offer_comment = $HTTP_POST_VARS['auction_offer_comment'];
                      }
                 
if ( empty($HTTP_POST_VARS['auction_offer_price_start']))
                      {
                       $auction_offer_price_start = $offer_rowset[$i]['auction_offer_price_start'];
                      }
else
                      {
                           $auction_offer_price_start = $HTTP_POST_VARS['auction_offer_price_start'];
                      }
                 
if ( empty($HTTP_POST_VARS['auction_offer_reserve_factor']))
                      {
                       $auction_offer_reserve_factor = $offer_rowset[$i]['auction_offer_reserve_factor'];
                      }
else
                      {
                           $auction_offer_reserve_factor = $HTTP_POST_VARS['auction_offer_reserve_factor'];
                      }
                 
if ( empty($HTTP_POST_VARS['auction_offer_direct_sell_price']))
                      {
                       $auction_offer_direct_sell_price = $offer_rowset[$i]['auction_offer_direct_sell_price'];
                      }
else
                      {
                           $auction_offer_direct_sell_price = $HTTP_POST_VARS['auction_offer_direct_sell_price'];
                      }                                                   
                     
if ( empty($HTTP_POST_VARS['auction_offer_shipping_price']))
                      {
                       $auction_offer_shipping_price = $offer_rowset[$i]['auction_offer_shipping_price'];
                      }
else
                      {
                           $auction_offer_shipping_price = $HTTP_POST_VARS['auction_offer_shipping_price'];
                      }
                 
if (empty($HTTP_POST_VARS['offer_special']) )
                      {
                           $auction_offer_special = 0;
                      }
else
                      {
                           $auction_offer_special = 1;
                      }                 
                 
if (empty($HTTP_POST_VARS['offer_on_top']) )
                      {
                           $auction_offer_on_top = 0;
                      }
else
                      {
                           $auction_offer_on_top = 1;
                      }
                 
if (empty($HTTP_POST_VARS['offer_bold']) )
                      {
                           $auction_offer_bold = 0;
                      }
else
                      {
                           $auction_offer_bold = 1;


I'm then trying to update the sql table like this:

Code:
$sql = "UPDATE " . AUCTION_OFFER_TABLE . "
                        SET auction_offer_title = '" . $auction_offer_title . "',
                     auction_offer_text = '" . $auction_offer_text . "',
                            auction_offer_admins_uncensored_text = '" . $auction_offer_admins_uncensored_text . "',
                     auction_offer_comment = '" . $auction_offer_comment . "',
                            auction_offer_price_start = " . $auction_offer_price_start . ",
                      auction_offer_reserve_factor = " . $auction_offer_reserve_factor . ",
                            auction_offer_direct_sell_price = " . $auction_offer_direct_sell_price . ",
                          auction_offer_shipping_price = " . $auction_offer_shipping_price . ",
                     auction_offer_special = " . $auction_offer_special . ",
                            auction_offer_on_top = " . $auction_offer_on_top . ",
                            auction_offer_bold = " . $auction_offer_bold . "
                  WHERE PK_auction_offer_id = " . $offer_id . "";                 


afterwards there are some IF statements regarding the sorting of the fields - original coding from this file, and after that is my tinkered command to populate the template:

Code:
$template->assign_block_vars('offer', array(
                     'L_AUCTION_OFFER_MARK_PAID' => $mark,
                     'U_AUCTION_OFFER_MARK_PAID' => $u_mark,

                     'AUCTION_OFFER_TITLE' => $offer_rowset[$i]['auction_offer_title'],
                     'AUCTION_OFFER_ID' => $offer_rowset[$i]['PK_auction_offer_id'],
                     'AUCTION_OFFER_OFFERER' => $offer_rowset[$i]['username'],
                     'AUCTION_OFFER_VIEWS' => $offer_rowset[$i]['auction_offer_views'],
                     'AUCTION_OFFER_PRICE_START' => $offer_rowset[$i]['auction_offer_price_start'],
                     'AUCTION_OFFER_RESERVE_FACTOR' => $offer_rowset[$i]['auction_offer_reserve_factor'],
                     'AUCTION_OFFER_DIRECT_SELL_PRICE' => $offer_rowset[$i]['auction_offer_direct_sell_price'],
                     'AUCTION_OFFER_SHIPPING_PRICE' => $offer_rowset[$i]['auction_offer_shipping_price'],
//               'S_ADD_EDIT_SHIPPING_PRICE' => append_sid("../auction_offer.$phpEx?mode=edit_shipping_price&" . POST_AUCTION_OFFER_URL . "=" . $offer_rowset[$i]['PK_auction_offer_id'] . ""),
// REMOVED BECAUSE ADDED MY CASE INTO THIS FILE FROM AUCTION-OFFER.php              'S_ADMIN_OFFER_EDIT' => append_sid("../auction_offer.$phpEx?mode=admin_offer_edit&" . POST_AUCTION_OFFER_URL . "=" . $offer_rowset[$i]['PK_auction_offer_id'] . ""),
// LATEST VER of BUTTON               'S_ADMIN_OFFER_EDIT' => append_sid("../auction_offer.$phpEx?mode=admin_offer_edit&" . POST_AUCTION_OFFER_URL . "=" . $offer_rowset[$i]['PK_auction_offer_id'] . ""),

// IF CASE IS IN AUCTION_OFFER.php:               'U_ADMIN_OFFER_EDIT' => append_sid("../auction_offer.$phpEx?mode=admin_offer_edit&" . POST_AUCTION_OFFER_URL . "=" . $offer_rowset[$i]['PK_auction_offer_id'] . ""),
                     'U_ADMIN_OFFER_EDIT' => append_sid("admin_auction_offer.$phpEx?mode=admin_offer_edit&" . POST_AUCTION_OFFER_URL . "=" . $offer_rowset[$i]['PK_auction_offer_id'] . ""),
                     'AUCTION_OFFER_ON_TOP_CHECKED' => ( $offer_rowset[$i]['auction_offer_on_top']) ? "checked=\"checked\"" : "",
                     'AUCTION_OFFER_SPECIAL_CHECKED' => ( $offer_rowset[$i]['auction_offer_special']) ? "checked=\"checked\"" : "",
                     'AUCTION_OFFER_PICTURE' => ( $offer_rowset[$i]['pic_id'] ) ? "X" : "",
                     'AUCTION_OFFER_SELL_ON_FIRST' => ( $offer_rowset[$i]['auction_offer_direct_sell_price']<0> $paid,
                     'AUCTION_OFFER_TIME_END' => create_date("m/d/Y - h:i:s", $offer_rowset[$i]['auction_offer_time_stop'], $board_config['board_timezone']),
                     'AUCTION_OFFER_TEXT' => $offer_rowset[$i]['auction_offer_text'],               
                     'AUCTION_OFFER_ADMINS_UNCENSORED_TEXT' => $offer_rowset[$i]['auction_offer_admins_uncensored_text'],               
                     'AUCTION_OFFER_COMMENT' => $offer_rowset[$i]['auction_offer_comment'],

                     'COUPON_USER_CREATED' => $coupon_rowset[$i]['coupon_creator'],
                     'COUPON_DATE_USED' => ( $coupon_rowset[$i]['auction_coupon_date_used']>0 ) ? create_date("m/d/Y - h:i:s", $coupon_rowset[$i]['auction_coupon_date_used'], $board_config['board_timezone']) : $lang['coupon_not_used'],
                     'COUPON_USER_USED' => ( $coupon_rowset[$i]['coupon_user']<coupon_rowset> append_sid("../auction_offer_view.$phpEx?" . POST_AUCTION_OFFER_URL . "=" . $offer_rowset[$i]['PK_auction_offer_id']),
                     'U_AUCTION_OFFER_DELETE' => append_sid("admin_auction_offer.$phpEx?" . POST_AUCTION_OFFER_URL . "=" . $offer_rowset[$i]['PK_auction_offer_id'] . "&mode=delete")));
             } // for
          } // if

     $template->assign_vars(array(
            'L_ADMIN_OFFER' => $lang['offer_admin'],
            'L_AUCTION_OFFER_ID' => $lang['auction_offer_id'],
            'L_ADMIN_OFFER_EXPLAIN' => $lang['offer_admin_explain'],
            'L_AUCTION_OFFER_TITLE' => $lang['auction_offer_title'],
            'L_AUCTION_OFFER_TIME_STOP' => $lang['auction_offer_time_stop'],
            'L_AUCTION_OFFER_PAID' => $lang['auction_offer_paid_status'],
            'L_AUCTION_OFFER_ON_TOP' => $lang['auction_offer_on_top_short'],
            'L_AUCTION_OFFER_PICTURE' => $lang['auction_offer_picture_short'],         
            'L_AUCTION_OFFER_PRICE_START' => $lang['auction_offer_price_start_short'],         
            'L_AUCTION_OFFER_RESERVE_FACTOR' => $lang['auction_offer_reserve_factor_short'],         
            'L_AUCTION_OFFER_DIRECT_SELL' => $lang['auction_offer_direct_sell_short'],         
            'L_AUCTION_OFFER_SHIPPING_PRICE' => $lang['auction_offer_shipping_price_short'],
            'L_AUCTION_OFFER_VIEWS' => $lang['auction_offer_views_short'],
            'L_AUCTION_OFFER_SPECIAL' => $lang['auction_offer_special_short'],
            'L_AUCTION_OFFER_BOLD' => $lang['auction_offer_bold_short'],
            'L_AUCTION_OFFER_SELL_ON_FIRST' => $lang['auction_offer_sell_on_comment_short'],
//            'L_AUCTION_OFFER_COMMENT' => $lang['auction_offer_comment_short'],
            'L_AUCTION_OFFER_OFFERER' => $lang['auction_offer_offerer'],
            'L_AUCTION_OFFER_DELETE' => $lang['auction_offer_delete'],
            'L_AUCTION_OFFER_MARK_PAID' => $lang['auction_offer_function'],
            'L_AUCTION_OFFER_DELETE' => $lang['auction_offer_delete'],
            'L_AUCTION_OFFER_SORT_JUST_PAID' => $lang['auction_offer_sort_just_paid'],
            'L_AUCTION_OFFER_SORT_JUST_NOT_PAID' => $lang['auction_offer_sort_just_not_paid'],
            'L_ADMIN_AUCTION_OFFER_FIELD_EDIT' => $lang['admin_auction_offer_field_edit'],
            'L_AUCTION_OFFER_TEXT' => $lang['auction_offer_text_short'],
            'L_AUCTION_OFFER_ADMINS_UNCENSORED_TEXT' => $lang['auction_offer_admins_uncensored_text_short'],
            'L_AUCTION_OFFER_COMMENT' => $lang['auction_offer_comment_short2'],
         
            'L_COUPON_USER_CREATED' => $lang['coupon_user_created'],
            'L_COUPON_DATE_USED' => $lang['coupon_date_used'],
            'L_COUPNG_USER_USED'=> $lang['coupon_user_used'],
            'L_COUPON_CREATE' => $lang['coupon_create'],
            'L_CHOOSE_COUPON_TYPE' => $lang['coupon_choose_type'],

            'S_AUCTION_COUPON_ACTION' => append_sid("admin_auction_coupon.$phpEx?mode=create"),

            'COUPON_LIST_DD' => $coupon_list_dd,
            'U_AUCTION_OFFER_SORT_TITLE' => append_sid("admin_auction_offer.$phpEx?sort=title"),
            'U_AUCTION_OFFER_SORT_USERNAME' => append_sid("admin_auction_offer.$phpEx?sort=username"),
            'U_AUCTION_OFFER_SORT_PAID' => append_sid("admin_auction_offer.$phpEx?sort=paid"),       
            'U_AUCTION_OFFER_SORT_JUST_PAID' => append_sid("admin_auction_offer.$phpEx?sort=just_paid"),
            'U_AUCTION_OFFER_SORT_JUST_NOT_PAID' => append_sid("admin_auction_offer.$phpEx?sort=just_not_paid"),
            'U_AUCTION_OFFER_SORT_TIME' => append_sid("admin_auction_offer.$phpEx")));


      $template->pparse("body");


The end result is that I can get an offer's original data to populate a form field, but if I try to edit/update the form with a new value (or just delete an existing value), I get this:

Couldn't update Admin's account changes. Please try again.

DEBUG MODE

SQL Error : 1064 You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ' auction_offer_reserve_factor = ,

UPDATE phpbb_auction_offer SET auction_offer_title = '', auction_offer_text = '', auction_offer_admins_uncensored_text = '', auction_offer_comment = '', auction_offer_price_start = , auction_offer_reserve_factor = , auction_offer_direct_sell_price = , auction_offer_shipping_price = , auction_offer_special = 0, auction_offer_on_top = 0, auction_offer_bold = 0 WHERE PK_auction_offer_id = 333

Line : 451
File : admin_auction_offer.php

I can see that 4 values are being received, but the 3 zeros for the special, bold, on-top are actually non-responsive: even if I click them and try uploading, the 0 value gets passed.

One of my concerns is, have I placed the order of all these commands okay, or is my problem that the coding so far does not allow for an "upload" of data following its initial display? Looking at the coding for populating the template I'm also concerned if I'm calling up the data correctly: I think the template is only going to show the original auction data and nothing from the form field... I reached this mod site doing a search into "$template->assign_block_vars" versus "$template->assign_vars" because I started thinking maybe I need to use "assign_vars"...

I can't find a phpbb2 file which does what I'm trying to do, but the closest example may be "usercp_register.php", although I don't see there a "display-edit-update" process...

One thing I saw there though that may be relevant is to put my conditional clauses into the template populating command like in usercp_register:
Code:

   $template->assign_vars(array(
      'USERNAME' => isset($username) ? $username : '',
      'CUR_PASSWORD' => isset($cur_password) ? $cur_password : '',
      'NEW_PASSWORD' => isset($new_password) ? $new_password : '',
      'PASSWORD_CONFIRM' => isset($password_confirm) ? $password_confirm : '',
      'EMAIL' => isset($email) ? $email : '',


Any help would be greatly appreciated![/code]
Back to top
guyb
Board Member



Joined: 30 Sep 2009

Posts: 11



PostPosted: Fri Oct 02, 2009 3:50 am 
Post subject: Re: help updating sql via template form

I may have "advanced" from this problem to something more concrete; please see my new issue here: http://www.phpbb2refugees.com/viewtopic.php?t=483
Thanks icon_mrgreen.gif
Back to top
Display posts from previous:   
Register or Login to Post    Index » MOD Writing  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 4 Hours
 
Jump to:  

Index • About • FAQ • Rules • Privacy • Search •  Register •  Login 
Not affiliated with or endorsed by the phpBB Group
Powered by phpBB2 © phpBB Group
Generated in 0.0819 seconds using 17 queries. (SQL 0.0152 Parse 0.0006 Other 0.0661)
phpBB Customizations by the phpBBDoctor.com
Template Design by DeLFlo and MomentsOfLight.com Moments of Light Logo