what code to use to update sql table?

[guyb]

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?

[Obsidian]

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]

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

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...

[Mike TUMS]

First of all:

Code: Select all

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

[igorw]

Nope.

Code: Select all

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

[Mike TUMS]

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

[guyb]

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 ?