Database

Re: Sql server deadlock issue with table bulk insert and update on same table

30-Jun-20 23:31

Sorry, I don't know. Check the documentation to see what it recommends.

David Mujica1-Jul-20 5:36

David Mujica

1-Jul-20 5:36

If you are using a script to initiate the bulk load, at the end of the script start the process for updating.
That is a simple way of synchronizing the processes.
Java | [Coffee]

Re: Sql server deadlock issue with table bulk insert and update on same table

kali siddhu15-Jul-20 23:44

kali siddhu

15-Jul-20 23:44

Both process are different based to the file availability process will kick start. Monthly I am getting deadlock issue when both the files are available at same time by utilizing the same table.

As per my knowledge, I need to set lock/ Isolation level at store procedure to overcome this issue.

Re: Sql server deadlock issue with table bulk insert and update on same table

ZurdoDev1-Jul-20 7:03

ZurdoDev

1-Jul-20 7:03

Do one then do the other.

Social Media - A platform that makes it easier for the crazies to find each other.

Everyone is born right handed. Only the strongest overcome it.
Fight for left-handed rights and hand equality.

Http 500 error with register page

and180y29-Jun-20 4:31

29-Jun-20 4:31

Hi Could anyone help me with the following code? It was working fine until I inserted a username column in the DB table and edited this to suit (I thought) It gives an HTTP500 error page not working.

<?php
// Include config file
require_once "config.php";
 
// Define variables and initialize with empty values
$email = $password = $confirm_password = "";
$username_err = $username = $confirm_username_err = "";
$email_err = $password_err = $confirm_password_err = "";
 
// Processing form data when form is submitted
if($_SERVER["REQUEST_METHOD"] == "POST"){
 
    // Validate username
    if(empty(trim($_POST["email"]))){
        $email_err = "Please enter your email.";
    } else{
        // Prepare a select statement
        $sql = "SELECT id FROM users WHERE email = ?";
        
        if($stmt = mysqli_prepare($link, $sql)){
            // Bind variables to the prepared statement as parameters
            mysqli_stmt_bind_param($stmt, "s", $param_email);
            
            // Set parameters
            $param_email = trim($_POST["email"]);
            
            // Attempt to execute the prepared statement
            if(mysqli_stmt_execute($stmt)){
                /* store result */
                mysqli_stmt_store_result($stmt);
                
                if(mysqli_stmt_num_rows($stmt) == 1){
                    $email_err = "This email is already registered.";
                } else{
                    $email = trim($_POST["email"]);
                }
            } else{
                echo "Oops! Something went wrong. Please try again later.";
            }

            // Close statement
            mysqli_stmt_close($stmt);
        }
	 }
    
    // Validate username
    if(empty(trim($_POST["username"]))){
        $username_err = "Please enter a username.";     
    } elseif(strlen(trim($_POST["username"])) < 6){
        $username_err = "username must have atleast 6 characters.";
    } else{
        $username = trim($_POST["username"]);
    }	
    }
    
    // Validate password
    if(empty(trim($_POST["password"]))){
        $password_err = "Please enter a password.";     
    } elseif(strlen(trim($_POST["password"])) < 6){
        $password_err = "Password must have atleast 6 characters.";
    } else{
        $password = trim($_POST["password"]);
    }
    
    // Validate confirm password
    if(empty(trim($_POST["confirm_password"]))){
        $confirm_password_err = "Please confirm password.";     
    } else{
        $confirm_password = trim($_POST["confirm_password"]);
        if(empty($password_err) && ($password != $confirm_password)){
            $confirm_password_err = "Password did not match.";
        }
    }
    
    // Check input errors before inserting in database
    if(empty($email_err) && empty($password_err) && empty($confirm_password_err)){
        
        // Prepare an insert statement
        $sql = "INSERT INTO users (email, username, password) VALUES (?, ?)";
         
        if($stmt = mysqli_prepare($link, $sql)){
            // Bind variables to the prepared statement as parameters
            mysqli_stmt_bind_param($stmt, "ss", $param_email,$param_username $param_password);
            
            // Set parameters
            $param_email = $email;
			$param_username = $username
            $param_password = password_hash($password, PASSWORD_DEFAULT); // Creates a password hash
            
            // Attempt to execute the prepared statement
            if(mysqli_stmt_execute($stmt)){
                // Redirect to login page
                header("location: login.php");
            } else{
                echo "Something went wrong. Please try again later.";
            }

            // Close statement
            mysqli_stmt_close($stmt);
        }
    }
    
    // Close connection
    mysqli_close($link);
}
?>
 
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Sign Up</title>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.css">
    <style type="text/css">
        body{ font: 14px sans-serif; }
        .wrapper{ width: 350px; padding: 20px; }
    </style>
</head>
<body>
    <div class="wrapper">
        <h2>Sign Up</h2>
        <p>Please fill this form to create an account.</p>
        <form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]); ?>" method="post">
            <div class="form-group <?php echo (!empty($email_err)) ? 'has-error' : ''; ?>">
                <label>Email</label>
                <input type="text" name="email" class="form-control" value="<?php echo $email; ?>">
                <?php echo $email_err; ?>
            </div>
			<form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]); ?>" method="post">
            <div class="form-group <?php echo (!empty($username_err)) ? 'has-error' : ''; ?>">
                <label>Username</label>
                <input type="text" name="username" class="form-control" value="<?php echo $username; ?>">
                <?php echo $username_err; ?>
            </div> 
            <div class="form-group <?php echo (!empty($password_err)) ? 'has-error' : ''; ?>">
                <label>Password</label>
                <input type="password" name="password" class="form-control" value="<?php echo $password; ?>">
                <?php echo $password_err; ?>
            </div>
            <div class="form-group <?php echo (!empty($confirm_password_err)) ? 'has-error' : ''; ?>">
                <label>Confirm Password</label>
                <input type="password" name="confirm_password" class="form-control" value="<?php echo $confirm_password; ?>">
                <?php echo $confirm_password_err; ?>
            </div>
            <div class="form-group">
                <input type="submit" class="btn btn-primary" value="Submit">
                <input type="reset" class="btn btn-default" value="Reset">
            </div>
            <p>Already have an account? <a href="login.php">Login here</a>.</p>
        </form>
    </div>    
</body>
</html>

ZurdoDev29-Jun-20 4:33

ZurdoDev

29-Jun-20 4:33

500 is a generic error from the server. You'll need to debug it or get the details of the error.

Social Media - A platform that makes it easier for the crazies to find each other.

Everyone is born right handed. Only the strongest overcome it.
Fight for left-handed rights and hand equality.

and180y29-Jun-20 4:45

29-Jun-20 4:45

Thanks for replying ZurdoDev. Smile | :)

I was thinking it had to be something in the prepared statement that I have done wrong but can't figure it out. I ran a query to create the table and columns so assume that must be correct?

ZurdoDev29-Jun-20 4:49

ZurdoDev

29-Jun-20 4:49

Somewhere you should be able to get the details of the error. I don't do much php but if you aren't seeing the error when you run it then maybe look in the logs.

Social Media - A platform that makes it easier for the crazies to find each other.

Everyone is born right handed. Only the strongest overcome it.
Fight for left-handed rights and hand equality.

Richard Deeming29-Jun-20 5:15

Richard Deeming

29-Jun-20 5:15

and180y wrote:

PHP

$sql = "INSERT INTO users (email, username, password) VALUES (?, ?)";
...
mysqli_stmt_bind_param($stmt, "ss", $param_email, $param_username $param_password);

Two obvious problems:

You're inserting into three columns, but you've only specified two values:

PHP

$sql = "INSERT INTO users (email, username, password) VALUES (?, ?, ?)";

You've only specified types for two parameters, and you're missing a comma between the last two parameters:

PHP

mysqli_stmt_bind_param($stmt, "sss", $param_email, $param_username, $param_password);

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

and180y29-Jun-20 5:21

29-Jun-20 5:21

Thanks for replying Richard. Smile | :)

I've fixed those errors but still having the same issue, it loaded the form once but now just a white page with the error.

and180y29-Jun-20 7:40

29-Jun-20 7:40

I have played around starting again from the basic code and adding in what I need and checking for errors, all is fine till I put the code below in.

 }

// Validate email
if(empty(trim($_POST["email"]))){
    $email_err = "Please enter your email.";
} elseif(strlen(trim($_POST["email"])) < 6){
    $email_err = "email must have at least 6 characters.";
} else{
    $email = trim($_POST["email"]);
}

Richard MacCutchan29-Jun-20 9:36

29-Jun-20 9:36

PHP

if(empty(trim($_POST["email"]))){

If the email variable is blank then calling trim on it will fail. You should change it to:

PHP

if(empty($_POST["email"])){

and180y29-Jun-20 12:37

29-Jun-20 12:37

Working perfect now. Thanks Richard. Smile | :)

Richard MacCutchan29-Jun-20 21:44

29-Jun-20 21:44

You're welcome.

and180y1-Jul-20 8:03

1-Jul-20 8:03

Richard, is there an error log generated somewhere for the DB? I get an intermittent fault (loads of folk saying the can't register but I can see some are able to) This is the error that shows on the form so not of much use. lol "Something went wrong. Please try again later".

Richard MacCutchan1-Jul-20 9:10

1-Jul-20 9:10

Sorry, I don't know offhand. Check the MySQL documentation. I see that you are posting that message in your code. So you need to check why the call to mysqli_stmt_execute failed.

and180y1-Jul-20 9:42

1-Jul-20 9:42

Thanks Richard,

I edited as you said the other night but wonder if my issue is somewhere in here?

if(empty($_POST["email"])){
     $email_err = "Please enter your email.";
 } elseif(strlen(trim($_POST["email"])) < 6){
     $email_err = "email must have at least 6 characters.";
 } else{
     $email = trim($_POST["email"]);
 }
 }

and180y1-Jul-20 13:11

1-Jul-20 13:11

Thanks Richard, progress made.

There is no error as such, if you try to register with the same email twice (yes I thought that as well) it throws the error I mentioned but isn't telling them the email is already registered. It does for username but the email was the bit I added in. Smile | :)

Richard MacCutchan1-Jul-20 22:37

1-Jul-20 22:37

Did you not realise that you first need to check whether the email is already registered?

and180y2-Jul-20 4:57

2-Jul-20 4:57

Hi Richard, thanks. Smile | :)

Everyday is a school day dealing with the humans I do. Smile | :)

Reporting data

Joan M19-Jun-20 10:03

Joan M

19-Jun-20 10:03

Hi all,

I've done a small database and PHP based web page to be able to track my working hours easily.

Last week a customer asked me how many time in total each month I had spend in one project and I had to get all the times, filter by month manually and sum it all to give him an answer... a matter of 10 minutes, but I want to automate this kind of things...

My question is:

I will have a table that will be able to be grouped by day, week, month, year or not grouped, but then, I have to get the sum of all hours that I worked in my office, the sum of the hours that I worked out of my office, the sum of the hours travelling...

I can only think of doing something like

SQL

SELECT  sumA,
        sumB,
        sumC
FROM
(
  SELECT sumA,
         type,
         "0" as sumB,
         "0" as sumC
  From table1

  WHERE type = A

UNION ALL

  SELECT sumB,
         type,
         "0" as sumA,
         "0" as sumC
  From table1

  WHERE type = B

UNION ALL

  SELECT sumC,
         type,
         "0" as sumA,
         "0" as sumB
  From table1

  WHERE type = C
)
AS VIEW1

Is this the way to do it?

I want to show the data in columns:

            Hours at the office   |   hours out of the office   |   hours travelling
_____________________________________________________________________________________________________
JANUARY    |                      |                             |
_____________________________________________________________________________________________________
FEBRUARY   |                      |                             |
_____________________________________________________________________________________________________
MARCH      |                      |                             |
_____________________________________________________________________________________________________
APRIL      |                      |                             |
_____________________________________________________________________________________________________
...

And I would like to solve this in the database part, not creating different tables and then working in the PHP area...

Sorry for this way too much generic question, I hope I made it clear enough for you to enlighten me.

Thank you in advance!

www.robotecnik.com[^] - robots, CNC and PLC programming

Mycroft Holmes19-Jun-20 12:25

Mycroft Holmes

19-Jun-20 12:25

Joan M wrote:
And I would like to solve this in the database part, not creating different tables and then working in the PHP area

Wrong tools for the jobs, database for servicing your data query and PHP for the presentation.

You could store your queries in a table, relate them to a query/stored proc and expose a title in a combo box for the user to select. Display would need to be an auto generated table to support different result sets.

The problem with this is that you may need to support different filtering parameters and you end up with an entire framework to support the reporting when it is faster and simpler to build a result page and query for each report request.

Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP

Joan M20-Jun-20 1:11

Joan M

20-Jun-20 1:11

hmmmm...

I meant this: I have all the data in the database, create a query and get the results that I show then in the web page using PHP.

I have all the "framework" done already, I can tune the queries easily filtering data, ordering and grouping at will...

But, maybe it would be much easier to create a "view" in PHPMyAdmin (I still don't know if this can be achieved but I'll look for it) and then the queries would be much easier.

After having the view, I would not have to create endless joins to get the data mixed correctly.

www.robotecnik.com[^] - robots, CNC and PLC programming

David Mujica22-Jun-20 4:27

David Mujica

22-Jun-20 4:27

What database are you using?

If it is MS-SQL, then there is a command call PIVOT which will do exactly what you want to do.
Java | [Coffee]