guyb Board Member
Joined: 30 Sep 2009
Posts: 11
|
Posted: 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] |
|