sql not inserting into database

Question

I know this is a pretty common question, but I am just looking for help in identifying what's wrong with my code. I'm attempting to create my database so that an admin can create new campsites with pictures from a form. My website seems to be working alright and when I test it, it gives me the fail message if values are not filled in and success message if required values are not filled in, but it is not passing the information on to my database. Any help would be very appreciated!

Also, I apologize for being a very bad coder.

Relevant website code:


        
      Add a New Campsite
      
        

            

            Fire Pit: 
            Electricity: 
            Sewage: 

      

  
  
 
 

;

fetch.inc.php / sql code:

Professor Abronsius · Accepted Answer

If you edit the field values for fire,sewer and electric by changing them to integers with a default value of 1 ( one) you will simplify some of the logic in php.

The insert statements ought really to use prepared statements to avoid sql injection.

I'd also strongly suggest that you use css to control the look of the form rather than the inline styles you have here - it makes the code leaner, easier to read and more importantly easier to change!

The code below is all on one page for me to test - you'll need to split the html and php into the two constituent pages as you originally had them ~ sorry for changing it around so much but I think you should be able to get it working now from this.

prepare( $sql );
            if( !$stmt ) throw new Exception('Failed to prepare sql statement');

            $stmt->bind_param('iiiiii', $length, $width, $fire, $sewer, $electric, $price );

            if( isset( $_FILES[ $filefield ] ) ){
                foreach( $_FILES[ $filefield ]['name'] as $i => $name ) {
                    if( !empty( $_FILES[ $filefield ]['tmp_name'][$i] ) ) {

                        $name = $_FILES[ $filefield ]['name'][$i];
                        $size = $_FILES[ $filefield ]['size'][$i];
                        $type = $_FILES[ $filefield ]['type'][$i];
                        $tmp  = $_FILES[ $filefield ]['tmp_name'][$i];
                        $err  = $_FILES[ $filefield ]['error'][$i];

                        $target="images/{$name}";
                        #$target='c:/temp/fileuploads/1/'.$name;

                        if( is_uploaded_file( $tmp ) ){
                            $bytes = move_uploaded_file( $tmp, $target );
                        } else {
                            throw new Exception( sprintf('Error: %s',uploaderror( $err ) ) );
                        }
                    }
                }
            }
            $result = $stmt->execute();
            $stmt->close();
            $conn->close();

            exit( header( "Location: ../admin.php?signup=$result" ) );

        }catch( Exception $e ){
            echo $e->getMessage();
        }
    }

?>



    
        
        
    
    
        
            
                Add a New Campsite

                

                    

                    


                    Fire Pit: 
                    Electricity: 
                    Sewage:

I had a bit of a play about after I made that last comment regarding storing a reference to the images being stored in the database - perhaps it may be of interest.

No doubt the actual database schema is far more complicated but I quickly built 2 tables for the demo.

create table `campsites` (
    `id` int(10) unsigned not null auto_increment,
    `length` smallint(5) unsigned not null default '0',
    `width` smallint(5) unsigned not null default '0',
    `fire` bit(1) not null default b'0',
    `sewer` bit(1) not null default b'0',
    `electric` bit(1) not null default b'0',
    `price` decimal(10,0) unsigned not null default '0',
    primary key (`id`)
)
collate='utf8_general_ci'
engine=innodb;

create table `campsite_images` (
    `id` int(10) unsigned not null auto_increment,
    `cid` int(10) unsigned not null default '0',
    `photo` varchar(128) not null default '0',
    primary key (`id`),
    index `cid` (`cid`),
    constraint `fk_cs_img` foreign key (`cid`) references `campsites` (`id`) on update cascade on delete cascade
)
collate='utf8_general_ci'
engine=innodb;



mysql> describe campsites;
+----------+------------------------+------+-----+---------+----------------+
| Field    | Type                   | Null | Key | Default | Extra          |
+----------+------------------------+------+-----+---------+----------------+
| id       | int(10) unsigned       | NO   | PRI | NULL    | auto_increment |
| length   | smallint(5) unsigned   | NO   |     | 0       |                |
| width    | smallint(5) unsigned   | NO   |     | 0       |                |
| fire     | bit(1)                 | NO   |     | b'0'    |                |
| sewer    | bit(1)                 | NO   |     | b'0'    |                |
| electric | bit(1)                 | NO   |     | b'0'    |                |
| price    | decimal(10,0) unsigned | NO   |     | 0       |                |
+----------+------------------------+------+-----+---------+----------------+


mysql> describe campsite_images;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| cid   | int(10) unsigned | NO   | MUL | 0       |                |
| photo | varchar(128)     | NO   |     | 0       |                |
+-------+------------------+------+-----+---------+----------------+

And modified the above code

prepare( $sql );
            if( !$stmt ) throw new Exception('Failed to prepare sql statement');
            $stmt->bind_param('iiiiii', $length, $width, $fire, $sewer, $electric, $price );


            $stmt_image=$conn->prepare( $sql_image );
            if( !$stmt_image )throw new Exception('Unable to prepare image sql statement');
            $stmt_image->bind_param('is', $id, $target );


            /* insert record for campsite */
            $results[]=$stmt->execute();

            /* Get the ID for the campsite that was just added */
            $id=$stmt->insert_id;

            /* Process any & all images that are uploaded */
            if( isset( $_FILES[ $filefield ] ) ){
                foreach( $_FILES[ $filefield ]['name'] as $i => $name ) {
                    if( !empty( $_FILES[ $filefield ]['tmp_name'][$i] ) ) {

                        $name = $_FILES[ $filefield ]['name'][$i];
                        $size = $_FILES[ $filefield ]['size'][$i];
                        $type = $_FILES[ $filefield ]['type'][$i];
                        $tmp  = $_FILES[ $filefield ]['tmp_name'][$i];
                        $err  = $_FILES[ $filefield ]['error'][$i];

                        $target = $imgdir . $name;

                        if( is_uploaded_file( $tmp ) ){
                            $bytes = move_uploaded_file( $tmp, $target );
                            $results[]=$stmt_image->execute();
                        } else {
                            throw new Exception( sprintf('Error: %s', uploaderror( $err ) ) );
                        }
                    }
                }
                $stmt_image->close();
            }

            $result=new stdClass;
            $result->failed=0;
            $result->success=0;

            array_walk( $results, function($value){
                global $result;
                if( $value==0 )$result->failed++;
                if( $value==1 )$result->success++;
            });

            $message=sprintf( 'Record(s) added - Failed:%d, Success:%d', $result->failed, $result->success );

            $stmt->close();
            $conn->close();



            if( $redirect ) exit( header( "Location: ../admin.php?signup=true" ) );

        }catch( Exception $e ){
            $message=$e->getMessage();
        }
    }

?>



    
        
        Campsite booking form
        
        
    
    
        
            
                Add a New Campsite
                $message";
                    }
                ?>
                

                    
                        
                        
                        
                    

                    
                        Fire Pit: 
                        Electricity: 
                        Sewage: 
                    

                    
                        
                        
                             Choose a file…

sql not inserting into database

Answers (2)

Related Questions