How to generate a student ID in PHP? And how to avoid goto?

Question

I want to generate a unique student ID. The format that I want is the last two digits of the current year plus 5 digits after that. For example: 2000001, 2000002, 2000003.. etc.. This is the extract of my code right now.

$pre = substr(strval(date("Y")),2);
$num = 1;
include "dbh.inc.php"; // this file merely creates the $conn variable that connects to mysql database.
$sql_cmd = "SELECT id FROM students WHERE id=?;";
$stmt = $conn->stmt_init();
if ($stmt) {
    $prepare = $stmt->prepare($sql_cmd);
    if ($prepare) {
        bind:
        $studentid = $pre . str_repeat('0', (4 - strlen($num)) ) . strval($num);
        $bind = $stmt->bind_param('s', $studentid);
        if ($bind) {
            $result = $stmt->execute();
            if ($result) {
                $num++;
                goto bind;
            }
            else {
                // insert student here using $studentid
            }
        }
    }
}

But I need to improve this because:

1. It uses a goto (I want to avoid it)
2. It seems overkill to prepare, bind, execute, etc everytime and query the database every loop.
3. It is obviously slow.

Please let me know if there is a better way of doing this.

Answer 1

You can generate new id on the MySQL side:

include "dbh.inc.php"; // this file merely creates the $conn variable that connects to mysql database.

$sql = 
    "SELECT CONCAT("
        . "DATE_FORMAT(CURDATE(), '%y'), "
        . "LPAD(COALESCE(MAX(RIGHT(id, 5)) + 1, 1), 5, '0')"
    . ") AS new_id "
    . "FROM students";

$result = $conn->query($sql);
if ($result) {
    if ($row = $result->fetch_assoc()) {
       // insert student here using $row['new_id']
    }
}

Or another option is to create an trigger on insert:

DELIMITER //

CREATE TRIGGER students_tr_bi BEFORE INSERT ON students
FOR EACH ROW
BEGIN

    SET NEW.id = (
        SELECT CONCAT(
            DATE_FORMAT(CURDATE(), '%y'),
            LPAD(COALESCE(MAX(RIGHT(id, 5)) + 1, 1), 5, '0')
        ) FROM students
    );
      
END//

DELIMITER ;

-- Usage:
INSERT INTO students (name) VALUES ('John');

Answer 2

Generate Student certificate in php mysql this code is useful to generate student certificate according to student id (data) in database

Points to remember before executing the code: 1.download fpdf library and include fpdf.php file 2.include your favourite font ttf file 3.Here we use php GD library : imagecreatefrom jpeg function

Most Important Point: Here we convert image into pdf then then place student data in certificate accordingly

<?php
require_once 'connection.php';
require 'assets/fpdf.php';
error_reporting(E_ALL);
ini_set('display_errors', 1);

  $id = $_GET['id'];  
  $franQuery   = "SELECT * FROM `certificate` where id=$id";
  // $franQuery   = "SELECT * FROM `certificate` where id=255";
  $franRecords = mysqli_query($con, $franQuery);
  $sr = 1;

  if($franRecords->num_rows>0){

    while ($row = mysqli_fetch_assoc($franRecords)) { 
      $font = __DIR__ . '/assets/Poppins-Bold.ttf';

      $image = imagecreatefromjpeg('assets/certificate.jpg');
      $color = imagecolorallocate($image,1,1,1);
      $name = $row['student_name'];
      $sondaugheroff = $row['sondaugheroff'];
      $school =  $row['school'];
      $marks_per = $row['marks_per'];
      $level = $row['level'];
      $cert_date = date('d/m/Y', strtotime($row['cert_date']));
      $place = $row['place'];
      $address = $row['address'];
      
      imagettftext($image,13,0,415,308,$color,$font,$name);
      imagettftext($image,13,0,175,340,$color,$font,$sondaugheroff);
      imagettftext($image,13,0,650,340,$color,$font,$school);
      imagettftext($image,13,0,480,375,$color,$font,$marks_per);
      imagettftext($image,13,0,160,410,$color,$font,$level);
      imagettftext($image,13,0,390,465,$color,$font,$cert_date);
      imagettftext($image,13,0,1030,465,$color,$font,$place);
      imagettftext($image,13,0,1145,465,$color,$font,$address);

      // header('Content-Type: image/jpg'); 
      $save =   imagejpeg($image,"assets/".$name.".jpg");
  
      $pdf = new FPDF('L','in',[11.7,8.27]);
      $pdf ->AddPage();
      
      $pdf->Image("assets/".$name.".jpg",0,0,11.7,8.27);
   
      $pdf->Output("assets/".$name.".pdf","I");
      imagedestroy($image);
    }
  }else{
    echo '<script>alert("Sorry... Your Certificate Not found")</script>';
  }

?>