Php Mysql make duplicate error customized

[PNewCode]

Hello everyone.
After a lot of research, I figured out how to prevent duplicate entries into the database by making the column to check as Unique (column name is link)

This works and gives an error, and prevents the entry to be sent to the database, unless it's a unique entry then it will submit. So everything works. I just want to change the error to something else

What I can't figure out, is how to change that error to a custom message, or even BETTER a custom image I made

Example "You already submitted that link"
or show "nono.jpg"

This is the error code that shows now (which is doing what it's supposed to, but I want to change it)
 

Quote

Fatal error: Uncaught mysqli_sql_exception: Duplicate entry 'bbb' for key 'link' in /var/www/vhosts/onesong-insert.php:241 Stack trace: #0 /var/www/vhosts/onesong-insert.php(241): mysqli_query() #1 {main} thrown in /var/www/vhosts/onesong-insert.php.php on line 241

and this is the script starting at that line

if (mysqli_query($connS, $sql)) {
      echo " ";
} else {
      echo "Error: " . $sql . "<br>" . mysqli_error($connS);
}

And here is the connS info (note: It's put as "S" because there is more than one connection in the full script, but that isn't a factor in this post because all works perfectly, I just need to change the error code)

error_reporting(E_ALL);
ini_set('display_errors', '1');




// Start the session
session_start();

$servernameS = "Deleted for posting";
$databaseS = "Deleted for posting";
$usernameS = "Deleted for posting";
$passwordS = "Deleted for posting";


// Create connection

$connS = mysqli_connect($servernameS, $usernameS, $passwordS, $databaseS);



  
// Check connection


if (!$connS) {
      die("Connection failed: " . mysqli_connect_error());
}

 
echo " ";


    $link1 = $_POST['link'];
    $link = mysqli_real_escape_string($conn , $link1);


/// Etc etc etc more code and stuff below this, deleted for posting

EDIT: I did try this but it didn't work

 

if (mysqli_query($connS, $sql)) {
      echo " ";
} else {
      echo "That link has already been sent";
}

 

Edited December 26, 2023 by PNewCode

[Barand]

Use try .. catch, for example

// pseudocode - in case you didn't notice)...

try {
   insert the record                                 // attemp the insert
}
catch (exception) {
   if (exception error code is 1062) {               // error detected - was it a duplicate?
       output your duplicate record message          //   yes it was so report it
   } else {
       throw (exception)                             // no it wasn't so let php handle the exception
   }
}

 

[PNewCode]

4 minutes ago, Barand said:

// pseudocode - in case you didn't notice)...

try {
   insert the record                                 // attemp the insert
}
catch (exception) {
   if (exception error code is 1062) {               // error detected - was it a duplicate?
       output your duplicate record message          //   yes it was so report it
   } else {
       throw (exception)                             // no it wasn't so let php handle the exception
   }
}

Please forgive me but where am I putting that in? Or replacing? Do I just add that in and not change what I have but add it before the "insert into...." part?

[Barand]

Your db connection code needs adjustment

//make connection to DB server

    // call mysql_report so that all errors are reported as execptions
    // this saves you from having to check every mysql function call to see if it worked or not

    mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);    
    $connS = mysqli_connect($servernameS, $usernameS, $passwordS, $databaseS);

Now you can remove your code which checks for errors and replace your insert query with the coed I gave you

[PNewCode]

23 minutes ago, Barand said:

Now you can remove your code which checks for errors and replace your insert query with the coed I gave you

Thank you very much. I have below my edit and I followed your other instructions but I obviously did something wrong with it. Now I just get a blank page. Here is what I have along with editing the db connection and removing the error code

 

try {
  
$sql = "INSERT INTO nametable (link) VALUES ('$link')";

}
catch (exception) {
   if (exception error code is 1062) {               // error detected - was it a duplicate?
       echo "Sorry this was already sent";          //   yes it was so report it
   } else {
       throw (exception)                             // no it wasn't so let php handle the exception
   }
}

 

[Barand]

You need to implement the method I gave you in PHP. For instance, executing that sql query would be a good move.

[PNewCode]

31 minutes ago, Barand said:

You need to implement the method I gave you in PHP. For instance, executing that sql query would be a good move.

Okay so I managed to make it work for just one database (see the code I edited that you gave below). I thought this logic would prevent it from continuing at all for the other 2 but that isn't happening.

Reason I have 3 databases for this
1 is the main one where my assistant removes them as they are being used
2 is the backup for one day which is what I'm using for this, so that a duplicate isn't sent for the whole day
3 is for a counter and backup database for all entries

What I need this to do is to prevent the duplicate being sent to all of them if the one in the 2nd database has the duplicate (which is the one I set the LINK column to unique)

I thought about setting the "link" column to unique on all three but that wouldn't do any good since the 1 and 3 databases get entries deleted constantly so there would be little chance of there being a duplicate.

So... another words, I need this to prevent entering at all for any of them, if the database (2) as the duplicate in it.

Here is what I have now

NOTE: Thank you for all your time with this. I very much appreciate it
 

// show all errors
error_reporting(E_ALL);
ini_set('display_errors', '1');




// Start the session
session_start();

// database 1
$servername = "removed for posting";
$database = "removed for posting";
$username = "removed for posting";
$password = "removed for posting";


// database 2
$servernameS = "removed for posting";
$databaseS = "removed for posting";
$usernameS = "removed for posting";
$passwordS = "removed for posting";


// database 3
$servernameD = "removed for posting";
$databaseD = "removed for posting";
$usernameD = "removed for posting";
$passwordD = "removed for posting";



// Create connection
mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);


$conn = mysqli_connect($servername, $username, $password, $database);
$connS = mysqli_connect($servernameS, $usernameS, $passwordS, $databaseS);
$connD = mysqli_connect($servernameD, $usernameD, $passwordD, $databaseD);


  
// Check connection
if (!$conn) {
      die("Connection failed: " . mysqli_connect_error());
}

if (!$connS) {
      die("Connection failed: " . mysqli_connect_error());
}

if (!$connD) {
      die("Connection failed: " . mysqli_connect_error());
}
 
echo " ";


// what is posting from the form
    $link1 = $_POST['link'];
    $link = mysqli_real_escape_string($conn , $link1);




// enter into database if no duplicate from db 2
// this should add to all three databases if no duplicate but if there is a duplicate in database 2 then it still sends to the other 2 databases

try {

$sql = "INSERT INTO nametable (link) VALUES ('$link')";

} catch (mysqli_sql_exception $e) {
    if ($e->getCode() == 1062) {
        echo "Sorry this was already put in tonight";
    } else {
        throw $e;// in case it's any other error
    }

}



// close all connections
mysqli_close($conn);
mysqli_close($connS);
mysqli_close($connD);

}
?>

 

[Barand]

The code you post doesn't insert anything, anywhere. All is does is define the content of the string $sql.

Are all three databases on the same server (host) or are you connecting to three separate hosts?

[PNewCode]

32 minutes ago, Barand said:

Are all three databases on the same server (host) or are you connecting to three separate hosts?

Yes they are all on the same hosting. Just 3 different databases.
And sorry, I thought this line was the insert
 

$sql = "INSERT INTO nametable (link) VALUES ('$link')";

 

[Barand]

If they are 3 databases on the same server all with the same usernames and passwords then you only need a single connection. A connection is to a server and not to to a database.

Try this, so that if any of the inserts fail the other two are cancelled.

// make connection to DB server

     // call mysql_report so that all errors are reported as execptions
     // this saves you from having to check every mysql function call to see if it worked or not

    mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);    
    $conn = mysqli_connect($servernameS, $usernameS, $passwordS, $databaseS);        // NOTE - single connection $conn
        
    if ($_SERVER['REQUEST_METHOD']=='POST')  {
        $link = trim($_POST['link']);
        if ($link != '')  {
            try {
                $conn->begin_transaction();
                $stmt1 = $conn->prepare("INSERT INTO DB1.nametable (link) VALUES (?)");   // define which DB to use - DB1, DB2 or DB3
                $stmt2 = $conn->prepare("INSERT INTO DB2.nametable (link) VALUES (?)");
                $stmt3 = $conn->prepare("INSERT INTO DB2.nametable (link) VALUES (?)");
                $stmt1->bind_param('i', $link);
                $stm1t->execute();
                $stmt2->bind_param('i', $link);
                $stmt2->execute();
                $stmt3->bind_param('i', $link);
                $stmt3->execute();
                $conn->commit();
            }
            catch(mysqli_sql_exception $e)  {
                $conn->rollback();
                if ($e->getCode() == 1062) {
                    echo "Sorry, this was already sent";
                }
                else {
                    throw $e;
                }
            }
        }
    }

 

[PNewCode]

8 minutes ago, Barand said:

If they are 3 databases on the same server all with the same usernames and passwords then you only need a single connection. A connection is to a server and not to to a database.

No there are over 30 databases on the server. But there are three that being used for this purpose. Also, they all have the same table name and password to connect, but not all the user names are the same

[Barand]

Forget my last reply then. That relied on there being a single connection. You are going to have to do it the hard way in your code rather than letting the mysql handle it. Only try the other 2 inserts if the first does not fails with a duplicate error.

[PNewCode]

I thought I'd make a page to check if it's already entered in database 2, and if it is then it shows an error image. But if it's not a duplicate then it redirects to the original page as normal. So I'm hunting a solution for that as it may be the easiest route to go.

Something like

Page 1
Gets the form and checks if the link being sent is a duplicate. If it is then it goes to page 2.
If it's not a duplicate then it goes to page 3

Page 2
Shows a custom message or image to show that the link has already been submitted

Page 3
Inserts the link into the database because in page 1, it was not a duplicate link being submitted

Cross your fingers I don't lose the rest of my hair trying to make this happen haha. Thank you for your efforts @Barand

[PNewCode]

For anyone that comes here to see if there is a solution, this is how I ended up going about it. It's not quite the resolution to fit exactly what to fix in the original question but it's a work-around that works perfectly.
And thanks to @Barand for taking the time to help, as it lead me to go this route and learn something new.

I added this at the top of the php page to check for the duplicate first and if no duplicate then it carries out the rest of the page (that does the instert, bla bla bla stuff).

And yes I know the font tags are frowned on but it works and it's an old habbit haha. Also, I had to remove the unique key in the database to make it work as well.

<?php



error_reporting(E_ALL);
ini_set('display_errors', '1');



$servername = "removed for posting";
$database = "removed for posting";
$username = "removed for posting";
$password = "removed for posting";


// Start the session
session_start();



// Create connection
$conn = mysqli_connect($servername, $username, $password, $database);

// Check connection
if (!$conn) {
      die("Connection failed: " . mysqli_connect_error());
}


 
echo " ";



    $link1 = $_POST['link'];
    $link = mysqli_real_escape_string($conn , $link1);

    

 
$dupesql = mysqli_query($conn,"SELECT * FROM nametable where link = '$link'");

if(!mysqli_num_rows($dupesql)<=0){

echo '<center><br><br><br><br><font color="#FFFFFF" size="7" face="Verdana, Arial, Helvetica, sans-serif"><b>Sorry!</b></font><br><br><br><br><font color="#FFFFFF" size="6" face="Verdana, Arial, Helvetica, sans-serif">This link<br>has already been entered.<br><br>Please choose a link</font></center>';

}else{

 

[Barand]

That is nothing like the solution I suggested. In fact you ignored just about everything in my posts.

If two people run your code simultaneously they will both think it is safe to add the same number and you are back where you started.

[PNewCode]

On 12/27/2023 at 10:07 AM, Barand said:

That is nothing like the solution I suggested. In fact you ignored just about everything in my posts.

Yes, thats why I said thanks for taking the time and it lead me to go this route. If you didn't help me to what had me not be able to use exactly that, then I wouldn't have thought about using this method. I didn't ignore anything. I tried your method and it failed. This method that I used works. Problem solved. I was trying to give you a positive shout out for helping me lead to that resolution that I like.

Edit: This was tested on 10 people submitting at the same time and it worked fine. All it was intended for was to check duplicate entries for a link and then kick back if someone beat them to it. It works flawlessly

Edited December 28, 2023 by PNewCode