Solved: Updating multiple rows in PHP & MySQL

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

Updating multiple rows in PHP & MySQL

Premium Content

Premium Content

Why Experts Exchange?

Ask ANY Question

Did You Know?