Relate field to mysql table

Asked

Viewed 215 times

0

I need to do a test crud for a company. In this test, I had to create the Name, Email, Phone and Address fields, and it should be possible to enter more than one address by name in this CRUD. I don’t think you can just create another field to enter another address. I think the point of the test is that I create relationship tables, but I have no idea how to start. I am doing CRUD with PHP and I already have everything ready, add, edit and delete. Everything is already working. Only now we need this option to put more than one address by name. I will put the code of the class and the BD. If you need any more, just let me know. Thanks in advance for your help.

SQL:

-- phpMyAdmin SQL Dump
-- version 4.8.3
-- https://www.phpmyadmin.net/
--
-- Host: localhost:3306
-- Generation Time: Nov 16, 2019 at 11:19 PM
-- Server version: 5.7.24
-- PHP Version: 7.3.7

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `crudoo`
--

-- --------------------------------------------------------

--
-- Table structure for table `contatos`
--

CREATE TABLE `contatos` (
  `id` int(11) UNSIGNED NOT NULL,
  `nome` varchar(100) DEFAULT NULL,
  `email` varchar(100) NOT NULL DEFAULT '',
  `telefone` varchar(20) NOT NULL,
  `endereco` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `contatos`
--

INSERT INTO `contatos` (`id`, `nome`, `email`, `telefone`, `endereco`) VALUES
(2, 'test2e', '[email protected]', '316497', 'rua a'),
(4, 'wanderson', '[email protected]', '985930559', 'rua brasil'),
(6, 'andrezinho', '[email protected]', '2222222222', 'rua j');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `contatos`
--
ALTER TABLE `contatos`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `contatos`
--
ALTER TABLE `contatos`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

PHP:

<?php
class Contato {

    private $pdo;

    public function __construct() {
        $this->pdo = new PDO("mysql:dbname=crudoo;host=localhost", "root", "root");
    }

    public function adicionar($email, $nome, $telefone, $endereco) {
        if($this->existeEmail($email) == false) {
            $sql = "INSERT INTO contatos (nome, email, telefone, endereco) VALUES (:nome, :email, :telefone, :endereco)";
            $sql = $this->pdo->prepare($sql);
            $sql->bindValue(':nome', $nome);
            $sql->bindValue(':email', $email);
            $sql->bindValue(':telefone', $telefone);
            $sql->bindValue(':endereco', $endereco);
            $sql->execute();

            return true;
        } else {
            return false;
        }
    }

    public function getInfo($id) {
        $sql = "SELECT * FROM contatos WHERE id = :id";
        $sql = $this->pdo->prepare($sql);
        $sql->bindValue(':id', $id);
        $sql->execute();

        if($sql->rowCount() > 0) {
            return $sql->fetch();
        } else {
            return array();
        }
    }

    public function getAll() {
        $sql = "SELECT * FROM contatos";
        $sql = $this->pdo->query($sql);

        if($sql->rowCount() > 0) {
            return $sql->fetchAll();
        } else {
            return array();
        }
    }

    public function editar($nome, $email, $telefone, $endereco, $id) {
        if($this->existeEmail($email) == false){
            $sql = "UPDATE contatos SET nome = :nome, email = :email, telefone = :telefone, endereco = :endereco WHERE id = :id";
            $sql = $this->pdo->prepare($sql);
            $sql->bindValue(':nome', $nome);
            $sql->bindValue(':email', $email);
            $sql->bindValue(':telefone', $telefone);
            $sql->bindValue(':endereco', $endereco);
            $sql->bindValue(':id', $id);
            $sql->execute();

            return true;

            } else {
                return false;
            }
    }

    public function excluir($id) {
            $sql = "DELETE FROM contatos WHERE id = :id";
            $sql = $this->pdo->prepare($sql);
            $sql->bindValue(':id', $id);
            $sql->execute();
        }

    private function existeEmail($email) {
        $sql = "SELECT * FROM contatos WHERE email = :email";
        $sql = $this->pdo->prepare($sql);
        $sql->bindValue(':email', $email);
        $sql->execute();

        if($sql->rowCount() > 0) {
            return true;
        } else {
            return false;
        }
    }
}
  • Brother, your question is half vague. It is missing to show what you already have ready. You quoted nothing about your code, language, etc... Edit the question and show what you already have ready.

  • See in the documentation: CREATE TABLE and particularly: FOREIGN KEY Constraints.

  • Address should be another table since it is a compound field(street, log, number, complement), normalizing bank tables can make a gigantic difference, especially in systems that will grow a lot

  • How do I link between tables? What will be the fields in the address table and how to link in the other? I’ll have to modify the PHP code too, right?

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.