问题描述
我使用codeigniter连接sqlsrv但是当我打开crud我收到:
我使用codeigniter运行在xampp php 5.6.8。 p> 错误号码:42000/156
[Microsoft] [SQL Server Native Client 10.0]关键字FROM附近的语法不正确。
显示`PERSONA_TEST`中的栏目
文件名:D:/Trabajo/xampp/htdocs/creditos/crud/application/models/grocery_crud_model.php
行号:431
我知道的是:
我有一个sql语句,不能使用sqlserver sintax。是否需要编辑grocery_crud_model?
谢谢!
我有一段时间,我使用sqlsrv。我编辑了grocery_crud_model.php
<?php
/ **
* PHP grocery CRUD
*
* LICENSE
*
* Grocery CRUD使用GPL v3(license-gpl3.txt)和MIT许可证(license-mit.txt) 。
*您不必做任何特殊的选择一个许可证或其他,您不必通知任何人您正在使用的许可证。
*有关这些许可证的详细信息,请参阅相应的许可证文件。
*您可以自由使用,修改和分发本软件,但所有版权信息必须保留。
*
* @package grocery CRUD
* @copyright版权所有(c)2010年至2012年,John Skoumbourdis
* @license https://github.com/scoumbourdis/grocery- crud / blob / master / license-grocery-crud.txt
* @version 1.4.2
* @author John Skoumbourdis< [email protected]>
* /
// ---------------------------------- --------------------------------------
/ **
* Grocery CRUD模型
*
*
* @package grocery CRUD
* @author John Skoumbourdis< [email protected]>
* @version 1.2
* @link http://www.grocerycrud.com/documentation
* /
class grocery_CRUD_Model extends CI_Model {
protected $ primary_key = null;
protected $ table_name = null;
protected $ relation = array();
protected $ relation_n_n = array();
protected $ primary_keys = array();
function __construct()
{
parent :: __ construct();
}
function db_table_exists($ table_name = null)
{
return $ this-> db-> table_exists($ table_name);
}
function get_list()
{
if($ this-> table_name === null)
return false;
$ select ={$ this-> table_name}。*;
// set_relation特殊查询
if(!empty($ this-> relation))
{
foreach($ this-> relation as $ relation )
{
list($ field_name,$ related_table,$ related_field_title)= $ relation;
$ unique_join_name = $ this-> _unique_join_name($ field_name);
$ unique_field_name = $ this-> _unique_field_name($ field_name);
if(strstr($ related_field_title,'{'))
{
$ related_field_title = str_replace(,& nbsp;,$ related_field_title);
$ select。=,CONCAT('。str_replace(array('{','}'),array(',COALESCE({$ unique_join_name}。, ),str_replace(',\\',$ related_field_title))')as $ unique_field_name;
}
else
{
$ select。=,$ unique_join_name。$ related_field_title AS $ unique_field_name;
}
if($ this-> field_exists($ related_field_title))
$ select。=,`{$ this-> table_name}`。$ related_field_title AS '{$ this-> table_name}。$ related_field_title';
}
}
// set_relation_n_n特殊查询。我们喜欢关于relationship_n_n的简单连接的子查询,因为它在大表上更快更稳定。
if(!empty($ this-> relation_n_n))
{
$ select = $ this-> relation_n_n_queries($ select);
}
$ this-> db-> select($ select,false);
$ results = $ this-> db-> get($ this-> table_name) - > result();
return $ results;
}
public function get_row($ table_name = null)
{
$ table_name = $ table_name === null? $ this-> table_name:$ table_name;
return $ this-> db-> get($ table_name) - > row();
}
public function set_primary_key($ field_name,$ table_name = null)
{
$ table_name = $ table_name === null? $ this-> table_name:$ table_name;
$ this-> primary_keys [$ table_name] = $ field_name;
}
protected function relation_n_n_queries($ select)
{
$ this_table_primary_key = $ this-> get_primary_key();
foreach($ this-> relation_n_n as $ relation_n_n)
{
list($ field_name,$ relation_table,$ selection_table,$ primary_key_alias_to_this_table,
$ primary_key_alias_to_selection_table,$ title_field_selection_table,$ priority_field_relation_table)= array_values((array)$ relation_n_n);
$ primary_key_selection_table = $ this-> get_primary_key($ selection_table);
$ field =;
$ use_template = strpos($ title_field_selection_table,'{')!== false;
$ field_name_hash = $ this-> _unique_field_name($ title_field_selection_table);
if($ use_template)
{
$ title_field_selection_table = str_replace(,& nbsp;,$ title_field_selection_table);
$ field。=CONCAT('。str_replace(array('{','}'),array(',COALESCE(,,' ,\\',$ title_field_selection_table))。');
}
else
{
$ field。=$ selection_table。$ title_field_selection_table;
}
//对不起Codeigniter,但你不能帮助我的子查询!
$ select。=,(SELECT GROUP_CONCAT(DISTINCT $ field)FROM $ selection_table
。LEFT JOIN $ relation_table ON $ relation_table。$ primary_key_alias_to_selection_table = $ selection_table $ primary_key_selection_table
。 WHERE $ relation_table。$ primary_key_alias_to_this_table =`{$ this-> table_name}`。$ this_table_primary_key GROUP BY $ relation_table。$ primary_key_alias_to_this_table)AS $ field_name;
}
return $ select;
}
function order_by($ order_by,$ direction)
{
$ this-> db-> order_by($ order_by,$ direction);
}
函数其中($ key,$ value = NULL,$ escape = TRUE)
{
$ this-> db-> where key,$ value,$ escape);
}
function or_where($ key,$ value = NULL,$ escape = TRUE)
{
$ this-> db-> or_where key,$ value,$ escape);
}
函数具有($ key,$ value = NULL,$ escape = TRUE)
{
$ this-> db-> having key,$ value,$ escape);
}
function or_having($ key,$ value = NULL,$ escape = TRUE)
{
$ this-> db-> or_having key,$ value,$ escape);
}
函数像($ field,$ match ='',$ side ='both')
{
$ this-> db-> like($ field,$ match,$ side);
}
函数or_like($ field,$ match ='',$ side ='both')
{
$ this-> db-> or_like($ field,$ match,$ side);
}
函数限制($ value,$ offset ='')
{
$ this-> db-> limit );
}
函数get_total_results()
{
// set_relation_n_n特殊查询。我们喜欢关于relationship_n_n的简单连接的子查询,因为它在大表上更快更稳定。
if(!empty($ this-> relation_n_n))
{
$ select ={$ this-> table_name}。*;
$ select = $ this-> relation_n_n_queries($ select);
$ this-> db-> select($ select,false);
}
return $ this-> db-> get($ this-> table_name) - > num_rows();
}
函数set_basic_table($ table_name = null)
{
if(!($ this-> db-> table_exists($ table_name)))
return false;
$ this-> table_name = $ table_name;
return true;
}
function get_edit_values($ primary_key_value)
{
$ primary_key_field = $ this-> get_primary_key();
$ this-> db->其中($ primary_key_field,$ primary_key_value);
$ result = $ this-> db-> get($ this-> table_name) - > row()
return $ result;
}
function join_relation($ field_name,$ related_table,$ related_field_title)
{
$ related_primary_key = $ this-> get_primary_key($ related_table);
if($ related_primary_key!== false)
{
$ unique_name = $ this-> _unique_join_name($ field_name);
$ this-> db-> join($ related_table。'as'。$ unique_name,$ unique_name。$ related_primary_key = {$ this-> table_name}。$ field_name,'left');
$ this-> relation [$ field_name] = array($ field_name,$ related_table,$ related_field_title);
return true;
}
return false;
}
function set_relation_n_n_field($ field_info)
{
$ this-> relation_n_n [$ field_info-> field_name] = $ field_info;
}
protected function _unique_join_name($ field_name)
{
return'j'.substr(md5($ field_name),0,8); //这个j是因为字符串以字母而不是数字开头更好
}
protected function _unique_field_name($ field_name)
{
return's'.substr(md5($ field_name),0,8); //这是因为更好的字符串以字母开头而不是数字
}
function get_relation_array($ field_name,$ related_table,$ related_field_title,$ where_clause,$ order_by,$ limit = null,$ search_like = null)
{
$ relation_array = array();
$ field_name_hash = $ this-> _unique_field_name($ field_name);
$ related_primary_key = $ this-> get_primary_key($ related_table);
$ select =$ related_table。$ related_primary_key;
if(strstr($ related_field_title,'{'))
{
$ related_field_title = str_replace(,& nbsp;,$ related_field_title);
$ select。=CONCAT('。str_replace(array('{','}'),array(',COALESCE(,,' ,\\',$ related_field_title))。')as $ field_name_hash;
}
else
{
$ select。=$ related_table. $ related_field_title as $ field_name_hash;
}
$ this-> db-> select($ select,false);
if($ where_clause!== null)
$ this-> db-> where($ where_clause);
if($ where_clause!== null)
$ this-> db-> where($ where_clause);
if($ limit!== null)
$ this-> db-> limit($ limit);
if($ search_like!== null)
$ this-> db-> having($ field_name_hash LIKE'%。$ this-> db-> escape_like_str ($ search_like)。%');
$ order_by!== null
? $ this-> db-> order_by($ order_by)
:$ this-> db-> order_by($ field_name_hash);
$ results = $ this-> db-> get($ related_table) - > result();
foreach($ results as $ row)
{
$ relation_array [$ row-> $ related_primary_key] = $ row-> $ field_name_hash;
}
return $ relation_array;
}
function get_ajax_relation_array($ search,$ field_name,$ related_table,$ related_field_title,$ where_clause,$ order_by)
{
return $ this-> get_relation_array ($ field_name,$ related_table,$ related_field_title,$ where_clause,$ order_by,10,$ search);
}
function get_relation_total_rows($ field_name,$ related_table,$ related_field_title,$ where_clause)
{
if($ where_clause!== null)
$ this-> db-> where($ where_clause);
return $ this-> db-> count_all_results($ related_table);
}
function get_relation_n_n_selection_array($ primary_key_value,$ field_info)
{
$ select =;
$ associated_field_title = $ field_info-> title_field_selection_table;
$ use_template = strpos($ related_field_title,'{')!== false ;;
$ field_name_hash = $ this-> _unique_field_name($ related_field_title);
if($ use_template)
{
$ related_field_title = str_replace(,& nbsp;,$ related_field_title);
$ select。=CONCAT('。str_replace(array('{','}'),array(',COALESCE(,,' ,\\',$ related_field_title))。')as $ field_name_hash;
}
else
{
$ select。=$ related_field_title as $ field_name_hash;
}
$ this-> db-> select('*,'。$ select,false);
$ selection_primary_key = $ this-> get_primary_key($ field_info-> selection_table);
if(empty($ field_info-> priority_field_relation_table))
{
if(!$ use_template){
$ this-> db-> order_by ({$ field_info-> selection_table}。{$ field_info-> title_field_selection_table});
}
}
else
{
$ this-> db-> order_by({$ field_info-> relation_table}。{$ field_info-> ; priority_field_relation_table});
}
$ this-> db->其中($ field_info-> primary_key_alias_to_this_table,$ primary_key_value);
$ this-> db-> join(
$ field_info-> selection_table,
{$ field_info-> relation_table}。{$ field_info-> primary_key_alias_to_selection_table} = { $ field_info-> selection_table}。{$ selection_primary_key}
);
$ results = $ this-> db-> get($ field_info-> relation_table) - > result();
$ results_array = array();
foreach($ results as $ row)
{
$ results_array [$ row-> {$ field_info-> primary_key_alias_to_selection_table}] = $ row-> {$ field_name_hash};
}
return $ results_array;
}
function get_relation_n_n_unselected_array($ field_info,$ selected_values)
{
$ use_where_clause =!empty($ field_info-> where_clause);
$ select =;
$ related_field_title = $ field_info-> title_field_selection_table;
$ use_template = strpos($ related_field_title,'{')!== false;
$ field_name_hash = $ this-> _unique_field_name($ related_field_title);
if($ use_template)
{
$ related_field_title = str_replace(,& nbsp;,$ related_field_title);
$ select。=CONCAT('。str_replace(array('{','}'),array(',COALESCE(,,' ,\\',$ related_field_title))。')as $ field_name_hash;
}
else
{
$ select。=$ related_field_title as $ field_name_hash;
}
$ this-> db-> select('*,'。$ select,false);
if($ use_where_clause){
$ this-> db-> where($ field_info-> where_clause);
}
$ selection_primary_key = $ this-> get_primary_key($ field_info-> selection_table);
if(!$ use_template)
$ this-> db-> order_by({$ field_info-> selection_table}。{$ field_info-> title_field_selection_table});
$ results = $ this-> db-> get($ field_info-> selection_table) - > result();
$ results_array = array();
foreach($ results as $ row)
{
if(!isset($ selected_values [$ row-> $ selection_primary_key]))
$ results_array [$ row-> ; $ selection_primary_key] = $ row-> {$ field_name_hash};
}
return $ results_array;
}
函数db_relation_n_n_update($ field_info,$ post_data,$ main_primary_key)
{
$ this-> db-> where($ field_info-> primary_key_alias_to_this_table,$ main_primary_key);
if(!empty($ post_data))
$ this-> db-> where_not_in($ field_info-> primary_key_alias_to_selection_table,$ post_data);
$ this-> db-> delete($ field_info-> relation_table);
$ counter = 0;
if(!empty($ post_data))
{
foreach($ post_data as $ primary_key_value)
{
$ where_array = array(
$ field_info - > primary_key_alias_to_this_table => $ main_primary_key,
$ field_info-> primary_key_alias_to_selection_table => $ primary_key_value,
);
$ this-> db-> where($ where_array);
$ count = $ this-> db-> from($ field_info-> relation_table) - > count_all_results();
if($ count == 0)
{
if(!empty($ field_info-> priority_field_relation_table))
$ where_array [$ field_info-> priority_field_relation_table] = $ counter;
$ this-> db-> insert($ field_info-> relation_table,$ where_array);
} elseif($ count> = 1&&!empty($ field_info-> priority_field_relation_table))
{
$ this-> db-> update($ field_info-> relation_table,array($ field_info-> priority_field_relation_table => $ counter),$where_array);
}
$ counter ++;
}
}
}
函数db_relation_n_n_delete($ field_info,$ main_primary_key)
{
$ this-> db-> where($ field_info-> primary_key_alias_to_this_table,$ main_primary_key);
$ this-> db-> delete($ field_info-> relation_table);
}
function get_field_types_basic_table()
{
$ db_field_types = array();
foreach($ this-> db-> field_data($ this-> table_name)as $ db_field_type)
{
$ type = explode( $ db_field_type-> type);
$ db_type = $ type [0];
if(isset($ type [1]))
{
if (substr($ type [1], - 1)==')')
{
$ length = substr($ type [1],0,-1);
}
else
{
list($ length)= explode(,$ type [1]);
$ length = substr($ length,0,-1);
}
}
else
{
$ length ='';
}
$ db_field_types [$ db_field_type-> name] ['db_max_length'] = $ length;
$ db_field_types [$ db_field_type-> name] ['db_type'] = $ db_type;
$ db_field_types [$ db_field_type-> name] ['db_null'] = $ db_field_type-> default =='null'?真假;
}
$ results = $ this-> db-> field_data($ this-> table_name);
foreach($ results as $ num => $ row)
{
$ row =(array)$ row;
$ results [$ num] =(object)(array_merge($ row,$ db_field_types [$ row ['name']]))
}
return $ results;
}
function get_field_types($ table_name)
{
$ results = $ this-> db-> field_data($ table_name);
return $ results;
}
函数db_update($ post_array,$ primary_key_value)
{
$ primary_key_field = $ this-> get_primary_key
return $ this-> db-> update($ this-> table_name,$ post_array,array($ primary_key_field => $ primary_key_value));
}
function db_insert($ post_array)
{
$ insert = $ this-> db-> insert($ this-> table_name,$ post_array);
if($ insert)
{
return $ this-> db-> insert_id();
}
return false;
}
function db_delete($ primary_key_value)
{
$ primary_key_field = $ this-> get_primary_key
if($ primary_key_field === false)
return false;
$ this-> db-> delete($ this-> table_name,array($ primary_key_field => $ primary_key_value));
if($ this-> db-> affected_rows()!= 1)
return false;
else
return true;
}
function db_file_delete($ field_name,$ filename)
{
if($ this-> db-> update($ this-> table_name ,array($ field_name =>''),array($ field_name => $ filename)))
{
return true;
}
else
{
return false;
}
}
function field_exists($ field,$ table_name = null)
{
if(empty($ table_name))
{
$ table_name = $ this-> table_name;
}
return $ this-> db-> field_exists($ field,$ table_name);
}
function get_primary_key($ table_name = null)
{
if($ table_name == null)
{
if ($ this-> primary_keys [$ this-> table_name]))
{
return $ this-> primary_keys [$ this-> table_name];
}
if(empty($ this-> primary_key))
{
$ fields = $ this-> get_field_types_basic_table();
foreach($ fields as $ field)
{
if($ field-> primary_key == 1)
{
return $ field- > name;
}
}
return false;
}
else
{
return $ this-> primary_key;
}
}
else
{
if(isset($ this-> primary_keys [$ table_name]))
{
return $ this-> primary_keys [$ table_name];
}
$ fields = $ this-> get_field_types($ table_name);
foreach($ fields as $ field)
{
if($ field-> primary_key == 1)
{
return $ field- > name;
}
}
return false;
}
}
函数escape_str($ value)
{
return $ this-> db-> escape_str值);
}
}
p>
$ active_group ='default';
$ query_builder = TRUE;
$ db ['default'] ['hostname'] ='xxx.xxx.xxx.xxx,11433';
$ db ['default'] ['username'] ='xxxx'; // usuario por defecto del SQL
$ db ['default'] ['password'] ='xxxx'; //Contaseñadel usuario
$ db ['default'] ['database'] ='xxxx'; // Base de datos que cree
$ db ['default'] ['dbdriver'] ='sqlsrv';
$ db ['default'] ['dbprefix'] ='';
$ db ['default'] ['pconnect'] = FALSE;
$ db ['default'] ['db_debug'] = TRUE;
$ db ['default'] ['cache_on'] = FALSE;
$ db ['default'] ['cachedir'] ='';
$ db ['default'] ['char_set'] =utf8;
$ db ['default'] ['dbcollat'] =utf8_general_ci;
$ db ['default'] ['swap_pre'] ='';
$ db ['default'] ['autoinit'] = TRUE;
$ db ['default'] ['stricton'] = FALSE;
这里是控制器:(需要指定主键)
<?php if(!defined('BASEPATH'))exit('不允许直接脚本访问);
/ * Heredamos de la clase CI_Controller * /
class Personas extends CI_Controller {
function __construct()
{
parent :: __构造();
/ * Cargamos la base de datos * /
$ this-> load-> database();
/ * Cargamos la libreria * /
$ this-> load-> library('grocery_crud');
/ *Añadimosel helper al controlador * /
$ this-> load-> helper('url');
}
function index()
{
/ *
* Mandamos todo lo que llegue a la funcion
* administracion
** /
redirect('personas / administracion');
}
/ *
*
** /
function administracion()
{
try {
/ * Creamos el objeto * /
$ crud = new grocery_CRUD();
/ * Seleccionamos el tema * /
$ crud-> set_theme('flexigrid');
/ * Seleccionmos el nombre de la tabla de nuestra base de datos * /
$ crud-> set_table('PERSONA_TEST');
/ * Le asignamos un nombre * /
$ crud-> set_subject('personas');
/ * Asignamos el idiomaespañol* /
$ crud-> set_language('spanish');
/ * Aqui le decimos a grocery que estos campos son obligatorios * /
$ crud-> required_fields(
'ID',
'NOMBRE',
'APELLIDO'
);
/ * Aqui le indicamos que campos deseamos mostrar * /
$ crud-> columns(
'ID',
'NOMBRE',
'APELLIDO'
);
$ crud-> set_primary_key('ID','PERSONA_TEST');
/ * Generamos la tabla * /
$ output = $ crud-> render();
/ * La cargamos en la vista factada en
/applications/views/productos/administracion.php * /
$ this-> load-> view('personas / administracion',$ output);
} catch(Exception $ e){
/ * Si algo sale mal cachamos el error y lo mostramos * /
show_error($ e-> getMessage()。 ---'。$ e-> getTraceAsString());
}
}
}
p>
<!DOCTYPE html>
< html>
< head>
< meta charset =utf-8/>
< title> Administracion Personas< / title>
<?php
foreach($ css_files as $ file):?>
< link type =text / css =stylesheethref =<?php echo $ file;?> />
<?php endforeach; ?>
<?php foreach($ js_files as $ file):?>
< script src =<??php echo $ file;?>>< / script>
<?php endforeach; ?>
< style type ='text / css'>
body
{
font-family:Arial;
font-size:14px;
}
a {
color:blue;
text-decoration:none;
font-size:14px;
}
a:hover
{
text-decoration:underline;
}
< / style>
< / head>
< body>
< h1>行政管理< / h1>
< div>
<?php echo $ output; ?>
< / div>
< / body>
< / html>
此外,我使用本教程的crud:
希望在未来帮助某人。
I have grocery crud with codeigniter running in xampp with php 5.6.8.
I made codeigniter connect with sqlsrv but when i open the crud i receive:
Error Number: 42000/156
[Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near the keyword 'FROM'.
SHOW COLUMNS FROM `PERSONA_TEST`
Filename: D:/Trabajo/xampp/htdocs/creditos/crud/application/models/grocery_crud_model.php
Line Number: 431
What i know:
I have a sql statement that's not made to work with sqlserver sintax. Should I need to edit grocery_crud_model?
Thanks!
After a bit of time i made to use sqlsrv. I edited the grocery_crud_model.php
<?php
/**
* PHP grocery CRUD
*
* LICENSE
*
* Grocery CRUD is released with dual licensing, using the GPL v3 (license-gpl3.txt) and the MIT license (license-mit.txt).
* You don't have to do anything special to choose one license or the other and you don't have to notify anyone which license you are using.
* Please see the corresponding license file for details of these licenses.
* You are free to use, modify and distribute this software, but all copyright information must remain.
*
* @package grocery CRUD
* @copyright Copyright (c) 2010 through 2012, John Skoumbourdis
* @license https://github.com/scoumbourdis/grocery-crud/blob/master/license-grocery-crud.txt
* @version 1.4.2
* @author John Skoumbourdis <[email protected]>
*/
// ------------------------------------------------------------------------
/**
* Grocery CRUD Model
*
*
* @package grocery CRUD
* @author John Skoumbourdis <[email protected]>
* @version 1.2
* @link http://www.grocerycrud.com/documentation
*/
class grocery_CRUD_Model extends CI_Model {
protected $primary_key = null;
protected $table_name = null;
protected $relation = array();
protected $relation_n_n = array();
protected $primary_keys = array();
function __construct()
{
parent::__construct();
}
function db_table_exists($table_name = null)
{
return $this->db->table_exists($table_name);
}
function get_list()
{
if($this->table_name === null)
return false;
$select = "{$this->table_name}.*";
//set_relation special queries
if(!empty($this->relation))
{
foreach($this->relation as $relation)
{
list($field_name , $related_table , $related_field_title) = $relation;
$unique_join_name = $this->_unique_join_name($field_name);
$unique_field_name = $this->_unique_field_name($field_name);
if(strstr($related_field_title,'{'))
{
$related_field_title = str_replace(" "," ",$related_field_title);
$select .= ", CONCAT('".str_replace(array('{','}'),array("',COALESCE({$unique_join_name}.",", ''),'"),str_replace("'","\\'",$related_field_title))."') as $unique_field_name";
}
else
{
$select .= ", $unique_join_name.$related_field_title AS $unique_field_name";
}
if($this->field_exists($related_field_title))
$select .= ", `{$this->table_name}`.$related_field_title AS '{$this->table_name}.$related_field_title'";
}
}
//set_relation_n_n special queries. We prefer sub queries from a simple join for the relation_n_n as it is faster and more stable on big tables.
if(!empty($this->relation_n_n))
{
$select = $this->relation_n_n_queries($select);
}
$this->db->select($select, false);
$results = $this->db->get($this->table_name)->result();
return $results;
}
public function get_row($table_name = null)
{
$table_name = $table_name === null ? $this->table_name : $table_name;
return $this->db->get($table_name)->row();
}
public function set_primary_key($field_name, $table_name = null)
{
$table_name = $table_name === null ? $this->table_name : $table_name;
$this->primary_keys[$table_name] = $field_name;
}
protected function relation_n_n_queries($select)
{
$this_table_primary_key = $this->get_primary_key();
foreach($this->relation_n_n as $relation_n_n)
{
list($field_name, $relation_table, $selection_table, $primary_key_alias_to_this_table,
$primary_key_alias_to_selection_table, $title_field_selection_table, $priority_field_relation_table) = array_values((array)$relation_n_n);
$primary_key_selection_table = $this->get_primary_key($selection_table);
$field = "";
$use_template = strpos($title_field_selection_table,'{') !== false;
$field_name_hash = $this->_unique_field_name($title_field_selection_table);
if($use_template)
{
$title_field_selection_table = str_replace(" ", " ", $title_field_selection_table);
$field .= "CONCAT('".str_replace(array('{','}'),array("',COALESCE(",", ''),'"),str_replace("'","\\'",$title_field_selection_table))."')";
}
else
{
$field .= "$selection_table.$title_field_selection_table";
}
//Sorry Codeigniter but you cannot help me with the subquery!
$select .= ", (SELECT GROUP_CONCAT(DISTINCT $field) FROM $selection_table "
."LEFT JOIN $relation_table ON $relation_table.$primary_key_alias_to_selection_table = $selection_table.$primary_key_selection_table "
."WHERE $relation_table.$primary_key_alias_to_this_table = `{$this->table_name}`.$this_table_primary_key GROUP BY $relation_table.$primary_key_alias_to_this_table) AS $field_name";
}
return $select;
}
function order_by($order_by , $direction)
{
$this->db->order_by( $order_by , $direction );
}
function where($key, $value = NULL, $escape = TRUE)
{
$this->db->where( $key, $value, $escape);
}
function or_where($key, $value = NULL, $escape = TRUE)
{
$this->db->or_where( $key, $value, $escape);
}
function having($key, $value = NULL, $escape = TRUE)
{
$this->db->having( $key, $value, $escape);
}
function or_having($key, $value = NULL, $escape = TRUE)
{
$this->db->or_having( $key, $value, $escape);
}
function like($field, $match = '', $side = 'both')
{
$this->db->like($field, $match, $side);
}
function or_like($field, $match = '', $side = 'both')
{
$this->db->or_like($field, $match, $side);
}
function limit($value, $offset = '')
{
$this->db->limit( $value , $offset );
}
function get_total_results()
{
//set_relation_n_n special queries. We prefer sub queries from a simple join for the relation_n_n as it is faster and more stable on big tables.
if(!empty($this->relation_n_n))
{
$select = "{$this->table_name}.*";
$select = $this->relation_n_n_queries($select);
$this->db->select($select,false);
}
return $this->db->get($this->table_name)->num_rows();
}
function set_basic_table($table_name = null)
{
if( !($this->db->table_exists($table_name)) )
return false;
$this->table_name = $table_name;
return true;
}
function get_edit_values($primary_key_value)
{
$primary_key_field = $this->get_primary_key();
$this->db->where($primary_key_field,$primary_key_value);
$result = $this->db->get($this->table_name)->row();
return $result;
}
function join_relation($field_name , $related_table , $related_field_title)
{
$related_primary_key = $this->get_primary_key($related_table);
if($related_primary_key !== false)
{
$unique_name = $this->_unique_join_name($field_name);
$this->db->join( $related_table.' as '.$unique_name , "$unique_name.$related_primary_key = {$this->table_name}.$field_name",'left');
$this->relation[$field_name] = array($field_name , $related_table , $related_field_title);
return true;
}
return false;
}
function set_relation_n_n_field($field_info)
{
$this->relation_n_n[$field_info->field_name] = $field_info;
}
protected function _unique_join_name($field_name)
{
return 'j'.substr(md5($field_name),0,8); //This j is because is better for a string to begin with a letter and not with a number
}
protected function _unique_field_name($field_name)
{
return 's'.substr(md5($field_name),0,8); //This s is because is better for a string to begin with a letter and not with a number
}
function get_relation_array($field_name , $related_table , $related_field_title, $where_clause, $order_by, $limit = null, $search_like = null)
{
$relation_array = array();
$field_name_hash = $this->_unique_field_name($field_name);
$related_primary_key = $this->get_primary_key($related_table);
$select = "$related_table.$related_primary_key, ";
if(strstr($related_field_title,'{'))
{
$related_field_title = str_replace(" ", " ", $related_field_title);
$select .= "CONCAT('".str_replace(array('{','}'),array("',COALESCE(",", ''),'"),str_replace("'","\\'",$related_field_title))."') as $field_name_hash";
}
else
{
$select .= "$related_table.$related_field_title as $field_name_hash";
}
$this->db->select($select,false);
if($where_clause !== null)
$this->db->where($where_clause);
if($where_clause !== null)
$this->db->where($where_clause);
if($limit !== null)
$this->db->limit($limit);
if($search_like !== null)
$this->db->having("$field_name_hash LIKE '%".$this->db->escape_like_str($search_like)."%'");
$order_by !== null
? $this->db->order_by($order_by)
: $this->db->order_by($field_name_hash);
$results = $this->db->get($related_table)->result();
foreach($results as $row)
{
$relation_array[$row->$related_primary_key] = $row->$field_name_hash;
}
return $relation_array;
}
function get_ajax_relation_array($search, $field_name , $related_table , $related_field_title, $where_clause, $order_by)
{
return $this->get_relation_array($field_name , $related_table , $related_field_title, $where_clause, $order_by, 10 , $search);
}
function get_relation_total_rows($field_name , $related_table , $related_field_title, $where_clause)
{
if($where_clause !== null)
$this->db->where($where_clause);
return $this->db->count_all_results($related_table);
}
function get_relation_n_n_selection_array($primary_key_value, $field_info)
{
$select = "";
$related_field_title = $field_info->title_field_selection_table;
$use_template = strpos($related_field_title,'{') !== false;;
$field_name_hash = $this->_unique_field_name($related_field_title);
if($use_template)
{
$related_field_title = str_replace(" ", " ", $related_field_title);
$select .= "CONCAT('".str_replace(array('{','}'),array("',COALESCE(",", ''),'"),str_replace("'","\\'",$related_field_title))."') as $field_name_hash";
}
else
{
$select .= "$related_field_title as $field_name_hash";
}
$this->db->select('*, '.$select,false);
$selection_primary_key = $this->get_primary_key($field_info->selection_table);
if(empty($field_info->priority_field_relation_table))
{
if(!$use_template){
$this->db->order_by("{$field_info->selection_table}.{$field_info->title_field_selection_table}");
}
}
else
{
$this->db->order_by("{$field_info->relation_table}.{$field_info->priority_field_relation_table}");
}
$this->db->where($field_info->primary_key_alias_to_this_table, $primary_key_value);
$this->db->join(
$field_info->selection_table,
"{$field_info->relation_table}.{$field_info->primary_key_alias_to_selection_table} = {$field_info->selection_table}.{$selection_primary_key}"
);
$results = $this->db->get($field_info->relation_table)->result();
$results_array = array();
foreach($results as $row)
{
$results_array[$row->{$field_info->primary_key_alias_to_selection_table}] = $row->{$field_name_hash};
}
return $results_array;
}
function get_relation_n_n_unselected_array($field_info, $selected_values)
{
$use_where_clause = !empty($field_info->where_clause);
$select = "";
$related_field_title = $field_info->title_field_selection_table;
$use_template = strpos($related_field_title,'{') !== false;
$field_name_hash = $this->_unique_field_name($related_field_title);
if($use_template)
{
$related_field_title = str_replace(" ", " ", $related_field_title);
$select .= "CONCAT('".str_replace(array('{','}'),array("',COALESCE(",", ''),'"),str_replace("'","\\'",$related_field_title))."') as $field_name_hash";
}
else
{
$select .= "$related_field_title as $field_name_hash";
}
$this->db->select('*, '.$select,false);
if($use_where_clause){
$this->db->where($field_info->where_clause);
}
$selection_primary_key = $this->get_primary_key($field_info->selection_table);
if(!$use_template)
$this->db->order_by("{$field_info->selection_table}.{$field_info->title_field_selection_table}");
$results = $this->db->get($field_info->selection_table)->result();
$results_array = array();
foreach($results as $row)
{
if(!isset($selected_values[$row->$selection_primary_key]))
$results_array[$row->$selection_primary_key] = $row->{$field_name_hash};
}
return $results_array;
}
function db_relation_n_n_update($field_info, $post_data ,$main_primary_key)
{
$this->db->where($field_info->primary_key_alias_to_this_table, $main_primary_key);
if(!empty($post_data))
$this->db->where_not_in($field_info->primary_key_alias_to_selection_table , $post_data);
$this->db->delete($field_info->relation_table);
$counter = 0;
if(!empty($post_data))
{
foreach($post_data as $primary_key_value)
{
$where_array = array(
$field_info->primary_key_alias_to_this_table => $main_primary_key,
$field_info->primary_key_alias_to_selection_table => $primary_key_value,
);
$this->db->where($where_array);
$count = $this->db->from($field_info->relation_table)->count_all_results();
if($count == 0)
{
if(!empty($field_info->priority_field_relation_table))
$where_array[$field_info->priority_field_relation_table] = $counter;
$this->db->insert($field_info->relation_table, $where_array);
}elseif($count >= 1 && !empty($field_info->priority_field_relation_table))
{
$this->db->update( $field_info->relation_table, array($field_info->priority_field_relation_table => $counter) , $where_array);
}
$counter++;
}
}
}
function db_relation_n_n_delete($field_info, $main_primary_key)
{
$this->db->where($field_info->primary_key_alias_to_this_table, $main_primary_key);
$this->db->delete($field_info->relation_table);
}
function get_field_types_basic_table()
{
$db_field_types = array();
foreach($this->db->field_data($this->table_name) as $db_field_type)
{
$type = explode("(",$db_field_type->type);
$db_type = $type[0];
if(isset($type[1]))
{
if(substr($type[1],-1) == ')')
{
$length = substr($type[1],0,-1);
}
else
{
list($length) = explode(" ",$type[1]);
$length = substr($length,0,-1);
}
}
else
{
$length = '';
}
$db_field_types[$db_field_type->name]['db_max_length'] = $length;
$db_field_types[$db_field_type->name]['db_type'] = $db_type;
$db_field_types[$db_field_type->name]['db_null'] = $db_field_type->default == 'null' ? true : false;
}
$results = $this->db->field_data($this->table_name);
foreach($results as $num => $row)
{
$row = (array)$row;
$results[$num] = (object)( array_merge($row, $db_field_types[$row['name']]) );
}
return $results;
}
function get_field_types($table_name)
{
$results = $this->db->field_data($table_name);
return $results;
}
function db_update($post_array, $primary_key_value)
{
$primary_key_field = $this->get_primary_key();
return $this->db->update($this->table_name,$post_array, array( $primary_key_field => $primary_key_value));
}
function db_insert($post_array)
{
$insert = $this->db->insert($this->table_name,$post_array);
if($insert)
{
return $this->db->insert_id();
}
return false;
}
function db_delete($primary_key_value)
{
$primary_key_field = $this->get_primary_key();
if($primary_key_field === false)
return false;
$this->db->delete($this->table_name,array( $primary_key_field => $primary_key_value));
if( $this->db->affected_rows() != 1)
return false;
else
return true;
}
function db_file_delete($field_name, $filename)
{
if( $this->db->update($this->table_name,array($field_name => ''),array($field_name => $filename)) )
{
return true;
}
else
{
return false;
}
}
function field_exists($field,$table_name = null)
{
if(empty($table_name))
{
$table_name = $this->table_name;
}
return $this->db->field_exists($field,$table_name);
}
function get_primary_key($table_name = null)
{
if($table_name == null)
{
if(isset($this->primary_keys[$this->table_name]))
{
return $this->primary_keys[$this->table_name];
}
if(empty($this->primary_key))
{
$fields = $this->get_field_types_basic_table();
foreach($fields as $field)
{
if($field->primary_key == 1)
{
return $field->name;
}
}
return false;
}
else
{
return $this->primary_key;
}
}
else
{
if(isset($this->primary_keys[$table_name]))
{
return $this->primary_keys[$table_name];
}
$fields = $this->get_field_types($table_name);
foreach($fields as $field)
{
if($field->primary_key == 1)
{
return $field->name;
}
}
return false;
}
}
function escape_str($value)
{
return $this->db->escape_str($value);
}
}
Here is the connection :
$active_group = 'default';
$query_builder = TRUE;
$db['default']['hostname'] = 'xxx.xxx.xxx.xxx,11433';
$db['default']['username'] = 'xxxx'; // usuario por defecto del SQL
$db['default']['password'] = 'xxxx'; //Contaseña del usuario
$db['default']['database'] = 'xxxx'; // Base de datos que cree
$db['default']['dbdriver'] = 'sqlsrv';
$db['default']['dbprefix'] = '';
$db['default']['pconnect'] = FALSE;
$db['default']['db_debug'] = TRUE;
$db['default']['cache_on'] = FALSE;
$db['default']['cachedir'] = '';
$db['default']['char_set'] = "utf8";
$db['default']['dbcollat'] = "utf8_general_ci";
$db['default']['swap_pre'] = '';
$db['default']['autoinit'] = TRUE;
$db['default']['stricton'] = FALSE;
Here is the controller: (need to specify primary key)
<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
/* Heredamos de la clase CI_Controller */
class Personas extends CI_Controller {
function __construct()
{
parent::__construct();
/* Cargamos la base de datos */
$this->load->database();
/* Cargamos la libreria*/
$this->load->library('grocery_crud');
/* Añadimos el helper al controlador */
$this->load->helper('url');
}
function index()
{
/*
* Mandamos todo lo que llegue a la funcion
* administracion().
**/
redirect('personas/administracion');
}
/*
*
**/
function administracion()
{
try{
/* Creamos el objeto */
$crud = new grocery_CRUD();
/* Seleccionamos el tema */
$crud->set_theme('flexigrid');
/* Seleccionmos el nombre de la tabla de nuestra base de datos*/
$crud->set_table('PERSONA_TEST');
/* Le asignamos un nombre */
$crud->set_subject('personas');
/* Asignamos el idioma español */
$crud->set_language('spanish');
/* Aqui le decimos a grocery que estos campos son obligatorios */
$crud->required_fields(
'ID',
'NOMBRE',
'APELLIDO'
);
/* Aqui le indicamos que campos deseamos mostrar */
$crud->columns(
'ID',
'NOMBRE',
'APELLIDO'
);
$crud->set_primary_key('ID','PERSONA_TEST');
/* Generamos la tabla */
$output = $crud->render();
/* La cargamos en la vista situada en
/applications/views/productos/administracion.php */
$this->load->view('personas/administracion', $output);
}catch(Exception $e){
/* Si algo sale mal cachamos el error y lo mostramos */
show_error($e->getMessage().' --- '.$e->getTraceAsString());
}
}
}
And the view:
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<title>Administracion Personas</title>
<?php
foreach($css_files as $file): ?>
<link type="text/css" rel="stylesheet" href="<?php echo $file; ?>" />
<?php endforeach; ?>
<?php foreach($js_files as $file): ?>
<script src="<?php echo $file; ?>"></script>
<?php endforeach; ?>
<style type='text/css'>
body
{
font-family: Arial;
font-size: 14px;
}
a {
color: blue;
text-decoration: none;
font-size: 14px;
}
a:hover
{
text-decoration: underline;
}
</style>
</head>
<body>
<h1>Administración de Personas</h1>
<div>
<?php echo $output; ?>
</div>
</body>
</html>
Also i used this tutorial for the crud:
Hope helps someone in the future.
这篇关于如何使用sql server运行grocery?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!