Jump to content

mysql_affected_rows is not working..

bugzy

By bugzy
in MySQL Help

 Share

Recommended Posts

bugzy Regular Member

bugzy

Posted
Posted

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?

Link to comment
Share on other sites
bugzy Regular Member

bugzy

Posted
  • Author
Posted

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...?

Link to comment
Share on other sites
bugzy Regular Member

bugzy

Posted
  • Author
Posted

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?

Link to comment
Share on other sites
Pikachu2000 Prolific Member

Pikachu2000

Posted
Posted

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');
}

Link to comment
Share on other sites
PFMaBiSmAd Advanced Member

PFMaBiSmAd

Posted
Posted

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.

Link to comment
Share on other sites
bugzy Regular Member

bugzy

Posted
  • Author
Posted

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:

Link to comment
Share on other sites
bugzy Regular Member

bugzy

Posted
  • Author
Posted

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.  :'(

Link to comment
Share on other sites
bugzy Regular Member

bugzy

Posted
  • Author
Posted

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  :)

Link to comment
Share on other sites
Pikachu2000 Prolific Member

Pikachu2000

Posted
Posted

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.

Link to comment
Share on other sites
DavidAM Member

DavidAM

Posted
Posted

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.

Link to comment
Share on other sites
bugzy Regular Member

bugzy

Posted
  • Author
Posted

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.

 

 

 

Link to comment
Share on other sites
PFMaBiSmAd Advanced Member

PFMaBiSmAd

Posted
Posted

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.

 

Link to comment
Share on other sites
bugzy Regular Member

bugzy

Posted
  • Author
Posted

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?

Link to comment
Share on other sites
fenway Advanced Member

fenway

Posted
Posted

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.

Link to comment
Share on other sites
bugzy Regular Member

bugzy

Posted
  • Author
Posted

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 :)

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • 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.