PHP code will SELECT from one MySQL table but not from another

Question

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.

Do you see any error messages? Could you please add them to the question too? — lna21
– lna21, Commented Jul 28, 2020 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
– lna21, Commented Jul 28, 2020 at 17:38
You must switch on error reporting to see any errors: How to get the error message in MySQLi? — Dharman
– Dharman ♦, Commented Jul 28, 2020 at 17:39
@lna21 That semicolon is optional and will not cause problems — Dharman
– Dharman ♦, Commented Jul 28, 2020 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
– Akash, Commented Jul 28, 2020 at 17:50

Evan Dinnon · Accepted Answer · 2020-07-28 18:26:06Z

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!

answered Jul 28, 2020 at 18:26

Evan Dinnon

1

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PHP code will SELECT from one MySQL table but not from another

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related