[RESOLVED] Add data to an existing record

matthewst

Here is my insert query:

if(isset($_POST['add']))
{
$ad_copy_rec = $_POST['ad_copy_rec'];
$query_insert_ad_copy_rec_dates = "INSERT INTO whiteboard_dates (rest_id, ad_copy_rec) VALUES ('$table_id', '$ad_copy_rec')";
}

Here is my form:

<td align="center" valign="top" width="60"><font size="-2"><?
			echo "$ad_copy_rec";
			?></p>
			<form method="post" enctype="multipart/form-data" name="FormName">
			<input type="text" class="formTextbox" name="ad_copy_rec" size="24" value='<?php echo"$ad_copy_rec"; ?>'/>
			<input type="submit" class="formTextbox" name="submit" value="Submit Ad Copy Rec Date">
			</form></font></td>

The problem I'm having is:
when the page loads it displays the value of $ad_copy_rec like it should
but when users put data in the ad_copy_rec box and hit submit the data in $ad_copy_rec is replaced with the new data

What I need:
i need the new data to just be appended to the old data
example:
05/23/2007

the user puts 05/29/2007 in the ad_copy_rec box and hits submit
it should display
05/23/2007, 05/29/2007

matthewst

ok now the #$*% thing won't submit at all

here is a pic of the entire table

devinemke

you should really rethink your data structure here. rather than store a comma delimited list of values in a single field i suggest you store each value in a separate table with a related key to your main table. this will make it easier to search/sort/index/maintain your data.

matthewst

....I...don't...know how to do that

matthewst

ok, fixed that now i'm back to the original problem

matthewst

here's the entire page if that helps

<?
include('include/user_check.php');
include('include/db_con.php');

//won't submit without this, however WITHOUT this when I put in a date and hit submit the page (not the database) updates to whatever i input
//but with this it only displays what i put when it loads, if i then input something different into the box it still only displays what i originally input
	if($submit) {
		mysql_query("INSERT INTO whiteboard_dates (rest_id, ad_copy_rec) VALUES ('$table_id', '$ad_copy_rec')", $db_link);
	}
//end won't submit problem	
?>


<html>
<head>
<title>ABC Advertising Agency, Inc :: Whiteboard</title>
<script language="javascript">
   function openNewWindow(url) {
    window.open(url,'newPage','scrollbars=no,resizable=no,width=400,height=250');
   }
  </script>
</head>
		<body bgcolor="white">
		<script type='text/JavaScript' src='scw.js'></script>
		<font face="Verdana, Arial, Helvetica, sans-serif">
		<div align="center">
			<table width="600" border="0" cellspacing="2" cellpadding="1" bgcolor="white">
				<tr>
					<td colspan="10" width="1343">

				<div id="popup"></div>
							<center>Whiteboard for<br>
							<?php
							$query="SELECT * FROM abc_tables WHERE table_id=$table_id";
							$result=mysql_query($query);
							while ($row = mysql_fetch_assoc($result))
							{
							$rest_name = $row['rest_name'];
							}

							echo"$rest_name";
							?><br>
						<img src="thin_horiz_line.png" width="250" height="1"></center>
				</td>
			</tr>
			<tr>
				<td align="center" valign="bottom" width="60"><font size="-2">Advertiser</font></td>
				<td align="center" valign="bottom" width="20"><font size="-2">Ad Size</font></td>
				<td align="center" valign="bottom" width="75"><font size="-2">Contract Rec</font></td>
				<td align="center" valign="bottom" width="35"><font size="-2">Ad Copy Rec</font></td>
				<td align="center" valign="bottom" width="30"><font size="-2">Pict</font></td>
				<td align="center" valign="bottom" width="30"><font size="-2">Copy Sent Out</font></td>
				<td align="center" valign="bottom" width="60"><font size="-2">Approved</font></td>
			</tr>
			<tr>
				<td colspan="10" width="1343">
					<div align="center">
						<img src="thin_horiz_line.png"></div>
				</td>
			</tr>


<?php
$query_table="SELECT * FROM abc_tables WHERE table_id=$table_id";
$result_table=mysql_query($query_table);
while ($row_table = mysql_fetch_assoc($result_table))
{
$table_id = $row_table['table_id'];
}
$query_ads="SELECT * FROM ad_order WHERE cust_id=$table_id";
$result_ads=mysql_query($query_ads);
while ($row_ads = mysql_fetch_assoc($result_ads))
{
$id = $row_ads['id'];
$company = $row_ads['company'];
$ad_size = $row_ads['size'];
$contract_rec = $row_ads['date_ordered'];

$query_ads_dates="SELECT * FROM job_log WHERE ad_id=$id";
$result_ads_dates=mysql_query($query_ads_dates);
while ($row_ads_dates = mysql_fetch_assoc($result_ads_dates))
{
$ad_id = $row_ads_dates['ad_id'];
$time = $row_ads_dates['time'];
}

if(isset($_POST['add']))
{
$ad_copy_rec = $_POST['ad_copy_rec'];
$query_insert_ad_copy_rec_dates = "INSERT INTO whiteboard_dates (rest_id, ad_copy_rec) VALUES ('$table_id', '$ad_copy_rec')";
}
$query_ads_rec_dates="SELECT ad_copy_rec FROM whiteboard_dates WHERE rest_id=$table_id";
$result_ads_rec_dates=mysql_query($query_ads_rec_dates);
while ($row_ads_rec_dates = mysql_fetch_assoc($result_ads_rec_dates))
{
$ad_copy_rec = $row_ads_rec_dates['ad_copy_rec'];
}
?>
			<tr>
			<td colspan="10" width="1343"><div align="center"><IMG SRC="thin_horiz_line.png"></div></TD>
			</TR>
			<tr>
			<td align="center" valign="top" width="60"><font size="-2"><?	
			echo "$company";
			?></font></td>

		<td align="center" valign="top" width="60"><font size="-2"><?
		echo "$ad_size";
		?></font></td>

		<td align="center" valign="top" width="60"><font size="-2"><?
		echo "$contract_rec";
		?></font></td>

		<td align="center" valign="top" width="60"><font size="-2"><?
		echo "$ad_copy_rec";
		?></p>
		<form method="post" enctype="multipart/form-data" name="FormName">
		<input type="text" class="formTextbox" name="ad_copy_rec" size="24" value='<?php echo"$ad_copy_rec"; ?>'/>
		<input type="submit" class="formTextbox" name="submit" value="Submit Ad Copy Rec Date">
		</form></font></td>

		<td align="center" valign="top" width="60"><font size="-2"><?
		echo "";
		?></font></td>

		<td align="center" width="60"><font size="-2"><?php
		$query_ads_dates="SELECT * FROM job_log WHERE ad_id=$id";
		$result_ads_dates=mysql_query($query_ads_dates);
		while ($row_ads_dates = mysql_fetch_assoc($result_ads_dates))
		{
		$time = $row_ads_dates['time'];
		$upload_date = date('m/d/y',$time);			
		echo "$upload_date<br>";}
		?></font></td>

		<td align="center" width="60"><font size="-2"><?php
		$query_ads_dates="SELECT * FROM job_log WHERE ad_id=$id";
		$result_ads_dates=mysql_query($query_ads_dates);
		while ($row_ads_dates = mysql_fetch_assoc($result_ads_dates))
		{
		$time = $row_ads_dates['date_approved'];
		$date_approved = date('m/d/y',$time);			
		echo "$date_approved<br>";}
		?></font></td>			

<?php
}
?>
<TR>
<td colspan="10" width="1343"><div align="center"><IMG SRC="thin_horiz_line.png"></div></TD>
</TR>
</TABLE>
</body>
</html>

matthewst

OK, i don't know how to proceed forward.

I'm open to all suggestions (I'm trying, and failing, to use the suggestions already posted) but remember I'm still kinda new to php and mysql.

Pic 1:
As you can see no ad copy rec date (still working on the last two columns)

Pic 2:
A user has entered a date and hit submit and different user has entered a new date
When the new user hits submit the new date just disappears

Pic 3:
This is what I need

matthewst

Here is what I have now.

<?php
$query_ads_rec_dates="SELECT ad_copy_rec FROM whiteboard_dates WHERE rest_id=$table_id";
$result_ads_rec_dates=mysql_query($query_ads_rec_dates);
while ($row_ads_rec_dates = mysql_fetch_assoc($result_ads_rec_dates))
{
$ad_copy_rec_old = $row_ads_rec_dates['ad_copy_rec'];
}
?>
/////////////////			
			<td align="center" valign="top" width="60"><font size="-2"><?
			echo "$ad_copy_rec_old";
			?></p>
			<form method="post" action="" enctype="multipart/form-data" name="FormName">
			<input type="text" class="formTextbox" name="ad_copy_rec_new" size="24" value='<?php echo"$ad_copy_rec_old"; ?>'/>
			<input type="submit" class="formTextbox" name="submit" value="Submit Ad Copy Rec Date">
			</form></font></td>

		<?php
		if($submit){
		if ($ad_copy_rec_old == "")
 		{
    	$sql ="INSERT INTO whiteboard_dates (rest_id, ad_copy_rec) VALUES ('$table_id', '$ad_copy_rec_new')";
		$result = mysql_query($sql);
		}}
		else
		{ 
		$sql ="UPDATE whiteboard_dates SET ad_copy_rec = $ad_copy_rec_old + ', ' +  $ad_copy_rec_new WHERE rest_id=$table_id";
		$result = mysql_query($sql);
		}
		?>

It submits the first time but won't "add" to the record after that.

roscor

hi I have a record update on my site stats and use the following, perhaps you may be able to use a similar set echoing out each record before the code then updates.

just a thought

include("dbinfo.inc.php");// database connection
    $conn = mysql_connect($host, $username, $password) or die(mysql_error());
    mysql_select_db($database,$conn) or die('MySQL error - ' . mysql_error() . "<hr>Query: $query"); 

$sql = ("UPDATE ip_tracking SET date_last = NOW() WHERE username ='$user'"); 
$sql1 = ("UPDATE ip_tracking SET ip_address ='$hostname' WHERE username = '$user'"); 
$sql2 = ("UPDATE ip_tracking SET landlordloid='$landlordloid' WHERE username = '$user'");

$result = mysql_query($sql); 
$result1 = mysql_query($sql1); 
$result2 = mysql_query($sql2);

$sql3 = mysql_query("SELECT * FROM ip_tracking WHERE  username ='$user'") or die(mysql_error());
while($visits = mysql_fetch_array($sql3))
{
$visits = $visits['visits'];
$visits_new = $visits + 1;
}

$query = "UPDATE ip_tracking SET visits = '$visits_new' WHERE username ='$user'";
mysql_query($query, $conn) or die(mysql_error());
}


if (empty($last_visited)){
$firsttime='First Visit';
$visits_new='First Visit';
 include("dbinfo.inc.php");

//$hostname=$_POST['ip_address'];
mysql_connect($host,$username,$password)or die(mysql_error());
mysql_select_db($database,$conn) or die(mysql_error());
  $query = "INSERT INTO ip_tracking VALUES ('$hostname',NOW(),'','$user','$landlordloid')";
          mysql_query($query);
}

matthewst

sorry opera crashed and i can't figure out how to delete this post

matthewst

thanks rosco

the issue I'm having is:
instead of just adding 1 to a number

$visits = $visits['visits']; 
$visits_new = $visits + 1;

I need to append new data to exesting data.

old_data

user enters data into a field an clicks submit

old_data, new_data

user enters data into a field and clicks submit

old_data, new_data, newer_data

roscor

Not sure if you can create a new field into your database once created or set the fields, like you stated old_data, new_data, newer_data then that field is occupied then submit to the next field thus retaining the existing value for you to recover. Not sure if it would work, just a thought.

phpseeker

Can you add a hidden element in your form to keep the old ad_copy value?

after you submit, you can concatenate the old one to new one:
$POST['ad_copy_old']. $POST['ad_copy_rec'].

matthewst

phpseeker, I couldn't get that code to post at all so i tried something similar.

This code will post but only the first entry it won't append the new data to the old.
(the database column ad_copy_rec is varchar)

<?php
$query_ads_rec_dates="SELECT ad_copy_rec FROM whiteboard_dates WHERE rest_id=$table_id";
$result_ads_rec_dates=mysql_query($query_ads_rec_dates);
while ($row_ads_rec_dates = mysql_fetch_assoc($result_ads_rec_dates))
{
$ad_copy_rec_old = $row_ads_rec_dates['ad_copy_rec'];
}
?>
			<tr>
			<td align="center" valign="top" width="60"><font size="-2"><?
			echo "$ad_copy_rec_old";
			?></p>
			<form method="post" action="" enctype="multipart/form-data" name="FormName">
			<input type="text" class="formTextbox" name="ad_copy_rec_new" size="24"/>
			<input type="submit" class="formTextbox" name="submit" value="Submit Ad Copy Rec Date">
			</form></font></td>

		<?php
		if($submit && $ad_copy_rec_old == ""){
		$sql ="INSERT INTO whiteboard_dates (rest_id, ad_copy_rec) VALUES ('$table_id', '$ad_copy_rec_new')";
		$result = mysql_query($sql);
		}
		if($submit && $ad_copy_rec_old != ""){
		$ad_copy_rec_together = $ad_copy_rec_old.",".$ad_copy_rec_new;
		$sql ="UPDATE whiteboard_dates SET ad_copy_rec = $ad_copy_rec_together WHERE rest_id=$table_id";
		$result = mysql_query($sql);
		}
		?>

phpseeker

I don't think the logic of your program is clear, because after you submit your form, the program will run from the beginning of it, i.e. from "$query_ads_rec_dates="SELECT ad_copy....",
not from " if($submit && $ad_copy_rec_old == ""){ ......".

So, we usually put the form code and the after-form code into two seperate files, otherwise , you need to add an if statement at the beginning of the program to judge if you have posted the form or not.

For example:

<?php
if ($_POST['submitted']='yes'){
// handle the input value
$oldvalue.$newvalue ......
}
else{ ?>
//prepare old value
<form method="post" action="" ....>
<input type="hidden" name="submitted" value="yes">
<input type="hidden" name="oldvalue" value=$oldvalue>
<input type="text" name="newvalue">
<input type="submit" value="submit">
</form>
<?php
}//else
?>

Note: the example is a layout of a program and an advice, not the real code.

matthewst

here's what i'm thinking

first page load, no data in db so none is shown

enter data into form hit submit, the page reloads
and since there is data in the db it displays it

new data is entered into the form, the new data is
appended to the old data (old_data, new_data) when
the user clicks submit

the page reloads and displays old_data, new_data

If i need two pages for this thats fine, I just thought it would work with one page.

matthewst

GOT IT!!

			<td align="center" valign="top" width="60"><font size="-2"><?
			echo "$ad_copy_rec_old";
			?></p>
			<form method="post" action="" enctype="multipart/form-data" name="FormName">
			<input type="text" class="formTextbox" name="ad_copy_rec_new" size="24"/>
			<input type="submit" class="formTextbox" name="submit" value="Submit Ad Copy Rec Date">
			</form></font></td>

		<?php
		if($submit && $ad_copy_rec_old == ""){
		$sql = "INSERT INTO whiteboard_dates (rest_id, ad_copy_rec) VALUES ('$table_id', '$ad_copy_rec_new')";
		$result = mysql_query($sql);
		} 
		else 
		{
		$new = $ad_copy_rec_old.",".$ad_copy_rec_new;
		$sql = "UPDATE whiteboard_dates SET ad_copy_rec = '$new' WHERE rest_id='$table_id'";
		$result = mysql_query($sql);
		}			
		?>