We help IT Professionals succeed at work.

New podcast episode! Our very own Community Manager, Rob Jurd, gives his insight on the value of an online community. Listen Now!

x

EditableGrid how to fetch rows from MySql in php

472 Views
Last Modified: 2017-03-30
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 ?
Comment
Watch Question

Most Valuable Expert 2011
Author of the Year 2014

Commented:
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

Author

Commented:
Is my code correct for the fetching ?
Most Valuable Expert 2011
Author of the Year 2014

Commented:
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

Author

Commented:
I am not getting any data with var_dump($row)

Author

Commented:
Here is a link to see the error : link
Most Valuable Expert 2011
Author of the Year 2014

Commented:
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.

Author

Commented:
Did you see the link ?
Most Valuable Expert 2011
Author of the Year 2014

Commented:
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.

Author

Commented:
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

Commented:
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);
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
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...
Most Valuable Expert 2011
Author of the Year 2014

Commented:
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.

Commented:
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?
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.