Solved

php/mysql update query with concatenate

Posted on 2009-05-12
6
1,167 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Don't Cry: How Liquid Web is Ensuring Security

WannaCry is just the start. Read how Liquid Web is protecting itself and its customers against new threats.

Question has a verified solution.

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

Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
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…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

696 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