подключиться к 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; }