Solved

php/mysql update query with concatenate

Posted on 2009-05-12
6
1,161 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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
PHP & MySql - Turn Select Query Results into a Glyphicon 3 30
hasing a url 16 25
google map tracking website 5 23
UPDATE query not working in mysqli php 8 50
Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to count occurrences of each item in an array.

776 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