0

I have a database from which I retrieve information successfully. 

 //Connect to mysql database
  $con = mysqli_connect($host,$user,$pass);
  mysqli_select_db($con, $databaseName);

  //Query database for data
  $sql = "SELECT * FROM donators";
  $result = mysqli_query($con, $sql);

I can send it to my browser by iterating print_r($row)

//this returns the result
while($row = mysqli_fetch_array($result))
  {
      print_r($row);
  }

But when I try to retrieve it by iterating $row['lastName'] it does not return anything at all. It works on all other rows. This specific row contains peoples lastName. I am also 100% certain I wrote 'lastName' right.

//no result here but woudl work without "firstName"=>$row['firstName']
while($row = mysqli_fetch_array($result))
  {
  $output[]= array( "index"=>$row['index'], "firstName"=>$row['firstName'], "lastName"=>$row['lastName']);
  }  
echo json_encode($output);

Anyone has an idea what can be / is causing this?

Rob Monhemius
  • 4,822
  • 2
  • 17
  • 49
  • 1
    Can you add the SQL SELECT query and your table structure to your question (the output of `SHOW CREATE TABLE tableName` should do)? – rickdenhaan Sep 16 '17 at 23:41
  • @rickdenhaan I added the SQL query to the question. The table structure is fairly simple. The column that causes the issue has a text-type and collation is: latin1_swedish_ci (I can't provide the output, for privacy reasons). – Rob Monhemius Sep 16 '17 at 23:52
  • 1
    Ok, what I was actually interested in with the table structure is what the column names are, according to MySQL. In the output of `SHOW CREATE TABLE` they should be enclosed by backticks, it's possible the `lastName` column has an (accidental) space in its name. – rickdenhaan Sep 16 '17 at 23:55
  • @rickdenhaan Some of the field-names in the lastName column do contain characters that can be used in code like `-`, `.` and `/`. I think something goes wrong when I try to encode it to json. If there was an error connecting to the database print_r($row) wouldn't have output any values either, yet it does. – Rob Monhemius Sep 17 '17 at 00:00
  • 1
    No, I'm not interested in the **values** of your data, I'm interested in the **structure** of your table. Alternatively, when you `print_r($row)` -- what are the **keys** of the array? Does it output `[lastName] => ***` or `[lastName ] => ***` (for example)? – rickdenhaan Sep 17 '17 at 00:03
  • Try with this: `$output[] = $row;` and in the JSON you can get the possibly values and keys, identify what data and modify the query to `SELECT myrow, otherrow, ... `. If is possibly post your keys that `print_r` show you – kip Sep 17 '17 at 00:06
  • @rickdenhaan it outputs something like: `[2] => d. Haan [lastName] => d. Haan` no extra spaces. – Rob Monhemius Sep 17 '17 at 00:08
  • Ok. Do you see any values if you `print_r($output)` instead of echo'ing the JSON version? – rickdenhaan Sep 17 '17 at 00:10
  • @rickdenhaan Yes that would output values. – Rob Monhemius Sep 17 '17 at 00:17
  • And `echo json_encode($output)` does not? – rickdenhaan Sep 17 '17 at 00:19
  • 1
    @RMo if you try with `$output[] = $row;` to set the values in output what produce the `echo json_encode($output);` ? – kip Sep 17 '17 at 00:20
  • @kip it would output nothing at all. – Rob Monhemius Sep 17 '17 at 00:28
  • It does work when I select a single record. Iterating over all (60) now to see if one fails ;) – Rob Monhemius Sep 17 '17 at 00:35
  • I found the record that causes the issue. It is a record that uses a lastName with an ö. Any idea how I can allow those kind of characters in my SQL and still use it in the php? – Rob Monhemius Sep 17 '17 at 00:43
  • That shouldn't be a problem, `json_encode()` should just encode it to something reasonable. You said the column was `latin1_swedish_ci`, it's possible PHP thinks it's in UTF-8. Can you try `mysqli_set_charset($con, 'latin1');` after connecting? – rickdenhaan Sep 17 '17 at 00:56
  • @rickdenhaan That didn't work. But I found this: https://stackoverflow.com/questions/2934563/how-to-decode-unicode-escape-sequences-like-u00ed-to-proper-utf-8-encoded-cha It seems to do the trick for me ;). Thanks a lot for helping me out (both rickdenhaan and kip). – Rob Monhemius Sep 17 '17 at 01:08

0 Answers0