We help IT Professionals succeed at work.

Updating multiple rows in PHP & MySQL

echocpt
echocpt asked
on
Hi, i have an edit function that changes the name of a category, and this category relates to articles in another table. I have the actual edit category function working fine.

However, what i need to do somehow is if the category title is changed then all articles that had the name of the category are also changed to the new category title.

I have a feeling this should be simple and would work with some sort of foreach article that has this name update to this name in a query.

Table are as follows...

content_category:
id
category // THIS IS THE LINK WITH CONTENT. WHEN THIS IS CHANGED I NEED CONTENT TO.
viewable
description

content:
id
content_category //THIS IS THE LINK WITH CONTENT_CATEGORY
title
keywords
content
date_added
user_added
viewable
add_home

Any ideas? Code is attached.

Thanks Alex
<?php 
require_once'../connection.php';
require_once'functions.php';
cookie();
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>
<?php displayHomeTitle(); ?>
- Admin</title>
<link href="../stylesheet.css" rel="stylesheet" type="text/css" />
</head>
<body>
<div id="toprun"></div id="toprun">
<div id="outer-main">
  <div id="inner-bg">
    <div id="inner-left">
      <div id="pagetitle">
        <?php displayHomeName();?>
      </div>
      <div id="navbar"></div>
      <div id="text">
       <?php if ($_GET['catedited']==true) { ?> Category Edited. <br /><br /><?php } ?>

<?php

//check if the form has been submited and submit == 'submit' and check that id is not empty
if(isset($_POST['edit']) && $_POST['edit'] == 'edit' && !empty($_POST['id'])){
  //$_POST variables
	$category = $_POST["category"];
	$description = $_POST["description"];
	$viewable = $_POST["viewable"];
	$id = $_POST["id"];
	
		// I think that this is where the query to change each article to same new name would go?
		
/*	This is some code i was trying out but couldnt get working.
$contsql="SELECT * FROM content WHERE content_category='$category'";
$resultcon=mysql_query($contsql);
$count = mysql_num_rows($resultcon);
$thi=$count['content_category'];

for($i=0;$i<$count;$i++){
$del_id = $thi[$i];
$sql = "UPDATE content SET content_category=\"" . $category . "\", WHERE id='$del_id'";
$result = mysql_query($sql);
}*/

	$sql = "UPDATE content_categories SET category=\"" . $category . "\", viewable=\"" . $viewable . "\", description=\"" . $description . "\" WHERE id='$id'";
  	$result = mysql_query($sql) or die("Can't update information<br />".mysql_error()."<br />");
	echo "<meta http-equiv=\"refresh\" content=\"0;URL=edit_category.php?catedited=true\">";
}//elseif id is set and isset cmd and cmd == edit show the form
elseif(isset($_GET['id']) && isset($_GET['cmd']) && $_GET['cmd'] == "edit"){   
	$id = $_GET["id"];
	$sql = "SELECT * FROM content_categories WHERE id=$id";
	$result = mysql_query($sql) or die("Can't select id $id".mysql_error()."<br />");        
	$myrow = mysql_fetch_array($result);
	$article = $myrow['category'];
	$selectq = "SELECT * FROM `content` WHERE content_category='$article'";
	$result1 = mysql_query($selectq);
      
?>
			<?php  
				$viewableY = "<input type='radio' name='viewable' value='Y'" . ( $myrow['viewable'] == 'Y' ? ' checked="checked"':'') . "/>";
				$viewableN = "<input type='radio' name='viewable' value='N'" . ( $myrow['viewable'] == 'N' ? ' checked="checked"':'') . "/>";
			?>
			<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
			<input type=hidden name="id" value="<?php echo $myrow["id"] ?>">
			<input type=hidden name="category" value="<?php echo $myrow["category"] ?>">
			<label>Curently housing these articles:
			<select name="title">
			<?php
			while($row = mysql_fetch_assoc($result1))
			{
		    echo "<option>" . $row['title'] . "</option>";
			}
			?>
			</select>
          </label>
          <br />
          <br />
          Make Viewable?:<br />
          <?php echo $viewableY; ?>
          <label>Yes</label>
          <?php echo $viewableN; ?>
          <label>No</label>
          <br />
          <br />
          <label>Title:<br />
          <INPUT TYPE="TEXT" NAME="category" VALUE="<?php echo $myrow["category"] ?>" SIZE=30>
          <br /><br />
          </label>
          <label>Content:<br />
          <TEXTAREA NAME="description" ROWS=10 COLS=52><? echo $myrow["description"] ?></TEXTAREA>
          <br /><br />
          </label>
          <input type="hidden" name="cmd" value="edit">
          <input type="submit" name="edit" value="edit">
        </form>

      
<?php 
}//else send them to another page (error page? previus page?)
else{

}
		?>
		
		
		<?php 
//If cmd has not been initialized
if(!isset($cmd)) 
{
   //display all the news
   $result = mysql_query("SELECT * FROM content_categories ORDER BY category ASC"); 
?>
        <table width="598" border="1" bordercolor="#e5e5e5" cellpadding="5" cellspacing="0" bgcolor="#FFFFFF">
          <tr>
            <td align="center" bgcolor="#FFFFFF"><strong>Category</strong></td>
            <td align="center" bgcolor="#FFFFFF"><strong>Description</strong></td>
            <td align="center" bgcolor="#FFFFFF"><strong>Viewable</strong></td>
          </tr>
          <?php
while($r=mysql_fetch_array($result)){
$id = $r['id'];
?>
          <tr>
            <td bgcolor="#FFFFFF"><?php echo "<a href='edit_category.php?cmd=edit&id=$id'>" ?><? echo $r['category']; ?></a></td>
            <td bgcolor="#FFFFFF"><? echo $r['description']; ?></td>
            <td bgcolor="#FFFFFF"><? echo $r['viewable']; ?></td>
          </tr>
          <?php
}
}
?>
        </table>
      </div>
      <br />
    </div id="inner-left">
    <div id="inner-right"><span class="h9">Navigation</span><br />
      <br />
      <?php displayMenu(); ?>
    </div>
  </div id="inner-bg">
</div id="outermain">
</body>
</html>

Open in new window

Comment
Watch Question

View Solutions Only

szewkam
Commented:
first of all, this is an example of bad database construction. Why do you save the name of the category inside related article table?
Change the article tables to save id of the category. And then when you change the name of category inside category table, all of the articles would have new name based on the same category id.
Anthony Perkins
Top Expert 2012

Commented:
>>Zones: PHP Scripting Language, PHP and Databases, MS SQL Server<<
I suggest you request this question be moved from MS SQL Server to a more appropriate zone such as:
http://www.experts-exchange.com/Database/MySQL/
echocpt

Author

Commented:
Ok, thats probably a fair point, but im still going to need to change all the IDs in table content to the new one so the question still stands. How can i change the multiple rows in content to the name of the new category?

Alex
echocpt

Author

Commented:
@acperkins, sorry my bad, hadn't realised id chosen MS not MySQL.

Thanks Alex
szewkam

Commented:
UPDATE content SET content_category = 'new_content' WHERE content_category = 'old_content'
echocpt

Author

Commented:
Hi, i understand the query but its not working. Do i not need to do some sort of loop, so foreach row with this name update to this?

Thanks
szewkam

Commented:
nope this query should work. Paste your code
echocpt

Author

Commented:
Hi, code attached.

Thanks Alex
$category = $_POST["category"];
	$description = $_POST["description"];
	$viewable = $_POST["viewable"];
	$id = $_POST["id"];
	$rql = "SELECT * FROM content_categories WHERE id=$id";
	$result = mysql_query($rql) or die("Can't select id $id".mysql_error()."<br />");        
	$myrow = mysql_fetch_array($result);
	$change = $myrow['category'];
	$tql = "UPDATE content SET content_category = ".$category." WHERE content_category = ".$change."";

Open in new window

szewkam
Commented:
as long as content_category is a text field, you have to wrap inserting data in quotes.
Try: 

$tql = "UPDATE content SET content_category = '".$category."' WHERE content_category = '".$change."'";

Open in new window

Commented:
Hi echocpt,
It may have been confusing for you to include the data properly in quotes or it could be even difficult to manage the code if you are not using a good WYSIWYG (What You See Is What You Get) editor. You can avoid mistakes in the query by using a very simplified format. You are already using double quotes and it would parse the value of the variable. All you would need to do is just encase the variable in single quotes and that's it. Concatenation is not the only way to do so.  So check out the code that I have attached. You will definitely find it more simpler than anything else and you will find it very handy.

Also you don't have to worry about the looping as the query will itself loop to update all the matching records in the database (DB). Just make sure that you do not use LIMIT 1 (or something similar) at the end of the query. If you use LIMIT 1, it will only update just 1 record.

I hope now you see why the query will update all the records. Try it out and let me know if it worked.

Thanks.
$tql = " UPDATE content SET content_category = $category WHERE content_category = '$change' ";

Open in new window

echocpt

Author

Commented:
Hi, thanks to both of you, i did get it working in the end but then changed the table to what szewkam suggest in his first post and agreed, it is much easier.

Thanks, ill award half each.

Thanks Alex
mepro

Commented:
Thank you Alex. Appreciate it.