本文介绍了如何使用sql server运行grocery?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述



我使用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(" ","&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 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(" ", "&nbsp;", $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(" ", "&nbsp;", $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(" ", "&nbsp;", $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(" ", "&nbsp;", $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:

http://sourcezilla.com/articulos/programacion/como-realizar-un-crud-sencillo-en-php-y-codeigniter-desde-cero.html

Hope helps someone in the future.

这篇关于如何使用sql server运行grocery?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

10-25 02:32