Using a Button to update a cell in database

[05bmckay]

So what I’m trying to accomplish is updating a cell in my MySQL database with a button click and I found this post Update database on button click? - #7 by droopsnoot but couldn’t figure out how to get it working with my code:

<?php

$connect = new mysqli("localhost", "root", "", "login_page");

 

$sqlQuery = "SELECT * FROM scores";

$qry = $connect->query($sqlQuery);

echo "<table class='greyGridTable'><tr><th>Name</th><th>1st Call</th><th>FO</th><th>OPPT</th><th>1st Deal</th><th>2nd Deal</th><th>3rd Deal</th></tr>";

while($row = $qry->fetch_assoc()){

echo "<tr class='<?php echo $color;?> lighten-2" class="<?php echo $color;?> lighten-2'>

<td>".$row["Name"]."</td>

<td>".$row["1stcall"]."</td>

<td>".$row["FO"]."</td>

<td>".$row["OPPT"]."</td>

<td>".$row["1stdeal"]."</td>

<td>".$row["2nddeal"]."</td>

<td>".$row["3rddeal"]."</td>

<td>

</tr>";

}

 

echo "</table>";

?>

I need to change “1stcall” 's value to a 1 on button click. Let me know if I need to give more information

[Drummin]

Like some of those responses in the other thread, if you create a form for each of those “deal cells”, which holds an input for both the record id and the “deal field” name, then only one “deal field” name is sent on POST. Something like this.

<form action="" method="post">
	<button class="btn btn-default" type="submit" name="SelectDeal">1st Deal</button>
	<input type="hidden" name="record_id" value="' .$row['id']. '"/>
	<input type="hidden" name="1stdeal" value="1"/>
</form>

You then have a logical IF statement saying IF Not Empty 1stdeal THEN value is 1 else value is 0. You could set each name to a variable using these conditions.

	$deal1st = (!empty($_POST['1stdeal']) ? 1 : 0);	
	$deal2nd = (!empty($_POST['2nddeal']) ? 1 : 0);	
	$deal3rd = (!empty($_POST['3rddeal']) ? 1 : 0);

Then all that is left is wrapping it all up within a form submission condition check using the button name and also checking that the record ID is not empty as you will use this to identify the record to update.

if($_SERVER["REQUEST_METHOD"] == "POST" && isset($_POST['SelectDeal']) && !empty($_POST['record_id'])):

endif;

As a bonus, if you wanted to show a button color based on the values in the database you could set avariable to echo a CLASS name or an inline style tag. Here’s an example of inline style type, which adds a green background color to records with a value of 1.

	$selected_1stdeal = (!empty($row['1stdeal']) ? ' style="background-color:#92CC58"' : '');
	$selected_2nddeal = (!empty($row['2nddeal']) ? ' style="background-color:#92CC58"' : '');
	$selected_3rddeal = (!empty($row['3rddeal']) ? ' style="background-color:#92CC58"' : '');

Putting it all together you would have this.

if($_SERVER["REQUEST_METHOD"] == "POST" && isset($_POST['SelectDeal']) && !empty($_POST['record_id'])):

	$deal1st = (!empty($_POST['1stdeal']) ? 1 : 0);	
	$deal2nd = (!empty($_POST['2nddeal']) ? 1 : 0);	
	$deal3rd = (!empty($_POST['3rddeal']) ? 1 : 0);
	
	$sql_update_deal = "UPDATE `scores` SET 
	 `1stdeal` = ?
	,`2nddeal` = ?
	,`3rddeal` = ? 
	WHERE `id` = ?";
	$query_update_deal = $connect->prepare($sql_update_deal);	
	$query_update_deal->bind_param("iiii", $deal1st, $deal2nd, $deal3rd, $_POST['record_id']);
	$query_update_deal->execute(); 
endif;

$sqlQuery = "SELECT * FROM scores";

$qry = $connect->query($sqlQuery);

echo '<table class="greyGridTable">
	<tr>
		<th>Name</th>
		<th>1st Call</th>
		<th>FO</th>
		<th>OPPT</th>
		<th>1st Deal</th>
		<th>2nd Deal</th>
		<th>3rd Deal</th>
	</tr>'."\r";

while($row = $qry->fetch_assoc()){
	
	$selected_1stdeal = (!empty($row['1stdeal']) ? ' style="background-color:#92CC58"' : '');
	$selected_2nddeal = (!empty($row['2nddeal']) ? ' style="background-color:#92CC58"' : '');
	$selected_3rddeal = (!empty($row['3rddeal']) ? ' style="background-color:#92CC58"' : '');

	echo '<tr class="'. $color .' lighten-2">
		<td>'.$row['Name'].'</td>
		<td>'.$row['1stcall'].'</td>
		<td>'.$row['FO'].'</td>
		<td>'.$row['OPPT'].'</td>
		<td>
			<form action="" method="post">
				<button class="btn btn-default" type="submit" name="SelectDeal"'.$selected_1stdeal.'>1st Deal</button>
				<input type="hidden" name="record_id" value="' .$row['id']. '"/>
				<input type="hidden" name="1stdeal" value="1"/>
			</form>
		</td>
		
		<td>
			<form action="" method="post">
				<button class="btn btn-default" type="submit" name="SelectDeal"'.$selected_2nddeal.'>2nd Deal</button>
				<input type="hidden" name="record_id" value="' .$row['id']. '"/>
				<input type="hidden" name="2nddeal" value="1"/>
			</form></td>
		
		<td>
			<form action="" method="post">
				<button class="btn btn-default" type="submit" name="SelectDeal"'.$selected_3rddeal.'>3rd Deal</button>
				<input type="hidden" name="record_id" value="' .$row['id']. '"/>
				<input type="hidden" name="3rddeal" value="1"/>
			</form></td>
	
	</tr>'."\r";

}

echo '</table>'."\r";

[05bmckay]

Hey Man, Thanks so much for the response I’m looking through your answer right now and making the edits. You were super helpful

Edit: It didn’t work right away and unfortunately I don’t think the this method that I’m trying to follow is the best way to do it so I’m going to try and do it another way.

[Drummin]

The “best Way”? Maybe not but it does work as explained.

Are your database field types for deals INT? or VARCHAR? You were saying values of 0 changing to 1 so I made the field INT. IF your fields are varchar, then the UPDATE query should bind those values as type s so they will be quoted. So the bind_param line would be as follows.

$query_update_deal->bind_param("sssi", $deal1st, $deal2nd, $deal3rd, $_POST['record_id']);

Note: When giving feedback specify what didn’t work.

[05bmckay]

I really appreciate all your help @Drummin but I’m really close to solving it another way. I’ll let you know when I’m done and I’ll post my code.
Also to your note about telling people what didn’t work. The part that didn’t work was all of it (I don’t know if this is a user error as I’m not a very good PHP programmer, like getting a D in my PHP college class bad, so…) but I was only able to see the <h2’> title and the buttons.

[Drummin]

YES, there were some coding issues. Mostly being in php then having OPEN php again and CLOSE php and adding echo inside echo. Doubled up class tags doesn’t help the mater either.

echo "<tr class='<?php echo $color;?> lighten-2" class="<?php echo $color;?> lighten-2'>

Compared to my version where we echo the tr tag with color variable inside the CLASS attribute.

echo '<tr class="'. $color .' lighten-2">

Note I also DEFINED this variable as I didn’t see it in the code you posted. SO you should try to write code with care and no errors before determining that the code works and fits your needs, errors will kill your script.