-1

I have a database name q8marketz there is a table product inside. This table has many fields eg:- product id, model, quantity, shipping, etc...

I have uploaded many products to my table... there is a field shipping, the shipping value is already set to 1for all products, so now I want to set all product shipping values to 0.

anybody can help me? Sorry my English is not perfect...

Two product details given below...


===Database q8marketz

== Table structure for table product

|------
|Field|Type|Null|Default
|------
|//**product_id**//|int(11)|No|
|model|varchar(64)|No|
|downpayment|varchar(32)|No|
|sku|varchar(64)|No|
|upc|varchar(12)|No|
|ean|varchar(14)|No|
|jan|varchar(13)|No|
|isbn|varchar(13)|No|
|mpn|varchar(64)|No|
|location|varchar(128)|No|
|quantity|int(4)|No|0
|stock_status_id|int(11)|No|
|image|varchar(255)|Yes|NULL
|manufacturer_id|int(11)|No|
|shipping|tinyint(1)|No|1
|price|decimal(15,4)|No|0.0000
|points|int(8)|No|0
|tax_class_id|int(11)|No|
|date_available|date|No|
|weight|decimal(15,8)|No|0.00000000
|weight_class_id|int(11)|No|0
|length|decimal(15,8)|No|0.00000000
|width|decimal(15,8)|No|0.00000000
|height|decimal(15,8)|No|0.00000000
|length_class_id|int(11)|No|0
|subtract|tinyint(1)|No|1
|minimum|int(11)|No|1
|sort_order|int(11)|No|0
|status|tinyint(1)|No|0
|date_added|datetime|No|0000-00-00 00:00:00
|date_modified|datetime|No|0000-00-00 00:00:00
|viewed|int(5)|No|0
== Dumping data for table product

|886|BA-HANDBAGS-001ss| | | | | | | |aaaa|95|7|data/hand-bags.jpg|7|1|32.5000|25|0|2012-09-03|0.00000000|1|0.00000000|0.00000000|0.00000000|1|1|1|1|1|2012-09-25 13:00:18|0000-00-00 00:00:00|0
|883|BA-HANDBAGS-001ss| | | | | | | |aaaa|95|7|data/hand-bags.jpg|7|1|32.5000|25|0|2012-09-03|0.00000000|1|0.00000000|0.00000000|0.00000000|1|1|1|1|1|2012-09-17 14:08:08|2012-09-25 13:00:06|9
== Table structure for table product

|------
|Field|Type|Null|Default
|------
|//**product_id**//|int(11)|No|
|model|varchar(64)|No|
|downpayment|varchar(32)|No|
|sku|varchar(64)|No|
|upc|varchar(12)|No|
|ean|varchar(14)|No|
|jan|varchar(13)|No|
|isbn|varchar(13)|No|
|mpn|varchar(64)|No|
|location|varchar(128)|No|
|quantity|int(4)|No|0
|stock_status_id|int(11)|No|
|image|varchar(255)|Yes|NULL
|manufacturer_id|int(11)|No|
|shipping|tinyint(1)|No|1
|price|decimal(15,4)|No|0.0000
|points|int(8)|No|0
|tax_class_id|int(11)|No|
|date_available|date|No|
|weight|decimal(15,8)|No|0.00000000
|weight_class_id|int(11)|No|0
|length|decimal(15,8)|No|0.00000000
|width|decimal(15,8)|No|0.00000000
|height|decimal(15,8)|No|0.00000000
|length_class_id|int(11)|No|0
|subtract|tinyint(1)|No|1
|minimum|int(11)|No|1
|sort_order|int(11)|No|0
|status|tinyint(1)|No|0
|date_added|datetime|No|0000-00-00 00:00:00
|date_modified|datetime|No|0000-00-00 00:00:00
|viewed|int(5)|No|0
S. Albano
  • 707
  • 7
  • 21
Mansoor abbas
  • 1
  • 1

2 Answers2

1

You can easily use an update query like this:

update product set shipping=0;

This will update all the rows to 0.

If you want to only specify certain selected rows, you can add a where clause to limit the number of rows that you update like this:

update product set shipping=0 where points>2;

This would update all the rows where points has a value greater than 2.

Edit: The code can be run directly from the mysql console, or via a database call from within PHP like this:

$dbh = new PDO($hostname, $username, $password);
$sql='update product set shipping=0 where points>2';
$stmt = $dbh->query($sql);
Fluffeh
  • 33,228
  • 16
  • 67
  • 80
  • @Mansoorabbas You might also be interested in reading this rather long question and answer I [wrote](http://stackoverflow.com/questions/12475850/how-can-an-sql-query-return-data-from-multiple-tables) which could well help you with SQL once you get your feet wet with single table bits :) – Fluffeh Sep 25 '12 at 10:10
0

You can use the SQL UPDATE statement to update existing records in your table.

SQL UPDATE Syntax :

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

For your examlpe use :

UPDATE product
SET shipping=0;

==>The code can be run directly from the mysql console.

To use PHP Call try the code:

<?php
 $dataBase = new PDO($hostname, $username, $password);
 $sqlQuery ='update product set shipping=0';
 $stmt = $dataBase->query($sqlQuery );
?>
Abdel
  • 582
  • 4
  • 6