Create a React Application with MySQL Database

Database Creation

Create a simple contact form database using MySQL. First, you’ll need to have MySQL installed and running on your system. Once you have that set up, follow the steps below:

1. Create a new database:

CREATE DATABASE contact_form;

2. Switch to the newly created database:

USE contact_form;

3. Create a table to store the contact form submissions:

CREATE TABLE submissions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  message TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

4. Test the table by inserting a sample record:

INSERT INTO submissions (name, email, message) 
VALUES ('John Doe', 'johndoe@example.com', 'Hello, this is a test message.');

5. Retrieve the submitted data from the table:

SELECT * FROM submissions;

That’s it! You now have a simple contact form database using MySQL. You can modify the table structure as per your requirements.

React Application Creation

Here are the steps to create a React application that collects contact information from users, stores it in a database, and allows you to perform create, read, update, and delete operations.

Please note that the instructions provided are a basic outline, and you may need to install additional dependencies based on your development environment.

1. Set up a new React application using Create React App:

npx create-react-app contact-form-app
cd contact-form-app

2. Install necessary dependencies:

npm install axios react-router-dom

3. Create a new folder called src/components and inside it, create four files: ContactForm.js, ContactList.js, ContactItem.js, and EditContactForm.js.
4. Replace the content of src/App.js with the following code:

import React from 'react';
import { BrowserRouter as Router, Switch, Route, Link } from 'react-router-dom';
import ContactForm from './components/ContactForm';
import ContactList from './components/ContactList';
import EditContactForm from './components/EditContactForm';

function App() {
  return (
    <Router>
      <div>
        <nav>
          <ul>
            <li>
              <Link to="/">Home</Link>
            </li>
            <li>
              <Link to="/contacts">Contacts</Link>
            </li>
          </ul>
        </nav>

        <Switch>
          <Route path="/contacts/edit/:id" component={EditContactForm} />
          <Route path="/contacts" component={ContactList} />
          <Route path="/" component={ContactForm} />
        </Switch>
      </div>
    </Router>
  );
}

export default App;

5. Open src/components/ContactForm.js and replace the content with the following code:

import React, { useState } from 'react';
import axios from 'axios';

function ContactForm() {
  const [name, setName] = useState('');
  const [email, setEmail] = useState('');
  const [message, setMessage] = useState('');

  const handleSubmit = async (e) => {
    e.preventDefault();
    
    try {
      const response = await axios.post('/api/contacts', { name, email, message });
      console.log(response.data); // You can modify this to show a success message
      setName('');
      setEmail('');
      setMessage('');
    } catch (error) {
      console.error(error);
    }
  };

  return (
    <div>
      <h2>Contact Form</h2>
      <form onSubmit={handleSubmit}>
        <div>
          <label htmlFor="name">Name:</label>
          <input type="text" id="name" value={name} onChange={(e) => setName(e.target.value)} />
        </div>
        <div>
          <label htmlFor="email">Email:</label>
          <input type="email" id="email" value={email} onChange={(e) => setEmail(e.target.value)} />
        </div>
        <div>
          <label htmlFor="message">Message:</label>
          <textarea id="message" value={message} onChange={(e) => setMessage(e.target.value)}></textarea>
        </div>
        <button type="submit">Submit</button>
      </form>
    </div>
  );
}

export default ContactForm;

6. Open src/components/ContactList.js and replace the content with the following code:

import React, { useState, useEffect } from 'react';
import axios from 'axios';
import { Link } from 'react-router-dom';

function ContactList() {
  const [contacts, setContacts] = useState([]);

  useEffect(() => {
    const fetchContacts = async () => {
      try {
        const response = await axios.get('/api/contacts');
        setContacts(response.data);
      } catch (error) {
        console.error(error);
      }
    };

    fetchContacts();
  }, []);

  return (
    <div>
      <h2>Contact List</h2>
      <ul>
        {contacts.map((contact) => (
          <li key={contact.id}>
            <Link to={`/contacts/edit/${contact.id}`}>{contact.name}</Link>
          </li>
        ))}
      </ul>
    </div>
  );
}

export default ContactList;

7. Open src/components/ContactItem.js and replace the content with the following code:

import React from 'react';

function ContactItem({ contact }) {
  return (
    <div>
      <h3>{contact.name}</h3>
      <p>Email: {contact.email}</p>
      <p>Message: {contact.message}</p>
    </div>
  );
}

export default ContactItem;

8. Open src/components/EditContactForm.js and replace the content with the following code:

import React, { useState, useEffect } from 'react';
import axios from 'axios';
import { useParams } from 'react-router-dom';

function EditContactForm() {
  const { id } = useParams();
  const [name, setName] = useState('');
  const [email, setEmail] = useState('');
  const [message, setMessage] = useState('');

  useEffect(() => {
    const fetchContact = async () => {
      try {
        const response = await axios.get(`/api/contacts/${id}`);
        const contact = response.data;
        setName(contact.name);
        setEmail(contact.email);
        setMessage(contact.message);
      } catch (error) {
        console.error(error);
      }
    };

    fetchContact();
  }, [id]);

  const handleSubmit = async (e) => {
    e.preventDefault();
    
    try {
      const response = await axios.put(`/api/contacts/${id}`, { name, email, message });
      console.log(response.data); // You can modify this to show a success message
    } catch (error) {
      console.error(error);
    }
  };

  return (
    <div>
      <h2>Edit Contact</h2>
      <form onSubmit={handleSubmit}>
        <div>
          <label htmlFor="name">Name:</label>
          <input type="text" id="name" value={name} onChange={(e) => setName(e.target.value)} />
        </div>
        <div>
          <label htmlFor="email">Email:</label>
          <input type="email" id="email" value={email} onChange={(e) => setEmail(e.target.value)} />
        </div>
        <div>
          <label htmlFor="message">Message:</label>
          <textarea id="message" value={message} onChange={(e) => setMessage(e.target.value)}></textarea>
        </div>
        <button type="submit">Update</button>
      </form>
    </div>
  );
}

export default EditContactForm;

9. Replace the content of src/index.js with the following code:

import React from 'react';
import ReactDOM from 'react-dom';
import './index.css';
import App from './App';

ReactDOM.render(<App />, document.getElementById('root'));

10. In the root directory of the project, create a new folder called server. Inside the server folder, create two files: server.js and database.js.
11. Open server/server.js and replace the content with the following code:

const express = require('express');
const app = express();
const cors = require('cors');
const database = require('./database');

app.use(cors());
app.use(express.json());

app.get('/api/contacts', async (req, res) => {
  const contacts = await database.getContacts();
  res.json(contacts);
});

app.get('/api/contacts/:id', async (req, res) => {
  const { id } = req.params;
  const contact = await database.getContact(id);
  res.json(contact);
});

app.post('/api/contacts', async (req, res) => {
  const { name, email, message } = req.body;
  const contact = await database.createContact(name, email, message);
  res.json(contact);
});

app.put('/api/contacts/:id', async (req, res) => {
  const { id } = req.params;
  const { name, email, message } = req.body;
  const contact = await database.updateContact(id, name, email, message);
  res.json(contact);
});

app.delete('/api/contacts/:id', async (req, res) => {
  const { id } = req.params;
  await database.deleteContact(id);
  res.sendStatus(204);
});

const port = process.env.PORT || 5000;
app.listen(port, () => {
  console.log(`Server is running on port ${port}`);
});

12. Open server/database.js and replace the content with the following code:

const mysql = require('mysql');
const util = require('util');

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'your_mysql_username',
  password: 'your_mysql_password',
  database: 'contact_form',
});

connection.connect((err) => {
  if (err) throw err;
  console.log('Connected to MySQL database');
});

const query = util.promisify(connection.query).bind(connection);

const getContacts = async () => {
  const contacts = await query('SELECT * FROM submissions');
  return contacts;
};

const getContact = async (id) => {
  const contact = await query('SELECT * FROM submissions WHERE id = ?', [id]);
  return contact[0];
};

const createContact = async (name, email, message) => {
  const result = await query('INSERT INTO submissions (name, email, message) VALUES (?, ?, ?)', [name, email, message]);
  const contactId = result.insertId;
  const contact = await getContact(contactId);
  return contact;
};

const updateContact = async (id, name, email, message) => {
  await query('UPDATE submissions SET name = ?, email = ?, message = ? WHERE id = ?', [name, email, message, id]);
  const contact = await getContact(id);
  return contact;
};

const deleteContact = async (id) => {
  await query('DELETE FROM submissions WHERE id = ?', [id]);
};

module.exports = {
  getContacts,
  getContact,
  createContact,
  updateContact,
  deleteContact,
};

Make sure to replace 'your_mysql_username' and 'your_mysql_password' in server/database.js with your MySQL username and password.

13. In the root directory of the project, open package.json and add the following scripts:

"proxy": "http://localhost:5000",
"start:server": "node server/server.js",
"start:client": "npm start",
"start": "concurrently \"npm run start:server\" \"npm run start:client\""

14. Start the application by running the following command:

npm start

Now you should be able to access the contact form at http://localhost:3000 and the contact list at http://localhost:3000/contacts. You can create, read, update, and delete contacts using the provided forms.

Note: This is a basic example to give you a starting point. In a real-world application, you would add form validations, error handling, and improve the user interface as per your requirements.