Page 1 of 1

PHP refuses to update database

Posted: Thu Mar 15, 2007 7:45 pm
by Citizen

Code: Select all

$newtype = intval($newtype);
$sql = "UPDATE `user` SET `type` = '$newtype' WHERE `id` = '$userid' LIMIT 1";
echo"$sql";
$result = mysql_query($sql) or die(mysql_error());
echo"<p>Congratulations, your user type has been changed!</p>";
echos:
UPDATE `user` SET `type` = '6' WHERE `id` = '64' LIMIT 1
Congratulations, your user type has been changed!
With no errors... but the database didn't update! I checked via phpmyadmin and the type still reads 3... any ideas?

Posted: Thu Mar 15, 2007 8:04 pm
by volka
try

Code: Select all

echo "<div>start</div>\n";
$sql = "SELECT `id`,`type` FROM `user` WHERE `id` = '$userid' LIMIT 1";
echo 'sql: ', $sql, "<br />\n";
$result = mysql_query($sql) or die(mysql_error());
if ( false!==($row = mysql_fetch_array($result, MYSQL_ASSOC)) ) {
	echo 'before: ', join(',', $row), "<br />\n";
}

$newtype = intval($newtype);
$sql = "UPDATE `user` SET `type` = '$newtype' WHERE `id` = '$userid' LIMIT 1";
echo 'sql: ', $sql, "<br />\n";
$result = mysql_query($sql) or die(mysql_error());

echo 'affected rows: ', mysql_affected_rows($result), "<br />\n";

$sql = "SELECT `id`,`type` FROM `user` WHERE `id` = '$userid' LIMIT 1";
echo 'sql: ', $sql, "<br />\n";
$result = mysql_query($sql) or die(mysql_error());
if ( false!==($row = mysql_fetch_array($result, MYSQL_ASSOC)) ) {
	echo 'after: ', join(',', $row), "<br />\n";
}
echo "<div>end</div>\n";

Posted: Thu Mar 15, 2007 8:11 pm
by Citizen
I got this (I have a debug thingy going as well):
start
sql: SELECT `id`,`type` FROM `user` WHERE `id` = '64' LIMIT 1
before: 64,3
sql: UPDATE `user` SET `type` = '6' WHERE `id` = '64' LIMIT 1
affected rows:
Warning: mysql_affected_rows(): supplied argument is not a valid MySQL-Link resource
1 /file/templates_c/%%-15%%-1591772959usertypechanger.html.php : 42 mysql_affected_rows(TRUE)
2 /include/classes/smarty/Smarty.class.php : 1943 include("/home/gamerbio/public_html/file/templates_c/%%-15%%-1591772959usertypechanger.html.php")
3 /file/templates_c/%%-10%%-1057870779_template.html.php : 112 Smarty->_smarty_include(""Array(2)"")
4 /include/classes/smarty/Smarty.class.php : 1293 include("/home/gamerbio/public_html/file/templates_c/%%-10%%-1057870779_template.html.php")
5 /include/classes/Template.class.php : 87 Smarty->fetch(""""""public/_template.html"""""", """""NULL""""", """""NULL""""", """""FALSE""""")
6 /include/classes/Template.class.php : 125 Template->fetch(""""""public/_template.html"""""")
7 /include/classes/App.class.php : 438 Template->view(""""""public/usertypechanger.html"""""", """"""public"""""")
8 /index.php : 54 App::run()


sql: SELECT `id`,`type` FROM `user` WHERE `id` = '64' LIMIT 1
after: 64,6
end

Just checked in phpmyadmin, the type is still set at 3... doesnt make sense does it?


Here is my full page code incase it helps:

(note I am using the smarty template system)

Code: Select all

<table>
<tr>
<td>
<div style="margin-left: 6%;height:500px;">
<h1>Select your desired user type:</h1>
{php}
$suser = $this->get_template_vars('aUser');
if($suser){
	$sql = "SELECT * FROM `user` WHERE `user` = '$suser' LIMIT 1";
	$result = mysql_query($sql) or die(mysql_error());
	$row = mysql_fetch_array($result);
	$oldtype = $row[type];
	$userid = $row[id];
	if($oldtype == '0' || $oldtype == '1' || $oldtype == '2'){
		echo"<p>Administators cannot change their user type for security reasons.</p>";
	}
	else{
		if($_POST['submit']){
			$newtype = mysql_real_escape_string($_POST['newtype']);
			if($newtype != '0' && $newtype != '1'){
				$newtype = intval($newtype);
				$sql = "UPDATE `user` SET `type` = '$newtype' WHERE `id` = '$userid' LIMIT 1";
				echo"$sql";
				$result = mysql_query($sql) or die(mysql_error());
				echo"<p>Congratulations, your user type has been changed!</p>";
			}
		}
		{/php}
		<form method='post' action="{url link=public.usertypechanger}">
		{php}
		$sql = "SELECT * FROM `membership` WHERE `userSelectable` = '1' ORDER BY `membership_id` DESC";
		$result = mysql_query($sql) or die(mysql_error());
		$numcheck = mysql_num_rows($result);
		for($i=0;$i<$numcheck;$i++){
			$row = mysql_fetch_array($result);
			$id = $row[membership_id];
			$name = $row[title];
			if($id == $oldtype){
				echo"<p><input type=\"radio\" name=\"newtype\" value=\"$id\" checked> $name";
			}
			if($id != $oldtype){
				echo"<p><input type=\"radio\" name=\"newtype\" value=\"$id\"> $name";
			}
		}
		{/php}
		<br /><br />
		<input type="submit" name="submit" value="Submit">
		</form>
	{php}
	}
}
{/php}
</div>
</td>
</tr>
</table>

Posted: Thu Mar 15, 2007 8:34 pm
by volka
Citizen wrote:I got this (I have a debug thingy going as well):
start
sql: SELECT `id`,`type` FROM `user` WHERE `id` = '64' LIMIT 1
before: 64,3
sql: UPDATE `user` SET `type` = '6' WHERE `id` = '64' LIMIT 1
[...]
sql: SELECT `id`,`type` FROM `user` WHERE `id` = '64' LIMIT 1
after: 64,6
end

Just checked in phpmyadmin, the type is still set at 3... doesnt make sense does it?
It really doesn't.
You're sure you're looking at the right table with phpmyadmin? Maybe a refresh via F5 will "fix" the problem.


btw: oops, my bad

Code: Select all

echo 'affected rows: ', mysql_affected_rows(), "<br />\n";
without $result.