Multiple items in a MySql column

To store the address in a single column and then extract the information you must ensure that any and all addresses are stored in the same pattern.

You can, for example, use some kind of separator (#, $, @) to separate each field and then use the explode function to separate.

Eg: Rua das Primas nº 69, Vila Velha, Espirito Santo, Cep.: 29101-000

You could save it like this (considering # as a separator):

Rua das Primas#69#Vila Velha#Espirito Santo##29101000

This is a bad idea.

@Egar's answer is correct in that it does what you want, but it has several problems:

• On the day that your boss wants to look for everyone who lives in a certain city, you will not have to do this in SQL - you will need to do a program that pulls everything out of the database and searches in PHP; forget the ability to use indexes to speed up the search.

• On the day you want to put an extra field (eg neighborhood) in the address, you're chipped - every place that accesses this address field will need to be changed to check how many '#''s in the address, is the old or new format, and add the new field if it does not already exist.

If you absolutely insist on ignoring the advice of Maicon, Daniel and mine and give one of priest of the balloon to> ignore the cries of people who have more experience than you and studied more than you that "this will give problem", consider the possibility of at least saving the data in JSON:

<?php

$endereco = array(
    'rua' => 'Pennsylvania Avenue NW',
    'numero' => '1600',
    'cidade' => 'Washington', 
    'estado' => 'DC',
    'cep' => '20500');

echo json_encode($endereco);

To recompose the array from the string, use json_decode . This solves the first and third (but not the second) issues I mentioned above.

(You can see this working at IDEOne .)

The problem you may have in storing in a single column is later to query. Let's say you want to filter the records of a single city. It will get a bit more complex

I agree with Wallace's observation, Edgar's response, is very insecure. It will work until it breaks on the first problem. To enter the data, you can serialize the data and write to the database:

$endereco = array(
    'rua' => 'Manuel da Nóbrega',
    'numero' => '32',
    'cidade' => 'São paulo', 
    'estado' => 'SP',
    'cep' => '12345-123');

$endereco_serializado = serialize($endereco);

Send the query to your database:

$sql="INSERT INTO TABELA (endereco) VALUES (:endereco)";
$stmt = $db->prepare($sql);
$stmt->execute(array(':endereco'=>$endereco_serializado));

And to recover:

 $sql="SELECT endereco FROM TABELA where id=?";
    $stmt = $dbh->prepare($sql);
    $stmt->execute($id);
    $result = $stmt->fetch(PDO::FETCH_ASSOC);

    $endereco = unserialize($result['endereco'])

echo $endereco;