We help IT Professionals succeed at work.
Get Started

Updating multiple rows in PHP & MySQL

echocpt
echocpt asked
on
327 Views
Last Modified: 2013-12-12
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
Commented:
This problem has been solved!
Unlock 3 Answers and 12 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE