duanquanzhi5560
duanquanzhi5560
2014-12-19 13:59

左连接和最后插入ID困境

已采纳

hi in want to build a registration and lottery app with php i want to make a users id in my prize table upon registration of users and i want to add to a number of my buy_count when users buy a ticket...

so i got 2 tables :

prize , users

prize : users_id , buy_count
users : fn , ln , pass...usual staff

this is the sctipt for users reg form :

$sql = 'INSERT INTO users (id,fn,ls,psw) VALUES (:id,:fn,:ln,:psw);
INSERT INTO prize (user_id, buy_count) VALUES (LAST_INSERT_ID(), 4)';

script gives me an error , whats wrong with it ? i want to add to users id in prize table upon user registration , whats the problem with my query ? whats the right way to do this ?


UPDATED :

so now this is my code :

<?php



try {
    require_once 'inc/connect.php';

    $sql =("
INSERT INTO users (fn,ls,psw) VALUES (:fn,:ln,:psw);
INSERT INTO prize (user_id) VALUES (LAST_INSERT_ID());
    ");



$form = $_POST;
$fn = $form['fn'];
$ln = $form['ln'];
$psw = $form['psw'];

    $values = array(
':fn' => $fn ,
':ln' => $ln ,
':psw' => $psw

    );

    $stmt = $db->prepare($sql);
    $stmt->execute($values);



    if ( $stmt ){
        echo "<p>Thank you. You have been registered</p>";
    } else {
        echo "<p>Sorry, there has been a problem inserting your details. Please contact admin.</p>";
    }

    $errorInfo = $db->errorInfo();
    if (isset($errorInfo[2])){
        $error = $errorInfo[2];
    }



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




<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <title>Database Connection with PDO</title>
    <link href="../../styles/styles.css" rel="stylesheet" type="text/css">
</head>
<body>
<h1>Connecting with PDO</h1>
<?php if(isset($error)) {
    echo "<p>$error</p>";
}else {

    ?>
<?php
if ( empty( $_POST ) ) {
    ?>



    <h1>Registration</h1>
    <form name="" action="" method="POST">
        <label for 'username'>Username: </label>
        <input type="text" name="fn"/>
        <label for 'password'>Password: </label>
        <input type="password" name="psw"/>
        <label for 'first_name'>First name: </label>
        <input type="text" name="fn"/>

        <br/>
        <button type="submit">Submit</button>
    </form>


<?php
} else {
    print_r( $_POST );
}
    ?>



<?php } //error  ?>

</body>
</html>

still doesnt do what i want ... doesnt add any data to the tables and AI users_id with last I I anyone can help with this ?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

1条回答

  • dsj0312 dsj0312 7年前

    Short answer: RTM

    Long answer (from the mysql documentation) When a new AUTO_INCREMENT value has been generated, you can also obtain it by executing a SELECT LAST_INSERT_ID() statement

    You are not generating a new ID automatically, you are specifying the value directly. So you can do one of two things, remove the id column and value :id placeholder from your first query OR use the same :id placeholder in your second query.

    点赞 评论 复制链接分享

相关推荐