MYSQL Update followed by Select query not working

[jumpinjacq]

Hi there,

 

I'm having a problem with updating a record with an UPDATE mysql query and then following that query with a SELECT query to get those values just updated.

 

This is what I'm trying to do...I'd like a member to be able to complete a recommended task and upon doing so, go to a page in their back office where they can check off that task as "Completed".  This completed task would be recorded in their member record in our database so that when they return to this list, it will remain as "Completed".  I'm providing the member with a submit button that will call the same page and then update depending on which task is clicked as complete.

 

Here is my code:

$memberid = $_SESSION['member'];

// Check if form has been submitted
if(isset($_POST['task_done']) && $_POST['task_submit'] == 'submitted')
{	
  $taskvalue = $_POST['task_value'];	
  $query = "UPDATE membertable SET $taskvalue = 'done' WHERE id = $memberid";
  $result = mysqli_query($dbc, $query);
}

$query ="SELECT task1, task2, task3 FROM membertable WHERE id = $memberid";
$result = mysqli_query($dbc, $query);
$row = mysqli_fetch_array($result, MYSQLI_ASSOC);

$_SESSION['task1'] = $row['task1'];
$_SESSION['task2'] = $row['task2'];
$_SESSION['task3'] = $row['task3'];
?>
<h4>Task List</h4>
<table>

  <form action="" method="post">
    <tr>
      <td>Task 1</td>
      <td><?php if($_SESSION['task1'] == 'done') {echo '<div>Completed</div>';} else{ echo '<input type="submit" name="task_done" value="Mark As Completed" />';} ?></td>
    </tr>
    <input type="hidden" name="task_value" value="task1" />
    <input type="hidden" name="task_submit" value="submitted" />				
  </form>

  <form action="" method="post">
    <tr>
      <td>Task 1</td>
      <td><?php if($_SESSION['task1'] == 'done') {echo '<div>Completed</div>';} else{ echo '<input type="submit" name="task_done" value="Mark As Completed" />';} ?></td>
    </tr>
    <input type="hidden" name="task_value" value="task2" />
    <input type="hidden" name="task_submit" value="submitted" />				
  </form>

  <form action="" method="post">
    <tr>
      <td>Task 1</td>
      <td><?php if($_SESSION['task1'] == 'done') {echo '<div>Completed</div>';} else{ echo '<input type="submit" name="task_done" value="Mark As Completed" />';} ?></td>
    </tr>
    <input type="hidden" name="task_value" value="task3" />
    <input type="hidden" name="task_submit" value="submitted" />				
  </form>

</table>

 

The problem that I am having is that the database is not updated with the value "done" but after submission, the screen displays "Completed" instead of "Mark As Completed".  So the value is being picked up as "done", but that is why I have the SELECT after the UPDATES, so that there is always a current value for whether a task is done or not.  Then I refresh and the screen returns the button to Mark As Complete.  Also, when I try marking all three tasks as, sometimes all three are updated, sometimes only one or two and again, I leave the page or refresh and the "Marked As Completed" buttons come back.  Bizarre.  If anyone can tell me where my logic is going wrong, I would appreciate it.

[mikosiko]

I really doubt that you have a field named "$taskvalue" in your table

 

$query = "UPDATE membertable SET $taskvalue = 'done' WHERE id = $memberid";

[mikosiko]

echo you raw query to check if the UPDATE is what you are expecting.

 

  $query = "UPDATE membertable SET $taskvalue = 'done' WHERE id = $memberid";

  echo "Query : " . $query;  // and check if sentence is valid

  $result = mysqli_query($dbc, $query);

 

I also noticed that in the 3 forms you are using $_SESSION['task1'] ... 

[jcbones]

You need to make sure $task_value has the desired contents (echo it). You should have only 1 task per row, and perhaps a separate table for task, and a separate table for task_completed.

 

My take:

Tables

CREATE TABLE `user` (
  id int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  username varchar(20) NOT NULL,
  `password` varchar(41) NOT NULL,
  PRIMARY KEY (id)
)

CREATE TABLE `task` (
  id int(11) NOT NULL AUTO_INCREMENT,
  task_name varchar(20) NOT NULL,
  task_class int(3) NOT NULL,
  task_level int(3) NOT NULL,
  PRIMARY KEY (id)
)

CREATE TABLE `task_complete` (
`user_id` INT( 11 ) NOT NULL ,
`task_id` INT( 11 ) NOT NULL
)

 

Now you can insert your task in their own table, totally separate of the users, this being much more efficient, in that you do not have to update 500 rows to add 1 task.  Simply by adding 1 task your users can immediately complete them.

Adding a user

INSERT INTO user (username,password) VALUES ('Big Bald Head',PASSWORD('IamAmonkey'));

 

Adding task

INSERT INTO task(task_name,task_class,task_level) VALUES('Find a Woman',5,100);

 

Now that you have a task, all users can now see it with a simple call to:

SELECT task_name FROM task ORDER BY id DESC;

 

If they complete it, you insert a row into task_complete.

INSERT INTO task_complete (user_id,task_id) VALUES (1,1);

 

To see how many task a user has completed, you just call the task completed table joined with the user and task tables.

SELECT u.username, t.task_name FROM task_complete AS c JOIN user AS u ON u.id = c.user_id JOIN task AS t ON t.id = c.task_id

 

If you need to limit it to a single user, just add the where clause

SELECT u.username, t.task_name FROM task_complete AS c JOIN user AS u ON u.id = c.user_id JOIN task AS t ON t.id = c.task_id WHERE c.user_id = 1

 

This Post is to help you get your database

.  Which will turn changes to your project from a headache, to a breeze.

 

 

[jumpinjacq]

I've done quite a bit of testing to see where the problem might be.  Including what some of you have suggested.  I made sure all the variables contained information and the right information, also checked for query errors, none, I got 1 as a $result response. I tested the query in the sql command prompt and the update worked.  Then I started right from scratch and added bit by bit right from creating a simple query to changing update  values to see if they would update in the table and display with the subsequent SELECT queries.  All worked perfectly until I put it in the format that I have above.

 

I like the your idea jcbones, I'll study your example as it may be a good solution in any case.

 

 

[jumpinjacq]

Here is an example of one of the tests that I ran just to check if my process was right, a simple update and select and checking to see if my variables were coming out in the wash.  And that is why I can't understand why my other code is not working.

 

$done_welcome_video = 'wrong';
echo $done_welcome_video;
echo '<br>';

if(isset($_POST['a']))
{
$memberid = 1;
$query = "UPDATE orientation SET done_welcome_video = 'red' WHERE memberid = $memberid";
echo $query;
echo '<br>';
$result = mysqli_query($dbc, $query);

 if($result)
 {
	echo 'good';
	echo '<br>';
	unset($_POST['a']);

 } else
 {
	echo 'bad';
	echo '<br>';
 }
}

if(isset($_POST['b']))
{
$memberid = 1;
$query = "UPDATE orientation SET done_welcome_video = 'black' WHERE memberid = $memberid";
echo $query;
echo '<br>';
$result = mysqli_query($dbc, $query);
if($result)
 {
	echo 'good';
	echo '<br>';
	unset($_POST['b']);
 } else
 {
	echo 'bad';
	echo '<br>';
 }
}
$memberid = 1;
$query ="SELECT * FROM orientation WHERE memberid = $memberid";
echo $query;
echo '<br>';
$result = mysqli_query($dbc, $query);
$row = mysqli_fetch_array($result, MYSQLI_ASSOC);

$done_welcome_video = $row['done_welcome_video'];
echo $done_welcome_video;
echo '<br>';
$done_welcome_video = 1;
echo $done_welcome_video;

?>
<form action="" method="post">
<input type="submit" name="a" value="red" />
<input type="submit" name="b" value="black" />				
</form>

 

One of the strangest things that I've noticed is that the code is recognizing that the task has been completed, but the database shows no data at all.  It's almost like my code has cached the values.

 

 

[Pikachu2000]

This may sound like a silly question, but are you sure there's a record in the orientation table with a value of 1 in the memberid field?

[jumpinjacq]

Yes, there is.

 

It's so odd, I've been playing around a bit:

 

Here is the test I've set up as it stands now.  Just a simple display of some of the info and I'll show what is displayed and some of the quirks that are happening...

 

$done_welcome_video = 'wrong';
echo $done_welcome_video;
echo '<br>';

if(isset($_POST['done_welcome']))
{
$memberid = 1;
$query1 = "UPDATE orientation SET done_welcome_video = 'done' WHERE memberid = $memberid";
echo $query1;
echo '<br>';
$result1 = mysqli_query($dbc, $query1);

 if($result1)
 {
	echo 'good';
	echo '<br>';
	unset($_POST['a']);

 } else
 {
	echo 'bad';
	echo '<br>';
 }
}

if(isset($_POST['done_booking']))
{
$memberid = 1;
$query2 = "UPDATE orientation SET done_booking_session = 'done' WHERE memberid = $memberid";
echo $query2;
echo '<br>';
$result2 = mysqli_query($dbc, $query2);
if($result2)
 {
	echo 'good';
	echo '<br>';
	unset($_POST['b']);
 } else
 {
	echo 'bad';
	echo '<br>';
 }
}

$memberid = 1;
$query3 ="SELECT * FROM orientation WHERE memberid = $memberid";
echo $query3;
echo '<br>';
$result3 = mysqli_query($dbc, $query3);
$row = mysqli_fetch_array($result3, MYSQLI_ASSOC);

$done_welcome_video = $row['done_welcome_video'];
$done_booking_session = $row['done_booking_session'];
echo $done_welcome_video;
echo '<br>';
echo $done_booking_session;
echo '<br>';


?>
<form action="" method="post">
<input type="submit" name="done_welcome" value="welcome" />		
</form>
<form action="" method="post">
<input type="submit" name="done_booking" value="booking" />				
</form>

 

In the database:

 

done_welcome_video = blue

done_booking_session = green

 

The result on screen after reloading the page from scratch:

 

wrong

SELECT * FROM orientation WHERE memberid = 1

blue

done

 

after I click the welcome button, which should update done_welcome_video to "done", this is what I get on the screen

 

wrong

UPDATE orientation SET done_welcome_video = 'done' WHERE memberid = 1

good

SELECT * FROM orientation WHERE memberid = 1

done

done

 

after I click the booking button, which should update done_booking_session to "done", this is what I get on the screen

 

wrong

UPDATE orientation SET done_booking_session = 'done' WHERE memberid = 1

good

SELECT * FROM orientation WHERE memberid = 1

done

done

 

Then I refresh phpmyadmin screen and the values are blue and green as they were.

 

I hope that makes it a little bit clearer as to what is happening.

 

 

 

[jumpinjacq]

I've done a lot of playing around, so if there will be some code that is not going to make sense, but I forgot to mention one thing, if I remove the updating part completely and leave only the query, I upload, then refresh the page and get the proper values showing:

 

wrong

SELECT * FROM orientation WHERE memberid = 1

blue

green

 

So now the database values are being pulled correctly.

 

 

[Drummin]

You are using $query and $result in both sections.  I know a lot of people use the same variables in a page.  If you've gone through everything else, field names etc, you might try making $query2 and $result2 for the display section.  Hey maybe I'm wrong but I always make my variables unique on a page. 

[jumpinjacq]

Yes, as you can see in the latest code, I've done exactly that, I've changed it so that all three queries/results are different.  Unfortunately, that did not fix the problem.  But thanks for the idea.

 

One thing I have to wonder, is why is it that the update query shows up after a page refresh, I've tried unsetting the submit value in several places and unless I unset at the beginning of the page, which prevents updates in any event, it remains on the screen telling me that the update is running, but not affecting the database.  This seems to be a fundamental misunderstanding on my part as to how the code runs and I still feel that there is a caching effect here and I don't know if it's a matter of understanding it or simple a procedural issue.

[Drummin]

Wondering why you are using mysqli.  Are you running php5?  I generally use the old mysql_query.  Hey just wondering.

[jumpinjacq]

If php5 means version, then my server has PHP Version 5.2.13.

[Drummin]

Having not worded with mysqli before I was struggling to get it to work.  Seems I was just using my host, username and password for the $dbc connect variable.

$dbc = mysqli_connect($host, $login, $pass);

As soon as I added the database name it worked fine.

$dbc = mysqli_connect($host, $login, $pass, $db);

[jumpinjacq]

Yes, the connection variable has to be included.

 

So just as an update to this issue, it seems that my testing is what did me in, there must be a caching or session thing going on because I've created another table to test and the update worked the first time only, then I went back to the original table and added another record and again the update worked fine.  The problem ensues when I try to test by clearing the values in the database columns and then going back to my page, refreshing and expecting that the "completed" status will not be there, but it is, even though the columns show no values in the database.  No explanation yet, but either way, it will work as is.