0

I have a MySQL database owns a table "news" with "ID" (Int(15)), "titel" (varchar(255)) and so on.

The following code let me get a json of the column "id". But if i want to get the column "titel" i get nothing out of this PHP.

$con=mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

$sql = "SELECT `titel` FROM `news` ORDER BY `datum` DESC";
$result = mysql_query($sql);
$json = array();

if(mysql_num_rows($result)){
while($row=mysql_fetch_assoc($result)){
$json['news'][]=$row;
}
}
mysql_close($con);
echo json_encode($json);

Here the table structure out of phpMyAdmin as table (xx = empty)

Name   Type         Collation       Attributes   Null   Default     

id     int(15)      xx              UNSINGED     No     None
titel  varchar(255) lati1_swedi..   xx           No     xx
Francisco
  • 10,918
  • 6
  • 34
  • 45
Lordlambda
  • 1
  • 2
  • 2
    Are you sure it is `titel` and not the english version `title`? Also, use basic debugging - `or die (mysql_error())` to diagnose -> `$result = mysql_query($sql) or die (mysql_error());` – Sean Apr 24 '15 at 15:34
  • Yes i´am sure that it is the german _titel_. I am new to prorgamming. I add a picture of the table structure. Maybe this get a hint. The same code workes well on other tables which includes varchar(255) ass well. – Lordlambda Apr 24 '15 at 15:38
  • 3
    Please, [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://jayblanchard.net/demystifying_php_pdo.html). – Jay Blanchard Apr 24 '15 at 15:45
  • Is `datum` a valid column name in your table? There is nothing in your code that you are showing that would prevent your query from returning rows. There must be something that you are not showing here. Also, you are putting all the returned rows into `$json['news']`, so it will be a multi-dimensional array under `'news'`, which you are accounting for, correct? – Sean Apr 24 '15 at 15:54
  • Yes _datum_ is a valid column name. Later on i get all i need for my android app out of the `$json['new']`. As mentioned before. If i select column _id_ instead of _titel_ the code works fine. – Lordlambda Apr 24 '15 at 16:08
  • Have you tried your query directly in MySQL, using MySQL workbench or phpMyAdmin? Does it work there? Do you get any errors from `mysql_error()`? You should also check the returned value from `mysql_num_rows($result)`, as it is in an `if()` that wraps your `while($row=mysql_fetch_assoc($result))`. – Sean Apr 24 '15 at 16:19
  • In phpMyAdmin the query gives me an result. There is no `mysql_error()`. How can i check the retuned value from `mysql_num_rows($result)`? – Lordlambda Apr 24 '15 at 16:36
  • `echo mysql_num_rows($result);` – Sean Apr 24 '15 at 16:39
  • wow that was easy. I get 521 row, same as in phpmyadmin. – Lordlambda Apr 24 '15 at 16:53
  • so if you get 521 for `mysql_num_rows($result)` then your `if(mysql_num_rows($result))` should return true, and it should do your `while($row=mysql_fetch_assoc($result))`. You could debug inside that loop by doing `print_r($row)`, or `echo $row['titel'];`, or even changing `$json['news'][]=$row['titel'];` – Sean Apr 24 '15 at 16:57
  • `$json['news'][]=$row['titel']` gives me `"news":[null,null,null,null,n...]`, `echo $row['titel']` gives me the titels in textformat. And last but not least `print_r ($row)` gives me the titles in `Array ( [titel] =>"")`format. – Lordlambda Apr 24 '15 at 20:21

1 Answers1

0

Changing the Line $json['news'][]=$row; to $json['news'][]=array_map('utf8_encode', $row); solved the problem. There exist characters like ä, ö.... in the database table. 

$con=mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

$sql = "SELECT `titel` FROM `news` ORDER BY `datum` DESC";
$result = mysql_query($sql);
$json = array();

if(mysql_num_rows($result)){
while($row=mysql_fetch_assoc($result)){

$json['news'][]=array_map('utf8_encode', $row);
}
}
mysql_close($con);
echo json_encode($json);
Lordlambda
  • 1
  • 2