Link to home
Start Free TrialLog in
Avatar of SniperCode Sheva
SniperCode Sheva

asked on

EditableGrid how to fetch rows from MySql in php

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); 

Open in new window


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 ?
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image

Could not load JSON from url 'loaddata.php?db_tablename=table_add'
That is not a standard PHP message.  It would be possible to help if we could see the code that generates that message.

If you're new to using JSON, you will find that it works a lot like XML.  It has its own set of built-in diagnostics.  This article may help you get things going.
https://www.experts-exchange.com/articles/22519/Understanding-JSON-in-PHP-and-JavaScript-Applications.html
Avatar of SniperCode Sheva
SniperCode Sheva

ASKER

Is my code correct for the fetching ?
I can't tell if it correct.  It doesn't look immediately wrong, but this is a data-dependent problem, and so it would be good to visualize the data using var_dump($row) after line 4 and var_dump($rows) after line 12.  Then you can examine the output of var_dump() and see if you're getting the data structure you expect.
http://php.net/manual/en/function.var-dump.php
I am not getting any data with var_dump($row)
Here is a link to see the error : link
That is the normal and expected result when the query finds no rows that match the WHERE clause.  But because of the way this function is constructed, you can have errors and not find out about them.  The function will return FALSE if the query fails.  A better strategy would be to visualize the error information.  Examples are in this article:
https://www.experts-exchange.com/articles/11177/PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

Scan the article for "mysqli::query" and you will find a way to trigger a PHP error when you've discovered a MySQLi error.  The PHP error will be logged and maybe displayed in the browser viewport.
Did you see the link ?
Yes, I saw the link but it does not tell me much of anything.  It would be good to know whether the PHP code posted here works or not.  It would be good to visualize the data, or if you can't find the data, it would be good to visualize the error messages.  I think an enlightened approach to debugging will always deconstruct the problem into small, testable units of code and data, then build the application back up from the tested units.
the php code didn't work , and you can go back to the link when I remove
fetch_pairs($mysqli,'SELECT name FROM table_product'),true

Open in new window

from the code the data are filled.
I am trying to do something like this
greetings SniperCode Sheva, , I have not used the "Editablegrid", , but in your PHP I looks like you have not used the correct SQL SELECT string, you have this -
       fetch_pairs($mysqli,'SELECT name FROM table_product')

BUT you do NOT get but ONE table column, the "name", , the PAIRS array needs TWO columns, the ID and the NAME columns as -
          fetch_pairs($mysqli,'SELECT id, name FROM table_product')

Also , I'm not sure about the needed symbol connection, syntax, in the string, but you use a hyphen as -
         'product-name'
and in their examples they use a _  as -
         'product_name'

maybe as -
    $grid->addColumn('product_name', 'Product', 'string' , fetch_pairs($mysqli,'SELECT id, name FROM table_product'), true);
ASKER CERTIFIED SOLUTION
Avatar of Member_2_248744
Member_2_248744
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Even with the new function it is not working !

I am having the same problem of this thread
But in my case when I put limit 18 it is showing me not all the data but some... in my table_product I have 29 rows but only 18 are shown...
Suggest you open a project in Gigs and work with Slick812 or another E-E expert on this one.  It's too complicated for me to understand.
I looked at all of screen shots you have in the github -
        https://github.com/webismymind/editablegrid-mysql-example/issues/33

like -
       http://prnt.sc/b32p2z

and in some of your attempts, you say it works as "Using 'limit 900' its working"
and then in others it gives you the javascript console error of "could not load JSON from url"
But since you get the browser ajax error "could not load JSON", what I would do is just place the ajax url with the page CODE that is NOT working in the address bar of your browser -
       loaddata.php?db_tablename=table_add
and see what that page is giving as text to the ajax, that causing the error.

I can not really understand the specific problem you say you are having, because the only script (PHP, javascript) ERROR you mention is the  "could not load JSON from url" and you say it is because of certain mysqli SELECT strings? In this "Editablegrid" thing, isn't there some way to turn on ERROR REPORTING  , , or an error log, or the PHP page error log that you look at to see if its a mysqli database error, or a "Editablegrid" code error?