mysql_affected_rows is not working..

bugzy · July 14, 2012

Hello guys I have this sql code and some notification..

<?php


$query = "Update category set cat_name='{$cat_name}', cat_position='{$cat_position}', cat_visibility='{$cat_visibility}' where cat_id = {$edit_cat_id} LIMIT 1";

	$result = mysql_query($query,$connection);

	if(mysql_affected_rows() == 1)
	{
		me_redirect_to('edited_category.php?edited=1');

	}
	else
	{
		echo "<span class=\"error_validation\">The data that have been entered are exactly the same as the previous information.<br>Type the information you want to change below.</span>" . mysql_error() . "";
	}


?>

If a user enter the same exact data that are already in the database it suppose to tell him the else statement, problem is, it is recognizing that there was a changed even though the data that just have been entered are exactly the same...

Anyone?

bugzy · July 14, 2012

Sorry guys... What I'm talking about here is'

mysql_affected_rows() not mysql_num_rows

I can't edit the original post already....

I hope a moderator will edit my thread title and 1st post.

Pikachu2000 · July 14, 2012

Have you echoed the query string and run it in phpMyAdmin, or the mySQL command line to see what happens? Have you echoed mysql_affected_rows() to see what value it actually holds, and how that may be related to the problem?

bugzy · July 14, 2012

Have you echoed the query string and run it in phpMyAdmin, or the mySQL command line to see what happens? Have you echoed mysql_affected_rows() to see what value it actually holds, and how that may be related to the problem?

I think I know what the problem is now..

This is the full code

<?php

//Menu Positiong Code


	if($cat_position < $edit_cat_position)
		{

			$update_query1 = "Update category set cat_position=NULL where cat_id = {$edit_cat_id}";

			$update_result1 = mysql_query($update_query1,$connection);


			$update_query2 = "Update category set cat_position=cat_position+1 where cat_position >= {$cat_position} AND cat_position < {$edit_cat_position}";

			$update_result2 = mysql_query($update_query2,$connection);

		}
		else
		{

			$update_query1 = "Update category set cat_position=NULL where cat_id = {$edit_cat_id}";

			$update_result1 = mysql_query($update_query1,$connection);


			$update_query2 = "Update category set cat_position=cat_position-1 where cat_position > {$edit_cat_position} AND cat_position <= {$cat_position}";

			$update_result2 = mysql_query($update_query2,$connection);

		}





		//Update Code



	$query = "Update category set cat_name='{$cat_name}', cat_position='{$cat_position}', cat_visibility='{$cat_visibility}' where cat_id = {$edit_cat_id} LIMIT 1";

	$result = mysql_query($query,$connection);

	if(mysql_affected_rows() == 1)
	{
		me_redirect_to('edited_category.php?edited=1');

	}
	else
	{
		echo "<span class=\"error_validation\">The data that have been entered are exactly the same as the previous information.<br>Type the information you want to change below.</span>" . mysql_error() . "";
	}
?>

I have menu positioning code which has an update query also, what I'm wondering is.. from what I read, mysql_affected_rows() will recognize the last mysql query that has been executed.

From my code above, the last mysql query is the Update command... I tried to remove the menu_positioning code and mysql_affected_rows() works! I wonder why it isn't working on the full code above...?

Pikachu2000 · July 14, 2012

What value does mysql_affected_rows() return when you echo it? That's an important piece of information to know since your conditional relies on that value.

bugzy · July 14, 2012

What value does mysql_affected_rows() return when you echo it? That's an important piece of information to know since your conditional relies on that value.

Pikachu2000 it's echoing out "1"

does it mean, it's not recognizing the last mysql statement update on my code above?

Pikachu2000 · July 14, 2012

I have a theory as to what might be happening. Make the following changes, and see what output it generates. BTW, You DO have error reporting set up, right?

$result = mysql_query($query,$connection);	

echo 'Error: ' . mysql_error() . '<br>Query string: ' . $query . '<br>';
echo '$result dump: ';
var_dump($result);

if(mysql_affected_rows() == 1) {
// me_redirect_to('edited_category.php?edited=1');
}

PFMaBiSmAd · July 14, 2012

I'm going to guess you have two different mysql connections and since you aren't using the $connection link resource in the mysql_affected_rows statement, it is using the result from a query on the other (last) connection, not the connection in the code using the $connection variable.

bugzy · July 14, 2012

I have a theory as to what might be happening. Make the following changes, and see what output it generates. BTW, You DO have error reporting set up, right?
$result = mysql_query($query,$connection);	

echo 'Error: ' . mysql_error() . '<br>Query string: ' . $query . '<br>';
echo '$result dump: ';
var_dump($result);

if(mysql_affected_rows() == 1) {
// me_redirect_to('edited_category.php?edited=1');
}

Pikachu2000, here's what I got. Btw I've use this page code but they have the same code and I'm getting also the same exact issue as the caregory page.

Error:
Query string: Update page set page_name='Bank', page_content='Sakre', page_position='2', page_visibility='1' where page_id = 66 LIMIT 1

$result dump: bool(true)

what do you think? :shrug:

bugzy · July 14, 2012

I'm going to guess you have two different mysql connections and since you aren't using the $connection link resource in the mysql_affected_rows statement, it is using the result from a query on the other (last) connection, not the connection in the code using the $connection variable.

@PFMaBiSmAd, I only using one connection and I'm pretty sure about it. :'(

Pikachu2000 · July 15, 2012

Well, it doesn't seem to be what I was thinking. Post the rest of the code from the script, I guess.

bugzy · July 15, 2012

Well, it doesn't seem to be what I was thinking. Post the rest of the code from the script, I guess.

<?php require_once("../includes/connection.php"); ?>
<?php require_once("../includes/functions.php"); ?>

<?php require("../includes/header.php"); ?>
<?php require("../includes/navigation.php"); ?>
<?php require("admin_sidebar.php"); ?>



<?php


if(!isset($_POST['submit']))
{
if(!isset($_GET['id']))
{
	me_redirect_to('category_list.php');
}
}


if(!isset($_POST['submit']))
{
if(!is_numeric($_GET['id']))
{
	me_redirect_to('category_list.php');
}
}



$count_query = "Select * from category where cat_visibility = 1";

$count_result = mysql_query($count_query,$connection);

$cat_count = mysql_num_rows($count_result);



if(isset($_GET['id']))
{
$i = 0;

$edit_cat_id = $_GET['id'];

$edit_query = "Select cat_name, cat_position, cat_visibility from category where cat_id = {$edit_cat_id}";

$edit_result = mysql_query($edit_query,$connection);

$num = mysql_num_rows($edit_result);


if($num != 1)
{
	me_redirect_to('category_list.php');
}



$edit_cat_name = mysql_result($edit_result,$i,'cat_name');
$edit_cat_position = mysql_result($edit_result,$i,'cat_position');
$edit_cat_visibility = mysql_result($edit_result,$i,'cat_visibility');




}


?>





<div id="content">

<h1>Edit Category</h1><br />

<?php

if(isset($_POST['submit']))
{


		//validtion if page_visibility is not set or not


		if(isset($_POST['cat_visibility']))
		{	
			$cat_visibility = me_mysql_prep($_POST['cat_visibility']);
			$cat_v = "has_content";
		}
		else
		{
			$cat_visibility = 0;
			$cat_v = "";
		}




		//if visibility = 0, page_position must be automaically be zero as well


		if($cat_visibility == 0)
		{
			$cat_position = 0;
		}

		else
		{
			$cat_position = me_mysql_prep($_POST['cat_position']);
		}



		//to check if the user has chose a page position

		if($_POST['cat_position'] == "none")
		{
			$cat_p = "";
		}
		else
		{
			$cat_p = "has content";
		}

$cat_name = me_mysql_prep(trim($_POST['cat_name']));



	//Validations

		if($edit_cat_name != $cat_name)
			{

				$q_name = "Select * from category where cat_name = '{$cat_name}'";

				$q_result = mysql_query($q_name,$connection);

				$q_num = mysql_num_rows($q_result);

				if($q_num == 1)
				{
					$num_dup = "";
				}
			else
				{
				$num_dup = "Do Not Exist";
				}
		}
		else
		{
			$num_dup = "Do Not Exist";
		}




		$require_fields = array($num_dup,$cat_name,$cat_p);

		$error_messages = array('Category with name "' .$cat_name. '" already existed','Category name cannot be empty','Pls. select category position');


		$errors = "";


		foreach($require_fields as $key => $value)		
		{
			if(empty($value))
			{
				$errors .= "*". $error_messages[$key]. "<br>";
			}
		}
		echo "<span class=\"error_validation\">". $errors . "</span>";



if(empty($errors))
{


	//Menu Positiong Code


	if($cat_position == 0 AND $cat_visibility == 0 AND $edit_cat_visibility == 1)
	{


			$update_query1 = "Update category set cat_position=NULL where cat_id = {$edit_cat_id}";

			$update_result1 = mysql_query($update_query1,$connection);


			$update_query2 = "Update category set cat_position=cat_position-1 where cat_position > {$edit_cat_position} AND cat_position > {$cat_position}";

			$update_result2 = mysql_query($update_query2,$connection);


	}
	else if($edit_cat_visibility < $cat_visibility)
	{
			$update_query1 = "Update category set cat_position=NULL where cat_id = {$edit_cat_id}";

			$update_result1 = mysql_query($update_query1,$connection);


			$update_query2 = "Update category set cat_position=cat_position+1 where cat_position >= {$cat_position}";

			$update_result2 = mysql_query($update_query2,$connection);

	}

	else if($cat_position < $edit_cat_position)
	{

			$update_query1 = "Update category set cat_position=NULL where cat_id = {$edit_cat_id}";

			$update_result1 = mysql_query($update_query1,$connection);


			$update_query2 = "Update category set cat_position=cat_position+1 where cat_position >= {$cat_position} AND cat_position < {$edit_cat_position}";

			$update_result2 = mysql_query($update_query2,$connection);

	}
	else
	{

			$update_query1 = "Update category set cat_position=NULL where cat_id = {$edit_cat_id}";

			$update_result1 = mysql_query($update_query1,$connection);


			$update_query2 = "Update category set cat_position=cat_position-1 where cat_position > {$edit_cat_position} AND cat_position <= {$cat_position}";

			$update_result2 = mysql_query($update_query2,$connection);

	}





		//Update Code



	$query = "Update category set cat_name='{$cat_name}', cat_position='{$cat_position}', cat_visibility='{$cat_visibility}' where cat_id = {$edit_cat_id} LIMIT 1";

	$result = mysql_query($query,$connection);

	if(mysql_affected_rows() == 1)
	{
		me_redirect_to('edited_category.php?edited=1');	
	}
	else
	{
		echo "<span class=\"error_validation\">The data that have been entered are exactly the same as the previous information.<br>Type the information you want to change below.</span>" . mysql_error() . "";
	}


}

	echo "<br><br>";




}









?>





<table>


<form action=<?php echo "edit_category.php?id=" .$_GET['id']. ""; ?> method="post" name="edit_cat">

<tr>
<td>Category Name</td>
    <td><input type="text" size="50" name="cat_name" value="<?php if(isset($_POST['submit'])) { echo $cat_name; } else { echo $edit_cat_name; } ?>"</td>

</tr>


<tr>

<td> </td>
<td> </td>

</tr>

<tr>
<td>Position</td>
    <td>
    <select name="cat_position">
    
    
     <?php

	if($edit_cat_visibility == 1)
	{
		for($count = 1; $count <= $cat_count; $count++)
			{

				echo "<option value=\"{$count}\"";

				if($edit_cat_position == $count)
					{
						echo " selected";
					}

				echo ">{$count}</option>";

			}
	}
	else
	{
				 echo "<option value=\"none\" selected>--Select Category Position--</option>";

				 for($count=1;$count <= $cat_count+1;$count++)
				{
					echo "<option value=\"{$count}\"";

					if(isset($_POST['cat_position']))
						{
							if($_POST['cat_position'] == $count)
								{
									echo " selected";
								}
						}

				echo ">{$count}</option>";
				}

	}


?>
    
    
    
    </select>
    </td>

</tr>


<tr>

<td> </td>
<td> </td>

</tr>


<tr>

<td>Visible?</td>
    <td>
    
    <input type="radio" name="cat_visibility" value="0"
    
    <?php

if($edit_cat_visibility == 0)
{
	echo " checked";
}

?>
     /> No
     
     
     <input type="radio" name="cat_visibility" value="1"
     
     <?php

 if($edit_cat_visibility == 1)
 {
	 echo " checked";
 }


 ?>

      /> Yes
     
     
    
    </td>

</tr>


<tr>

<td> </td>
<td> </td>

</tr>


<tr>
<td></td>

<td><input type="submit" name="submit" value="Edit Category" /></td>

</tr>




</table>




</div>


<?php require("../includes/footer.php"); ?>

Sorry if the code is messed up, I'm still on the process of learning the proper way to organize code

Pikachu2000 · July 15, 2012

Nothing in particular jumps out at me as the cause of the problem. Edit the code to echo the query, run the script as you normally would, and make sure it updates the record. Leave the record intact and copy the echoed query into the phpMyAdmin SQL tab and run it. See how many rows phpMyAdmin says are affected.

The Little Guy · July 15, 2012

pass $connection as the first parameter: mysql_affected_rows($connection)

DavidAM · July 15, 2012

If I understand your code, you are depending on mySql to NOT update the row if all of the provided values are the same values that are already in the table. But mySql is saying it updated the row. Is that correct?

1) Is there an UPDATE TRIGGER on this table that sets some other field (like LastModifieDate) when a row is updated?

2) Is there a TIMESTAMP column that is automatically set to the current time when a row is updated?

3) Did you TRIM your input strings before the original INSERT? and are you TRIMing your input strings before this UPDATE?

On second thought ...

Your Menu Positioning Code is setting the cat_position to NULL for the edit_cat_id. So the cat_position column is going to be updated in the UPDATE query you are talking about in your original post.

bugzy · July 15, 2012

pass $connection as the first parameter: mysql_affected_rows($connection)

Tried this and it didn't work.. :-[

bugzy · July 15, 2012

If I understand your code, you are depending on mySql to NOT update the row if all of the provided values are the same values that are already in the table. But mySql is saying it updated the row. Is that correct?

1) Is there an UPDATE TRIGGER on this table that sets some other field (like LastModifieDate) when a row is updated?

2) Is there a TIMESTAMP column that is automatically set to the current time when a row is updated?

3) Did you TRIM your input strings before the original INSERT? and are you TRIMing your input strings before this UPDATE?

On second thought ...

Your Menu Positioning Code is setting the cat_position to NULL for the edit_cat_id. So the cat_position column is going to be updated in the UPDATE query you are talking about in your original post.

Hello!

You're correct on your 1st statement..

As for the answers on your questions..

1) None. I doesn't have column date yet on all of my table.

2) None. Doesn't have timestamp.

3) Yes. There's a trimming... but the problem is not there I think.. the "mysql_affected_rows()" is working if I'm removing the page positioning code

here

<?php

//Menu Positiong Code


	if($cat_position == 0 AND $cat_visibility == 0 AND $edit_cat_visibility == 1)
	{


			$update_query1 = "Update category set cat_position=NULL where cat_id = {$edit_cat_id}";

			$update_result1 = mysql_query($update_query1,$connection);


			$update_query2 = "Update category set cat_position=cat_position-1 where cat_position > {$edit_cat_position} AND cat_position > {$cat_position}";

			$update_result2 = mysql_query($update_query2,$connection);


	}
	else if($edit_cat_visibility < $cat_visibility)
	{
			$update_query1 = "Update category set cat_position=NULL where cat_id = {$edit_cat_id}";

			$update_result1 = mysql_query($update_query1,$connection);


			$update_query2 = "Update category set cat_position=cat_position+1 where cat_position >= {$cat_position}";

			$update_result2 = mysql_query($update_query2,$connection);

	}

	else if($cat_position < $edit_cat_position)
	{

			$update_query1 = "Update category set cat_position=NULL where cat_id = {$edit_cat_id}";

			$update_result1 = mysql_query($update_query1,$connection);


			$update_query2 = "Update category set cat_position=cat_position+1 where cat_position >= {$cat_position} AND cat_position < {$edit_cat_position}";

			$update_result2 = mysql_query($update_query2,$connection);

	}
	else
	{

			$update_query1 = "Update category set cat_position=NULL where cat_id = {$edit_cat_id}";

			$update_result1 = mysql_query($update_query1,$connection);


			$update_query2 = "Update category set cat_position=cat_position-1 where cat_position > {$edit_cat_position} AND cat_position <= {$cat_position}";

			$update_result2 = mysql_query($update_query2,$connection);

	}
?>

The problem seemed to be on the code above.. If I'm removing it, the mysql_affected_rows is working.

PFMaBiSmAd · July 15, 2012

The Menu Positioning Code block is ALWAYS updating cat_position=NULL. If none of the specific conditional statements in that block are true, you have an else {} statement in that block that is updating cat_position=NULL. So, when you run that final update query that sets cat_position='{$cat_position}', the row IS being UPDATED, unless $cat_position contains the literal string NULL and even then, because you have single-quotes around it in the query statement, mysql won't treat it as the mysql NULL keyword, but as a string data value consisting of the letters - N, U, L, and L.

Also, in the Menu Positioning Code block, the second UPDATE query inside each conditional block doesn't have cat_id = {$edit_cat_id} in the WHERE clause, so they are updating the entire table, regardless of the cat_id value. I'm not sure if that is or is not what you are trying to do in that code.

I recommend you clean up all the excess white-space in your code so that related code can be seen all at once in your editor. If values are numbers, don't put single-quotes around them inside query statements.

bugzy · July 15, 2012

The Menu Positioning Code block is ALWAYS updating cat_position=NULL. If none of the specific conditional statements in that block are true, you have an else {} statement in that block that is updating cat_position=NULL. So, when you run that final update query that sets cat_position='{$cat_position}', the row IS being UPDATED, unless $cat_position contains the literal string NULL and even then, because you have single-quotes around it in the query statement, mysql won't treat it as the mysql NULL keyword, but as a string data value consisting of the letters - N, U, L, and L.

Also, in the Menu Positioning Code block, the second UPDATE query inside each conditional block doesn't have cat_id = {$edit_cat_id} in the WHERE clause, so they are updating the entire table, regardless of the cat_id value. I'm not sure if that is or is not what you are trying to do in that code.

I recommend you clean up all the excess white-space in your code so that related code can be seen all at once in your editor. If values are numbers, don't put single-quotes around them inside query statements.

PFMaBiSmAd thanks for that..

So it means that.. mysql_affected_rows are going to treat all the sql statement in a if-else block and not just the last sql statement literally?

fenway · July 15, 2012

So it means that.. mysql_affected_rows are going to treat all the sql statement in a if-else block and not just the last sql statement literally?

Since you're passing it a recordset, it necessarily pertains to that recordset. If you re-use a variable, then it's the last one -- so flowpaths can mess with you. Obviously, a cleaner code layout would help you see that. But different variables names will prevent scoping errors, too.

bugzy · July 15, 2012

So it means that.. mysql_affected_rows are going to treat all the sql statement in a if-else block and not just the last sql statement literally?

Since you're passing it a recordset, it necessarily pertains to that recordset. If you re-use a variable, then it's the last one -- so flowpaths can mess with you. Obviously, a cleaner code layout would help you see that. But different variables names will prevent scoping errors, too.

Thanks fenway

Sign In

mysql_affected_rows is not working..

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information