CODEWITHSUNDEEP
phpsqlopencart
Ammar Ul Hassan
Ammar Ul Hassan

Reputation: 906

Adding column if not exists in sql Opencart

I am creating a model in open cart. Using vqMod i am writing code in model. I want to alter table by adding a coloumn if it does not exists in table. Here is my code.

$field_name = 'GeoZoneID';
$col = $this->db->query("SELECT ".$field_name." FROM ".DB_PREFIX."order");

    if (!$col){
            $this->db->query("ALTER TABLE ".DB_PREFIX."order ADD ".$field_name." int(11) First");
            $zone_id=array();
    $zone_id=$this->db->query("SELECT zgz.geo_zone_id FROM  `oc_zone_to_geo_zone` AS zgz   WHERE zone_id = (SELECT shipping_zone_id FROM oc_order WHERE order_id = '".$order_id."' ) ");
    $zone_id=$zone_id->row['geo_zone_id'];
    $query=$this->db->query("UPDATE  `oc_order` SET GeoZoneID ='".$zone_id."' WHERE order_id ='".$order_id."'");                
        } 
     else {
    $zone_id=array();
    $zone_id=$this->db->query("SELECT zgz.geo_zone_id FROM  `oc_zone_to_geo_zone` AS zgz   WHERE zone_id = (SELECT shipping_zone_id FROM oc_order WHERE order_id = '".$order_id."' ) ");
    $zone_id=$zone_id->row['geo_zone_id'];
    $query=$this->db->query("UPDATE  `oc_order` SET GeoZoneID ='".$zone_id."' WHERE order_id ='".$order_id."'");                

        }

I dont know why its not executing it does not alter my tabel.

Upvotes: 1

Views: 1460

Answers (2)

Chitresh goyal
Chitresh goyal

Reputation: 333

Just Use below function in your model part. It will create your column if doesn't exist in DB.

public function checkFieldInModel() {
  $isModelField = FALSE;
  $result = $this->db->query( "DESCRIBE `".DB_PREFIX."order`;" );
  foreach ($result->rows as $row) {
     if ($row['Field'] == 'GeoZoneID') {
        $isModelField = TRUE;
        break;
     }
  }
  if (!$isModelField) {
     $sql = "ALTER TABLE `".DB_PREFIX."option` ADD `GeoZoneID` int( 11 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT ''";
     $this->db->query( $sql );
  }
}

Or just add a column Direct in DB using this:

ALTER table database.table_name add column_name data_type CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;

ALTER table cart.oc_product_option_value add pc_code varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;

Upvotes: 0

Ammar Ul Hassan
Ammar Ul Hassan

Reputation: 906

I ended up fixing my problem like this..

$hasModelChoiceField = FALSE;
$result = $this->db->query( "DESCRIBE `".DB_PREFIX."order`;" );
foreach ($result->rows as $row) {
if ($row['Field'] == 'GeoZoneID') {
$hasModelChoiceField = TRUE;
break;
 }
 }
 if (!$hasModelChoiceField) {
 $sql = "ALTER TABLE `".DB_PREFIX."order` ADD GeoZoneID INT( 11 ) CHARACTER SET utf8 COLLATE utf8_general_ci  DEFAULT ''";
 $this->db->query( $sql );
 $zone_id=array();
 $zone_id=$this->db->query("SELECT zgz.geo_zone_id FROM `" . DB_PREFIX . "oc_zone_to_geo_zone`  AS zgz WHERE zone_id = ( SELECT shipping_zone_id FROM `".DB_PREFIX."order`WHERE order_id = '".$order_id."' ) ");$zone_id=$zone_id>row['geo_zone_id'];$query=$this->db->query("UPDATE  `" . DB_PREFIX . "order` SET GeoZoneID ='".$zone_id."' WHERE order_id ='".$order_id."'");
 }
else{
    $zone_id=array();
    $zone_id=$this->db->query("SELECT zgz.geo_zone_id FROM`oc_zone_to_geo_zone` AS zgzWHERE zone_id = ( SELECT shipping_zone_idFROM oc_orderWHERE order_id = '".$order_id."' ) ");$zone_id=$zone_id->row['geo_zone_id'];$query=$this->db->query("UPDATE  `" . DB_PREFIX . "order` SET GeoZoneID ='".$zone_id."' WHERE order_id ='".$order_id."'");

}

Upvotes: 1

Related Questions