1

i have a large data in my mysql database (500 rows), when i am trying to get all of them , the data is not coming from database.

Working Code

 <?php
include '../db/db.php';

$categoriesDataQuery = mysqli_query($conn, "SELECT * FROM subcategories LIMIT 268");
while ($row = mysqli_fetch_assoc ($categoriesDataQuery)) {
    $categoryData [] = $row;
}

$categoryNewData = $categoryData;
print (json_encode ($categoryNewData)) ;
mysqli_close ($conn);

?>

Not Working Code

<?php

include '../db/db.php';

$categoriesDataQuery = mysqli_query($conn, "SELECT * FROM subcategories");
while ($row = mysqli_fetch_assoc ($categoriesDataQuery)) {
    $categoryData [] = $row;
}

$categoryNewData = $categoryData;
print (json_encode ($categoryNewData)) ;

mysqli_close ($conn);

?>

Maybe Mysql database have some limitation when user select a large data ?!

Who ever encountered this?

Giorgi Asaturyan
  • 393
  • 1
  • 5
  • 16
  • Why do you make that $categoryNewData variable when its just a copy of the other one? Anyway, 500+ rows is not "that" much - What does count($categoryData) say on the latter example? – MHopstad May 31 '18 at 12:21
  • 500 records is not really "large data" MySQL and PHP are able to handle much more data. do you have any error in the log ? – ᴄʀᴏᴢᴇᴛ May 31 '18 at 12:22
  • `[31-May-2018 11:53:01 UTC] PHP Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in /home/asata/public_html/etsports/data/get_subcategories.php on line 6 [31-May-2018 11:53:01 UTC] PHP Notice: Undefined variable: categoryData in /home/asata/public_html/etsports/data/get_subcategories.php on line 10 [31-May-2018 11:53:02 UTC] PHP Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in /home/asata/public_html/etsports/data/get_subcategories.php on line 6` – Giorgi Asaturyan May 31 '18 at 12:25
  • This is my log )) – Giorgi Asaturyan May 31 '18 at 12:25
  • Today i have added 300 rows , and its broken like this, before my code have worked well ) – Giorgi Asaturyan May 31 '18 at 12:26
  • When you get a boolean as a result for `mysqli_query` it means the request failed. you have to add `echo mysqli_error($conn)` to get the SQL error. see http://php.net/manual/fr/mysqli.error.php – ᴄʀᴏᴢᴇᴛ May 31 '18 at 12:28
  • i have added echo mysqli_error($conn);, before mysqlli_clise, and no changes, still blank page – Giorgi Asaturyan May 31 '18 at 12:30
  • yes you still have a blank page because you try to loop over a boolean (`while ($row = mysqli_fetch_assoc ($categoriesDataQuery))`). it will raise the eror you have into your logs. the mysqli_error has to be just after the query and before the while loop – ᴄʀᴏᴢᴇᴛ May 31 '18 at 12:32
  • Still the same, maybe is there any other way to select data from database? maybe i have some structure issue? – Giorgi Asaturyan May 31 '18 at 12:35
  • You should handle errors properly as mentioned above. It's also a good idea to initialise your `$categoryData` variable to an empty array before pushing data to it. – DarthJDG May 31 '18 at 12:42
  • 1
    You can temporarily add this line at the beginning of your code `ini_set('display_errors', true);`. Then you will see the errors instead of a blank page. Your `SELECT` statement looks fine. – drews May 31 '18 at 12:43
  • [how-to-get-mysqli-error-information-in-different-environments](https://stackoverflow.com/questions/22662488/how-to-get-mysqli-error-information-in-different-environments) – Paul Spiegel May 31 '18 at 12:45
  • I agree with @drews : you should enable error reporting while you are in dev mode, it will be easier to debug instead of having fo search in the logs – ᴄʀᴏᴢᴇᴛ May 31 '18 at 12:48
  • thanks guys, i have done all tricks, but still any changes, still blank page – Giorgi Asaturyan May 31 '18 at 12:49
  • also i have a similar code like this, the difference are only in rows count – Giorgi Asaturyan May 31 '18 at 12:51
  • Now i have tried to change table name to another (workouts instead of subcategory) and data gets correctly. @ᴄʀᴏᴢᴇᴛ – Giorgi Asaturyan May 31 '18 at 12:56
  • yes, still no changes – Giorgi Asaturyan May 31 '18 at 12:57
  • this is strange... `subcategory` is not a reserved keyword... – ᴄʀᴏᴢᴇᴛ May 31 '18 at 12:59
  • yes, i think so too, but when i had a few amount of rows - it have worked Great, this code worked about 2 years, but today i have added a lot of rows, and it displays blank page – Giorgi Asaturyan May 31 '18 at 13:00
  • @ᴄʀᴏᴢᴇᴛ but when i set limit to under 260 rows , it works well also – Giorgi Asaturyan May 31 '18 at 13:01
  • Maybe i have to change some mysql setting to increase coming data? what do you think? – Giorgi Asaturyan May 31 '18 at 13:13
  • 1
    Ah, if it's not a MySQL error, then it's a string encoding issue. I had a similar problem when json_encode returned an empty string instead of throwing an error when it encountered an invalid character. Do you have any non-ASCII characters in your data? If your MySQL table uses utf-8, you need to make sure your connection to the database uses the same encoding. `mysqli_set_charset($conn, "utf8");` – DarthJDG May 31 '18 at 13:41
  • ooooooh, you are GENIUS, thanks bro, its worked for me – Giorgi Asaturyan May 31 '18 at 13:47
  • @GiorgiAsaturyan no problem, I posted it as an answer. – DarthJDG May 31 '18 at 14:06

1 Answers1

2

The problem is not with the SQL query or the amount of data that you're trying to encode. For some reason, json_encode can return an empty string instead of throwing an error when it encounters an invalid character.

You have to make sure that the SQL connection's charater encoding matches your database table's encoding. For example if you're using UTF-8 encoding on your table, you need to call the following function to force the same encoding on the PHP client:

mysqli_set_charset($conn, "utf8");

See the PHP docs for more information.

DarthJDG
  • 16,511
  • 11
  • 49
  • 56