MySQL Insert fiasco

[mjc302]

Hi guys, new to the forum.

 

Im in need of some advice. I am setting up a shopping cart using opencart. I have an xml product feed, and am writing a parser script to import/update products.

 

The xml parsing via SimpleXML, image downloads, and category inserts work without a hitch. The product Inserts, not so much.

 

there's 4 tables involved: product, product_description, product_to_category, product_to_store

 

all INSERTs are successful for 3 out of 4 tables.

2,540 products get inserted.

However, the product_description table shows only 2,143 rows. For some reason, not all INSERTs to this table produce a valid row.

 

I cleared the tables and run the script many times, and each time produces the same results which leads me to believe the problem has something to do with the data instead of the script, but i have no idea where to start troubleshooting.

 

ive included the applicable code

 

class SpicyDB extends mysqli{

function doesExist ($name, $type) {
  switch ($type) {
    case "product":
        $query = "SELECT product_id from spicyvib_product WHERE model='$name'";
        break;
    case "category":
        $query = "SELECT category_id from spicyvib_category_description WHERE name='$name'";
        break;
  }
  
  $result = $this->query($query);
  
  if ($result->num_rows >= 1)
  $status = $result->fetch_row();
  else
  $status = false;
  
  return $status;
}



function addCategory ($name) {
  $query = "INSERT INTO spicyvib_category (parent_id) VALUES (0)";
  $this->query($query);
  $last_id = $this->insert_id;
  $query2 = "INSERT INTO spicyvib_category_description (category_id, name) VALUES ($last_id, '$name')";
  $this->query($query2);
  $query3 = "INSERT INTO spicyvib_category_to_store (category_id, store_id) VALUES ($last_id, 0)";
  $this->query($query3);
}

function getCatIDS () {
  $catIDS = array();
  $result = $this->query("SELECT category_id, name FROM spicyvib_category_description");
   while ($row = $result->fetch_assoc()) {
    $name = $row['name'];
    $id = $row['category_id'];
    $catIDS[$name] = $id;
    }
   return $catIDS;
  }


function addProduct ($data, $catIDS) {
  $this->query("INSERT INTO spicyvib_product SET model = '" . $data['model'] . "', quantity = '1', minimum = '1', subtract = '0', stock_status_id = '7', image = '" . $data['image'] . "', date_available = NOW(), manufacturer_id = '0', price = '" . (float)$data['price'] . "', cost = '" . (float)$data['cost'] . "', weight_class_id = '5', length_class_id = '3', status = '1', tax_class_id = '0', date_added = NOW()");
  
  $last_id = $this->insert_id;
  
  $this->query("INSERT INTO spicyvib_product_to_store SET product_id = '" . (int)$last_id . "', store_id = '0'");
  
  $this->query("INSERT INTO spicyvib_product_description SET product_id = '" . (int)$last_id . "', language_id = '1', name = '" . $data['name'] . "', description = '" . $data['description'] . "'");
  
  $categoryList = $data['category'];
  $categoryArr = explode(';', $categoryList);
   foreach ($categoryArr as $currCategory) {
    if (!empty($currCategory)) {

 if (!isset($catIDS[$currCategory])) {
  $currCategory = 'Miscellaneous';
 }
    $this->query("INSERT INTO spicyvib_product_to_category SET product_id = '" . (int)$last_id . "', category_id = '" . (int)$catIDS[$currCategory] . "'");
    }
   }

}
}





$db = new SpicyDB(DB_HOST, DB_USER, DB_PW, DB_NAME);

$categoryXml = new SimpleXMLElement(CAT_FNAME, NULL, TRUE);
foreach ($categoryXml->category as $cat) {
$name = (string)$cat->name;
$exists = $db->doesExist($name, 'category');
  if (!$exists) {
   $db->addCategory($name);
  }
}

$category_ids = $db->getCatIDS();

$finalXML = new SimpleXMLElement(OLD_FNAME, NULL, TRUE);

foreach ($finalXML->items->item as $item) {
$data = array();
  $updated = (string)$item->lastupdated;
  
  $data['model'] = (string)$item->model;
  $data['name'] = (string)$item->title;
  
  $image_str = (string)$item->image;
  $image = basename($image_str);
  $data['image'] = 'data/' . $image;
  
  $data['price'] = (float)$item->suggested_retail;
  $data['cost'] = (float)$item->price;
  
  if (empty($data['price'])) {
   $data['price'] = ($data['cost'] * 2);
  } 
  
  $data['description'] = (string)$item->description;
  $data['category'] = (string)$item->category;
  
  switch ($updated) {
   case "UPDATE":
    $model = $data['model'];
    if (!$db->doesExist($model, 'product')) {
 $db->addProduct($data, $category_ids);
}
       break;
   }

}

 

 

[DavidAM]

You are not checking for errors on any of your queries.  So they could be failing and you don't know it.  I don't use the mysqli object, so I'm not sure what the exact process is, but after you do '$this->query(...)' you should be checking for any mysql error and displaying it or handling it.

 

I suspect that if you add error checking you will find that some of the string data you are adding contains a single-quote mark: like "12' snake" or "O'Roark something".  If there is any possibility that a string will contain a single-quote mark - and there is unless YOU personally built the data and made sure it did not - you need to escape the string.  It will not hurt to escape the string if there are no special characters, so just do it.  Again, I don't use mysqli, but with plain vanilla mysql it is mysql_real_escape_string().

 

$this->query("INSERT INTO spicyvib_product SET model = '" . 
  mysql_real_escape_string($data['model']) . "', quantity = '1', minimum = '1', 
  subtract = '0', stock_status_id = '7', image = '" .  
  mysql_real_escape_string($data['image']) . "', date_available = NOW(),  
  manufacturer_id = '0', price = '" . (float)$data['price'] . "',  
  cost = '" . (float)$data['cost'] . "', weight_class_id = '5',  
  length_class_id = '3', status = '1', tax_class_id = '0', date_added = NOW()");

 

by the way, it is usually easier to debug, if you build the sql into a string and then execute the string. That way, you can echo the string and see the query that is being built to help figure out what is wrong with it:

 

$sql = "INSERT INTO spicyvib_product SET model = '" . 
  mysql_real_escape_string($data['model']) . "', quantity = '1', minimum = '1', 
  subtract = '0', stock_status_id = '7', image = '" .  
  mysql_real_escape_string($data['image']) . "', date_available = NOW(),  
  manufacturer_id = '0', price = '" . (float)$data['price'] . "',  
  cost = '" . (float)$data['cost'] . "', weight_class_id = '5',  
  length_class_id = '3', status = '1', tax_class_id = '0', date_added = NOW()";
echo $sql;
$this->query($sql); 

 

[mjc302]

...something i COMPLETELY overlooked, maybe i need a break

 

THANK YOU! (it worked)