display query error message as an alert in php?

Try like this

if (mysql_error()) {
    echo "<script type='text/javascript'>
       alert('Error, You have not used our services before, so no details for you to visit and explore');
    </script>";
}

EVen you can write the script without echoing it(Not sure but works maximum) and try to avoid using mysql_* functions due to they are depricated.Instead use mysqli_* functions or PDO statements

You can simply add a few HTML and/or JS in your die :

if (mysql_error()) 
    die('<script type="text/javascript">
             alert("Error, You have not used our services before, so no details for you to visit and explore
         </script>' );

PHP DOC for die and exit

Technically, that query should never return an error unless either:

1. the table or column doesn't exist,
2. there's a parse error in the statement.

In both cases, the error message you want to show doesn't apply; you will want to test the number of affected rows instead.

That said, it's not really possible to create an alert using server-side code; but you could create an AJAX endpoint instead:

if (errors) {
    $response = array('error' => 'Error message here');
} else {
    // update this if you want to pass data back to caller
    $response = array();
}
header('Content-Type: application/json');
echo json_encode($response);
exit;

On the client side you would submit the form using AJAX like so:

$.ajax({
    url: '/path/to/endpoint',
    data: { whatever },
    success: function(response) {
        if (response.error) {
            alert(response.error);
            return;
        }
        // yay, it worked
    }
});

Alternatively, and perhaps to serve older browsers, you could just generate a (stylized) HTML response. I personally dislike a page refresh followed by an alert() dialogue window.

Sorry, but your code is plain wrong (you're doing mysql_query twice). I guess you meant

$sql="UPDATE customers SET  password='$id' WHERE c_number='$users1' ";
$result = mysql_query($sql);
if (mysql_error()) die('Error, You have not used our services before, so no details for you to visit and explore');

To quote the PHP manual: *This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information*

But anyway, let's make this work (and more secure):

$sql="UPDATE customers SET  password='".myqsl_escape_string($id)."' WHERE c_number='".mysql_escape_string($users1)."' ";

This prevents SQL injection, by escaping every possible special char in both $id and $users1 (i suspect at least one of these is user submitted data).

$result = mysql_query($sql);
if ($result===false) die('Error, You have not used our services before, so no details for you to visit and explore');

The key here is to use the result of mysql_query, which is FALSE if the query failed. It is good practice to use the '===' comparator here, since we don't want to look for 0 or an empty string, but only the boolean false. For more information, see the PHP mysql_query manual page