douxiong5438
douxiong5438
2014-04-13 23:32

将所有INSERT语句合并为一个

已采纳

I'm creating a page for a class and I have lots of different while loops with lots of different INSERT statements. I didn't think there would be a problem with that until I discovered that they weren't being inputed right. They were all inputed but what happened was that they were all inputed in separate rows as opposed to being one entry across their individual columns. I need it so that all of the INSERT statements are combined into one while still checking to make sure that the full name isn't yet in the database. Please help me rewrite my code so that it can do just that.

Here is the code that needs to be rewritten so that it just has one INSERT statement:

<?php 
$con = mysql_connect("localhost","a7068104_user2","wiseguy1345");
if(!$con) {
    die("could not connect to localhost:" .mysql_error());
}

header("refresh:1.5; url=NamesAction.php");

mysql_select_db("a7068104_world") or die("Cannot connect to database");

$name = mysql_real_escape_string($_POST['firstname']);
$query = "SELECT * FROM names_1 WHERE firstname='$name'";
$result = mysql_query($query);
if(mysql_num_rows($result) > 0 ){
        echo "Your first name is already in the database and will not be added again!";
}
else {
        $query = "INSERT INTO names_1 (firstname) VALUES('$name')";
        $result = mysql_query($query);
        if($result) {
            echo "Your first name was successfully added to the database!";
        }
        else{
            echo "Your first name couldn't be added to the database!";
        }
}

$name = mysql_real_escape_string($_POST['lastname']);
$query = "SELECT * FROM names_1 WHERE lastname='$name'";
$result = mysql_query($query);
if(mysql_num_rows($result) > 0 ){
        echo "Your last name is already in the database and will not be added again!";
}
else {
        $query = "INSERT INTO names_1 (lastname) VALUES('$name')";
        $result = mysql_query($query);
        if($result) {
            echo "Your first name was successfully added to the database!";
        }
        else{
            echo "Your first name couldn't be added to the database!";
        }
}

$name = mysql_real_escape_string($_POST['firstname'] . " " . $_POST['lastname']);
$query = "SELECT * FROM names_1 WHERE fullname='$name'";
$result = mysql_query($query);
if(mysql_num_rows($result) > 0 ){
        echo "Your full name is already in the database and will not be added again!";
}
else {
        $query = "INSERT INTO names_1 (fullname) VALUES('$name')";
        $result = mysql_query($query);
}

$age = mysql_real_escape_string($_POST['age']);
    $query = "INSERT INTO names_1 (age) VALUES('$age')";
    $result = mysql_query($query);
    if($result) {
        echo "Your name was successfully added to the database!";
    }
    else {
        echo "Your name couldn't be added to the database!";
    }

mysql_close($con);
?>

<html>
<head>
    <link rel="stylesheet" href="Site.css">
    <?php include("Header.php"); ?>
    </div>
</head>

<body>
    <div id="main">
        <h1>Names</h1>
        <p>You will be redirected back to the <b>Names</b> page in a moment.</p>
        <?php include("Footer.php");?>
    </div>
</body>
</html>
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

2条回答

  • dst8922 dst8922 7年前

    When inserting a row with multiple columns you can just combine the two groups of keys and values like so:

    INSERT INTO names_1 (firstname, lastname) VALUES ('John', 'Smith')

    The other alternative is in your database mark both the Firstname and Lastname as Unique columns. Then you can do the following.

    INSERT INTO names_1 (firstname, lastname) VALUES ('John', 'Smith') ON DUPLICATE KEY UPDATE names_1 SET x="blah" WHERE blah

    http://dev.mysql.com/doc/refman/5.6/en/insert-on-duplicate.html

    点赞 评论 复制链接分享
  • doufa5001 doufa5001 7年前

    This should accomplish what you are setting out to do.

    <?php 
    $con = mysql_connect("localhost","a7068104_user2","wiseguy1345");
    if(!$con) {
        die("could not connect to localhost:" .mysql_error());
    }
    
    header("refresh:1.5; url=NamesAction.php");
    
    mysql_select_db("a7068104_world") or die("Cannot connect to database");
    
    $fullname = mysql_real_escape_string($_POST['firstname'] . " " . $_POST['lastname']);
    $query = "SELECT * FROM names_1 WHERE fullname='" . $fullname . "'";
    $result = mysql_query($query);
    if(mysql_num_rows($result) > 0 ){
            echo "Your full name is already in the database and will not be added again!";
    }
    else {
        $query = "INSERT INTO names_1 (firstname, lastname, fullname, age) VALUES('" .     $_POST['firstname'] . "','" . $_POST['lastname'] . "','" . $fullname . "'," . $_POST['age'] . ")";
        $result = mysql_query($query);
    }
    
    
    mysql_close($con);
    ?>
    
    <html>
        <head>
            <link rel="stylesheet" href="Site.css"/>
            <?php include("Header.php"); ?>
    
        </head>
    
        <body>
            <div id="main">
                <h1>Names</h1>
                <p>You will be redirected back to the <b>Names</b> page in a moment.</p>
                <?php include("Footer.php");?>
            </div>
        </body>
    </html>
    
    点赞 评论 复制链接分享