SQL and update

Gainax · Jul 10, 2008

At the moment, I'm inserting a new row into my database using the INSERT command. But I'd rather use an UPDATE, to change the details of a row, rather than inserting a whole different row.

The rows need to be updated via it's 'catid'.

The current code in my save.php file is:

mysql_select_db("priority", $con);
$catid = ($_POST['catid']);
$briefed = ($_POST['briefed']);
$job = ($_POST['job']);
$category = ($_POST['category']);
$needed = ($_POST['needed']);
$status = ($_POST['status']);
$orderid = ($_POST['orderid']);

$sql = "INSERT INTO categories (catid, briefed, job, category, needed, status, orderid) VALUES ('', '$briefed', '$job', '$category', '$needed', '$status', '' )";
mysql_query($query);

What would be the SQL to turn that into updating an exisiting row based on it's catid, rather than inserting a totally different row?

Thanks

Loai_Najati · Jul 10, 2008

Do you know the SELECT statement ?
You can do what you want using it.
First, Try
If you failed, Tell me

Thanks

Gainax · Jul 10, 2008

I know the select statement.

All I'm looking to do, is update and exisiting row's details via a form input.

The row's details should be based on the row's catid.

Can you help me with the code to do this?

I have a form, which is taking the form's input and iserting into the databse, but not updating a row.

			echo '<form action="save.php" name="insert_priority" METHOD="post">';
			echo '<table>';
			echo '<td><input class="briefed" type="text" name="briefed"'; if(isset($_POST['briefed'])){echo "value=\"" . $_POST['briefed'] . "\"";}echo ' /></td>';
			echo '<td><input class="job" type="text" name="job"'; if(isset($_POST['job'])){echo "value=\"" . $_POST['job'] . "\"";}echo ' /></td>';
			echo '<td><input class="category" type="text" name="category"'; if(isset($_POST['category'])){echo "value=\"" . $_POST['category'] . "\"";}echo ' /></td>';
			echo '<td><input class="needed" type="text" name="needed"'; if(isset($_POST['needed'])){echo "value=\"" . $_POST['needed'] . "\"";}echo ' /></td>';
			echo '<td><input class="status" type="text" name="status"'; if(isset($_POST['status'])){echo "value=\"" . $_POST['status'] . "\"";}echo ' /></td>';
			echo '<td class="gap"></td>';
			echo '<td class="gap"></td>';
			echo '<td class="save"><input type="image" style="margin-top: 4px;" src="images/sm-tick-box.jpg" height="20" class="save" alt="Submit ">
</td>';
			echo '</table>';
			echo '</form>';

Thank you

Loai_Najati · Jul 10, 2008

Updating existing rows in a table requires to run the UPDATE statement with a WHERE clause to identify the row. The following sample script updates one row with two new values:

<?php
include "mysql_connection.php";

$sql = "UPDATE fyi_links SET notes='Nice site.', counts=8"
. " WHERE id = 102";
if (mysql_query($sql, $con)) {
print(mysql_affected_rows() . " rows updated.\n");
} else {
print("SQL statement failed.\n");
}

mysql_close($con);
?>

If you run this script, you will get something like this:

1 rows updated.

Gainax · Jul 10, 2008

How would I implement that into:

Index.php

<?

require('db.php');

$demo = new SortableExample();
$list = $demo->getList();


?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
	<title>ABA Online Priority List - <?php echo date("l F j Y");?></title>
	<link rel="stylesheet" type="text/css" href="style.css">
<!--[if IE 7]>
<link rel="stylesheet" type="text/css" href="style_ie7.css">
<![endif]-->
<!--[if IE 6]>
<link rel="stylesheet" type="text/css" href="style_ie6.css">
<![endif]-->

<script src="js/prototype.js"></script>
<script src="js/scriptaculous.js"></script>
<script src="js/edit.js" type="text/javascript"></script>

<script>
	Event.observe(window,'load',init,false);
	function init() {
		Sortable.create('listContainer',{tag:'div',onUpdate:updateList});
	}
	function updateList(container) {
		var url = 'ajax.php';
		var params = Sortable.serialize(container.id);
		var ajax = new Ajax.Request(url,{
			method: 'post',
			parameters: params,
			onLoading: function(){$('workingMsg').show()},
			onLoaded: function(){$('workingMsg').hide()}
		});
	}

</script>

</head>
<body>


<h2>ABA Online Priority List - <?php echo date("l F j Y");?></h2>
<table>
	<td class="briefed">Briefed By:</td>
	<td class="job">Job No.</td>
	<td class="category">DIGITAL</td>
	<td class="needed">Needed By:</td>
	<td class="status">Status</td>
	<td class="priority">Priority</td>
	<td class="edit">Edit</td>
	<td class="delete">Delete</td>

</table>

<div id="listContainer">

<?
foreach($list as $item)
{

	if(isset($_GET['state']) && $_GET['state'] == 'edit' && $_GET['catid'] == $item['catid'])
	{
		echo '<form action="save.php" name="insert_priority" METHOD="post">';
		echo '<table>';
		echo '<td><input class="briefed" type="text" name="briefed"'; if(isset($_POST['briefed'])){echo "value=\"" . $_POST['briefed'] . "\"";}echo ' /></td>';
		echo '<td><input class="job" type="text" name="job"'; if(isset($_POST['job'])){echo "value=\"" . $_POST['job'] . "\"";}echo ' /></td>';
		echo '<td><input class="category" type="text" name="category"'; if(isset($_POST['category'])){echo "value=\"" . $_POST['category'] . "\"";}echo ' /></td>';
		echo '<td><input class="needed" type="text" name="needed"'; if(isset($_POST['needed'])){echo "value=\"" . $_POST['needed'] . "\"";}echo ' /></td>';
		echo '<td><input class="status" type="text" name="status"'; if(isset($_POST['status'])){echo "value=\"" . $_POST['status'] . "\"";}echo ' /></td>';
		echo '<td class="gap"></td>';
		echo '<td class="gap"></td>';
		echo '<td class="save"><input type="image" style="margin-top: 4px;" src="images/sm-tick-box.jpg" height="20" class="save" alt="Submit ">
</td>';
			echo '</table>';
			echo '</form>';

	}
	else
	{
		//display normal row
		?>
		<div id="item_<?=$item['catid'];?>">
		<p class="briefed"><?=$item['briefed'];?></p>
		<p class="job_number"><?=$item['job'];?></p>
		<p class="category"><?=$item['category'];?></p>
		<p class="needed"><?=$item['needed'];?></p>
		<p class="status"><?=$item['status'];?></p>
		<p class="order_id"><?=$item['orderid'];?></p>
		<p class="link"><a href="index.php?state=edit&amp;catid=<?=$item['catid']?>"><img src="images/edit_image.jpg" border="0" height="20px;"  alt="Edit Row" title="Edit Row"  /></a></p>
		<p class="delete"><a href="delete_row.php?catid=<?=$item['catid']?>"><img src="images/delete_image.jpg" height="20px" border="0" alt="Delete Row" title="Delete Row" /></a></p>

		</div>
		<?
	}
}

?>
<p class="add_button"><a href="addRow.php"><img src="images/add_button.jpg" height="30" border="0" alt="Add Row" title="Add Row" /></a></p>

</div>

</body>
</html>

and...

save.php

<?php

header('Location: index.php');
echo '<html>';
echo '<head>';
//echo '<meta http-equiv="REFRESH" content="1;url=http://localhost/aba_priority_list/index.php">';

$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }


mysql_select_db("priority", $con);
$catid = ($_POST['catid']);
$briefed = ($_POST['briefed']);
$job = ($_POST['job']);
$category = ($_POST['category']);
$needed = ($_POST['needed']);
$status = ($_POST['status']);
$orderid = ($_POST['orderid']);



$sql = "INSERT INTO categories (catid, briefed, job, category, needed, status, orderid) VALUES ('', '$briefed', '$job', '$category', '$needed', '$status', '' )";
mysql_query($query);
echo $sql;


if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "<br />";
echo "Updated row";

mysql_close($con);



?>

I can add a row, delete and I want to be able to edit a row.

Thanks

SQL and update

GGainax

LLoai_Najati

GGainax

LLoai_Najati

GGainax