Solved

php/mysql update query with concatenate

Posted on 2009-05-12
6
1,155 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:yamya
Comment Utility
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
Comment Utility
I have succeeded with a form to load the existing data.
Thank you for the pointers.
0
 

Author Closing Comment

by:yamya
Comment Utility
i am pointed in the right direction, thank you!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Introduction Many web sites contain image galleries; a common design for these galleries includes a page with a collection of thumbnail images.  You can click on each of the thumbnail images to see the larger version of the image.  This is easily i…
Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

762 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now