Solved

php/mysql update query with concatenate

Posted on 2009-05-12
6
1,163 Views
Last Modified: 2013-12-12
My goal is to update an existing and specific column.row by getting user text-string input and combining it with existing text-string that replaces the original.

The research I've done suggests to gather the update in a temporary table to facilitate concatenate. That makes sense to me. My usual method (find a script or snippet to copy and modify) has failed, and I am unable to get beyond this code on my own.

i've only succeeding in returning blank pages in the browser. i have NOT tried sessions and suspect that could be at the root of the problem. Do I have to go back to the books on this one? [i am hoping to avoid this, you can see]

/*this is designed to show the user a list of records. the user is to select the record that needs an update to the application field, and by entering the record id and the new info, have a concatenated entry written to the database.*/

thanks in advance for your review and comments.
amy*/


<!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>Joe udpate</title>
</head>
 
<body>
 
<?php
 
// create connection
$connection = mysql_connect("user.hostmysql.com","user","user");
 
// test connection
if (!$connection) {
echo "Couldn't make a connection!";
exit;
}
 
// select database
$db = mysql_select_db("db", $connection);
 
// test selection
if (!$db) {
echo "Couldn't select database!";
exit;
}
 
// create SQL statement
$sql = "SELECT id, chem_id, application, RD_Prod_Num
FROM RD_Product_Application
ORDER BY RD_Prod_Num ASC";
 
// execute SQL query and get result
$sql_result = mysql_query($sql,$connection);
 
// start results formatting
echo "<TABLE width=95% BORDER=1>";
echo "<TR><TH width=12>Record ID </th><TH width=10>Chem_ID</TH><TH width=15>Product Num </TH><TH>Application Info</TH>";
 
// format results by row
while ($row = mysql_fetch_array($sql_result)) {
$id = $row["id"];
$chem_id = $row["chem_id"];
$RD_Prod_Num = $row["RD_Prod_Num"];
$application = $row["application"];
 
 
echo "<TR><TD>$id</TD><TD>$chem_id</TD><TD>$RD_Prod_Num</TD><TD>$application</TD>";
}
 
echo "</TABLE>";
 
// free resources and close connection
mysql_free_result($sql_result);
mysql_close($connection);
?> 
<hr />
<h2> Find Record ID above, then Enter Record ID in form below. <hr />Click Submit Button at end of form when finished. </h2>
<form method="post" action="update1.php">
Record ID: <input type="text" name="recordid" />
Application Info UPDATE:<input type="text" name="applicationUpdate" />
<input type="submit" name="submit" value="update Record" />
 
</form>
 
</body>
</html>

Open in new window

0
Comment
Question by:yamya
  • 4
  • 2
6 Comments
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 24370491
Is the code you posted giving a blank page, or does it give a blank page when you submit the form?

Do you have access to web server error log? If yes, check there for error messages.

Insert this at the start of the php code (line 11 in the snippet) to make it display errors:

error_reporting(E_ALL);
0
 

Author Comment

by:yamya
ID: 24370668
i have made progress with this code on rows 57-72, using JS to require a RecordID and prevent duplicate data.

the fine point remaining is to concatenate the string from the application column in the opening query with the string in the form field applicationUpdate.

in the immediate moment, a copy/paste or reentry is required, along with the latest notes.
i considered using a table just for the updates and then joining them by way of a common id field for the view.

this will be used over our intranet, on a secure terminal server that manages sessions and security, so the most simple of solutions is desired.

your input is most welcome.

thanks
amy

<hr />
<h2> Find Record ID above, then Enter Record ID in form below. <hr />Click Submit Button at end of form when finished. </h2><h3>To keep the existing application info, copy and paste from the list above into the Form Field below; date and add additional notes and then hit submit. Hit REFRESHH to see the updated list</h3>
 
<form method="post" action="update.php" onsubmit="return validate_form(this)">
(required) Record ID: <input type="text" name="recordid" />
Application Info UPDATE:<input type="text" name="applicationUpdate" />
<input type="submit" name="submit" value="update record" />
 
</form>
<?php
if (submit) {
	$query=mysql_query("UPDATE RD_Product_Application SET application='$applicationUpdate' WHERE id='$recordid'");
$result=($query);
if ($result) {
	echo "Updated record with " .$applicationUpdate ." ";
}
}
?>
<p><a href="upda

Open in new window

0
 
LVL 39

Accepted Solution

by:
Roger Baklund earned 500 total points
ID: 24370902
You should not use the name "submit" for the submit button, because this is the name of a method in the form JS object.

When testing if the form is submitted, you can not do this:

if (submit) {

...but you can do this:

if ($_POST) {

You must fetch the applicationUpdate and recordid fields, you can do it like this:

$applicationUpdate  = $_POST['applicationUpdate '];
$recordid = $_POST['recordid '];

This code:

$result=($query);
if ($result) {

...is unnecessary, it is the same as this:

if ($query) {

>> in the immediate moment, a copy/paste or reentry is required, along with the latest notes.
>> i considered using a table just for the updates and then joining them by way of a common id field for the view.

The "normal" way to do it is to put the existing value into the form, let the user edit it, then update it directly in the table. To do that you need an extra step: loading the selected record into the form. I don't know the details of your application, tell me if you want to do it this way.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:yamya
ID: 24371011
Oh Yes Please that is exactly what I've been trying to accomplish.

- load form with existing values
-  user updates as needed any or all non-key columns
-  db updated

this is intended as a quite simple running journal to be used by four people, globally, as an info share during early development.

all good code welcomed.
thanks!
0
 

Author Comment

by:yamya
ID: 24376950
I have succeeded with a form to load the existing data.
Thank you for the pointers.
0
 

Author Closing Comment

by:yamya
ID: 31580781
i am pointed in the right direction, thank you!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question