A MySQL database houses 11,000+ rows and 3 assigned columns: prices, UPC codes, and descriptions. Running LAMP on the same machine (Ubuntu 18.04), I am using PHP to grab each column of the database on page load and echo it into javascript arrays that are then searched by the user in a text box. The only problem is that my query only lets me select 644 items before it then loads nothing. This is a problem because 10,356+ rows are not being displayed.
php file:
<?php
session_start();
$conn = new mysqli($servername, $username, $password, $dbname);
$sql = "SELECT * FROM sys.pds limit 644;";
$result = $conn->query($sql);
if (!$result) {
printf("Errormessage: %s\n", mysqli_error($conn));
}
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
$pds_code[] = $row["Code"];
$pds_desc[] = $row['Description'];
$pds_prce[] = $row["Price"];
}
}
$sqlDesc = json_encode($pds_desc);
$sqlPrice = json_encode($pds_prce);
$sqlCode = json_encode($pds_code);
echo "pds_descs = ". $sqlDesc . ";\n";
echo "pds_codes = ". $sqlCode . ";\n";
echo "pds_Prices = ". $sqlPrice . ";\n";
?>
Returns (from Chrome console):
pds_descs = ["desc1","desc2","etc..."];
pds_codes = ["code1","code2","etc..."];
pds_Prices = ["price1","price2","etc..."];
When the limit is set to 645 (or removed) the arrays become:
pds_descs = ; <--What triggers the error in the console
pds_codes = ["code1","code2","etc..."];
pds_Prices = ["price1","price2","etc..."];
It gets more interesting Since the "Description" column is the only one not receiving anything, I tried referencing the column by number instead of name, replacing
$pds_desc[] = $row['Description'];
with
$pds_desc[] = $row[0];
*0 is the correct row for "Description". Instead of throwing the same output, is fills a small (20-30 row) array filled with null:
pds_descs = [null,null,null,...];
pds_codes = ["code1","code2","etc..."];
pds_Prices = ["price1","price2","etc..."];
How do I fix to load the full 11,000+ database into its arrays in js to remove the 644 limit? I read about using Ajax to search client-->server instead of sideloading the database to the client on load but haven't ever used it and feel like this is still an avenue to explore. I also read about increasing the memory to MySQL/PHP but after much effort and modifying php.ini and my.cnf still nothing happened.
The error was that entry 645 contained non-ASCII letters which broke the json_encode(); statement. To solve this, I ran a query in MYSQL:
SELECT *
FROM sys.pds
WHERE Description <> CONVERT(Description USING ASCII);
This brought up a list of entries non in compliance with ASCII (I had 8 entries) to which I modified the element normal alphanumeric characters and applied. After a refresh of the chrome webpage all 11,745 rows could be loaded!