webismymind / editablegrid-mysql-example Goto Github PK

I've stored image links on MySQL database. Is there a way they can be displayed in the grid?

Wonderful software. Thank you.
I notice that the message line does not display in this mysql-example. It does in the 'full example' in the full master. I found that I could not work out how to activate or have the message to display. It could be useful.

Kindly help me in implementing Filter and Charts !

I've managed to add an Input mask in the add window. But when it comes to update a record row it doesn't work (I'm using jquery.inputmask.js plugin)

Hi
I try to get data from another table into one column:

function fetch_d($mysqli,$query){
if (!($res = $mysqli->query($query)))return FALSE;
$row = $res->fetch_array();
return $row['email'];
}

$grid->addColumn('email', 'email', 'string' , fetch_d($mysqli,'SELECT email FROM contactinfo WHERE id=1'),true);

But it's not working. Could you help me?

I have added my own table and most values update with no problem. But unfortunately the datetime fields will not update. They display fine, but on updating they turn red and return to their initial state.

How can I debug this error? echoing the query doesn't seem to work, so I have no clue what is going wrong.
Can it be caused by these lines in update.php:
$date_info = date_parse_from_format('d/m/Y', $value); $value = "{$date_info['year']}-{$date_info['month']}-{$date_info['day']}";

They seem to fine, they pick up the date from the vield and then write in y-m-d to database.
Unfortunately can't see an error message or a message in the log files.

Hope to hear from you :)

just like the name say, i like to know if there is a way to do this with a bucle for and database, like
for (content)
{
grid->
}

There is a little issue with the grid that it displays number as double, For example when the id is 1000 it displays the value as 1.000 though the datatype is integer. I have checked the code but unable to find the correct one.

Hi!

I use a database that has a date column, and I want to highlight the row that contains a date that is previous to the current date.
Is it possible to permanently highlight a row with value match (like: if it's less than X, then mark yellow)? (I only found the error and succes highlights)

Thanks

Hi, When I try update any field i view red light, and field doesn't update in BBDD.

EDIT: I have find the problem (i was using a custom database). I remade update.php and works fine.

I've nginx, php-fpm, and mariadb set up for trying EditableGrid out.

I have imported demo.sql and filled config.php with the database credentials.

I get the following page when I open localhost/index.html in the browser:

Please help me with that.

My table name changes and i need it as a variable and currently have it in a php session variable. However, i cant figure out how to get my php session variable to the demo.js file to define the table name. Any ideas?

hidden is set to true, the column is getting displayed, no hidden column
how modify editablegid.js or how fix this error

I have a DB which Im editing with this but one of the tables doesnt work and I really don't know why, the one that works is biologicos and fotografias doesn't

When I tried to run the example, the xml produced stopped after "MetaData". The example produced the header portion but there was no data. The database was set up correctly. I noticed that the EditableGrid.php packaged with the example was different than the one in the base release so I replaced the EditableGrid.php in the example with the one in the latest release and it now works.

This is a great package and I really appreciate all the work that went into it. Thanks...

In your demo, the filter only works on name and firstname. If I want to find all rows with Brazil, which is the country column, then how do I set this up?

Hello,
i try to use datepicker but when i modify the date, it doesn't run.
Date stays the same than before and i have an error red colored..

how can i correct this ?

thank you

hello,
in the add form, i would like to add the way to upload a file (jpeg...)
How can i do this ?

thanks

1. Is which css file is the column header position changed. I'm trying to get the column headers centered but nothing I've tried is working.

2. Is it possible to only have the Action column show up when a user is logged in? I wasn't sure how it would affect the xml etc. Same goes for editing. I'm wanting the data to be view-able to everything but only admin's can edit/delete etc.

3. Is it possible to hide the column if a user isn't logged in. I don't want all users to be able to delete rows, just ones logged in or logged in as an admin user etc.I'm not sure how to hide that since the column is rendered in js. I kind of faked it so that a non-logged in user can't delete but they still still the icon and column. I have this in the loaddata.php

if (!isset($_SESSION['user_id'])) {
$grid->addColumn('action', '', '', NULL, false, '');
} else {
$grid->addColumn('action', 'Del Action', 'html', NULL, false, 'id');
}

4. Also I see from other posts that it sounds like you may still be working on this project. If that's the case are you planing on adding "rows per page" functionality?

Awesome work by the way.

I would like to understand how to parameterize the query goes to populate the combobox with the name of my column Tg_Filter.

For I would that the query was

select Tg_Filter, VALUE from T_TG WHERE Tg_Filter = ????

Where Is That ???? is equivalent to the field Tg_Filter table T_Main

How can I do?

Table structure

T_MAIN

Id integer autoincrement
FullName string
Tg_Filter integer

T_TG
Id integer autoincrement
Tg_Filter integer
Value string

T_Main

1;Francesco;1
2;Andrea;2
3;Sara;2
4;Carmen;2

T_TG
1;1;stringa1
2;1;stringa2
3;2;stringa3
4;2;stringa4

My Code

// create a new EditableGrid object
$grid = new EditableGrid();
$db_tablename = (isset($_GET['db_tablename'])) ? $_GET['db_tablename'] : 'T_UTENTI';

$connection = new PDO($str_conn,$config['db_user'],$config['db_password']);
/* 
*  Add columns. The first argument of addColumn is the name of the field in the databse. 
*  The second argument is the label that will be displayed in the header
*/

$grid->addColumn('ID', 'MyID', 'integer', NULL, false);     
$grid->addColumn('FULLNAME', 'MyFullName', 'string');  
$grid->addColumn('Tg_Filter', 'MyFilter', 'string',fetch_pairs($connection,'select Tg_Filter, VALUE from T_TG WHERE Tg_Filter=???? '),true);  
$grid->addColumn('action', 'ACTION', 'html', NULL, false, 'ID');


$app=$connection->prepare('SELECT * FROM T_Main');
$app->execute();

$result = $app->fetchAll();

$connection = null;

// send data to the browser
$grid->renderXML($result);

Currently trying to figure out how to modify an integer column so that it does not display decimal points.
For example one of my columns consists of 7 digit numbers and currently is being displayed as 5.014.546. How can I change this so that it is displayed without the decimal points?

I am trying to test the editablegrid but nothing is returning from EditableGrid.php. After the foreach in the getPOJO function something is going wrong and loses all the variables. So I get an empty page.

I am using the demo table from the example. The php version is 5.6

Hi there!
I cannot update my table when i change information. The row highlighted red and not update the information

update.php

<?php

/*
 * 
 * This file is part of EditableGrid.
 * http://editablegrid.net
 *
 * Copyright (c) 2011 Webismymind SPRL
 * Dual licensed under the MIT or GPL Version 2 licenses.
 * http://editablegrid.net/license
 */

require_once('config.php');         

// Database connection                                   
$mysqli = mysqli_init();
$mysqli->options(MYSQLI_OPT_CONNECT_TIMEOUT, 5);
$mysqli->real_connect($config['db_host'],$config['db_user'],$config['db_password'],$config['db_name']); 

// Get all parameters provided by the javascript
$colname = $mysqli->real_escape_string(strip_tags($_POST['colname']));
$id = $mysqli->real_escape_string(strip_tags($_POST['id']));
$coltype = $mysqli->real_escape_string(strip_tags($_POST['coltype']));
$value = $mysqli->real_escape_string(strip_tags($_POST['newvalue']));
$tablename = $mysqli->real_escape_string(strip_tags($_POST['tablename']));



// This very generic. So this script can be used to update several tables.
$return=false;
if ( $stmt = $mysqli->prepare("UPDATE $tablename SET $colname = $value WHERE idoferta = $id")) {
    $return = $stmt->execute();
    $stmt->close();

}             
$mysqli->close();        

echo $return ? "ok" : "error";

If you need anything else, let me know.
Thank You for the help ;)

Added Editablegrid to an exsisting php project. When logged in as admin the member can edit the grid and the dropdown fields show the value . but when log out and an normal user views the page (non editable). the php-Mysql display show the ID # where the value should be . how to get grid to save the value not the ID in mysql from a dropdown menu?

This is the Grid showing the value

the loaddata code

<?php     
session_start();

/*
 * examples/mysql/loaddata.php
 * 
 * This file is part of EditableGrid.
 * http://editablegrid.net
 *
 * Copyright (c) 2011 Webismymind SPRL
 * Dual licensed under the MIT or GPL Version 2 licenses.
 * http://editablegrid.net/license
 */


/**
 * This script loads data from the database and returns it to the js

  *
 */


require_once('config.php');      
require_once('EditableGrid.php');  



/**
 * fetch_pairs is a simple method that transforms a mysqli_result object in an array.
 * It will be used to generate possible values for some columns.
*/
function fetch_pairs($mysqli,$query){
    if (!($res = $mysqli->query($query)))return false;
    $rows = array();
    while ($row = $res->fetch_assoc()) {
        $first = true;
        $key = $value = null;
        foreach ($row as $val) {
            if ($first) { $key = $val; $first = false; }
            else { $value = $val; break; } 
        }
        $rows[$key] = $value;
    }
    return $rows;
} 


// Database connection
$mysqli = mysqli_init();
$mysqli->options(MYSQLI_OPT_CONNECT_TIMEOUT, 5);
$mysqli->real_connect($config['db_host'],$config['db_user'],$config['db_password'],$config['db_name']); 


// create a new EditableGrid object
$grid = new EditableGrid();

/* 
*  Add columns. The first argument of addColumn is the name of the field in the databse. 
*  The second argument is the label that will be displayed in the header
*/
$grid->addColumn('asset_hardware_id', 'ID', 'integer', NULL, false); 
$grid->addColumn('location', 'Location', 'string', fetch_pairs($mysqli,'SELECT id, location FROM location'),true);  
$grid->addColumn('cube', 'Room', 'string');  
$grid->addColumn('asset_type', 'Type', 'string', fetch_pairs($mysqli,'SELECT id, assets_hardware_type FROM assets_hardware_type'),true); 
$grid->addColumn('vendor', 'Vendor', 'string', fetch_pairs($mysqli,'SELECT vendor_id, vendor FROM vendors'),true ); 
 $grid->addColumn('model', 'Model', 'string');
 $grid->addColumn('asset_tag', 'Asset Tag', 'string');
 $grid->addColumn('serialnumber', 'Serial Number', 'string');
 $grid->addColumn('delltag', 'Dell Tag', 'string');
 $grid->addColumn('user', 'Name', 'string');
 $grid->addColumn('physically_inventoried', 'Inv?', 'boolean');


$mydb_tablename = (isset($_GET['db_tablename'])) ? stripslashes($_GET['db_tablename']) : 'assets_hardware';

 $result = $mysqli->query("SELECT * FROM ".$mydb_tablename. " WHERE location =  '". $_SESSION['loc'] ."'  and cube = '". $_SESSION['room'] ."'");
$mysqli->close();

// send data to the browser
$grid->renderJSON($result);

this is the PHP website output after using the editablegrid dropdown.

Is there any way to make the table non-editable for some or all columns?

Hello, I have the SQL example working. I see the full table and it interacts correctly with the SQL database. However, the Action cell is blank for each record/row. I'm expecting to see a way to delete the record from the database. Is there anything missing from the setup that would trigger something appearing in that cell that would call the delete.php file?

Hi,
Thanks for the awesome example. I have a few questions?

1. I have a long text in one of the columns. When i click to edit the text, it appears as one single line. Is it possible to wrap text while editing also?
2. How can insert new line character in the long text? (i.e. I want the text to appear as bullets or numbered list)
3. How can I enable charts? I really have no idea :(

12.01.2014 - Hello, Please can you help me.
An error occurs in current demo files. "setCellRenderer: Invalid Column: action"
What can i do? (I need the delete function for the gridrow)

Hey!
I'm using your editablegrid and have a question/problem:
I want to begin my datepicker with monday instead of sunday. How do I solve that issue? As far as I know do you initialize the datepicker in editablegrid-2.1.0-b25.js but that is not really readable.

Thanks!

I have the PHP demo running... when I delete a record it does it on the DB but does not refresh the grid view and the item still on the grid... I am using the php example files.

Hi,

I want to display the data of a table with the next columns

• id
• name
• countryId
  And the column 'countryId' is an integer but in the example country is an string
  i don't know what i hav to write
• $grid->addColumn('countryId', 'Country', 'integer', fetch_pairs($mysqli,'SELECT id, name FROM Country'),true );
• $grid->addColumn('countryId', 'Country', 'string', fetch_pairs($mysqli,'SELECT id, name FROM Country'),true );
  When I test one of the two codes, the datagrid don't display.

What have I to do ?

When I try to update a cell, it just flashes red then returns to the original value. It doesn't give an error. Some tip?

hi,
i just try this repo to build my research app.
my sql query something like it:
SELECT * FROM table_name WHERE id='4';

how implement use this script?
any clue.
thanks.

I have made my own database and table with everything working except for the delete function. When I click the trash icon my alert says:
Are you sure you want to delete the row id null

My table has a column called id and it is unique and auto increment. Since it's reading null I assume something isn't working properly? How to I track down the problem?

This is my delete.php but I am quite sure I did not change this.

`<?php

require_once('config.php');

// Database connection
$mysqli = mysqli_init();
$mysqli->options(MYSQLI_OPT_CONNECT_TIMEOUT, 5);
$mysqli->real_connect($config['db_host'],$config['db_user'],$config['db_password'],$config['db_name']);

// Get all parameter provided by the javascript
$id = $mysqli->real_escape_string(strip_tags($_POST['id']));
$tablename = $mysqli->real_escape_string(strip_tags($_POST['tablename']));

// This very generic. So this script can be used to update several tables.
$return=false;
if ( $stmt = $mysqli->prepare("DELETE FROM ".$tablename." WHERE id = ?")) {
$stmt->bind_param("i", $id);
$return = $stmt->execute();
$stmt->close();
}
$mysqli->close();

echo $return ? "ok" : "error";
`

Hello Jybeaujean, thanks for the work effort is this grid. As i'm trying to implement it I was wondering if its possible to have the update/delete/add commands done through a single php file with functions.

Instead of this for delete row.
$.ajax({
url: 'delete.php',
type: 'POST',
dataType: "html",
data: {
tablename : self.editableGrid.name,
id: id

You could do something like
url: 'functions.php/delete_row'
type: 'POST'

functions.php would have
function delete_row(){......}

The reason I ask is I'm wanting to use the grid for many diff table view's. I'd like to avoid having a dozen different update/delete/add files for each table. I think I'd be fine with just the one delete file but the add/update would differ and each table has diff columns to change. Please let me know if this is possible. I've tried to figure this out on google searches but the function doesn't seem to be getting ran.

Thanks for you time.

FYI...
I've tried some things like.
url: 'inc/functions.php/p2_delete?id='+id+'&tablename='+self.editableGrid.name,

I've put junk in the function like so and nothing is returned..

function p2_delete(){
if(isset($_POST['id']) && !empty($_POST['tablename'])) {
echo "here";
print "here";
var_dump($_POST['tablename']);
php_info();
}
sql code here
}

Is there a function to show (on header or footer) the number of rows displayed with or without filter(s).
Something like the image below...

Hi, great tool, thank you.

Please help.
I'm trying to just display one column of data (the first), not all the columns.
When I comment out all addColumn lines except the first column (using your demo), then no data is displayed in the grid.

It appears only if I include the columns 'country' or 'continent' does the grid show results.
If I exclude both those columns, the grid returns no rows, even though the other columns are there.

Thanks

Thank you for this mysql example -- it's very informative. However, I've been trying to add charts to this mysql example but having problems. Do you mind adding charts to this example?

Thanks.

We want to fetch 1000 Customer Names in Select Dropdown Like this But Whole Page is blank???

$grid->addColumn('Customer_No', 'Customer', 'string', fetch_pairs($mysqli,'SELECT No, Name FROM customers'),true ); in loaddata.php

I an unable to update row by primary key instead of row index. Reason being, once deleted the rows after the deleted row are out of alignment and updates are made to misaligned cells based on rowid. New to this so if there is any guidance thanks in advance.

The latest untouched download version shows the message "HighCharts library not loaded" and the "rows per page" feature is not working too

I am a new developer and i am not getting where to start using this, can you help where to start PHP Mysql Editable Grid

I had an issue with loaddata.php

patch loaddata.php < patch.txt

save between SNIP (but do not include SNIP as patch.txt, Or find the - lines and replace them with + lines.
===> SNIP <===
--- loaddata.php.original.broken 2017-08-02 00:56:32.000000000 +0930
+++ loaddata.php.fixed 2017-08-08 15:47:37.000000000 +0930
@@ -75,10 +75,10 @@

$query = 'SELECT *, date_format(lastvisit, "%d/%m/%Y") as lastvisit FROM '.$mydb_tablename ;
$queryCount = 'SELECT count(id) as nb FROM '.$mydb_tablename;

-$totalUnfiltered =$mysqli->query($queryCount)->fetch_row()[0];
-$total = $totalUnfiltered;

+//m3f $totalUnfiltered =$mysqli->query($queryCount)->fetch_row()[0];
+//m3f $total = $totalUnfiltered;
+$totalUnfiltered =$mysqli->query($queryCount)->fetch_row();
+$total = $totalUnfiltered[0];

/* SERVER SIDE /
/ If you have set serverSide : true in your Javascript code, $_GET contains 3 additionnal parameters : page, filter, sort
@@ -98,7 +98,9 @@
$filter = $_GET['filter'];
$query .= ' WHERE name like "%'.$filter.'%" OR firstname like "%'.$filter.'%"';
$queryCount .= ' WHERE name like "%'.$filter.'%" OR firstname like "%'.$filter.'%"';

• $total =$mysqli->query($queryCount)->fetch_row()[0];
  +//m3f $total =$mysqli->query($queryCount)->fetch_row()[0];

• $totalUnfiltered =$mysqli->query($queryCount)->fetch_row();
• $total = $totalUnfiltered[0];
  }

if ( isset($_GET['sort']) && $_GET['sort'] != "" )
===> SNIP <===

Is which css file is the column header position changed. I'm trying to get the column headers centered but nothing I've tried is working.

Is it possible to only have the Action column show up when a user is logged in? I wasn't sure how it would affect the xml etc. Same goes for editing. I'm wanting the data to be view-able to everything but only admin's can edit/delete etc.

Also I see from other posts that it sounds like you may still be working on this project. If that's the case are you planing on adding "rows per page" functionality?

Awesome work by the way.

I am trying to do an editable grid table in order to modify directly.
There is a field which I need to list all of it content so that I can select one.
Look at my code :
function fetch_pairs($mysqli,$query){ if (!($res = $mysqli->query($query)))return FALSE; $rows = array(); while ($row = $res->fetch_assoc()) { $first = true; $key = $value = null; foreach ($row as $val) { if ($first) { $key = $val; $first = false; } else { $value = $val; break; } } $rows[$key] = $value; } return $rows; } $grid->addColumn('product-name', 'Product', 'string' , fetch_pairs($mysqli,'SELECT name FROM table_product'),true);
In my database I have two tables:
-table_add{id,product-name}
-table_product{id,name}

But the list is not appearing.
I am getting this error:
Could not load JSON from url 'loaddata.php?db_tablename=table_add'

Any idea why ?

Thanks for the demo. It is exactly what I need but I am having a problem and I need a little direction.
I downloaded the zip file, extrated it then up loaded it to my web server. config.php is correct as when I load "loaddata.php" stand alone, I see the xml. When I load index.html, however, I see the header, buttons, everything BUT the table that should have been rendered. I put an alert in the DatabaseGrid constructor just before the this.fetchGrid which triggers. In "fetchGrid, I have alert's before and after "loadXML" which trigger but no data displays.

Any ideas where to look?

Hello! Use your software for my purposes, very helpful. How do I set up the ability to edit dates? I use your configuration and settings (just use other names of the columns).

Hi i've tried putting my own table and it seems that it doesnt work :'(
please help me

this is my loaddata.php

Whenever I click a date field, the text field has a red background, then I select a date and it flashes red, indicating it cannot change the value in my mysql database. Any other data type can be changed just fine (e.g. string). Any ideas?

Thanks