replace not updating in database

Question

I have a table with id which is the primary key and user_id which is a foreign key but the session is based on this in my code. I have tried EVERYTHING, so I will post my full code. The form should insert if there is not a user_id with the same session_id in the table. If there is, it should update. At the moment, when the user has not visited the form before (no user_id in the table) and data is inserted in, the page returns to the location page: but the data is not inserted in the table. if the user changes the data once it is updated it doesn't change either. This is the table structure:

`thesis` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `thesis_Name` varchar(200) NOT NULL,
  `abstract` varchar(200) NOT NULL,
  `complete` int(2) NOT NULL DEFAULT '1',
   PRIMARY KEY (`id`),
    KEY `user_id` (`user_id`)
   )

The code I have been using (and failing):

$err = array();


$user_id = intval($_SESSION['user_id']);
// otherwise
if (isset($_POST['doThesis'])) {
$link = mysql_connect(DB_HOST, DB_USER, DB_PASS) or die("Couldn't make connection."); 
// check if current user is banned
$the_query = sprintf("SELECT COUNT(*) FROM users WHERE `banned` = '0' AND `id` = '%d'",
$user_id);

$result = mysql_query($the_query, $link);
$user_check = mysql_num_rows($result);
// user is ok
if ($user_check > 0) {

    // required field name goes here...
    $required_fields = array('thesis_Name','abstract');
    // check for empty fields
    foreach ($required_fields as $field_name) {
        $value = trim($_POST[$field_name]);
        if (empty($value)) {
            $err[] = "ERROR - The $field_name is a required field" ;
        }
    }   // no errors
    if (empty($err)) {
        $id = mysql_real_escape_string($_POST['id']);
        $thesis_Name = mysql_real_escape_string($_POST['thesis_Name']);
        $abstract = mysql_real_escape_string($_POST['abstract']);
  //replace query
        $query = "REPLACE INTO thesis ( thesis_Name, abstract) VALUES ('$thesis_Name',
       '$abstract') where id='$_SESSION[user_id]'";
        if (!mysql_query($the_query))
            echo "the query failed";
        else header ("location:myaccount.php?id=' . $user_id");
   }}}

        $rs_settings = mysql_query("SELECT * from thesis WHERE user_id = $user_id;");


        ?>


        <br>
        <form action="thesis.php" method="post" name="regForm" id="regForm" >
            class="forms">
<?php
$num_rows = mysql_num_rows($rs_settings);
    if($num_rows > 0) { ?>

<?php while ($row_settings = mysql_fetch_array($rs_settings)) {?>
 Title of Proposed Thesis<span class="required">*</span>
 <textarea name="thesis_Name" type="text" style="width:500px; height:150px" 
id="thesis_Name" size="600"><?php echo $row_settings['thesis_Name']; ?> </textarea>
                </tr>

                <tr>
                    <td>Abstract<span class="required">*</span>
                    </td>
                    <td><textarea name="abstract" style="width:500px; height:150px"  
 type="text" id="abstract"  size="600"><?php echo $row_settings['abstract']; ?>  
  </textarea></td>
                </tr>


                <?php }
                } else { ?>

//shows fields again without echo

I've tried var_dum($query) but nothing appears

PS I know the code isn't perfect but I'm not asking about this right now

You are executing mysql_query($the_query), not mysql_query($query). — cmbuckley
– cmbuckley, Commented Apr 28, 2012 at 11:13
its right in showing this the query failedREPLACE INTO thesis ( thesis_Name, abstract) VALUES (' test', 'test ') where user_id='55' — user1296762
– user1296762, Commented Apr 28, 2012 at 11:46

Martin Wilson · Accepted Answer · 2012-04-28 18:36:05Z

1

I can't see how your replace statement will ever insert the initial row, as the where clause is always going to be false (there won't be a row with that user Id). I think of you want to use replace you need to replace into thesis (id, userid, etc) without a where clause. If id and userid have a unique constraint and a row for userid exists then it will be updated; if it doesn't exist it will be inserted. However- if you don't know id- which you won't if you are using auto increment, then I'm not sure you can do this with replace. See http://dev.mysql.com/doc/refman/5.0/en/replace.html

Why don't you check for the existence of a row an then use update or insert?

BTW, is the idea that a user can enter multiple theses into a form, or just one? Your table suggests they can have multiple. If this is what you are trying to achieve then I think you should be storing the id of each thesis in a hidden field as part of the form data. You would then be able to use REPLACE INTO thesis (id, user_id, thesis_name, abstract) VALUES ($id, $user_id, $thesis_name, $abstract) where id is the id of the thesis obtained from each hidden field. If this is not present, i.e. the user has entered a new thesis, then use NULL for id in the insert. This will work using the REPLACE INTO as the id column is auto increment.

edited Apr 28, 2012 at 18:36

answered Apr 28, 2012 at 9:04

Martin Wilson

3,3861 gold badge26 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1296762 Over a year ago

thank you for your useful advice! I tried experimenting with this but was never successful how would you do it?

Martin Wilson Over a year ago

To see if a row exists for a user, execute: SELECT 1 FROM thesis WHERE user_id = $user_id. If this returns a row then you know one exists for this user so you can execute UPDATE thesis SET [etc] WHERE user_id=user_id. Otherwise, execute INSERT INTO thesis (user_id, etc) VALUES ($user_id, etc)

Lawrence Cherone · Accepted Answer · 2012-04-28 09:06:44Z

Perhaps you mean user_id not id:

$query = "REPLACE INTO thesis ( thesis_Name, abstract) 
          VALUES ('$thesis_Name','$abstract') 
          WHERE user_id='{$_SESSION['user_id']}'";

Or if you do mean the id from $_POST['id']

$query = "REPLACE INTO thesis ( thesis_Name, abstract) 
          VALUES ('$thesis_Name','$abstract') 
          WHERE id='$id'";

Also instead of REPLACE you should use UPDATE. Im pretty sure its faster because REPLACE basically deletes the row then inserts it again, im pretty sure you need all the fields and values else your insert default values. From the manual:

Values for all columns are taken from the values specified in the REPLACE statement. Any missing columns are set to their default values, just as happens for INSERT

So you should use:

$query = "UPDATE thesis 
          SET thesis_Name='$thesis_Name', abstract='$abstract' 
          WHERE id='$id'";

Dhruvisha · Accepted Answer · 2012-04-28 11:18:12Z

0

You are doing everything right just one thing you are doing wrong Your replace query variable is $query and you executing $the_query. you wrong here:

$query = "REPLACE INTO thesis ( thesis_Name, abstract) VALUES ('$thesis_Name',
   '$abstract') where id='$_SESSION[user_id]'";
    if (!mysql_query($the_query)) // this is wrong 
        echo "the query failed";

replace it with:

$query = "REPLACE INTO thesis ( thesis_Name, abstract) VALUES ('$thesis_Name',
   '$abstract') where id='$_SESSION[user_id]'";
    if (!mysql_query($query)) // use $query
        echo "the query failed";

answered Apr 28, 2012 at 11:18

Dhruvisha

2,5301 gold badge22 silver badges31 bronze badges

1 Comment

user1296762 Over a year ago

Is there something I can put in to show what the query is trying to enter?

Collectives™ on Stack Overflow

replace not updating in database

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related