MySQL works, but can it be optimized?

[BuildMyWeb]

im trying to improve my novice MySQL skills.  the following works but it makes 4 calls to the db.  im wondering if there is a more efficient way to accomplish the below, ie. less database queries.  i will comment below what is happening to make this easier to understand what im trying to accomplish.

 

basically, a user is trying to create a new project that has a UNIQUE name as well as invite other users to access it.  members accounts are identified by their unique email address.  we have two tables.  $table_projects stores the project NAME and a unique ID.  $table_project_members stores EMAIL of users invited matched with the ID from $table_projects.

        // check to see if the submitted PROJECT NAME was used before.  it must be UNIQUE
        $query_sel         = "SELECT project_name FROM " . $table_projects . " WHERE project_name = '" . $project_name . "'";
        $result_sel     = $db_connect->query( $query_sel ); // execute the query
        $num_rows_sel    = $result_sel->num_rows;
        
        // if project name was already used
        if( $num_rows_sel > 0 )
        {
            header( 'Location: ../project_new.php?msg_raw=pna' );
        }
        
        else
        {
            // enter our newly submitted project info into the db 
            // post project to db
            $query_np     = "INSERT INTO $table_projects
                        ( id, project_name, author, status, timestamp )
                        VALUES( 0, '" . $project_name . "', '" . USER_EMAIL . "', 'active', '" . $ts . "')";
                        
            $result_np = $db_connect->query( $query_np ); // execute the query
    
            // if project posted to db successfully
            if( $result_np )
            {
                // get the id of our newly created project to place in project_members entry below
                $query_id         = "SELECT id FROM " . $table_projects . " WHERE project_name = '" . $project_name . "'";
                $result_id         = $db_connect->query( $query_id ); // execute the query
                $row_id         = $result_id->fetch_assoc(); // put field values for the row in an array
                
                // unique ID of our project
                $project_id     = $row_id['id'];
        
                // if users were added
                if( !empty($u_emails) )
                {
                    // split textarea into separate email addresses
                    $array_u_emails = explode("\n", $u_emails);
            
                    foreach( $array_u_emails as $address )
                    {
                        $address = trim($address);
                        
                        // add the email(unique) of each user that was invited to the project 
                        // along with the project id we queried above
                        // post users to db
                        $query_members     = "INSERT INTO $table_project_members
                                        ( id, project_id, user_email )
                                        VALUES( 0, '" . $project_id . "', '" . $address . "')";
                                    
                        $result_members = $db_connect->query( $query_members ); // execute the query
                    }
                } // close if( !empty($u_emails) )
                
                // unset NEW PROJECT SESSION vars
                unset( $_SESSION['np_project_name'] );
                unset( $_SESSION['np_u_emails'] );
                
                header( 'Location: ../thankyou.php?status=np&&arg=' . $project_name );
            }
            
            else
            {
                error( 30001 );
            }
        } // close else

[mac_gyver]

provided the project_name column is a unique key (it should be anyway), the first SELECT query is not needed. you can just attempt to run the INSERT query and it won't insert if the project_name is already in use. depending on the mode your database server is set to, you will either get a duplicate key error (you can test for the specific error number that would be returned for a duplicate key) or you will get a warning (some of the database libraries have a function/method to get the last warning that was returned.) you can also use the IGNORE keyword in the INSERT query, then after you test if the query ran without any errors, you can test if the affected rows value is a one (inserted the row) or zero (did not insert the row.)

 

after the INSERT query, you don't need a separate SELECT query to get the last insert id. just about all database libraries have a function/method to get the last inserted id as this information is automatically returned to the client and simply must be accessed.

 

your final insert query in the loop for the email records can and should be replaced by a single multi-value insert query.

Edited August 25, 2013 by mac_gyver

[mac_gyver]

assuming you are using the mysqli database library, the following should (untested) be equivalent to what you posted above -

$query = "INSERT IGNORE INTO $table_projects (project_name,author,status,timestamp)
                    VALUES('$project_name','".USER_EMAIL."','active','$ts')";

if($db_connect->query($query)){
    // INSERT IGNORE ... query ran without any errors, check number of affected rows
    if($db_connect->affected_rows == 0){
        // not inserted
        header('Location: ../project_new.php?msg_raw=pna');
    } else {
        // row was inserted
        $project_id = $db_connect->insert_id;
        
        // if users were added
        if(!empty($u_emails)){
            // split textarea into separate email addresses
            $array_u_emails = explode("\n", $u_emails);

            $values = array();
            foreach( $array_u_emails as $address ){
                $address = trim($address);
                $values[] = "($project_id,'$address')";
            }
            $query = "INSERT INTO $table_project_members (project_id,user_email)
                                                        VALUES " . implode(',',$values);
            $db_connect->query($query); // execute the query
        }

        // unset NEW PROJECT SESSION vars
        unset( $_SESSION['np_project_name'] );
        unset( $_SESSION['np_u_emails'] );

        header( 'Location: ../thankyou.php?status=np&&arg=' . $project_name );
    }
} else {
    error( 30001 ); // an actual query error
}

[BuildMyWeb]

thanks mac.  INSERT IGNORE... ill have to read up on that.  appreciate the help.

[vinny42]

Don't use INSERT IGNORE because you neverwant to ignore errors or warnings. There are a handfull of applications that do benefit from the IGNORE, but basicallt IGNORE is one of the many ways that MySQL cheats on proper database use by making it possible to say "look database, I don't care that my data doesn't match my schema, just work with it and don't complain".

 

Like mac_gyver says; this is the correct procedure:

 

1. create a unique constraint (unique index) on whatever it is that makes a project unique.

2. When creating a new project, just run a regular INSERT query. Don't use IGNORE because you don't want corrupted data.

2a. If the INSERT would violate the UNIQUE then MySQL will give an error and PHP will catch that.

2b. If the INSERT worked then last_insert_id() will return the id of the new record.

 

Thus, you only need one query to create a new project.