функции mysql

подключиться к mysql server через php

$connect = mysqli_connect('localhost', 'root', '','tesystem')
or die('Not connection: ' . mysqli_error());

проверить соединение

$/* change character set to utf8 */<br>if (mysqli_set_charset($connect, "utf8")) {<br>printf("Error loading character set utf8: %s\n", mysqli_error($link));<br>} else {<br>printf("Current character set: %s\n", mysqli_character_set_name($link));<br>}

выполнить sql-запрос

$enquiry = "SELECT ID FROM item";

    $fromBD = mysqli_query($connect,$enquiry);

проверить не возникла ошибка после выполнения запроса

if(!empty(mysqli_error($connect))) {
    printf("Error: %s\n%s\n\n", mysqli_error($connect), $query);
    die;
}

Пример 1

<?php

include ('../config.php');

// $mysqli = new mysqli(DB_HOSTNAME, DB_USERNAME, DB_PASSWORD, DB_DATABASE);
// $mysqli->set_charset ('utf8');
$link = mysqli_connect(DB_HOSTNAME, DB_USERNAME, DB_PASSWORD,DB_DATABASE)
or die('Не удалось соединиться: ' . mysqli_error());
mysqli_set_charset($link, "utf8");


// $n = 0;
$query = "SELECT * FROM oc_category";
$result = query_do($link,$query); 

// while ($data = mysqli_fetch_array($result)) {

// $query = "SELECT * FROM oc_category_description WHERE category_id = " . $data['category_id'];
// $result2 = query_do($link,$query);
// $data2 = mysqli_fetch_array($result2);

// $name = mb_strtolower($data2['name'],"utf8");
// $meta_title = "Купить $name в Москве | компания «Мульти-Газ»";
// $query = "UPDATE oc_category_description SET meta_title = '".$meta_title ."' WHERE category_id = " . $data['category_id'];

// query_do($link,$query);

// }

$query = "SELECT * FROM oc_product";
$result = query_do($link,$query); 
while ($data = mysqli_fetch_array($result)) {

 $query = "SELECT * FROM oc_product_description WHERE product_id = " . $data['product_id'];
 $result2 = query_do($link,$query);
 $data2 = mysqli_fetch_array($result2);

 $name = mb_strtolower($data2['name'],"utf8");
 $meta_title = "Купить $name в Москве | компания «Мульти-Газ»";
 $query = "UPDATE oc_product_description SET meta_title = '".$meta_title ."' WHERE product_id = " . $data['product_id'] ." AND meta_title LIKE 'Купить%'";

 query_do($link,$query);

}

mysqli_close($link);
function query_do($link,$sql)
{
 $result = mysqli_query($link,$sql);    
 if(!empty(mysqli_error($link))) {
      printf("Error: %s\n%s\n\n", mysqli_error($link), $sql);
      mysqli_close($link);
      die;
 }
 return $result;
}

Пример 2

include('config.php');
$massiv  = [
 'row'=>['first'=>1,'second'=>2,'third'=>3],
 'rows'=>[
 ['first'=>11,'second'=>22,'third'=>33],
 ['first'=>111,'second'=>222,'third'=>333],
 ['first'=>1111,'second'=>2222,'third'=>3333],
 ['first'=>11111,'second'=>22222,'third'=>33333],
 ['first'=>111111,'second'=>222222,'third'=>333333],
 ]
];
$massiv2  = [
 'row'=>['first'=>1,'second'=>2,'third'=>3],
 'rows'=>[
 ['first'=>11,'second'=>122,'third'=>33],
 ['first'=>111,'second'=>1222,'third'=>333],
 ['first'=>1111,'second'=>12222,'third'=>3333],
 ['first'=>11111,'second'=>122222,'third'=>33333],
 ['first'=>111111,'second'=>1222222,'third'=>333333],
 ]
];
$res = array_column($massiv['rows'],'second');
$res2 = array_column($massiv2['rows'],'second');
$result = array_merge($res,$res2);
$resultarr = "('".implode($result,"'),('")."')";

 
        $mysqli = new mysqli(DB_HOSTNAME, DB_USERNAME, DB_PASSWORD, DB_DATABASE);
        $mysqli->set_charset('utf8');

$query = "CREATE TEMPORARY TABLE `oc_tmp_msproduct` (
  `ms_id` varchar(255) NOT NULL
  -- `product_id` int(11) NOT NULL,
  -- `type` varchar(64) NOT NULL,
  -- `updated` datetime NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8";
$result = query_do($link,$query); 

$query = "INSERT INTO `oc_tmp_msproduct` VALUES $resultarr";
$result = query_do($link,$query); 
$query = "SELECT * FROM `oc_tmp_msproduct`";
$result = query_do($link,$query); 

 echo '<pre>';
 var_dump(getSqlArray($result));
 echo '</pre>';


mysqli_close($link);
function query_do($link,$sql)
{
 $result = mysqli_query($link,$sql);    
 if(!empty(mysqli_error($link))) {
      printf("Error: %s\n%s\n\n", mysqli_error($link), $sql);
      mysqli_close($link);
      die;
 }
 return $result;
}
function getSqlArray($result)
{
    $out = ['row'=>[],'num_row'=>0];
    if(!$result) return $out;
    while ($data = $result->fetch_array()) {
        $out['row'][] = $data;
        $out['num_row']++;
    }
    return $out;
}

Пример 3

<?php
include('config.php');

//скачать xml yaml и сохранить на ПК

if(!file_exists('file_1.xml')) {
 $url1 = 'https://www.tss.ru/bitrix/catalog_export/yandex_800463.xml';
 $str = file_get_contents($url1); 
 file_put_contents('file_1.xml',$str); 
}

if(!file_exists('file_2.xml')) {
 $url2 = 'https://www.tss.ru/bitrix/catalog_export/yandex_800463.xml';
 $str = file_get_contents($url2); 
 file_put_contents('file_2.xml',$str); 
}

//спарсить xml yaml

$data = simplexml_load_file("file_1.xml");
$data = simplexml_load_file("file_2.xml");

$product_sql  = '';
$prodesc_sql  = '';
$proattr_sql  = '';
$protocat_sql = '';
$protost_sql  = '';
$protola_sql   = '';
foreach ($data->shop->offers->offer as $row) {
 $id = intval($row['id']);
 
 // available - статус товара «в наличии» / «на заказ».
 $available = boolval($row['available']);
 
 // currencyId - идентификатор категории товара.
 $categoryId = intval($row->categoryId);
 
 // name - название товара.
 $name = strval($row->name); 
 
 // description - описание.
 $description = strval($row->description);

 $count = 0;
 foreach ($row->param as $param) {
 // echo '<pre>';
 // var_dump($param['name'] == 'Артикул');
 // echo '</pre>';
 if($param['name'] == 'Артикул') {
   $model = strval($param);
   $count++;
 }
 if($param['name'] == 'Гарантия, срок (мес)') {
   $guaperiod  = strval($param);
   $count++;
 }
 if($count == 2) break;
 }
 
 $product_sql  .= "('$id','$model','$available'),";
 $prodesc_sql  .= "('$id','$name','$description'),";
 $proattr_sql  .= "('$id','1111','1','$guaperiod'),";
 $protocat_sql .= "('$id','$categoryId','1'),";
 $protost_sql  .= "('$id','0'),";
 $protola_sql  .= "('$id','0','0'),";

}

$product_sql  = rtrim($product_sql,',');
$prodesc_sql  = rtrim($prodesc_sql,',');
$proattr_sql  = rtrim($proattr_sql,',');
$protocat_sql = rtrim($protocat_sql,',');
$protost_sql  = rtrim($protost_sql,',');
$protola_sql  = rtrim($protola_sql,',');

// создать mysql подключение к БД через php

$link = mysqli_connect(DB_HOSTNAME, DB_USERNAME, DB_PASSWORD,DB_DATABASE)
or die('Не удалось соединиться: ' . mysqli_error());

$query = array();
$query[] = "INSERT IGNORE INTO ".DB_PREFIX."product (`product_id`,`model`,`status`) VALUES $product_sql";
$query[] = "INSERT IGNORE INTO ".DB_PREFIX."product_description (`product_id`,`name`,`description`) VALUES $prodesc_sql";
$query[] = "INSERT IGNORE INTO ".DB_PREFIX."product_attribute (`product_id`,`attribute_id`,`language_id`,`text`) VALUES $proattr_sql";
$query[] = "INSERT IGNORE INTO ".DB_PREFIX."attribute (`attribute_id`,`attribute_group_id`,`sort_order`) VALUES ('1111','1','1')";
$query[] = "INSERT IGNORE INTO ".DB_PREFIX."attribute_description (`attribute_id`,`language_id`,`name`) VALUES ('1111','1','Гарантия, срок (мес)')";
$query[] = "INSERT IGNORE INTO ".DB_PREFIX."product_to_category (`product_id`,`category_id`,`main_category`) VALUES $protocat_sql";
$query[] = "INSERT IGNORE INTO ".DB_PREFIX."product_to_store (`product_id`,`store_id`) VALUES $protost_sql";
$query[] = "INSERT IGNORE INTO ".DB_PREFIX."product_to_layout (`product_id`,`store_id`,`layout_id`) VALUES $protola_sql";


foreach ($query as $sql) {
 $result = mysqli_query($link,$sql);    
 if(!empty(mysqli_error($link))) {
     printf("Error: %s\n%s\n\n", mysqli_error($link), $sql);
     mysqli_close($link);
     die;
 }
}
mysqli_close($link);

Пример 5

class DB extends mysqli {
	public function __construct($host, $user, $pass, $db) {
		parent::init();
		if (!parent::options(MYSQLI_INIT_COMMAND, "SET AUTOCOMMIT = 1"))
			throw new Exception("MYSQLI_INIT_COMMAND Fail");
		if (!parent::options(MYSQLI_OPT_CONNECT_TIMEOUT, 5))
			throw new Exception("MYSQLI_OPT_CONNECT_TIMEOUT Fail");
		if (!parent::real_connect($host, $user, $pass, $db))
			throw new Exception("Connection ERROR. ".mysqli_connect_errno().": ".mysqli_connect_error());
		//Debug("MySQL connection established");
	}
	public function __destruct() {
		parent::close();
		//Debug("MySQL connection closed");
	}
}

function dbConnection() {
	$param = LocalQuery('paramlist', array());
	$result = $param->xpath('//elem/*');
	$param_map = array();
	$param_map['DBHost'] = 'localhost';
	$param_map['DBUser'] = 'root';
	$param_map['DBPassword'] = '';
	$param_map['DBName'] = 'billmgr';
	foreach ($result as $node) {
		$param_map[$node->getName()] = (string) $node;
	}
	$db = new DB($param_map['DBHost'], $param_map['DBUser'], $param_map['DBPassword'], $param_map['DBName']);
	$db->set_charset('utf8');
	return $db;
}