Jump to content

How to update a query with a new value


phunnydoode

Recommended Posts

Currently I show a list of users and their information. You can than click that specific user and it shows you their information to edit. My problem is it's not updating the query with the new input information. 

Here is the page that displays the registration information:

<?php
$con=mysqli_connect('localhost', 'root', '');

/* check connection */

if (mysqli_connect_errno($con)) {
	trigger_error('Database connection failed: '  . mysqli_connect_error(), E_USER_ERROR);
}

 $query = "SELECT * FROM `bencobricks` . `users`";
 $result = mysqli_query($con, $query) or trigger_error("Query Failed! SQL: $query - Error: ". mysqli_error($con), E_USER_ERROR); 

 
 ?>

<table width="1000" border="0" cellspacing="1" cellpadding="0">
<tr>
<td>
<table width="1000" border="1" cellspacing="0" cellpadding="3">
<tr>
<td colspan="4"><strong>List data from mysql </strong> </td>
</tr>

<tr>
<td align="center"><strong>Username</strong></td>
<td align="center"><strong>Email</strong></td>
<td align="center"><strong>Membership</strong></td>
<td align="center"><strong>First Name</strong></td>
<td align="center"><strong>Last Name</strong></td>
<td align="center"><strong>Gender</strong></td>
<td align="center"><strong>Birthdate</strong></td>
<td align="center"><strong>Sets</strong></td>
<td align="center"><strong>Checkbox</strong></td>
<td align="center"><strong>Admin Flag</strong></td>
</tr>

<?php
while($rows=mysqli_fetch_array($result)){
	
	?>

<tr>
<td><?php echo $rows['username']; ?></td>
<td><?php echo $rows['email']; ?></td>
<td><?php echo $rows['membership']; ?></td>
<td><?php echo $rows['firstName']; ?></td>
<td><?php echo $rows['lastName']; ?></td>
<td><?php echo $rows['gender']; ?></td>
<td><?php echo $rows['dateOfBirth']; ?></td>
<td><?php echo $rows['sets']; ?></td>
<td><?php echo $rows['checkbox']; ?></td>
<td><?php echo $rows['adminFlag']; ?></td>

<td align="center"><a href="update.php?userID=<?php echo $rows['userID']; ?>">update</a></td>
</tr>

<?php
}
?>

</table>
</td>
</tr>
</table>

<?php
mysqli_close($con);
?>

Page that displays specific user for updating:
 

<?php
$con=mysqli_connect('localhost', 'root', '');

/* check connection */

if (mysqli_connect_errno($con)) {
	trigger_error('Database connection failed: '  . mysqli_connect_error(), E_USER_ERROR);
}

$id=$_GET['userID'];

$query = "SELECT * FROM `bencobricks` . `users` WHERE `userID` = '$id'";
$result = mysqli_query($con, $query) or trigger_error("Query Failed! SQL: $query - Error: ". mysqli_error($con), E_USER_ERROR);
$rows=mysqli_fetch_array($result);

?>

<table width="1000" border="0" cellspacing="1" cellpadding="0">
<tr>
<form name="form1" method="post" action="update_ac.php">
<td>
<table width="100%" border="0" cellspacing="1" cellpadding="0">
<tr>
<td> </td>
<td colspan="3"><strong>Update data in mysql</strong> </td>
</tr>
<tr>
<td align="center"> </td>
<td align="center"> </td>
<td align="center"> </td>
<td align="center"> </td>
</tr>
<tr>
<td align="center"><strong>Username</strong></td>
<td align="center"><strong>Email</strong></td>
<td align="center"><strong>Membership</strong></td>
<td align="center"><strong>First Name</strong></td>
<td align="center"><strong>Last Name</strong></td>
<td align="center"><strong>Gender</strong></td>
<td align="center"><strong>Birthdate</strong></td>
<td align="center"><strong>Sets</strong></td>
<td align="center"><strong>Checkbox</strong></td>
<td align="center"><strong>Admin Flag</strong></td>
</tr>
<tr>

<td align="center">
<input name="username" type="text" id="username" value="<?php echo $rows['username'] ;
echo (isset($_POST['username']) ? $_POST['username'] : ''); ?>"> 
</td>
<td align="center">
<input name="email" type="text" id="email" value="<?php echo $rows['email']; ?>" size="15">
</td>
<td>
<input name="membership" type="text" id="membership" value="<?php echo $rows['membership']; ?>" size="15">
</td>
<td>
<input name="firstName" type="text" id="firstName" value="<?php echo $rows['firstName']; ?>" size="15">
</td>
<td>
<input name="lastName" type="text" id="lastName" value="<?php echo $rows['lastName']; ?>" size="15">
</td>
<td>
<input name="gender" type="text" id="gender" value="<?php echo $rows['gender']; ?>" size="15">
</td>
<td>
<input name="dateOfBirth" type="text" id="dateOfBirth" value="<?php echo $rows['dateOfBirth']; ?>" size="15">
</td>
<td>
<input name="sets" type="text" id="sets" value="<?php echo $rows['sets']; ?>" size="15">
</td>
<td>
<input name="checkbox" type="text" id="checkbox" value="<?php echo $rows['checkbox']; ?>" size="15">
</td>
<td>
<input name="adminFlag" type="text" id="adminFlag" value="<?php echo $rows['adminFlag']; ?>" size="15">
</td>
</tr>
<tr>
<td> </td>
<td>
<input name="id" type="hidden" id="id" value="<?php echo $rows['userID']; ?>">
</td>
<td align="center">
<input type="submit" name="Submit" value="Submit">
</td>
<td> </td>
</tr>
</table>
</td>
</form>
</tr>
</table>

<?php
// close connection 
mysqli_close($con);

?>

Finally the update query: 
 

<?php
function test_input($data){
	$data = trim($data);
	$data = stripslashes($data);
	$data = htmlspecialchars($data);
	return $data;
}

$con=mysqli_connect('localhost', 'root', '');

/* check connection */

if (mysqli_connect_errno($con)) {
	trigger_error('Database connection failed: '  . mysqli_connect_error(), E_USER_ERROR);
}

// update data in mysql database 
if (empty($_POST["username"]))
{/* can never be empty due to design */}
else
{$username= test_input($_POST['username']);}
$id = 'id';
$query = "UPDATE `bencobricks`. `users` SET `username` = '$username', `password` = 'NULL', `email` = 'email', `membership` = 'membership', `firstName` = 'firstName', `lastName` = 'lastName',
`gender` = 'gender', `dateOfBirth` = 'dateOfBirth', `date` = 'NULL', `sets` = 'sets', `checkbox` = 'checkbox', `adminFlag` = 'adminFlag' WHERE `userID` = '$id'";




$result = mysqli_query($con, $query) or trigger_error("Query Failed! SQL: $query - Error: ". mysqli_error($con), E_USER_ERROR);

// if successfully updated. 
if($result){
echo "Successful";
echo "<BR>";
echo "<a href='editUser.php'>View Users Page</a>";
}

else {
echo "ERROR";
}

?>

post-174346-0-15695700-1422340794_thumb.png

post-174346-0-09445600-1422340795_thumb.png

Link to comment
https://forums.phpfreaks.com/topic/294253-how-to-update-a-query-with-a-new-value/
Share on other sites

You are just using strings in the query values, instead of variables.

 

$query = "UPDATE `bencobricks`. `users` SET `username` = '$username', `password` = 'NULL', `email` = 'email', `membership` = 'membership', `firstName` = 'firstName', `lastName` = 'lastName',
`gender` = 'gender', `dateOfBirth` = 'dateOfBirth', `date` = 'NULL', `sets` = 'sets', `checkbox` = 'checkbox', `adminFlag` = 'adminFlag' WHERE `userID` = '$id'";

Also, as long as you're using MySQLi, why not do it right and use prepared statements to avoid the SQL injection possibilities your script currently has? You're already a bit ahead of the game by using mysqli_* instead of mysql_*, so why not go all the way?

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.