douchongzhang9267
2013-08-12 17:58
浏览 50
已采纳

当MySQL数据库表为空时如何插入行?

I hope this isn't a repeat question. I have searched all over to find an answer to no avail.

I want to insert into a row of a table. It sounds simple enough, but if the table is empty, it will not work. I can't figure out why. As long as there is one row in the table, it works fine. Any help is appreciated. Thanks.

My code:

<?php

$fname = $_POST['fname'];
$mi = $_POST['mi'];
$lname = $_POST['lname'];
$phone = $_POST['phone'];
$email = $_POST['email'];
$add1 = $_POST['add1'];
$add2 = $_POST['add2'];
$city = $_POST['city'];
$state = $_POST['state'];
$zip = $_POST['zip'];

$con = mysqli_connect("localhost","database_username","database_password","database"); 

$sql1 = "INSERT INTO employee (fname, mi, lname, phone, email, add1, add2, city, state, zip) VALUES ('$fname', '$mi', '$lname', '$phone', '$email', '$add1', '$add2', '$city', '$state', '$zip')";

mysqli_query($con,$sql1);

mysqli_close($con);

?>
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

5条回答 默认 最新

  • dpv46227 2013-08-13 04:10
    已采纳

    I made it work after many hours of trying everything I could think of to do... Ultimately, I ended up checking for an empty table first, and then re-creating the table if it was empty.

    I'm not sure why this works, but this is my code... it's a bit inelegant, but I could find no better solution. I know that I have not escaped my values. I will later. For now, I simply wanted to solve this problem that I have been wracking my brain about for days.

    <?php
    
    $fname = $_POST['fname'];
    $mi = $_POST['mi'];
    $lname = $_POST['lname'];
    $phone = $_POST['phone'];
    $email = $_POST['email'];
    $add1 = $_POST['add1'];
    $add2 = $_POST['add2'];
    $city = $_POST['city'];
    $state = $_POST['state'];
    $zip = $_POST['zip'];
    
    $con = mysqli_connect("localhost","username","password","database"); 
    if (!$con) {
            die('Could not connect: ' . mysql_error());
               }
    
    $sql = 'CREATE TABLE employee'
         . ' ('
         . ' fname text,'
         . ' mi varchar(1),'
         . ' lname text,'
         . ' email varchar(100),'
         . ' phone varchar(15)'
         . ' add1 varchar(100),'
         . ' add2 varchar(100),'
         . ' city varchar(100),'
         . ' state varchar(25)'
         . ' zip varchar(10),'
         . ' );';
    
    $sql1 = "INSERT INTO employee (fname, mi, lname, phone, email, add1, add2, city, state, zip,) VALUES ('$fname', '$mi', '$lname',  '$phone',  '$email','$add1', '$add2', '$city', '$state', '$zip')";
    
    $sql2 = "SELECT * FROM employee";
    
    $sql3 = mysqli_query($con,"SELECT count(*) FROM employee");
    
    if ($sql3 == FALSE) {
        trigger_error(mysql_error()." in ".$sql3);
        exit();
    }
    else 
    {
        $result = mysqli_fetch_array($sql3);
    }
    
    if($result[0] != 0)
    {
        mysqli_query($con,$sql1);
    }
    else
    {
        mysqli_query($con,$sql);
        mysqli_query($con,$sql1);
    }
    
    mysqli_close($con);
    
    ?>
    
    点赞 打赏 评论
  • ds0802 2013-08-12 18:08

    Your code is correct but it may some times data have some special characters such as \ / ? etc. thus suggest you to change all variable of the code from

    $fname = $_POST['fname'];
    

    to

    $fname = addslashes($_POST['fname']);
    

    then try it it will be done

    点赞 打赏 评论
  • dongmi5607 2013-08-12 18:09

    You are not escaping the values. If any value contains an apostrophe, your query will faile. Since they come from post you must use mysqli_real_escape_string.

    点赞 打赏 评论
  • duanshanduo3363 2013-08-12 18:14

    use following execute to insert a row

           mysqli_query($con,$sql1);
    
        musqli_execute($sql1);
    
    点赞 打赏 评论
  • duanfen9983 2013-08-13 04:16

    if variable is a string use '".$fname."' foreach variable in query

    点赞 打赏 评论

相关推荐 更多相似问题