php - sql没有插入数据库

Question

我知道这是一个很常见的问题，但我只是在寻求帮助以确定我的代码有什么问题。我正在尝试创建我的数据库，以便管理员可以使用表单中的图片创建新的露营地。我的网站似乎工作正常，当我测试它时，如果未填写值，它会给我失败消息，如果未填写所需值，它会给我成功消息，但它不会将信息传递到我的数据库。任何帮助将不胜感激!

另外，我为自己是一个非常糟糕的编码员而道歉。

相关网站代码:

<section id="SIGNUP" style="text-align: center;" class="main-container">
        <div class="container" style="width:100%;">
      <h2 style="text-align:center; font-size:50px;">Add a New Campsite</h2>
      <form id="newsletter" <form class="signup-form" action="includes/fetch.inc.php" method="POST" enctype="multipart/form-data" style="padding-top:50px; padding-bottom:50px; border-bottom:#e8491d 3px solid; border-top:#e8491d 3px solid;">
        <input type="integer"  name="length" style="padding:4px; height:5%; width:25%; text-align:center; font-size:30px;" placeholder="Site Length"><br>
            <input type="integer" name="width" style="padding:4px; height:5%; width:25%; text-align:center; font-size:30px;" placeholder="Site Width"><br>
            <label for="fire">Fire Pit: </label><input type="checkbox" name="fire" value="No">
            <label for="electric">Electricity: </label><input type="checkbox" name="electric" value="No">
            <label for="sewer">Sewage: </label><input type="checkbox" name="sewer" value="No"></br>
      <input type="decimal" name="price" style="padding:4px; height:5%; width:25%; text-align:center; font-size:30px;" placeholder="Price"><br>
  <input type="file" id="upload_file" name="upload_file[]" onchange="preview_image();" multiple/>
  <input type="submit" name="submit_image" value="Upload Image"/>
 </form>
 <div id="image_preview"></div>
</div>
</section>;

fetch.inc.php/sql代码:

<?php
if(isset($_POST['submit_image'])) {

  require_once("dbh.inc.php");

    $length = mysqli_real_escape_string($conn, $_POST['length']);
    $width = mysqli_real_escape_string($conn, $_POST['width']);
  $price = mysqli_real_escape_string($conn, $_POST['price']);
    //Error Handlers
    //Check for empty fields

    if (empty($length) || empty($width) || empty($price)) {
        header("Location: ../admin.php?null-value");
        exit();
  } else {

//insert image address into images folder and add the address to reservations table
    for($i=0;$i<count($_FILES["upload_file"]["name"]);$i++)
    {
     $uploadfile=$_FILES["upload_file"]["tmp_name"][$i];
     $folder="images/";
     move_uploaded_file($_FILES["upload_file"]["tmp_name"][$i], "$folder".$_FILES["upload_file"]["name"][$i]);
    $image = "$folder".$_FILES["upload_file"]["name"];


    $sql = "INSERT INTO campsites (length, width, fire, sewer, electric, price)
    VALUES ('$length', '$width', '$fire', '$sewer', '$electric', '$price');";
    mysqli_query($conn, $sql);
    header("Location: ../admin.php?signup=Success");
    exit();

// Transform checkbox values into binary sql values

        if (isset($_POST['fire']) && ($_POST['fire'] == "value")) {
            $fire .= "1";
        } else {
            $fire .= "0";
        }

    if (isset($_POST['sewer']) && ($_POST['sewer'] == "value")) {
      $sewer .= "1";
    } else {
      $sewer .= "0";
    }

    if (isset($_POST['electric']) && ($_POST['electric'] == "value")) {
      $electric .= "1";
    } else {
      $electric .= "0";
    }
  }
}

}
exit();
?>

Answer 1

如果您编辑 fire、sewer 和 electric 的字段值，将它们更改为默认值为 1 的整数(一)你将简化php中的一些逻辑。

insert 语句应该真正使用prepared statements 来避免 sql 注入(inject)。

我还强烈建议您使用 css 来控制表单的外观，而不是这里的内联样式 - 它使代码更精简、更易于阅读，更重要的是更易于编写改变!

下面的代码都在一个页面上供我测试 - 你需要将 html 和 php 分成两个组成页面，因为你原来有它们 ~ 抱歉改变了这么多，但我认为你应该现在可以让它工作了。

<?php

    if( $_SERVER['REQUEST_METHOD']=='POST' && isset( $_POST['length'], $_POST['width'], $_POST['price'] ) ){
        try{

            require_once("dbh.inc.php");

            function uploaderror( $code ){ 
                switch( $code ) { 
                    case UPLOAD_ERR_INI_SIZE: return "The uploaded file exceeds the upload_max_filesize directive in php.ini"; 
                    case UPLOAD_ERR_FORM_SIZE: return "The uploaded file exceeds the MAX_FILE_SIZE directive that was specified in the HTML form"; 
                    case UPLOAD_ERR_PARTIAL: return "The uploaded file was only partially uploaded"; 
                    case UPLOAD_ERR_NO_FILE: return "No file was uploaded"; 
                    case UPLOAD_ERR_NO_TMP_DIR: return "Missing a temporary folder"; 
                    case UPLOAD_ERR_CANT_WRITE: return "Failed to write file to disk"; 
                    case UPLOAD_ERR_EXTENSION: return "File upload stopped by extension"; 
                    default: return "Unknown upload error";
                }
            }

            $filefield='upload_file';
            $status=false;
            $sql = "insert into `campsites` ( `length`, `width`, `fire`, `sewer`, `electric`, `price`) values (?,?,?,?,?,?);";

            $length=filter_input( INPUT_POST,'length',FILTER_SANITIZE_NUMBER_INT );
            $width=filter_input( INPUT_POST,'width',FILTER_SANITIZE_NUMBER_INT );
            $price=filter_input( INPUT_POST,'price',FILTER_SANITIZE_NUMBER_INT );

            $fire=isset( $_POST['fire'] ) ? filter_input( INPUT_POST,'fire',FILTER_SANITIZE_NUMBER_INT ) : 0;
            $sewer=isset( $_POST['sewer'] ) ? filter_input( INPUT_POST,'sewer',FILTER_SANITIZE_NUMBER_INT ) : 0;
            $electric=isset( $_POST['electric'] ) ? filter_input( INPUT_POST,'electric',FILTER_SANITIZE_NUMBER_INT ) : 0;





            if( !$length or !$width or !$price ){
                exit( header("Location: ../admin.php?null-value") );
            }

            $stmt=$conn->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();
        }
    }

?>

<!doctype html>
<html>
    <head>
        <meta charset='utf-8' />
        <title></title>
    </head>
    <body>
        <section id="SIGNUP" class="main-container">
            <div class="container">
                <h2>Add a New Campsite</h2>

                <form class="signup-form" method="POST" enctype="multipart/form-data">

                    <input type="number"  name="length" placeholder="Site Length"><br>
                    <input type="number" name="width" placeholder="Site Width"><br>

                    <label for="fire">Fire Pit: </label><input type="checkbox" name="fire" value=1>
                    <label for="electric">Electricity: </label><input type="checkbox" name="electric" value=1>
                    <label for="sewer">Sewage: </label><input type="checkbox" name="sewer" value=1></br>

                    <input type="number" name="price" placeholder="Price"><br>
                    <input type="file" id="upload_file" name="upload_file[]" onchange="preview_image();" multiple/>

                    <input type="submit" name="submit_image" value="Upload Images & Save"/>
                </form>
                <div id="image_preview"></div>
            </div>
        </section>
    </body>
</html>

---

在我发表关于存储对存储在数据库中的图像的引用的最后评论之后，我玩了点小游戏 - 也许它可能很有趣。

毫无疑问，实际的数据库模式要复杂得多，但我很快为演示构建了 2 个表。

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       |                |
+-------+------------------+------+-----+---------+----------------+

并修改了上面的代码

<?php

    if( $_SERVER['REQUEST_METHOD']=='POST' && isset( $_POST['length'], $_POST['width'], $_POST['price'] ) ){
        try{
            $redirect=false;
            $message=false;
            $filefield='upload_file';
            $status=false;
            $imgdir='c:/temp/fileuploads/1/';
            $results=array();

            #require_once("dbh.inc.php");

            $dbhost =   'localhost';
            $dbuser =   'root'; 
            $dbpwd  =   'xxx'; 
            $dbname =   'xxx';
            $conn   =   new mysqli( $dbhost, $dbuser, $dbpwd, $dbname );




            function uploaderror( $code ){ 
                switch( $code ) { 
                    case UPLOAD_ERR_INI_SIZE: return "The uploaded file exceeds the upload_max_filesize directive in php.ini"; 
                    case UPLOAD_ERR_FORM_SIZE: return "The uploaded file exceeds the MAX_FILE_SIZE directive that was specified in the HTML form"; 
                    case UPLOAD_ERR_PARTIAL: return "The uploaded file was only partially uploaded"; 
                    case UPLOAD_ERR_NO_FILE: return "No file was uploaded"; 
                    case UPLOAD_ERR_NO_TMP_DIR: return "Missing a temporary folder"; 
                    case UPLOAD_ERR_CANT_WRITE: return "Failed to write file to disk"; 
                    case UPLOAD_ERR_EXTENSION: return "File upload stopped by extension"; 
                    default: return "Unknown upload error";
                }
            }



            $sql = "insert into `campsites` ( `length`, `width`, `fire`, `sewer`, `electric`, `price`) values (?,?,?,?,?,?);";
            $sql_image = 'insert into `campsite_images` (`cid`,`photo`) values (?,?)';

            $length=filter_var( filter_input( INPUT_POST,'length',FILTER_SANITIZE_NUMBER_INT ), FILTER_VALIDATE_FLOAT );
            $width=filter_var( filter_input( INPUT_POST,'width',FILTER_SANITIZE_NUMBER_INT ), FILTER_VALIDATE_FLOAT );
            $price=filter_var( filter_input( INPUT_POST,'price',FILTER_SANITIZE_NUMBER_INT ), FILTER_VALIDATE_FLOAT );

            $fire=isset( $_POST['fire'] ) ? filter_input( INPUT_POST,'fire',FILTER_SANITIZE_NUMBER_INT ) : 0;
            $sewer=isset( $_POST['sewer'] ) ? filter_input( INPUT_POST,'sewer',FILTER_SANITIZE_NUMBER_INT ) : 0;
            $electric=isset( $_POST['electric'] ) ? filter_input( INPUT_POST,'electric',FILTER_SANITIZE_NUMBER_INT ) : 0;





            if( !$length or !$width or !$price ){
                if( $redirect ) exit( header( "Location: ../admin.php?null-value" ) );
            }
            if( !is_numeric( $length ) or !is_numeric( $width ) or !is_numeric( $price ) ){
                throw new Exception('Non-Float values for length, width and price are not allowed.');
            }

            $stmt=$conn->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();
        }
    }

?>

<!doctype html>
<html>
    <head>
        <meta charset='utf-8' />
        <title>Campsite booking form</title>
        <style>
            html,html *{
                font-family:calibri,verdana,arial;
                box-sizing:border-box;
            }
            #signup{
                text-align: center;
                width:50%;
                float:none;
                margin:0 auto;
            }
            h2{text-align:center; font-size:4rem;}
            h3{font-size:0.95rem;color:green;}
            input{padding:1rem;}

            input[type='text'],
            input[type='number']{
                float:none;
                width:calc(33% - 1rem );
                margin:0.5rem auto;
            }
            fieldset{
                width:90%;
                border:none;
                margin:1rem auto;
                float:none;
            }
            input[type='file']{
                width: 0.1px;
                height: 0.1px;
                opacity: 0;
                overflow: hidden;
                position: absolute;
                z-index: -1;
            }
            input[type='file'] + label{
                font-weight: 700;
                color: black;
                background-color: #E5E4E2;
                display: inline-block;
                border:1px solid black;
                padding:0.25rem;
                width:90%;
                cursor:pointer;
                float:none;
                margin:0 auto;
            }


            label[for='upload_file']:hover {
                background: rgba(240,182,198,0.75)!important;
            }

            input[type='submit']{
                width:90%;
                float:none;
                margin:1rem auto;
            }
            #services label{
                padding:1rem;
                display:inline-block;
                clear:none;
                float:none;
                margin:0 auto;
                width:calc(33% - 5px);!important;
            }
            ul#list{font-size:0.7rem;}
            #image_preview img{margin:0.25rem;padding:0.25rem;outline:1px dotted gray;}
        </style>
        <script>
            /* self-executing anonymous function */
            (function(){

                var getaspect=function(w,h){
                    w=parseFloat(w);
                    h=parseFloat(h);
                    if( w==h )return 1;
                    else if( w > h ) return 2;
                    else if( h > w ) return 3;
                    else return 4;
                }
                var getratio=function(w,h){
                    return parseFloat(w) / parseFloat(h);
                }
                var roundNumber=function(i,p){
                    return Math.floor( i * Math.pow( 10, p ) ) / Math.pow( 10, p );
                };
                var getfilesize=function(i){
                    var kb=1024;
                    var mb=Math.pow(kb,2);
                    var gb=Math.pow(kb,3);
                    var tb=Math.pow(kb,4);

                    if( i > 0 && i < kb ) return i+'bytes';
                    else if( i >= kb && i < mb ) return roundNumber( Math.abs( i / kb ),2 ) + 'Kb';
                    else if( i >= mb && i < gb ) return roundNumber( Math.abs( i / mb ),2 ) + 'Mb';
                    else if( i >= gb && i < tb ) return roundNumber( Math.abs( i / gb ),2 ) + 'Gb';
                    else if( i >= tb ) return roundNumber( Math.abs( i / tb ),2 ) + 'Tb';
                };

                var size=150;
                var options={
                    capture:false,
                    once:false,
                    passive:true
                };
                document.addEventListener('DOMContentLoaded',function(e){

                    var oImages=[];
                    var oInput=document.getElementById('upload_file');
                    var oPreview=document.getElementById('image_preview');
                    var oList=document.getElementById('list');

                    oInput.addEventListener('change',function(event){
                        var files=this.files;
                        for( var i=0; i < files.length; i++ ){
                            var file=files.item( i );
                            var obj={
                                'file':file,
                                'name':file.name,
                                'size':file.size,
                                'lastModified':file.lastModified,
                                'lastModifiedDate':file.lastModifiedDate,
                                'type':file.type
                            };
                            oImages.push( obj );

                            var li=document.createElement('li');
                                li.dataset.name=obj.name;
                                li.dataset.lastmod=obj.lastModifiedDate;
                                li.dataset.type=obj.type;
                                li.innerHTML=obj.name + ' [ '+getfilesize( obj.size )+' ]';

                            oList.appendChild( li );

                            /********************/
                            /* Show a preview */
                            var img = document.createElement('img');
                                img.file=obj.file;
                                img.dataset.name=obj.name;
                                img.title=obj.name;
                                img.onload=function(event){

                                    var ratio=getratio( this.width,this.height );
                                    switch( getaspect( this.width, this.height ) ){
                                        case 1:
                                            this.width=size;
                                            this.height=size;
                                        break;
                                        case 2:
                                            this.width=size;
                                            this.height=size / ratio;
                                        break;
                                        case 3:
                                            this.height=size;
                                            this.width=size * ratio;
                                        break;
                                        case 4:
                                            alert('error')
                                        break;
                                    }
                                    window.URL.revokeObjectURL( this.src );
                                };

                            /* add new thumbnail to the DOM */
                            oPreview.appendChild( img );

                            /* read the file and set the image source */
                            var reader = new FileReader();
                                reader.onload = (function(a) { return function(e) { a.src = e.target.result; }; })( img );
                                reader.readAsDataURL( obj.file );

                        }
                    }.bind( oInput ),options );
                },options );
            })();
        </script>
    </head>
    <body>
        <section id='signup' class='main-container'>
            <div class='container'>
                <h2>Add a New Campsite</h2>
                <?php
                    if( $message ){
                        echo "<h3>$message</h3>";
                    }
                ?>
                <form class='signup-form' method='POST' enctype='multipart/form-data'>

                    <fieldset id='dimensions'>
                        <input type='number'  name='length' placeholder='Site Length' step=1 min=1 max=1000 />
                        <input type='number' name='width' placeholder='Site Width' step=1 min=1 max=1000 />
                        <input type='number' name='price' placeholder='Price' step='0.5' min=0 max=1000 />
                    </fieldset>

                    <fieldset id='services'>
                        <label for='fire'>Fire Pit: <input type='checkbox' name='fire' value=1></label>
                        <label for='electric'>Electricity: <input type='checkbox' name='electric' value=1></label>
                        <label for='sewer'>Sewage: <input type='checkbox' name='sewer' value=1></label>
                    </fieldset>

                    <fieldset id='files'>
                        <input type='file' id='upload_file' name='upload_file[]' multiple/>
                        <label for='upload_file' title='Optional: Upload photos'>
                            <svg xmlns='http://www.w3.org/2000/svg' width='20' height='17' viewBox='0 0 20 17'>
                                <path d='M10 0l-5.2 4.9h3.3v5.1h3.8v-5.1h3.3l-5.2-4.9zm9.3 11.5l-3.2-2.1h-2l3.4 2.6h-3.5c-.1 0-.2.1-.2.1l-.8 2.3h-6l-.8-2.2c-.1-.1-.1-.2-.2-.2h-3.6l3.4-2.6h-2l-3.2 2.1c-.4.3-.7 1-.6 1.5l.6 3.1c.1.5.7.9 1.2.9h16.3c.6 0 1.1-.4 1.3-.9l.6-3.1c.1-.5-.2-1.2-.7-1.5z'></path>
                            </svg> <span>Choose a file…</span>
                        </label>
                    </fieldset>

                    <fieldset id='bttns'>
                        <input type='submit' name='submit_image' value='Upload Images & Save'/>
                    </fieldset>

                </form>
                <div id='image_preview'></div>
                <ul id='list'></ul>
            </div>
        </section>
    </body>
</html>