Modifying insert to update

[Tezread]

I have an insert query that I am trying to migrate to an update query- but i get an error message "Duplicate entry '0' for key 1".

Here is my code for the insert at the moment:

<?php
include("connect.php");
session_start();
require_once('common_functions.php'); 
$_SESSION['form1_data'] = $_POST;

// build main query from form 1
	$sql = "INSERT INTO quality_info SET quality_id =  NULL ";
	foreach ($_SESSION['form1_data'] as $col =>$val ){
		if($col <> 'submit'){
		$sql .=", $col = '$val' ";
		}
	}
//////////////////////////////////
mysql_query($sql, $connection) or die(mysql_error()); 

//get last insert id
$sql ="select LAST_INSERT_ID() as id from quality_info";
$result = mysql_query($sql, $connection) or die(mysql_error()); 
$row=mysql_fetch_array($result);
$id = $row['id'];

?>

mu update query is the same and I know I cannot have duplicate primary key integers which is why the error message occurs but i dont know how to get round it. thx in advance

[devinemke]

if quality_id is an auto_incrementing primary key integer then you should not specifiy it at all in your INSERT queries. in your UPDATE queries you should only refer to it in the WHERE clause (not in the SET clause).

[Tezread]

devinemke wrote:

if quality_id is an auto_incrementing primary key integer then you should not specifiy it at all in your INSERT queries. in your UPDATE queries you should only refer to it in the WHERE clause (not in the SET clause).

could you give an example of this- i think you mean something on the lines of declaring an id as a variable then

UPDATE quality_info where quality_id = $quality_id

[devinemke]

an insert query:

INSERT INTO quality_info SET field1 = 'value1', field2 = 'value2'

an update query:

UPDATE quality_info SET field1 = 'value1', field2 = 'value2' WHERE quality_id = '123'

[Tezread]

my apologies- im still not certain how this would work in the context of updating an entire table without specifying fields as in my insert example.

<?php 
include("connect.php"); 
session_start(); 
require_once('common_functions.php'); 
$_SESSION['form1_data'] = $_POST; 

// update everything in one hit
    $sql = "update quality_info SET $_SESSION['form1_data'] = $_POST; 
    foreach ($_SESSION['form1_data'] as $col =>$val ){ 
        if($col <> 'submit'){ 
        $sql .=", $col = '$val' "; 
        } 
    } 
////////////////////////////////// 
-//is this code relevant any longer?
mysql_query($sql, $connection) or die(mysql_error()); 

//get last insert id 
$sql ="select LAST_INSERT_ID() as id from quality_info"; 
$result = mysql_query($sql, $connection) or die(mysql_error()); 
$row=mysql_fetch_array($result); 
$id = $row['id']; 

?> 

[devinemke]

re-examine my two query examples above. the first one is an INSERT query where we are creating a new record. the second is an UPDATE query where we are modifying and existing record. the WHERE clause is only relevant to the UPDATE query as we have to tell mySQL which record(s) we want to update. if you leave out the WHERE clause then mySQL will update all of the records (which is probably not what you want). in either case you need to specify what values you wish to set for which fields. in the case of an INSERT query, any fields that are left out will inheirit the default value (specified during table creation) or if the field is set to auto_increment the value will be the next sequential number. my original point in my first post is that you should simply leave out any refernce to the primary key field when setting the fields/values and let mySQL do the auto_incrementing for you.

[Tezread]

devinemke wrote:

an insert query:

INSERT INTO quality_info SET field1 = 'value1', field2 = 'value2'

an update query:

UPDATE quality_info SET field1 = 'value1', field2 = 'value2' WHERE quality_id = '123'

I understand this but with the update form im trying to design it is not specified which fields need to be updated. therefore

update quality_info set field 1 = 'value1, where quality_id ='123' etc......... is not an appropriate approach.

In this case the where clause is a variable 'quality_name' and is chosen from a list box in the form. The user chooses a quality name then clicks find and the prepopulated form is displayed. The fields to be updated are also variable in some respects as the user may update only 1 field or maybe 7.. Im trying to make this as dynamic as possible. I know how to do the update like you specify in the example but if if do it this way im having to create lots of inefficient lines of code. Ive done searches on 'foreach' + 'update' and had a crack at some stuff for myself but im stuck on the process of using 'SET' to set all fields. A where clause is unneccessary I think because the form is prepoplated and if the user doesn't change a field then it doesn't matter as the prepopulated data will remain the same in the update.

here is my one of my efforts so far (which remains to be tested)

	foreach ($_SESSION['form1_data'] as $col =>$val ){
		if($col <> 'submit'){
		 $sql = "UPDATE quality_info SET ($_SESSION['form1_data']WHERE $col = '" . $val . "'"; 
		}
	}

[devinemke]

let's back up here. post an example of what your form looks like.

[bpat1434]

Don't forget about the mySQL feature ON DUPLICATE in the UPDATE query syntax.... 😉

[Tezread]

devinemke wrote:

let's back up here. post an example of what your form looks like.

here is part of my form:

<?php
if (isset ($_POST['submit']) && $_POST['submit'] == 'find') 
{ 
    $result = mysql_query ("SELECT * FROM quality_info WHERE quality_name = '" . $_POST['quality_name'] . "'"); 
    if (mysql_num_rows ($result)) {$row = mysql_fetch_assoc ($result);} 
    else {echo 'no records found matching ' . $_POST['quality_name'];} 

echo ' 
<form action="' . $_SERVER['PHP_SELF'] . '" method="POST"> 


      <td width="6%"></td>
<table width="100%"  border="0">
  <tr>

   <td width="3%">&nbsp;</td>
    <td width="1%">&nbsp;</td>
    <td width="1%">&nbsp;</td>
    <td width="1%">&nbsp;</td>
    <td width="1%">&nbsp;</td>
    <td width="1%">&nbsp;</td>
    <td width="1%">&nbsp;</td>
  </tr>
  <tr>
    <td><strong>Quality name </strong></td>
    <td width="39%"><input type="text" name="quality_name" value="' . $row['quality_name'] . '"></td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><strong>Authoritative/associative/governing body </strong></td>
     <td width="39%"><input name="authority_body" type="text" size="50" value="' . $row['authority_body'] . '"></td>
     <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>

[devinemke]

your form (as posted) only has 2 fields: quality_name and authority_body. your earlier posts in this thread suggest that the primary key field from this table is quality_id. your form (again, the part of it you posted) does not pass the quality_id to the form processing code. consequently if you run and UPDATE query without specifying which record you want to update mySQL will happily update all of the records (which does not appear to what you are trying to do).

look at this simplified exampled example:

<?php
// connect to db here

if (!isset($_POST['submit']))
{
	echo '
	<form action="" method="POST">
	find quality_id: <input type="text" name="quality_id"><br>
	<input type="submit" name="submit" value="find">
	</form>
	';
}

if (isset($_POST['submit']) && $_POST['submit'] == 'find')
{
	$result = mysql_query('SELECT * FROM quality_info WHERE quality_id = ' . $_POST['quality_id']) or exit(mysql_error());
	if (mysql_num_rows($result))
	{
		$row = mysql_fetch_assoc($result);

	echo '
	update this row<br>
	<form action="" method="POST">
	';

	foreach ($row as $key => $value)
	{
		echo $key . ': <input type="text" name="data[' .  $key .']" value="' . $value . '"><br>';
	}

	echo '
	<input type="submit" name="submit" value="update">
	</form>
	';
}
else
{
	echo 'no rows found matching that ID';
}
}

if (isset($_POST['submit']) && $_POST['submit'] == 'update')
{
	foreach ($_POST['data'] as $key => $value)
	{
		$sql_array = $key . " = '" . $value . "'";
	}
	$sql = 'UPDATE quality_info SET ' . implode(',', $sql_array) . ' WHERE quality_id = ' . $_POST['data']['quality_id'];
	$result = mysql_query($sql) or exit(mysql_error());
	echo 'record updated';
}
?>

[Tezread]

this looks fantastic. Is this a self submission form where no seperate formhandler is required? i.e:

echo '<form action="' . $_SERVER['PHP_SELF'] . '" method="POST">'

also- where is your example would I start to echo all my fields in my form- after an id has been selected and find clicked?

thx

[devinemke]

Tezread wrote:

where is your example would I start to echo all my fields in my form

my example already does that via the foreach loop.

just to be clear: this example assumes that quality_id is a unique field so that only a single row will be returned. if you want to update several rows in one form (perhaps a broader search criteria) then you would have to structure the loop logic dfferently to display multiple rows.

[bpat1434]

you mean this:

if (isset($_POST['submit']) && $_POST['submit'] == 'find')
{
    $result = mysql_query('SELECT * FROM quality_info WHERE quality_id = ' . $_POST['quality_id']) or exit(mysql_error());
    if (mysql_num_rows($result))
    {
        $row = mysql_fetch_assoc($result);

    echo '
    update this row<br>
    <form action="" method="POST">
    ';

    foreach ($row as $key => $value)
    {
        echo $key . ': <input type="text" name="data[' .  $key .']" value="' . $value . '"><br>';
    }

    echo '
    <input type="submit" name="submit" value="update">
    </form>
    ';
}
else
{
    echo 'no rows found matching that ID';
}
}

The form submits back to itself, but it's not self submitting.

[Tezread]

foreach ($row as $key => $value)
{
echo $key . ': <input type="text" name="data[' . $key .']" value="' . $value . '"><br>';
}

I am trying to modify the loop above so the form doesn't just show input text fields, but textareas, and checkboxes - i.e the correct fields according to those in the form.

the process here if my understanding serves me correct is:

if field type is a input text then....

foreach ($row as $key => $value)
{
echo $key . ': <input type="text" name="data[' . $key .']" value="' . $value . '"><br>';
}

or if field type is checkbox then.....

foreach ($row as $key => $value)
{
echo $key . ': <input type="checkbox" name="data[' . $key .']" value="' . $value . '"><br>';
}

or if field type is a textarea then.......

foreach ($row as $key => $value)
{
echo $key . ': <input type="textarea" name="data[' . $key .']" value="' . $value . '"><br>';
}

Is it possible to create an array in a foreach loop and in this case how?

[bpat1434]

I am trying to modify the loop above so the form doesn't just show input text fields, but textareas, and checkboxes

Unless you stored that information in the database with the other input info, it can't be done.

[Tezread]

Thx guys for your help. I have gone back to the beginning and come up with an improved approach based on suggestions in this thread and my current form.
One key thing I missed in the beginning what that I was not referring to my primary key. I have come up with some new error messages that I have not come across before...

//connect & start session
session_start();
require_once('common_functions.php'); 
$_SESSION['form1_data']='';


 //list box to choose item to update
if (!isset ($_POST['submit'])) 
{ 
    echo '<form action="' . $_SERVER['PHP_SELF'] . '" method="POST">' 
        . 'quality name: <select name="quality_name">' . "\n"; 

$sql = "SELECT quality_id FROM quality_info"; 

$result = mysql_query($sql, $connection) 
    OR die(mysql_error()); 

while ($row = mysql_fetch_assoc($result)) 
{ 
    echo '<option value="' . $row['quality_id'] . '">' 
        . $row['quality_id'] . "</option>\n"; 
} 
//click find to create prepopulated form
    echo '</select><input type="submit" name="submit" value="find" /></form>'; 
} 


 </form>



<div id="Layer1" style="position:absolute; width:774px; height:115px; z-index:1; left: 28px; top: 132px; background-color: #FFFFFF; layer-background-color: #FFFFFF; border: 1px none #000000;">

//show all fields in the form and have them populated
if (isset ($_POST['submit']) && $_POST['submit'] == 'find') 
{ 
    $result = mysql_query ("SELECT * FROM quality_info WHERE quality_id = '" . $_POST['quality_id'] . "'"); 
    if (mysql_num_rows ($result)) {$row = mysql_fetch_assoc ($result);} 
    else {echo 'no records found matching ' . $_POST['quality_id'];} 

echo ' 
<form action="' . $_SERVER['PHP_SELF'] . '" method="POST"> 

      <td width="6%"></td>
<table width="100%"  border="0">
  <tr>

   <td width="3%">&nbsp;</td>
    <td width="1%">&nbsp;</td>
    <td width="1%">&nbsp;</td>
    <td width="1%">&nbsp;</td>
    <td width="1%">&nbsp;</td>
    <td width="1%">&nbsp;</td>
    <td width="1%">&nbsp;</td>
  </tr>
  <tr>
    <td><strong>Quality name </strong></td>
    <td width="39%"><input type="text" name="quality_name" value="' . $row['quality_name'] . '"></td>
    <td>&nbsp;</td>
//////////////////////////////////////////////////////////////////////////////////////////
/////////rest of form goes here -  is is too big to display!!!!//////////////////////
/////////////////////////////////////////////////////////////////////////////////////

//this is where the update is handled
if (isset($_POST['submit']) && $_POST['submit'] == 'update') 
{ 
    foreach ($_POST['form1_data'] as $col => $val) 
    { 
        $sql_array = $col . " = '" . $val . "'"; 
    } 
    $sql = 'UPDATE quality_info SET ' . implode(',', $sql_array) . ' WHERE quality_id = ' . $_POST['form1_data']['quality_id']; 
    $result = mysql_query($sql) or exit(mysql_error()); 
    echo 'Your record has been updated'; 
} 

the resulting error messages:

Warning: Invalid argument supplied for foreach() in /disk1/home3/mindseye/public_html/notts_quality/info_resource/quality_info_form_updatev10.php on line 1799

Notice: Undefined variable: sql_array in /disk1/home3/mindseye/public_html/notts_quality/info_resource/quality_info_form_updatev10.php on line 1803

Warning: implode() [function.implode]: Bad arguments. in /disk1/home3/mindseye/public_html/notts_quality/info_resource/quality_info_form_updatev10.php on line 1803
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE quality_id =' at line 1

[devinemke]

you are getting those errors because there is no array called $_POST['form1_data']. you did not post all of your update form but clearly form1_data[] is not any of the field names.

[Tezread]

OK I think Im almost there guys. thx for your patience. I have rejigged this some more and come out with the following solution but the same error occurs. I am lost on how to post the session array form1_data so is can be called in the implode sql array segment. . The update needs to say update quality_info set 'the array of fields in the $_session form1_data' where the quality_id is equal to that one selected for update in the list box. Foreach row and column in the form1_data session update the value. or words to that effect

<?php
//this is where the update is handled

$sql = 'UPDATE quality_info SET ' . implode(',', $sql_array) . ' WHERE quality_id = ' . $_POST['quality_id']['quality_id']; 
foreach ($_SESSION['form1_data'] as $col => $val){
		if($col <> 'submit'){

    $sql_array = $col . " = '" . $val . "'"; 
} 
}
$result= mysql_query($sql, $connection) or die(mysql_error()); 
    echo 'Your record has been updated'; 

?>