[SOLVED] add data to an existing record

matthewst · June 22, 2007

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

pikemsu28 · June 22, 2007

your query is adding a new row into the database. should it be updating the row that is displayed in your form?

if it's meant to add a new row, why can you not pull all dates associated with the ID, implode them with a comma, and display the dates: 05/23/2007, 05/29/2007

DaveEverFade · June 22, 2007

If I'm understanding you right then it sounds like it might be easier to get the id and dates you need to update from the table first then use UPDATE instead. ie

"UPDATE whiteboard_dates SET ad_copy_rec = $new_dates WHERE rest_id=$table_id"

where $new_dates is the previous data stored in ad_copy_rec concatenated with ", ".$_POST['ad_copy_rec']

....

pikemsu28 · June 22, 2007

that would work!

matthewst · June 22, 2007

thats the thing, not all the records have a value in ad_copy_rec to start with

here is a pic of the table, the column ad_copy_rec is empty now but it will be populated with dates then more dates then even more dates

[attachment deleted by admin]

pikemsu28 · June 22, 2007

use an IF statement that will concatenate the previous value (if not empty) with $_POST[''ad_copy_rec']

Else just use $_POST[''ad_copy_rec'] to update your database

matthewst · June 22, 2007

here's the entire page if that helps, i'm going to smash my head against a wall brb

<?
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>

pikemsu28 · June 22, 2007

hmm...it will take me a few to go thru this code.

first, where is the initial $table_id variable being populated from?

<?php

//i believe this is line 33

$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";

?>

this query will execute when the page loads but i dont see where the $table_id variable is being set.

matthewst · June 22, 2007

$table_id is carried over from another page

matthewst · June 25, 2007

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

[attachment deleted by admin]

pikemsu28 · June 25, 2007

ok...i'm going to try and put into words how i would handle this. i can't follow your code very well because you have too many select queries that should probably be combined into one join query.

but anyway, here we go....

populate your fields just as you are doing and set your variable $ad_copy_rec = (whatever your query is to populate the old value). if this value is null simply set the variable to blank "".

change the name of your input box to something like: name="new_ad_copy_rec"

when submitted use the $_post['new_ad_copy_rec" to concatenate the old value ($ad_copy_rec) and make an insert value.

if(isset($_POST['submit']))
{
  $insert_ad_copy_rec = $ad_copy_rec.",".$_POST['new_ad_copy_rec'];
  $query = "UPDATE whiteboard_dates set ad_copy_rec='$insert_ad_copy_rec' WHERE rest_id='$table_id'";
  $submit = mysql_query($query, $db_link) or die(mysql_error());
}
//this will UPDATE the current record in whiteboard_dates with the new value that includes the old and new value.

see if this all makes sense and try to adjust your code. another way to do it is to turn the old value into an array and add the new value to the array.

redarrow · June 25, 2007

U can get it all going now but now becouse you wouldnt use a join your need

to use a array or use substr or str_split to get valadation.

i think a join is better from the database.

ps. ur coding is bang on nice code anyway.

matthewst · June 26, 2007

I'll try to join the quieries first.

matthewst · June 26, 2007

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.

pikemsu28 · June 27, 2007

do you want to insert a new row if $ad_copy_rec_old is blank? i thought from your original post that the record will alreay exist but not have a value for that field.

anyway.

try this:

if($submit)
{
  $ad_copy_rec_new = $_POST['ad_copy_rec_new'];
  if ($ad_copy_rec_old == "")
  {
    //$sql ="INSERT INTO whiteboard_dates (rest_id, ad_copy_rec) VALUES ('$table_id', '$ad_copy_rec_new')";
  //use this if record already exists but ad_copy_rec is empty
  $sql ="UPDATE whiteboard_dates SET ad_copy_rec = $ad_copy_rec_new WHERE rest_id=$table_id";
    $result = mysql_query($sql);
  }
  else
  {
    //this will set a new variable to post with both values. the (.",".) will concatenate the two values together separated by a comma
    $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);
  }// end of if ($ad_copy_rec_old == "") else
}//end of if($submit)

try something like that. hopefully it'll work

matthewst · June 27, 2007

pikemsu, yes i need to insert a new row if there is nothing in ad_copy_rec

I tried your code as is and didn't have any luck. So I modified it a bit.

With this I'm a little closer, but it will only add the first record. It won't

append the new data to the old data.

(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);
  			}
		?>

pikemsu28 · June 27, 2007

if(isset($_POST['submit']))
{
$ad_copy_rec_new = $_POST['ad_copy_rec_new'];

if($ad_copy_rec_old == '')
{
	$sql = "INSERT INTO whiteboard_dates (rest_id, ad_copy_rec) VALUES ('$table_id', '$ad_copy_rec_new')";
	echo $sql;//replace with $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'";
	echo $sql;//replace with $result = mysql_query($sql);
}
}

try that

matthewst · June 28, 2007

pike your a genius!!! Thanks for the help everyone!!

Sign In

[SOLVED] add data to an existing record

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information