Multiple items in a Mysql column

Question

How do I insert and display separate data in a single Mysql mo column? Ex: I have a column (catalogo_address), in it I want to insert the street, the city, and the state in one column and then be able to separate each one in PHP.

Answer 1

This is a bad idea.

The @Edgar response is correct in the sense that she does what you want, but she has several problems:

• If the citizen puts a ː#' in his address, it will break his code.

• The day your boss wants to search everyone who lives in a given city, you won’t be able to do this in SQL - you’ll need to make a program that pulls everything from the database and searches in PHP; forget the possibility of using indexes to speed up the search.

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

---

If you absolutely insist on ignoring the advice of Maicon, Daniel and me and playing balloon priest and 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 storing 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) problems I mentioned above.

(You can see it working on Ideone.)

Answer 2

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 sort of separator (#, $, @) to separate each field and then use the function explodeto separate.

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

You could keep it that way (considering # as a separator):

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

Answer 3

I agree with Wallace’s observation, Edgar’s answer, is very insecure. It will work until it breaks down in 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 your insertion query to the 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;

Answer 4

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