Transferring data from one table to another

**computerfox** · Mar 13 '10, 03:01 AM

Okay, I got everything working. Thank you VERY VERY much. My problem right now it that I can only update that one ID and I have a loop to print out the data with a unique link which includes that ID. That being said, is there some way that I can make the ID an incrementing int like in C++ or JAVA?

**computerfox** · Mar 13 '10, 03:17 AM

Nevermind. I got it.

**computerfox** · Mar 17 '10, 04:51 AM

okay, so i was screwing around with making cookies for the site and my update script just stopped working. maybe it's something small that i'm missing, but can someone help me again?

Code:

<html>
<?
$ID=$_GET['ID'];
$username="***************";
$password="***************";
$database="****************";
mysql_connect("*******************",$username,$password);
mysql_select_db($database);
$query="SELECT*  FROM ********** ";
$result=mysql_query($query);


//Print output
$row=mysql_fetch_assoc($result);

$ID=$row['ID'];
$Time=$row['Time'];
$Client=$row['Client'];
$Number=$row['Number'];
$Address=$row['Address'];
$Issue=$row['Issue'];
$Notes=$row['Notes'];
$Status=$row['Status'];
$Charge=$row['Charge'];


//Print "ID: $ID<br>";
?>


<form method="POST">
<input type="hidden" name="ud_ID" value="<? echo $ID; ?>">
Client: <input type="text" name="ud_Client" value="<? echo $Client; ?>"><br>
Address: <input type="text" name="ud_Address" value="<? echo $Address; ?>"><br>
Number: <input type="bigint(20)" name="ud_Number" value="<? echo $Number; ?>"><br>
Issue: <input type="longtext" name="ud_Issue" value="<? echo $Issue; ?>"><br>
Notes: <input type="longtext" name="ud_Notes" value="<? echo $Notes; ?>"><br>
Status: <input type="tinyint(1)" name="ud_Status" value="<? echo $Status; ?>"><br>
Charge: <input type="double" name="ud_Charge" value="<? echo $Charge; ?>"><br>
<input type="Submit"  value="Update">
</form>

<input type="Submit" onClick="window.close()" value="Close Window">
<?

//Update
$ud_ID=$_POST['ud_ID'];
$ud_Client=$_POST['ud_Client'];
$ud_Number=$_POST['ud_Number'];
$ud_Address=$_POST['ud_Address'];
$ud_Issue=$_POST['ud_Issue'];
$ud_Notes=$_POST['ud_Notes'];
$ud_Status=$_POST['ud_Status'];

//Print "ID: $ID<br>";
//Print "ud_ID: $ud_ID<br>";

mysql_query("UPDATE inprogress SET Time=NOW(),Client='$ud_Cleint',Number='$ud_Number',Address='$ud_Address',Issue='$ud_Issue',Notes='$ud_Notes,Status='ud_Status',Charge='$ud_Charge' WHERE ID='$ID' ");

?>


</html>

**computerfox** · Mar 17 '10, 05:24 AM

i realized that one of the reasons why i'm having trouble updating is because my for is inserting more than one row giving it the same id.

work_order.php

Code:

<div id=icon style="position:absolute;left:900">

<img src="http://www.1fixcomputermedic.com/Pictures/Site/ambulance.jpg">

</div>

<div id=cm style="position:absolute;">

<img src="/Pictures/Site/work_order.jpg"><br>

<br>

</div>

<div id=data style="position:absolute;top:300;">
<form id="workOrder" method="POST">
<input type="hidden" name="ID" value="<? echo $ID; ?>">
Client:<input type="text" name="Client" value="<? echo $Client; ?>"><br>
Number:<input type="bigint(20)" name="Number" value="<? echo $Issue; ?>"><br>
Address:<input type="text" name="Address" value="<? echo $Issue; ?>"><br>
Issue: <input type="longtext" name="Issue" value="<? echo $Issue; ?>"><br>
Notes: <input type= "longtext" name="Notes" value= "<? echo $Notes; ?>"><br>
Status: <input type="tinyint(1)" name="Status" value="<? echo $Status; ?>"><br>
Charge: <input type="tinyint(1)" name="Charge" value="<? echo $Charge; ?>"><br>

<input type="Submit" value="Submit">
</form>
</div>

<?
//Get data from form
$ID=$_POST['ID'];
$Client=$_POST['Client'];
$Number=$_POST['Number'];
$Address=$_POST['Address'];
$Issue=$_POST['Issue'];
$Notes=$_POST['Notes'];
$Status=$_POST['Status'];
$Charge=$_POST['Charge'];

//Insert new data into inprogress

$query="INSERT INTO inprogress (Client,Time,Number,Address,Issue,Notes,Status,Charge) VALUES ('$Client', NOW(), '$Number', '$Address','$Issue','$Notes','$Status','$Charge') ";

mysql_query($query);

mysql_close();
?>


</html>

now when i go into the mysqladminphp i get the correct values from the work order, but i still can't update anything.... any ideas?

**Atli** · Mar 17 '10, 11:51 AM

Hey.

The two previous code examples, are the exactly like they are on your server? (Meaning; you didn't copy/paste small parts of them, but the whole thing)

If so, I see a problem with both of them.

For example, the update script in post #18. - The UPDATE query is the last thing on the page, but it ALWAYS executes, even when there has no data been posted. Even the first time you see it, and the form is displayed, the UPDATE query is executed but with no values.

You need to make sure the values you are inserting into the query are valid.
Generally you should try to do something like this:
[code=php]<?php
// Check if the updated values have been sent.
if(isset($_POST['id'], $_POST['newValue1'], $_POST['newValue2']))
{
// Fetch the updated values, making sure they
// are safe to use in the SQL query.
$id = (int)$_POST['id'];
$newValue1 = mysql_real_esca pe_string($_POS T['newValue1']);
$newValue2 = mysql_real_esca pe_string($_POS T['newValue2']);

// Create and execute the SQL query
$sql = "UPDATE `table` SET
`value1` = '$newValue1',
`value2` = '$newValue2'
WHERE
`id` = $id
LIMIT 1";
$result = mysql_query($sq l);

// Make sure the SQL query was successful
if($result) {
echo "Updated!";
}
else {
echo "Error! " . mysql_error();
}
}
else
{
// Get and "id" from the URL, or default to 1
($id = @$_GET['id']) or $id = 1;

// Fetch values
$sql = "SELECT `value1`, `value2`
FROM `table`
WHERE `id` = $id";
$result = mysql_query($sq l) or die("Failed to fetch values. " . mysql_error());
$row = mysql_fetch_ass oc($result);

// Prepare values for being printed into HTML
$oldValue1 = htmlentities($r ow['value1'], ENT_QUOTES, 'UTF-8');
$oldValue2 = htmlentities($r ow['value2'], ENT_QUOTES, 'UTF-8');

// Print the HTML
echo <<<HTML
<form action="{$_SERV ER['PHP_SELF']}" method="post">
<input type="hidden" name="id" value="{$id}">
Value1: <input type="text" name="newValue1 " value="{$oldVal ue1}"><br>
Value2: <input type="text" name="newValue2 " value="{$oldVal ue2}"><br>
<input type="submit" value="Update">
</form>
HTML;

}
?>[/code]

**computerfox** · Mar 17 '10, 08:46 PM

I did what you said, and it's still having problems updating :(

This is how my script looks now:

Code:

<html>
<?

if(isset($ud_ID,$ud_Client, $ud_Number,$ud_Address,$ud_Issue,$ud_Notes,$ud_Status,$ud_Charge)){

//Update
$ud_ID=$_POST['ud_ID'];
$ud_Client=$_POST['ud_Client'];
$ud_Number=$_POST['ud_Number'];
$ud_Address=$_POST['ud_Address'];
$ud_Issue=$_POST['ud_Issue'];
$ud_Notes=$_POST['ud_Notes'];
$ud_Status=$_POST['ud_Status'];
$ud_Charge=$_POST['ud_Charge'];

$query="UPDATE inprogress SET Time=NOW(),Client='$ud_Cleint',Number='$ud_Number',Address='$ud_Address',Issue='$ud_Issue',Notes='$ud_Notes,Status='ud_Status',Charge='$ud_Charge' WHERE ID='$ID' LIMIT 1 ";


 mysql_query("$query");
}

else{
$ID=$_GET['ID'];
$username="*************";
$password="**************";
$database="***************";
mysql_connect("**************",$username,$password);
mysql_select_db($database);
$query="SELECT*  FROM inprogress ";
$result=mysql_query($query);


//Print output
$row=mysql_fetch_assoc($result);

$ID=$row['ID'];
$Time=$row['Time'];
$Client=$row['Client'];
$Number=$row['Number'];
$Address=$row['Address'];
$Issue=$row['Issue'];
$Notes=$row['Notes'];
$Status=$row['Status'];
$Charge=$row['Charge'];
}
?>


<form method="POST">
<input type="hidden" name="ud_ID" value="<? echo $ID; ?>">
Client: <input type="text" name="ud_Client" value="<? echo $Client; ?>"><br>
Address: <input type="text" name="ud_Address" value="<? echo $Address; ?>"><br>
Number: <input type="bigint(20)" name="ud_Number" value="<? echo $Number; ?>"><br>
Issue: <input type="longtext" name="ud_Issue" value="<? echo $Issue; ?>"><br>
Notes: <input type="longtext" name="ud_Notes" value="<? echo $Notes; ?>"><br>
Status: <input type="tinyint(1)" name="ud_Status" value="<? echo $Status; ?>"><br>
Charge: <input type="double" name="ud_Charge" value="<? echo $Charge; ?>"><br>
<input type="Submit"  value="Update">
</form>

<input type="Submit" onClick="window.close()" value="Close Window">

<?




//mysql_close();

?>


</html>

Also, when would I use a "@" and I recently bought an SSL, so would that effect anything with how the script works?

**Atli** · Mar 17 '10, 09:25 PM

Your code is not structured correctly. This is the basic idea of what you should be doing:
[code=pseudo]if ( the data has been posted ) {
fetch the posted data,
create the UPDATE query,
and execute the query.
}
ELSE {
fetch an ID from the URL or use 1 if there is no ID in the URL
fetch the row data from MySQL
insert the data into the HTML form and print it
}[/code]

There are a number of problems in your code:

The isset() clause at the beggining is meant to test for the existence of the $_POST elements, not the local variables you later create to hold the data. (See how I use it in my earlier example.)
You are using the wrong $ID in the update query. That variable doesn't exists in that part of the code. You should be using the $ud_ID created in the lines preceding the UPDATE query.
When you execute the UPDATE query, you have not yet opened a connection to MySQL. - You have to open a MySQL before using any of the MySQL functions. Doing it later, in another part of the code, will not work.
The SELECT query does not have a WHERE clause, so it will always fetch ALL the data in the table. You need to add a WHERE clause to fetch only the ID of the row you want to edit. (See my earlier example.)
The HTML for the form is outside the IF... ELSE structure, so it will show regardless of whether or not the UPDATE query is executed. - If you want this to be the case, you also need to put the SELECT outside the ELSE clause, or you will get an empty form after you submit it.

Also, when would I use a "@" and I recently bought an SSL, so would that effect anything with how the script works?

From PHP's perspective a SSL request is no different from a normal request, except for the URL using a "https" prefix rather than "http".

The @ char is used in PHP to silence errors. You don't really need to use it, but it can be handy when you know an error may occur and that it will have no effect on your code. (Or even be a normal part of the code's function.)

Like in my example earlier, when I do:
[code=php]($id = @$_GET['id']) or $id = 1;[/code]
the part inside the parenthesis tries to fetch an "id" from the URL and assign it to $id. If there is no "id" in the URL, $id will be NULL and the "or" will catch that and set the $id to 1. -- It's basically: "Set $id as the 'id' from the URL, or 1 of there is no 'id' in the URL".

The @ is there because if there is no "id" in the URL, the attempt to fetch it would generate an "undefined index" warning, but because I know it may happen and I have already written in a fail-safe for it, I use @ to silence the warning.

**computerfox** · Mar 18 '10, 12:15 AM

Code:

<?

if(isset($_POST['ID'],$_POST['ud_Client'], $_POST['ud_Number'],$_POST['ud_Address'],$_POST['ud_Issue'],$_POST['ud_Notes'],$_POST['ud_Status'],$_POST['ud_Charge'])){

//Update
$ud_ID=mysql_real_escape_string($_POST['ud_ID']);
$ud_Client=mysql_real_escape_string($_POST['ud_Client']);
$ud_Number=mysql_real_escape_string($_POST['ud_Number']);
$ud_Address=mysql_real_escape_string($_POST['ud_Address']);
$ud_Issue=mysql_real_escape_string($_POST['ud_Issue']);
$ud_Notes=mysql_real_escape_string($_POST['ud_Notes']);
$ud_Status=mysql_real_escape_string($_POST['ud_Status']);
$ud_Charge=mysql_real_escape_string($_POST['ud_Charge']);
//16
$query="UPDATE inprogress SET Time=NOW(),Client='$ud_Cleint',Number='$ud_Number',Address='$ud_Address',Issue='$ud_Issue',Notes='$ud_Notes,Status='ud_Status',Charge='$ud_Charge' WHERE ID='$ud_ID' LIMIT 1 ";

$username="";
$password="";
$database="";
mysql_connect("",$username,$password);
mysql_select_db($database);
$query="SELECT*  FROM inprogress ";
$result=mysql_query($query);
//28
 mysql_query("$query");
}

else{

$ID=@$_GET['ID'] or $ID=1;
$username="";
$password="";
$database="";
mysql_connect("",$username,$password);
mysql_select_db($database);
$query="SELECT*  FROM inprogress WHERE ID=$ID ";
$result=mysql_query($query);

$row=mysql_fetch_assoc($result);

    $Client = htmlentities($row['Client'], ENT_QUOTES, 'UTF-8');
    $Address = htmlentities($row['Address'], ENT_QUOTES, 'UTF-8');
    $Number = htmlentities($row['Number'], ENT_QUOTES, 'UTF-8');
    $Isse = htmlentities($row['Issue'], ENT_QUOTES, 'UTF-8');
    $Notes = htmlentities($row['Notes'], ENT_QUOTES, 'UTF-8');
    $Charge = htmlentities($row['Charge'], ENT_QUOTES, 'UTF-8');
    $Status = htmlentities($row['Status'], ENT_QUOTES, 'UTF-8');

 echo <<<HTML;

<form action="{$_SERVER['PHP_SELF']}" method="POST">
<input type="hidden" name="ud_ID" value="<? echo $ID; ?>">
Client: <input type="text" name="ud_Client" value="<? echo $Client; ?>"><br>
Address: <input type="text" name="ud_Address" value="<? echo $Address; ?>"><br>
Number: <input type="bigint(20)" name="ud_Number" value="<? echo $Number; ?>"><br>
Issue: <input type="longtext" name="ud_Issue" value="<? echo $Issue; ?>"><br>
Notes: <input type="longtext" name="ud_Notes" value="<? echo $Notes; ?>"><br>
Status: <input type="tinyint(1)" name="ud_Status" value="<? echo $Status; ?>"><br>
Charge: <input type="double" name="ud_Charge" value="<? echo $Charge; ?>"><br>
<input type="Submit"  value="Update">
</form>


//<input type="Submit" onClick="window.close()" value="Close Window">

HTML;

}

?>

This give me an error. What did I do wrong?

**Atli** · Mar 18 '10, 01:06 AM

Line #49. The semi-colon following the HTML can't be there. Nothing can follow the delimiter ("HTML", in your case) and the delimiter must only contain characters that would be valid for a variable name. (This also includes white-spaces, by the way. - A common error when using heredoc syntax.)

Also, inside the HTML (between #49 and #64) the "<?php echo ...; ?>" need to be replaced by "{...}". - When used like that, the HTML is a string, so you just add the variables to it like you would a normal string. For example, line #54 should be:
[code=php]Client: <input type="text" name="ud_Client " value="{$Client }"><br>[/code]

And you need to remove or relocate the comment on line #65 outside the "<<<HTML ... HTML;" delimiters. (Or it will be printed to the HTML as-is.)

**computerfox** · Mar 18 '10, 01:40 AM

I changed it, but now it's saying that the fetch isn't valid on line 39.... WTF?

Code:

<?

if(isset($_POST['ID'],$_POST['ud_Client'], $_POST['ud_Number'],$_POST['ud_Address'],$_POST['ud_Issue'],$_POST['ud_Notes'],$_POST['ud_Status'],$_POST['ud_Charge'])){

//Update
$ud_ID=mysql_real_escape_string($_POST['ud_ID']);
$ud_Client=mysql_real_escape_string($_POST['ud_Client']);
$ud_Number=mysql_real_escape_string($_POST['ud_Number']);
$ud_Address=mysql_real_escape_string($_POST['ud_Address']);
$ud_Issue=mysql_real_escape_string($_POST['ud_Issue']);
$ud_Notes=mysql_real_escape_string($_POST['ud_Notes']);
$ud_Status=mysql_real_escape_string($_POST['ud_Status']);
$ud_Charge=mysql_real_escape_string($_POST['ud_Charge']);

$query="UPDATE inprogress SET Time=NOW(),Client='$ud_Cleint',Number='$ud_Number',Address='$ud_Address',Issue='$ud_Issue',Notes='$ud_Notes,Status='ud_Status',Charge='$ud_Charge' WHERE ID='$ud_ID' LIMIT 1 ";

$username="";
$password="";
$database="";
mysql_connect("",$username,$password);
mysql_select_db($database);
$query="SELECT*  FROM inprogress WHERE ID=$ID";
$result=mysql_query($query);

 mysql_query($query);
}

else{

$ID=@$_GET['ID'] or $ID=1;
$username="";
$password="";
$database="";
mysql_connect("",$username,$password);
//mysql_select_db($database);
$query="SELECT*  FROM gancsosa_crystalworks.inprogress WHERE ID=$ID ";
$result=mysql_query($query);

$row=mysql_fetch_assoc($result);

    $Client = htmlentities($row['Client'], ENT_QUOTES, 'UTF-8');
    $Address = htmlentities($row['Address'], ENT_QUOTES, 'UTF-8');
    $Number = htmlentities($row['Number'], ENT_QUOTES, 'UTF-8');
    $Isse = htmlentities($row['Issue'], ENT_QUOTES, 'UTF-8');
    $Notes = htmlentities($row['Notes'], ENT_QUOTES, 'UTF-8');
    $Charge = htmlentities($row['Charge'], ENT_QUOTES, 'UTF-8');
    $Status = htmlentities($row['Status'], ENT_QUOTES, 'UTF-8');

 echo <<<HTML

<form action="{$_SERVER['PHP_SELF']}" method="POST">
<input type="hidden" name="ud_ID" value="{$ID}">
Client: <input type="text" name="ud_Client" value="{$Client}"><br>
Address: <input type="text" name="ud_Address" value="{$Address}"<br>
Number: <input type="bigint(20)" name="ud_Number" value="{$Number}"><br>
Issue: <input type="longtext" name="ud_Issue" value="{$Issue}"><br>
Notes: <input type="longtext" name="ud_Notes" value="{$Notes}"><br>
Status: <input type="tinyint(1)" name="ud_Status" value="{$Status}"><br>
Charge: <input type="double" name="ud_Charge" value="{$Charge}"><br>
<input type="Submit"  value="Update">
</form>


<input type="Submit" onClick="window.close()" value="Close Window">

HTML;

}

?>

**computerfox** · Mar 18 '10, 02:45 AM

123456789123456 789123456789

**computerfox** · Mar 18 '10, 06:47 PM

i went back to pure php since the embedded html was getting me confused and now it's working.

I just need to get the script to work as a loop again

**computerfox** · Mar 18 '10, 09:00 PM

any ideas how i can do that?

**computerfox** · Mar 18 '10, 09:19 PM

Code:

Print '<th><a href="https://www.1fixcomputermedic.com/update.php?id " target="_blank">Edit</a></th>';

any ideas why this doesn't bring in id as a variable?

**Atli** · Mar 18 '10, 09:23 PM

The "id" in the URL of that link has no value. Where is the value supposed to be coming from?

Transferring data from one table to another

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment