-1

I have a table houses with this sample data:

id   name        info
--------------------------------------------------------------------------------------
1    House 1     {"adress":"Hello Street","Number":"123","City":"Rome","CAP":"12345"}
2    House 2     {"adress":"Bye Street","Number":"456","City":"Amsterdam","CAP":"6789"}
3    House 3     {"adress":"SQL Street","Number":"987","City":"Paris","CAP":"54321"}

This is my PHP code:

$query=mysqli_query($conn,"SELECT * FROM houses WHERE name LIKE '%$housename%'")or die(mysqli_error($conn));
$row=mysqli_fetch_array($query);

echo <?=($row['info'])?>;

In this case, it will output the whole string, how do I output only a piece of the string for example only the address:

Adress:Hello Street or "adress":"Hello Street"

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mast3ronee
  • 5
  • 4
  • 1
    It's JSON. So decode it with json_decode() and then echo the specific property within the decoded object – ADyson Aug 09 '23 at 16:36
  • 1
    Does this answer your question? [How to extract and access data from JSON with PHP?](https://stackoverflow.com/questions/29308898/how-to-extract-and-access-data-from-json-with-php) – ADyson Aug 09 '23 at 16:39
  • 2
    **Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/32391315) – Dharman Aug 09 '23 at 16:55
  • @ADyson I tried your advice, but I don't understand how do I extract a specific text. – Mast3ronee Aug 09 '23 at 17:04
  • Something like `= json_decode($row['info'], TRUE)['adress']; ?>;` Also not sure if it is a typo here or not but `echo` isn't doing anything in your sample. `=` does the outputting; the `echo` wont even be seen by PHP. – user3783243 Aug 09 '23 at 17:14
  • Thats working! Thanks @user3783243 and ADyson – Mast3ronee Aug 09 '23 at 17:45
  • Read the link I provided to learn the basic principles you need in order to extract any data from any JSON in future – ADyson Aug 09 '23 at 17:50
  • It's unusual that it's a good idea to try to shoe-horn multiple values into a single database column. If this is a new project you're working on, it's not too late to put them in separate ones. – droopsnoot Aug 09 '23 at 17:58
  • `select id, select json_value(info, '$.adress') adress from houses...` – ysth Aug 10 '23 at 01:17

0 Answers0