CODEWITHSUNDEEP
phpmysqldatabaseforms
Megan Taylor
Megan Taylor

Reputation: 97

Add data to corresponding entries in MySQL database

I have a submission form that sends data to a database. Then I output that data from the database in HTML. Now, I have to submit one more piece of data through a form back to the database. I can't figure out how to make the new data correspond to the existing entries.

This is my output code (data from database to HTML with new form for additional data):

<html>
<body>
<?php

mysql_connect(localhost,root,root);
@mysql_select_db(test) or die( "Unable to select database");
$query="SELECT * FROM submission";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();
?>
<table border="0" cellspacing="2" cellpadding="2">
<tr>
<th><font face="Arial, Helvetica, sans-serif">Read By</font></th>
<th><font face="Arial, Helvetica, sans-serif">Date/Time</font></th>
<th><font face="Arial, Helvetica, sans-serif">URL</font></th>
<th><font face="Arial, Helvetica, sans-serif">Uploaded File</font></th>
<th><font face="Arial, Helvetica, sans-serif">Email / Twitter</font></th>
<th><font face="Arial, Helvetica, sans-serif"></font></th>
</tr>

<?php
$i=0;
while ($i < $num) {

$Date=mysql_result($result,$i,"Date");
$url=mysql_result($result,$i,"url");
$uploadedfile=mysql_result($result,$i,"uploadedfile");
$contact=mysql_result($result,$i,"contact");
?>

<tr><form name="reader" action="reader.php" method="POST">
<td><font face="Arial, Helvetica, sans-serif"><input type="checkbox" name="reader" value="Max"> Max <input type="checkbox" name="reader" value="Aaron"> Aaron</font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $Date; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $url; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $uploadedfile; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $contact; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><input type="submit" value="Submit"></font></td>
</form>
</tr>

<?php
$i++;
}
?>
</body>
</html>

And this is the reader.php code:

    <?php

$error=0;

$con = mysql_connect('localhost', 'root', 'root'); //Replace with your actual MySQL DB Username and Password
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("longform", $con); //Replace with your MySQL DB Name
$reader=mysql_real_escape_string($_POST['reader']); //This value has to be the same as in the HTML form file
$sql="INSERT INTO submission (reader) VALUES ('$reader')"; /*collect is the name of the MySQL table where the form data will be saved.name, email and comments are the respective table fields*/

if (!mysql_query($sql,$con)) {
 die('Error: ' . mysql_error());
}

$message="Done!";
mysql_close($con); 


?>

Right now, reader.php adds a new database entry with the contents of the form, I need it to add that data to the existing corresponding entry.

EDIT: So, I just realized I need to be using UPDATE instead of INSERT, but I have no idea how...

EDIT: I think I've made some progress in the right direction, thanks to commenters. New code still isn't working though:

Output + Form:

<html>
<body>
<?php

$id=$_GET['id'];

mysql_connect(localhost,root,root);
@mysql_select_db(longform) or die( "Unable to select database");
$query="SELECT * FROM submission";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();
?>
<table border="0" cellspacing="2" cellpadding="2">
<tr>
<th><font face="Arial, Helvetica, sans-serif">Read By</font></th>
<th><font face="Arial, Helvetica, sans-serif">Date/Time</font></th>
<th><font face="Arial, Helvetica, sans-serif">URL</font></th>
<th><font face="Arial, Helvetica, sans-serif">Uploaded File</font></th>
<th><font face="Arial, Helvetica, sans-serif">Email / Twitter</font></th>
<th><font face="Arial, Helvetica, sans-serif"></font></th>
</tr>

<?php
$i=0;
while ($i < $num) {

$Date=mysql_result($result,$i,"Date");
$url=mysql_result($result,$i,"url");
$uploadedfile=mysql_result($result,$i,"uploadedfile");
$contact=mysql_result($result,$i,"contact");
?>

<tr><form name="reader" action="reader.php" method="POST">
<td style="display:none;"><input type="hidden" name="unique_id" value="<? echo $id; ?>"></td>
<td><font face="Arial, Helvetica, sans-serif"><input type="checkbox" name="reader" value="Max"> Max <input type="checkbox" name="reader" value="Aaron"> Aaron</font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $Date; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $url; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $uploadedfile; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $contact; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><input type="submit" value="Submit"></font></td>
</form>
</tr>

<?php
$i++;
}
?>
</body>
</html>

reader.php:

<?php

$error=0;

$con = mysql_connect('localhost', 'root', 'root'); //Replace with your actual MySQL DB Username and Password
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("longform", $con); //Replace with your MySQL DB Name

$reader=mysql_real_escape_string($_POST['reader']); //This value has to be the same as in the HTML form file

$query="UPDATE submission SET reader='$reader' WHERE id='$unique_id'";
mysql_query($query);
echo "Record Updated";
mysql_close();

?>

Any help with this would totally rock my world.

EDIT: It Works! I just wanted to post the functional code here in case someone else needs it:

output.php

<html>
<body>
<?php

mysql_connect(localhost,root,root);
@mysql_select_db(test) or die( "Unable to select database");
$query="SELECT * FROM table WHERE field = '';
";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();
?>
<table border="0" cellspacing="2" cellpadding="2">
<tr>
<th></th>
<th><font face="Arial, Helvetica, sans-serif">Read By</font></th>
<th><font face="Arial, Helvetica, sans-serif">Date/Time</font></th>
<th><font face="Arial, Helvetica, sans-serif">URL</font></th>
<th><font face="Arial, Helvetica, sans-serif">Uploaded File</font></th>
<th><font face="Arial, Helvetica, sans-serif">Email / Twitter</font></th>
<th><font face="Arial, Helvetica, sans-serif"></font></th>
</tr>

<?php
$i=0;
while ($i < $num) {
$id=mysql_result($result,$i,"id");
$date=mysql_result($result,$i,"date");
$url=mysql_result($result,$i,"url");
$uploadedfile=mysql_result($result,$i,"uploadedfile");
$contact=mysql_result($result,$i,"contact");
?>

<tr><form action="reader.php" method="POST">
<td><input type="hidden" name="id" value="<? echo $id; ?>"></td>
<td><font face="Arial, Helvetica, sans-serif"><input type="checkbox" name="reader" value="Max"> Max <input type="checkbox" name="reader" value="Aaron"> Aaron</font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $date; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $url; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $uploadedfile; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $contact; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><input type="submit" value="Submit"></font></td>
</form>
</tr>

<?php
$i++;
}
?>
</body>
</html>

reader.php

<?php

$error=0;

$con = mysql_connect('localhost', 'root', 'root'); //Replace with your actual MySQL DB Username and Password
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("longform", $con); //Replace with your MySQL DB Name

$reader=mysql_real_escape_string($_POST['reader']); //This value has to be the same as in the HTML form file
$id=mysql_real_escape_string($_POST['id']);
$query="UPDATE submission SET reader='$reader' WHERE id='$id'";
mysql_query($query);
echo "Record Updated";
mysql_close();
?>

Upvotes: 1

Views: 1499

Answers (7)

fncomp
fncomp

Reputation: 6178

This is where you seem to be having trouble: 

$sql="INSERT INTO submission (reader) VALUES ('$reader')";

Try replacing that with: 

if($_POST['unique_id']){
    $sql = "UPDATE submission (reader) VALUES ($reader) WHERE id = $unique_id LIMIT 1;";
}else{
    $sql="INSERT INTO submission (reader) VALUES ('$reader')";
}

Upvotes: 0

jon_darkstar
jon_darkstar

Reputation: 16768

//Assuming - parameters ready, connection establish and $connString set
$sql1 = "SELECT * FROM submissions";
$res = mysql_query($sql, $connString);

//loop like you did, but check out foreach loops, mysql_fetch_assoc, alternative forms

    $id=mysql_result($result,$i,"id");  //similarly for other fields
    //HTML form, elements, submit button as necessary for elements in $res
    echo "<input type='hidden' name='submission_row_id' id='prev_insert_id_".$i."' value='$prevId' />"; 
       //within loop and form.  HTML with php tag for variable is fine too, im just fairly unfamiliar

//end loop

reader.php - keep most of yours

$reader=mysql_real_escape_string($_POST['reader']); 
$id = $_POST['submission_row_id'];
$sql2="INSERT INTO submission (id, reader) VALUES ($id,'$reader')";
$sql2success = mysql_query($sql2, $connString);
$outmessage = $sql2success ? 
              "Successful -  table1 row $prevId updated" :
               "Failure on update - $sql2"

echo $outmessage; //among other things

Upvotes: 0

Sean Reifschneider
Sean Reifschneider

Reputation: 1261

In your form you probably want to add a hidden field that is the ID to update. So for each of the rows you pull out of the database, you would display the fields you want them to be able to edit, then also include in the form type=hidden fields that include the object ID or other identifying information. For example:

<form>
    <input type="text" name="full_name" value="<?php echo $full_name ?>" />
    <input type="hidden" name="id" value="<?php echo $id ?>" />
</form>

Upvotes: 1

marcelo
marcelo

Reputation: 193

Hey Megan, zod is correct. First, you need to pass the id for the table submission. Then, you need to check if the record exists. You can do this two ways:

SELECT COUNT(*) as total FROM submission WHERE ID = $id

If total > 0, then you know you need to update; otherwise, you need to insert the record.

If you are using MySQL (like you code shows). You can also use the "ON DUPLICATE KEY UPDATE"

INSERT INTO submission (id, Date,url,uploadedfile,contact) VALUES (1,'2010-12-31','www.google.com', 'Megan')
 ON DUPLICATE KEY UPDATE Date = now();

It's a handy tool, but you have to check if it applies to you. You can get more information regarding this feature in the following site: http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html

Upvotes: 0

zod
zod

Reputation: 12417

get the id of the existing row which you want to insert.

Actually its not insert. Its update.

For that you have to run update query.

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

make condition check in php.

if(id exist)
run update
else
run insert

http://www.w3schools.com/php/php_mysql_update.asp

http://www.weberdev.com/get_example-4392.html

understand the examples in the above link and try to implement it in your script

Upvotes: 0

Jose Vega
Jose Vega

Reputation: 10258

Expanding on zod answer, each entry in the database should have a unique identifier, which is what zod refers to id. 

 id INT NOT NULL AUTO_INCREMENT, 
 PRIMARY KEY(id),

You can now refer to each entry by its id, and when "add[ing] th[e] data to the existing corresponding entry." You can say

 UPDATE .. FROM ... WHERE id = '$id'

Upvotes: 0

staticsan
staticsan

Reputation: 30555

Short answer: You need to re-engineer your form.

Less short answer: Your page is capable of showing existing entries and creating new ones. What is doesn't do is provide a way to edit exiting ones (which is what you want to do).

And edit form and a new form are almost identical: the key difference is that form needs to submit the current ID of the entry being edited. The submission page can then use that to figure out if it needs to build an INSERT statement or an UPDATE statement.

Rendering the form for editing also has to be done. The URL needs to provide a way to specify which row, usually by ID, which the page can use to get just that row from the database for pre-filling the edited fields.

(Longer term, you also really should either find a framework or build your own. This will let you isolate SQL calls away from the page generation and data processing.)

Upvotes: 0

Related Questions