0

I am working to develop an Android app that stores and logs coffee shops that I go to. It is just a small personal project I have been working on for some time now. I recently changed servers and rebuilt my SQL server on it. Since then I have only been able to SELECT from one Table, the other table returns no values.

The PHP code for running the GET command is identical for both tables, the only differences being the table name and contents. The code also worked prior to the mySQL server rebuild. This leads me to believe that there is something wrong with my SQL server, which I simply imported the data from the previous server when I rebuilt it.

<?php
//DATABASE CONNECTION INFO LEFT OUT//

$connect = mysqli_connect($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME);

if (mysqli_connect_errno()) {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
    die();
}

$stmt = $connect->prepare("
SELECT ShopID
     , IconURL
     , Title
     , Address
     , City
     , State
     , Zip
     , Rating 
   FROM CoffeeShops
 ;");

$stmt->execute();

$stmt->bind_result($ShopID, $IconURL, $Title, $Address, $City, $State, $Zip, $Rating);



$shops = array(); 

while($stmt->fetch()){
    $temp = array();
    $temp['ShopID'] = $ShopID; 
    $temp['IconURL'] = $IconURL; 
    $temp['Title'] = $Title; 
    $temp['Address'] = $Address; 
    $temp['City'] = $City; 
    $temp['State'] = $State; 
    $temp['Zip'] = $Zip; 
    $temp['Rating'] = $Rating; 
    array_push($shops, $temp);
}


echo json_encode($shops);
 mysqli_close($connect);
?>

The above code is for the Table that will not return data. The other code is identical and works just fine as mentioned above.

Strawberry
  • 33,750
  • 13
  • 40
  • 57
Evan Dinnon
  • 1
  • Do you see any error messages? Could you please add them to the question too? – lna21 Jul 28 '20 at 17:38
  • Also in the "$stmt = $connect->prepare("SELECT ShopID, IconURL, Title, Address, City, State, Zip, Rating FROM CoffeeShops;");" line delete the semicolon after CoffeeShops as that may be causing the error. – lna21 Jul 28 '20 at 17:38
  • You must switch on error reporting to see any errors: [How to get the error message in MySQLi?](https://stackoverflow.com/a/22662582/1839439) – Dharman Jul 28 '20 at 17:39
  • @lna21 That semicolon is optional and will not cause problems – Dharman Jul 28 '20 at 17:39
  • Switching to error reporting will be the best solution as you can see the error right away. Some possible issues: 1) Statement not binding, try if($stmt->bind_result(...)) { echo ('Binded');} else { echo( 'failed to bind'); } //I had this issue. 2) The table permissions aren't correct – Akash Jul 28 '20 at 17:50
  • I do not receive any errors, even after switching on error reporting. When testing with Postman I just receive no data. Also when testing directly with my app no data is received. Also, I tested the if statement telling me if it binds or not and it does bind. Just no data is actually collected. – Evan Dinnon Jul 28 '20 at 17:51
  • @EvanDinnon try to debug it. see you are already getting inside while($stmt->fetch()) if you are inside than try to display $shops and $temp variables in it with print_r – Rupal Javiya Jul 28 '20 at 17:56
  • @RupalJaviya When printing the temp array and the shop array directly the data appears as it should. It appears to be failing to JSON encode the array in the last step. This is odd considering it works with my other table, – Evan Dinnon Jul 28 '20 at 18:03
  • @EvanDinnon Then that can be problem with data encoding. see the answer here https://stackoverflow.com/questions/39140174/php-json-encode-doesnt-show-anything-with-multidimensional-array – Rupal Javiya Jul 29 '20 at 10:55

1 Answers1

0

After using print_r to display some of the variables I discovered the issue had to do with the json encoding. Utilizing print_r once again as well as json_last_error() I found the issue having to do with UTF-8 encoding.

Including the following line of code ended up solving my issue.

$shops = mb_convert_encoding($shops, "UTF-8", "auto");

Looking back into my app it appears I had a special character that in one of the titles that was throwing everything off. This also is rather odd considering it worked before i rebuilt mySQL server.

Thank you to all who helped!

Evan Dinnon
  • 1