what code to use to update sql table?

Discuss phpBB 3.0.x in general.
Forum rules
Please post any phpBB 3.1.x related topics in the phpBB 3.1.x discussion forum.
guyb
New member
New member
Posts: 17
Joined: 30 Sep 2009, 08:21

what code to use to update sql table?

Postby guyb » 13 Oct 2009, 07:47

In PHPBB 2 I'm trying to receive data from form fields and upload them into an sql table, but I've seen variations of sql UPDATEs and I don't know which kind to use:

I've seen data received in like this:

Code: Select all

$data_input = $HTTP_POST_VARS['data_input'];


Or like this:

Code: Select all

$data_input = ( isset($HTTP_POST_VARS['data_input']) ) ? $HTTP_POST_VARS['data_input'] : $HTTP_GET_VARS['data_input'];



and used to UPDATE sql like this:

Code: Select all

data_input = '" . $data_input . "',



But I've also seen sql UPDATEd like this:

Code: Select all

forum_status = " . intval($HTTP_POST_VARS['forumstatus']) . ",


and like this (with ''):

Code: Select all

topic_last_poster_id = '$topic_last_poster_id',


Or this (without ''):

Code: Select all

session_time = $current_time,


Can anyone give me some direction as to what goes with what and when?

User avatar
Obsidian
Supporter
Supporter
Posts: 736
Joined: 13 May 2008, 15:20
Real name: Damian

Re: what code to use to update sql table?

Postby Obsidian » 13 Oct 2009, 13:02

Okay.

Code: Select all

$data_input = ( isset($HTTP_POST_VARS['data_input']) ) ? $HTTP_POST_VARS['data_input'] : $HTTP_GET_VARS['data_input'];


That checks to see if the data needed is in $_POST, and if it is not it checks for the same data in $_GET.

Code: Select all

data_input = '" . $data_input . "',


That's one method for entering in a string within SQL.

Code: Select all

forum_status = " . intval($HTTP_POST_VARS['forumstatus']) . ",


That's a secure way of entering in an integer value into an SQL query, since it forces it to be an integer.

Code: Select all

topic_last_poster_id = '$topic_last_poster_id',


That's just a method of entering in a value within a query, a string value specifically.

Code: Select all

session_time = $current_time,


That is for entering in a numeric value into a query (notice the lack of quotes around the variable -- this is why). I'd say it is probably used for floating decimal values, but if possible I wouldn't use this method; use the method above as it typecasts the variable to the integer variable type.

guyb
New member
New member
Posts: 17
Joined: 30 Sep 2009, 08:21

Re: what code to use to update sql table?

Postby guyb » 13 Oct 2009, 13:43

Ah! Thanks for clarifying all that for me.

Taking the explanation a step farther, if my UPDATE is taking data from a form and that data is either pre-existing (i.e. already displayed in the field) or newly entered/altered, I suppose this would be the best way of receiving in that kind data:

Code: Select all

$data_input = ( isset($HTTP_POST_VARS['data_input']) ) ? $HTTP_POST_VARS['data_input'] : $HTTP_GET_VARS['data_input'];


In which case my sql UPDATE should be like this -?

Code: Select all

data_input = '" . $data_input . "',


If this is correct, for the UPDATE how do I script numbers or boolean values?

Update
I'm running tests using this code and the data seems to be getting received fine...

Code: Select all

      while( $i < count($total_offers_to_update) )
      {
         $offer_id = intval($total_offers_to_update[$i]);
    $auction_offer_title = ( isset($HTTP_POST_VARS['auction_offer_title']) ) ? $HTTP_POST_VARS['auction_offer_title'] : $HTTP_GET_VARS['auction_offer_title'];
    $auction_offer_text = ( isset($HTTP_POST_VARS['auction_offer_text']) ) ? $HTTP_POST_VARS['auction_offer_text'] : $HTTP_GET_VARS['auction_offer_text'];
   $auction_offer_admins_uncensored_text = ( isset($HTTP_POST_VARS['auction_offer_admins_uncensored_text']) ) ? $HTTP_POST_VARS['auction_offer_admins_uncensored_text'] : $HTTP_GET_VARS['auction_offer_admins_uncensored_text'];
   $auction_offer_comment = ( isset($HTTP_POST_VARS['auction_offer_comment']) ) ? $HTTP_POST_VARS['auction_offer_comment'] : $HTTP_GET_VARS['auction_offer_comment'];
   $auction_offer_price_start = ( isset($HTTP_POST_VARS['auction_offer_price_start']) ) ? $HTTP_POST_VARS['auction_offer_price_start'] : $HTTP_GET_VARS['auction_offer_price_start'];
   $auction_offer_reserve_factor = ( isset($HTTP_POST_VARS['auction_offer_reserve_factor']) ) ? $HTTP_POST_VARS['auction_offer_reserve_factor'] : $HTTP_GET_VARS['auction_offer_reserve_factor'];
   $auction_offer_direct_sell_price = ( isset($HTTP_POST_VARS['auction_offer_direct_sell_price']) ) ? $HTTP_POST_VARS['auction_offer_direct_sell_price'] : $HTTP_GET_VARS['auction_offer_direct_sell_price'];
   $auction_offer_shipping_price = ( isset($HTTP_POST_VARS['auction_offer_shipping_price']) ) ? $HTTP_POST_VARS['auction_offer_shipping_price'] : $HTTP_GET_VARS['auction_offer_shipping_price'];
   $auction_offer_special = ( isset($HTTP_POST_VARS['offer_special']) ) ? ( ($HTTP_POST_VARS['offer_special']) ? TRUE : 0 ) : $HTTP_GET_VARS['offer_special'];
   $auction_offer_on_top = ( isset($HTTP_POST_VARS['offer_on_top']) ) ? ( ($HTTP_POST_VARS['offer_on_top']) ? TRUE : 0 ) : $HTTP_GET_VARS['offer_on_top'];
   $auction_offer_bold = ( isset($HTTP_POST_VARS['offer_bold']) ) ? ( ($HTTP_POST_VARS['offer_bold']) ? TRUE : 0 ) : $HTTP_GET_VARS['offer_bold'];

$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 = " . doubleval($auction_offer_initial_price) . ",         
auction_offer_reserve_factor = " . doubleval($auction_offer_reserve_factor) . ",
auction_offer_direct_sell_price = " . doubleval($auction_offer_direct_sell_price) . ",
auction_offer_shipping_price = " . doubleval($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 . "'";


Except that sql debug has a problem somewhere around where my WHERE statement is just above.

Code: Select all

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 'WHERE PK_auction_offer_id ='357'' at line 13

UPDATE phpbb_auction_offer SET auction_offer_title = 'NEW BANKNOTES!', auction_offer_text = 'Test description Banknotes NEW', auction_offer_admins_uncensored_text = 'Test special comments Banknotes NEW', auction_offer_comment = 'test editable comments banknotes NEW', auction_offer_price_start = 0, auction_offer_reserve_factor = 0.8, auction_offer_direct_sell_price = 7, auction_offer_shipping_price = 6, auction_offer_special = 1, auction_offer_on_top = 1, auction_offer_bold = WHERE PK_auction_offer_id ='357'

Line : 233
File : admin_auction_offer.php


There may be at least 2 problems: when I edit or leave alone the variable called "auction_offer_price_start", it zeros it out. And there is also some problem with my checkboxes because in the debug above "auction_offer_bold" is passed back blank even though it was "special" which I unticked, and when I deselect all 3 checkboxes ("special", "on top" and "bold"), the debug below still insists that "special" is checked (though instead of passing back a 0 the other 2 options are passed back blank):

Code: Select all

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_bold = WHERE PK_auction_offer_id ='357'' at l

UPDATE phpbb_auction_offer SET auction_offer_title = 'TEST title banknotes', auction_offer_text = 'Test description Banknotes', auction_offer_admins_uncensored_text = 'Test special comments Banknotes', auction_offer_comment = 'test editable comments banknotes', auction_offer_price_start = 0, auction_offer_reserve_factor = 0.7, auction_offer_direct_sell_price = 150, auction_offer_shipping_price = 4, auction_offer_special = 1, auction_offer_on_top = , auction_offer_bold = WHERE PK_auction_offer_id ='357'

Line : 233
File : admin_auction_offer.php


I've run a different test now in which I selected just 1 of the 3 checkboxes, pressed "update" and saw that all 3 boxes are checked (and the starting price is again zeroed out). If I deselect the checkboxes and press "update", all 3 are returned back filled in...

...another test yields even wierder results: I have 2 test item listings at the moment; I tried editing a text field on my 1st item and when I "updated", all the values of the 2nd item were recorded in place of what was there. The only value which remained untouched was that item's unique "offer_id".

Any help would be greatly - very *GREATLY* - appreciated :mrgreen:

doing further checking I see that the checkbox values are set as "tinyint" in the database, and the "start price" is set as "decimal" - but the "direct sell price" value is also decimal and sql debug sees that fine...

User avatar
Mike TUMS
Member
Member
Posts: 162
Joined: 16 Jan 2007, 05:51
Real name: Mihail
Location: Moscow, RU

Re: what code to use to update sql table?

Postby Mike TUMS » 14 Oct 2009, 14:34

First of all:

Code: Select all

$data_input = mysql_real_escape_string(isset($_POST['data_input']) ? $_POST['data_input'] : $_GET['data_input'];
Image
Introduction forum's welcome bot. Guru of Indian-style coding .
Russian spy. Master of copypasta. Google's right hand.
Advanced lamer of Modders.


May translate your MOD to Russian.

User avatar
igorw
Past Contributor
Past Contributor
Posts: 1967
Joined: 01 Jun 2006, 20:48
Real name: Igor

Re: what code to use to update sql table?

Postby igorw » 14 Oct 2009, 15:36

Nope.

Code: Select all

$data_input = isset($_POST['data_input']) ?  mysql_real_escape_string($_POST['data_input']) :  mysql_real_escape_string($_GET['data_input']);

User avatar
Mike TUMS
Member
Member
Posts: 162
Joined: 16 Jan 2007, 05:51
Real name: Mihail
Location: Moscow, RU

Re: what code to use to update sql table?

Postby Mike TUMS » 14 Oct 2009, 21:05

eviL<3 wrote:Nope.

Code: Select all

$data_input = isset($_POST['data_input']) ?  mysql_real_escape_string($_POST['data_input']) :  mysql_real_escape_string($_GET['data_input']);

damn, you right, i'm sorry
Image
Introduction forum's welcome bot. Guru of Indian-style coding .
Russian spy. Master of copypasta. Google's right hand.
Advanced lamer of Modders.


May translate your MOD to Russian.

guyb
New member
New member
Posts: 17
Joined: 30 Sep 2009, 08:21

Re: what code to use to update sql table?

Postby guyb » 14 Oct 2009, 21:28

Thanks for the replies!

I think I'm making progress on most of the fields including the decimals, but I can't seem to get checkbox values UPDATEd correctly: the original selections display fine, but subsequent changes which I UPDATE seem to override any future changes I make.

Working backwards, here's how my 3 checkboxes are assigned to the template:

Code: Select all

                     'AUCTION_OFFER_BOLD_CHECKED' => ( $offer_rowset[$i]['auction_offer_bold']) ? "checked=\"checked\"" : "",
                     '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\"" : "",


And here are 3 different ways I've tried to receive default or edited values:
1.

Code: Select all

$auction_offer_special = ( isset($HTTP_POST_VARS['offer_special']) ) ? ( ($HTTP_POST_VARS['offer_special']) ? TRUE : 0 ) : $HTTP_GET_VARS['offer_special'];


2.

Code: Select all

   $auction_offer_special = $HTTP_POST_VARS['offer_special'];


3.

Code: Select all

                if (empty($HTTP_POST_VARS['offer_special']) )
                      {
                           $auction_offer_special = 0;
                      }
                 else
                      {
                           $auction_offer_special = 1;
                      }


My sql UPDATE for them looks like this:

Code: Select all

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 . "'";


Depending on which of the 3 methods above I use I sometimes get a debugging error regarding returned values of "on". Any ideas which way I'm supposed to code the $POST ?


Return to “phpBB 3.0.x "Olympus" discussion”

Who is online

Users browsing this forum: CommonCrawl [Bot] and 2 guests