Text : How To Retrieve Records from MySQL Database in Next or React JS

 Code for the topic How to Retrieve Records from MySQL Database in Next JS.

API code:  contacts.php

<?php

  header('Content-Type: application/json');

  header("Access-Control-Allow-Origin: *");

  class Contact {

    // login

    function getContacts($json){

      // {userId : 1}

      include 'connection-pdo.php';

      $json = json_decode($json, true);

      $sql = "SELECT * FROM tblcontacts

              WHERE contact_userId = :userId

              ORDER BY contact_name";

      $stmt = $conn->prepare($sql);

      $stmt->bindParam(':userId', $json['userId']);

      $stmt->execute();

      $returnValue = $stmt->fetchAll(PDO::FETCH_ASSOC);

      unset($conn); unset($stmt);

      return json_encode($returnValue);

    }

  }

  //submitted by the client - operation and json

  if ($_SERVER['REQUEST_METHOD'] == 'GET'){

    $operation = $_GET['operation'];

    $json = $_GET['json'];

  }else if($_SERVER['REQUEST_METHOD'] == 'POST'){

    $operation = $_POST['operation'];

    $json = $_POST['json'];

  }

  $contact = new Contact();

  switch($operation){

    case "getContacts":

      echo $contact->getContacts($json);

      break;

    }

?>

Login

"use client";

import { useState } from "react";

import axios from 'axios';

import { useRouter } from 'next/navigation';

const Login = () => {

  const [username, setUsername] = useState("");

  const [password, setPassword] = useState("");

  const router = useRouter();

  const login = async() => {

    const url = "http://localhost/contacts-api/users.php";

    const jsonData = {username:username, password:password}

    var response = await axios.get(url, {

      params:{json: JSON.stringify(jsonData), operation: "login"}

    });

    //check if valid user

    if(response.data.length > 0){

      let params = new URLSearchParams();

      params.append('fullname', response.data[0].usr_fullname);

      params.append('userId', response.data[0].usr_id);

      router.push(`/main?${params}`);

    }else{

      alert("Invlid username or password.");

    }

  }

  return (

    <>

      <h1>Login to MySQL Database</h1>

      <input

        type="text"

        placeholder="Username"

        value={username}

        onChange={(e) => {

          setUsername(e.target.value);

        }}

      />

      <br/>

      <input

        type="password"

        placeholder="Password"

        value={password}

        onChange={(e) => {

          setPassword(e.target.value);

        }}

      />

      <br/>

      <button onClick={login}>Login</button>

    </>

  );

};

export default Login;

Main

"use client";

import { useSearchParams } from "next/navigation";

import { useEffect, useState } from "react";

import axios from "axios";

const Main = () => {

  const searchParams = useSearchParams();

  const [contactsList, setContactsList] = useState([]);

  const fullName = searchParams.get("fullname");

  const userId = searchParams.get("userId");

  const getContacts = async () => {

    const url = "http://localhost/contacts-api/contacts.php";

    const jsonData = { userId: userId };

    const response = await axios.get(url, {

      params: { json: JSON.stringify(jsonData), operation: "getContacts" },

    });

    setContactsList(response.data);

  };

  useEffect(() => {

    getContacts();

  }, []);

  return (

    <>

      <h1>Contacts of {fullName}</h1>

      <table>

        <thead>

          <th>Name</th>

          <th>Address</th>

          <th>Phone</th>

          <th>Actions</th>

        </thead>

        <tbody>

          {contactsList.map((contact, index) => {

            return (

              <tr key={index}>

                <td>{contact.contact_name}</td>

                <td>{contact.contact_address}</td>

                <td>{contact.contact_phone}</td>

                <td>

                  <button>Update</button>

                </td>

              </tr>

            );

          })}

        </tbody>

      </table>

    </>

  );

};

export default Main;