[SOLVED] How to update values in database through php?

[Neo_odessa]

I have module php file and trying to update values in DB. I have column 'product_quantity_collected' in table 'order_detail' need to be updated. I`ve tried do this methods

if (Tools::getValue('product_quantity_collected'))
Db::getInstance()->insert("ps_order_detail", $product_quantity_collected); 
Db::getInstance() -> update ('ps_order_detail', $product_quantity_collected);

But nothing. I`m obtaining values but after refreshing page it not updating.

 

If sombody can help please share right code. Many thanks.

Edited June 8, 2015 by Neo_odessa (see edit history)

[vekia]

so you want to add or to update?
 

if update, try:
 

$query = "UPDATE `"._DB_PREFIX_."order_detail` SET product_quantity_collected='SOMETHING'";
Db::getInstance()->Execute($query);

don't forget to use WHERE clause to change only selected element in order_detail table.

[Neo_odessa]

so you want to add or to update?

 

if update, try:

 

$query = "UPDATE `"._DB_PREFIX_."order_detail` SET product_quantity_collected='SOMETHING'";
Db::getInstance()->Execute($query);

don't forget to use WHERE clause to change only selected element in order_detail table.

I have tried do like this but values not updating maybe I made some mistake. I use the code:

		if (Tools::getValue('product_quantity_collected'))
	$query = "UPDATE `"._DB_PREFIX_."order_detail` SET product_quantity_collected='product_quantity_collected'";
	"WHERE od.id_order='" . $id_ord . "' AND od.product_id='" . $id_prod . "' AND od.id_shop='" . $id_shop . "';";
Db::getInstance()->Execute($query);	 

Please help if have some ideas. Thanks.

Edited May 31, 2015 by Neo_odessa (see edit history)

[bellini13]

if (Tools::getValue('product_quantity_collected'))
    $query = "UPDATE `"._DB_PREFIX_."order_detail` SET product_quantity_collected='product_quantity_collected'";
    "WHERE od.id_order='" . $id_ord . "' AND od.product_id='" . $id_prod . "' AND od.id_shop='" . $id_shop . "';";

Db::getInstance()->Execute($query);    

it is considered bad practice to take a value provided from the request, and use it in a database statement.  A malicious user could easily destroy your database

 

Your code also has numerous errors, give this a try.

if (Tools::getValue('product_quantity_collected'))
{
    $quantity = (int)Tools::getValue('product_quantity_collected');
    $query = "UPDATE `"._DB_PREFIX_."order_detail` SET product_quantity_collected=". $quantity . " WHERE od.id_order=" . $id_ord . " AND od.product_id=" . $id_prod . " AND od.id_shop=" . $id_shop . ";";
    Db::getInstance()->Execute($query);    
}

[vekia]

I have tried do like this but values not updating maybe I made some mistake. I use the code:

		if (Tools::getValue('product_quantity_collected'))
	$query = "UPDATE `"._DB_PREFIX_."order_detail` SET product_quantity_collected='product_quantity_collected'";
	"WHERE od.id_order='" . $id_ord . "' AND od.product_id='" . $id_prod . "' AND od.id_shop='" . $id_shop . "';";
Db::getInstance()->Execute($query);	 

Please help if have some ideas. Thanks.

 

your $query variable isn't complete

 

if (Tools::getValue('product_quantity_collected'))

    $query = "UPDATE `"._DB_PREFIX_."order_detail` SET product_quantity_collected='product_quantity_collected'"; //end of the query

    "WHERE od.id_order='" . $id_ord . "' AND od.product_id='" . $id_prod . "' AND od.id_shop='" . $id_shop . "';"; // missed content

Db::getInstance()->Execute($query);    

[Neo_odessa]

if (Tools::getValue('product_quantity_collected'))
    $query = "UPDATE `"._DB_PREFIX_."order_detail` SET product_quantity_collected='product_quantity_collected'";
    "WHERE od.id_order='" . $id_ord . "' AND od.product_id='" . $id_prod . "' AND od.id_shop='" . $id_shop . "';";

Db::getInstance()->Execute($query);    

it is considered bad practice to take a value provided from the request, and use it in a database statement.  A malicious user could easily destroy your database

 

Your code also has numerous errors, give this a try.

if (Tools::getValue('product_quantity_collected'))
{
    $quantity = (int)Tools::getValue('product_quantity_collected');
    $query = "UPDATE `"._DB_PREFIX_."order_detail` SET product_quantity_collected=". $quantity . " WHERE od.id_order=" . $id_ord . " AND od.product_id=" . $id_prod . " AND od.id_shop=" . $id_shop . ";";
    Db::getInstance()->Execute($query);    
}

I`ve tried to do this way that you suggest but it unsuccesful Maybe something wrong with defines variable

[Neo_odessa]

your $query variable isn't complete

 

if (Tools::getValue('product_quantity_collected'))

    $query = "UPDATE `"._DB_PREFIX_."order_detail` SET product_quantity_collected='product_quantity_collected'"; //end of the query

    "WHERE od.id_order='" . $id_ord . "' AND od.product_id='" . $id_prod . "' AND od.id_shop='" . $id_shop . "';"; // missed content

Db::getInstance()->Execute($query);    

What content I`ve missed? You thinck $id_ord,   $id_prod - it wrong definitions in that case. Can you please inspect the code above (I`ve shared whole file) maybe something defines is wrong? I can`t understand what. Thanks for your reply 

[PascalVG]

Assuming the product_quantity_collected is an already added column in the ps_product_detail field (it's not default available, assume added by the module):

 

Try Bellini's query, only add the definition (alias) of 'od' table, as it is not known yet.

 

if (Tools::getValue('product_quantity_collected'))
{
    $quantity = (int)Tools::getValue('product_quantity_collected');
    $query = "UPDATE `"._DB_PREFIX_."order_detail` od SET product_quantity_collected=". $quantity . " WHERE od.id_order=" . $id_ord . " AND od.product_id=" . $id_prod . " AND od.id_shop=" . $id_shop . ";";
    Db::getInstance()->Execute($query);    
}

 

 

My 2 cents,

pascal.

[vekia]

What content I`ve missed? You thinck $id_ord,   $id_prod - it wrong definitions in that case. Can you please inspect the code above (I`ve shared whole file) maybe something defines is wrong? I can`t understand what. Thanks for your reply 

 

your $query variable is:

 $query = "UPDATE `"._DB_PREFIX_."order_detail` SET product_quantity_collected='product_quantity_collected'"; 

this part is not included to $query variable:

 "WHERE od.id_order='" . $id_ord . "' AND od.product_id='" . $id_prod . "' AND od.id_shop='" . $id_shop . "';"; 

 

how to create php variables:

http://www.w3schools.com/php/php_variables.asp

 

 

in simple words you've got:

 

$query="A"; "B";

it's bad syntax, it should be:

$query="A B";

 

[Neo_odessa]

your $query variable is:

 $query = "UPDATE `"._DB_PREFIX_."order_detail` SET product_quantity_collected='product_quantity_collected'"; 

this part is not included to $query variable:

 "WHERE od.id_order='" . $id_ord . "' AND od.product_id='" . $id_prod . "' AND od.id_shop='" . $id_shop . "';"; 

 

how to create php variables:

http://www.w3schools.com/php/php_variables.asp

 

 

in simple words you've got:

 

$query="A"; "B";

it's bad syntax, it should be:

$query="A B";

 

I got you about syntax. I`ve ended query without "WHERE ....."

 

I`ve changed code:

	if (Tools::getValue('product_quantity_collected'))
    $query = "UPDATE `"._DB_PREFIX_."order_detail` SET od product_quantity_collected='product_quantity_collected'
    WHERE od.id_order='" . $id_ord . "' AND od.product_id='" . $id_prod . "' AND od.id_shop='" . $id_shop . "'";
Db::getInstance()->Execute($query);

It is right syntax? 

But nothing happens.

I`ve try this way 

$product_quantity_collected=isset($_POST['product_quantity_collected'])?trim($_POST['product_quantity_collected']):""; 
		$product_quantity_collected=htmlspecialchars(stripslashes($product_quantity_collected));
		$order_detail->product_quantity_collected=$product_quantity_collected;

With this code it changes values to "0" when I`m trying update. Unfortunately it not save values in DB. Maybe I was wrong with variable definitions from tpl where input values.

[Neo_odessa]

Assuming the product_quantity_collected is an already added column in the ps_product_detail field (it's not default available, assume added by the module):

 

Try Bellini's query, only add the definition (alias) of 'od' table, as it is not known yet.

 

if (Tools::getValue('product_quantity_collected'))

{

    $quantity = (int)Tools::getValue('product_quantity_collected');

    $query = "UPDATE `"._DB_PREFIX_."order_detail` od SET product_quantity_collected=". $quantity . " WHERE od.id_order=" . $id_ord . " AND od.product_id=" . $id_prod . " AND od.id_shop=" . $id_shop . ";";

    Db::getInstance()->Execute($query);    

}

 

 

My 2 cents,

pascal.

I`ve tryed do this way you suggest but nothing. Values not saving to database

[PascalVG]

I see in post number 11 you tried, but you put the 'od' BEHIND the SET. Try putting it BEFORE.

[Neo_odessa]

I see in post number 11 you tried, but you put the 'od' BEHIND the SET. Try putting it BEFORE.

It no matter I`ve tried before and after it no effect

[Neo_odessa]

I`ve contact to developer and he made changes to update values. It simple that I thought. Problem solved