469,307 Members | 1,772 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,307 developers. It's quick & easy.

Transferring data from one table to another

computerfox
276 100+
I'm writing a work order system using PHP and MySQL and I'm stuck on two things-transferring data and updating the data.

Here is my code:

i'm using checklogin.php to do most of everything so it's most likely something I'm missing here.

checklogin.php

Expand|Select|Wrap|Line Numbers
  1.  
  2. <?php
  3. $host="example.com"; // Host name 
  4. $username="gancsosa"; // Mysql username 
  5. $password="******"; // Mysql password 
  6. $db_name="gancsosa_members"; // Database name 
  7. $tbl_name="logon"; // Table name
  8.  
  9. // Connect to server and select databse.
  10. mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
  11. mysql_select_db($db_name);
  12. // username and password sent from form 
  13. $Username=$_POST['Username']; 
  14. $Password=$_POST['Password'];
  15.  
  16. // To protect MySQL injection (more detail about MySQL injection)
  17. $Username = stripslashes($Username);
  18. $Password = stripslashes($Password);
  19. $Username = mysql_real_escape_string($Username);
  20. $Password = mysql_real_escape_string($Password);
  21.  
  22. $sql="SELECT * FROM $tbl_name WHERE username='$Username' and password='$Password'";
  23. $result=mysql_query($sql);
  24.  
  25. // Mysql_num_row is counting table row
  26. $count=mysql_num_rows($result);
  27. // If result matched $myusername and $mypassword, table row must be 1 row
  28.  
  29. if($count==1){
  30. // Register $myusername, $mypassword and redirect to file "login_success.php"
  31. session_register("Username");
  32. session_register("Password"); 
  33.  
  34.  
  35. mysql_select_db(gancsosa_crystalworks);
  36.  
  37. echo "\n";
  38.  
  39. $link = mysql_connect('example.com', 'gancsosa', '*****');
  40. mysql_select_db("gancsosa_crystalworks") or die(mysql_error()); 
  41. $data = mysql_query("SELECT * FROM inprogress")
  42. or die(mysql_error()); 
  43.  
  44.  
  45. //Transfer data
  46.  
  47.  
  48.  
  49. Print "<br><br>";
  50.  
  51. Print "Inprogress<br><br>";
  52.  
  53. Print "<table border cellpadding=3>"; 
  54. while($info = mysql_fetch_array( $data )) 
  55. @mysql_query( "UPDATE INTO complete SELECT  FROM inprogress  WHERE Status='1'  ");
  56.  
  57. Print "<th>ID: ".$info['ID'] . "</th> "; 
  58. Print "<th>Time: " .$info['Time'] . "</th>";
  59. Print "<th>Name: ".$info['Client'] . " </th>"; 
  60. Print "<th>Number: " .$info['Number'] . "</th>";
  61. Print "<th>Address: " .$info['Address'] . "</th>";
  62. Print "<th>Issue: " .$info['Issue'] . "</th>";
  63. Print "<th>Notes: " . $info['Notes'] . "</th>";
  64. Print "<th>Status: " .$info['Status'] . "</th>";
  65. Print "<th>Charge: $" .$info['Charge'] . "</th>";
  66. Print '<th><a href="http://www.1fixcomputermedic.com/update.php" target="_blank">Edit?</a><th>';
  67. Print "<br><br>";
  68. Print "</table>"; 
  69.  
  70. Print "<br>";
  71. Print "<br>";
  72.  
  73.  
  74.  
  75.  
  76. Print "<br><br><br>Complete<br><br>";
  77.  
  78. $data = mysql_query("SELECT * FROM complete") 
  79. or die(mysql_error()); 
  80.  
  81.  
  82. Print "<br><br>";
  83.  
  84. Print "<table border cellpadding=6>"; 
  85. while($info = mysql_fetch_array( $data )) 
  86. Print "<tr>"; 
  87. Print "<th>ID:</th> <td>".$info['ID'] . "</td> "; 
  88. Print "<th>Time:</th> <td>" .$info['Time'] . "</td>";
  89. Print "<th>Name:</th> <td>".$info['Client'] . " </td>"; 
  90. Print "<th>Number:</th> <td>" .$info['Number'] . "</td>";
  91. Print "<th>Address:</th> <td>" .$info['Address'] . "</td>";
  92. Print "<th>Issue:</th> <td>" .$info['Issue'] . "</td>";
  93. Print "<th>Notes:</th><td>" . $info['Notes'] . "</td>";
  94. Print "<th>Notes:</th> <td>" .$info['Notes2'] . "</td>";
  95. Print "<th>Status:</th> <td>" .$info['Status'] . "</td>";
  96. Print "<th>Charge:</th> <td>$" .$info['Charge'] . "</td></tr>";
  97. Print "</table>"; 
  98. }
  99.  
  100. else {
  101. echo "Wrong Username or Password\r\n";
  102.  
  103. echo "Please try again....<br><br>";
  104. echo '<a href="http://www.1fixcomputermedic.com/login.php">CLICK</a>';
  105.  
  106. }
  107. ?>
  108.  
  109.  
Now for my second problem.

update.php

Expand|Select|Wrap|Line Numbers
  1.  
  2. <html>
  3. <?
  4.  
  5. $ID=$_GET['ID'];
  6. $username="gancsosa";
  7. $password="*****";
  8. $database="gancsosa_crystalworks";
  9. mysql_connect("example.com",$username,$password);
  10. @mysql_select_db($database);
  11. $query=" SELECT * FROM inprogress WHERE ID='$ID";
  12. $result=mysql_query($query);
  13. mysql_close();
  14.  
  15.  
  16. //Print output
  17.  
  18. $Client=mysql_result($result,"Client");
  19. $Number=mysql_result($result,"Number");
  20. $Address=mysql_result($result,"Address");
  21. $Issue=mysql_result($result,"Issue");
  22. $Notes=mysql_result($result,"Notes");
  23. $Status=mysql_result($result,"Status");
  24. $Charge=mysql_result($result,"Charge");
  25.  
  26. ?>
  27.  
  28. <form action="updated.php" method="post">
  29. <input type="hidden" name="ud_ID" value="<? echo $ID; ?>">
  30. Client: <input type="varchar(56)" name="ud_Client" value="<? echo $Client; ?>"><br>
  31. Address: <input type="text" name="ud_Address" value="<? echo $Address; ?>"><br>
  32. Number: <input type="bigint(20)" name="ud_Number" value="<? echo $Number; ?>"><br>
  33. Issue: <input type="longtext" name="ud_Issue" value="<? echo $Issue; ?>"><br>
  34. Notes: <input type="longtext" name="ud_Notes" value="<? echo $Notes; ?>"><br>
  35. Status: <input type="tinyint(1)" name="ud_Status" value="<? echo $Status; ?>"><br>
  36. <input type="Submit" value="Update">
  37. </form>
  38.  
  39. <?
  40. //Update
  41. $ud_Address=$_POST['ud_Address'];
  42. $ud_Number=$_POST['ud_Number'];
  43. $ud_Issue=$_POST['ud_Issue'];
  44. $ud_Notes=$_POST['ud_Notes'];
  45. $ud_Status=$_POST['ud_Status'];
  46.  
  47.  
  48. $username="gancsosa";
  49. $password="*****";
  50. $database="gancsosa_crystalworks";
  51. mysql_connect("example.com",$username,$password);
  52. @mysql_select_db($database) or die( "Unable to select database");
  53.  
  54. $query="UPDATE inprogress SET Address='$ud_Address', Number='$ud_Number', Issue='$ud_Issue', Notes='$ud_Notes2', Status='$ud_Status' WHERE ID='ud_ID' ";
  55. mysql_query($query);
  56. mysql_close();
  57.  
  58. ?>
  59.  
  60.  
  61. </html>
  62.  
  63.  
Any help would be GREATLY appreciated.
Mar 12 '10 #1
35 9101
computerfox
276 100+
Anyone? Any ideas? ..............
Mar 12 '10 #2
computerfox
276 100+
Thank you for replacing my actual information. LOL I was quite worried about everyone seeing it.
Mar 12 '10 #3
Atli
5,058 Expert 4TB
Hey.

What are you having problems with, exactly?
What are you expecting the code to do? What is it actually doing?
Are you getting any error messages? - We LOVE error messages :)
(Keep in mind that we can't actually run the code on our own test machines.)

A few things I notice, though, that may help.
  • In both of your code examples, you connect to MySQL twice. This is unnecessary, and a waste of resources. A single call to the mysql_connect function is enough, unless you have a specific reason for needing to call it more often.
  • On lines #31 and #32 you use the session_register function. This function is deprecated and should not be used anymore. - Instead, you should register session variables like so:
    Expand|Select|Wrap|Line Numbers
    1. $_SESSION['username'] = $username;
  • Indentation - You should always write your code as if the guy who will be maintaining it is a homicidal maniac that knows where you live. It will make your own work on the code easier, and it makes it easier for people like myself - who were not involved in writing it - to work on it.
Mar 12 '10 #4
computerfox
276 100+
Thank you for the response and advise.

No error messages. It's just not transferring the information from one table to another.

When I go into the data page, I want to make sure that the data is in the right tables.
Mar 12 '10 #5
Atli
5,058 Expert 4TB
Ok, so after going a bit more thoroughly over it, I think I've found what is causing your main problem.

#1
In your second script, "update.php", you have a SQL query at the very end of it, line #54. - The problem is the WHERE clause. Instead of using the "ud_ID" from the form (the $_POST array), you are using the name as a static string. That will cause MySQL to either fail with an error (which you don't seem to even check for), or always update the first row. - Either way, you need to replace that with the value passed from the form. (Like you do with the other values in that query.)

#2
Earlier in that script, you do this:
Expand|Select|Wrap|Line Numbers
  1. $Client=mysql_result($result,"Client");
  2. $Number=mysql_result($result,"Number");
  3. $Address=mysql_result($result,"Address");
  4. // etc...
This is incorrect usage of the mysql_result function. Instead, you should be fetching the entire row using the mysql_fetch_assoc function and using the return array you get from that.
Expand|Select|Wrap|Line Numbers
  1. $row = mysql_fetch_assoc($result);
  2. $Client=$row['Client'];
  3. $Number=$row['Number'];
  4. $Address=$row['Address'];
  5. // etc...
#3
On line #13 of the second example, you have a SQL query. It is missing a quotation mark at the end. The single-quote after the $ID is missing. It needs to be added. - Also, there is an extra space in front of the SELECT that should be removed.

#4
I don't see anything in the first code that would "break" it. It should work, even though there are a few things that could use some "tuning". - However, there is one thing that should be removed or fixed. On line #56 you have a SQL query that is just... wrong. - There is no such thing as a UPDATE INTO query. -Not sure exactly what you were trying to do there, but I would simply remove that line.

Hope that helps any.
Mar 12 '10 #6
computerfox
276 100+
LOL!!!! Yeah, I had INSERT INTO and it wouldn't transfer the data so I tried out the UPDATE. LOL!!!! I'm not THAT much of a PHP noob. Am I?
Mar 12 '10 #7
computerfox
276 100+
I did what you told me to, but unfortunately it still doesn't update the data. :(
Mar 12 '10 #8
computerfox
276 100+
I got it to transfer from table to table, but it still doesn't update for some REALLY annoying reason. I'm either being really stupid or something's dumb with the code....
Mar 12 '10 #9
Atli
5,058 Expert 4TB
How does your update script look now?
Mar 12 '10 #10
computerfox
276 100+
Expand|Select|Wrap|Line Numbers
  1.  
  2. <html>
  3. <?
  4.  
  5. $ID=$_GET['ID'];
  6. $username="gancsosa";
  7. $password="************";
  8. $database="gancsosa_crystalworks";
  9. mysql_connect("njcomputermedic.fatcowmysql.com",$username,$password);
  10. mysql_select_db($database);
  11. $query="SELECT*  FROM gancsosa_crystalworks.inprogress WHERE ID='$ud_ID' ";
  12. $result=mysql_query($query);
  13.  
  14.  
  15. //Print output
  16. $row=mysql_fetch_assoc($result);
  17.  
  18. $ID=$row['ID'];
  19. $Client=$row['Client'];
  20. $Number=$row['Number'];
  21. $Address=$row['Address'];
  22. $Issue=$row['Issue'];
  23. $Notes=$row['Notes'];
  24. $Status=$row['Status'];
  25. $Charge=$row['Charge'];
  26.  
  27. ?>
  28.  
  29. <form action="updated.php" method="post">
  30. <input type="hidden" name="ud_ID" value="<? echo $ID; ?>">
  31. Issue: <input type="longtext" name="ud_Issue" value="<? echo $Issue; ?>"><br>
  32. Notes: <input type="longtext" name="ud_Notes" value="<? echo $Notes; ?>"><br>
  33. Status: <input type="tinyint(1)" name="ud_Status" value="<? echo $Status; ?>"><br>
  34. <input type="Submit" value="Update">
  35. </form>
  36.  
  37. <?
  38. //Update
  39. $ud_ID=$_POST['ud_ID'];
  40. $ud_Issue=$_POST['ud_Issue'];
  41. $ud_Notes=$_POST['ud_Notes'];
  42. $ud_Status=$_POST['ud_Status'];
  43.  
  44.  
  45. $username="gancsosa";
  46. $password="***********";
  47. $database="gancsosa_crystalworks";
  48. mysql_connect("*******************",$username,$password);
  49.  
  50. $query="UPDATE  gancsosa_crystalworks.inprogress SET  Issue='$ud_Issue', Notes='$ud_Notes2', Status='$ud_Status' WHERE ID='$ud_ID' ";
  51.  
  52. mysql_query($query);
  53.  
  54. ?>
  55.  
  56.  
  57. </html>
  58.  
Mar 12 '10 #11
Atli
5,058 Expert 4TB
Line #11. The WHERE clause is using the wrong ID variable. It is using $ud_ID where it should be using $ID. (The $ud_ID variable is created later in the code.)
Mar 12 '10 #12
computerfox
276 100+
I changed that and I even tested the value of ID that the _GET function was pulling in and it printed nothing. So I'm guessing it's not updating because it's not pulling in the information because it's not pulling in the ID :(
Mar 12 '10 #13
Atli
5,058 Expert 4TB
How does the URL you are requesting look like?
The "$ID=$_GET['ID'];" line is pulling the ID from the URL, so it should look something like:
http://localhost/update.php?ID=xx
Where "xx" is the ID of the record you are trying to update.
Mar 12 '10 #14
computerfox
276 100+
Okay, so i made it http://localhost/update.php?ID=01.

It takes in the information, but doesn't update the table with new information after submit has been clicked. I'm guessing it's something with my query....
Mar 12 '10 #15
computerfox
276 100+
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?
Mar 13 '10 #16
computerfox
276 100+
Nevermind. I got it.
Mar 13 '10 #17
computerfox
276 100+
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?

Expand|Select|Wrap|Line Numbers
  1.  
  2. <html>
  3. <?
  4. $ID=$_GET['ID'];
  5. $username="***************";
  6. $password="***************";
  7. $database="****************";
  8. mysql_connect("*******************",$username,$password);
  9. mysql_select_db($database);
  10. $query="SELECT*  FROM ********** ";
  11. $result=mysql_query($query);
  12.  
  13.  
  14. //Print output
  15. $row=mysql_fetch_assoc($result);
  16.  
  17. $ID=$row['ID'];
  18. $Time=$row['Time'];
  19. $Client=$row['Client'];
  20. $Number=$row['Number'];
  21. $Address=$row['Address'];
  22. $Issue=$row['Issue'];
  23. $Notes=$row['Notes'];
  24. $Status=$row['Status'];
  25. $Charge=$row['Charge'];
  26.  
  27.  
  28. //Print "ID: $ID<br>";
  29. ?>
  30.  
  31.  
  32. <form method="POST">
  33. <input type="hidden" name="ud_ID" value="<? echo $ID; ?>">
  34. Client: <input type="text" name="ud_Client" value="<? echo $Client; ?>"><br>
  35. Address: <input type="text" name="ud_Address" value="<? echo $Address; ?>"><br>
  36. Number: <input type="bigint(20)" name="ud_Number" value="<? echo $Number; ?>"><br>
  37. Issue: <input type="longtext" name="ud_Issue" value="<? echo $Issue; ?>"><br>
  38. Notes: <input type="longtext" name="ud_Notes" value="<? echo $Notes; ?>"><br>
  39. Status: <input type="tinyint(1)" name="ud_Status" value="<? echo $Status; ?>"><br>
  40. Charge: <input type="double" name="ud_Charge" value="<? echo $Charge; ?>"><br>
  41. <input type="Submit"  value="Update">
  42. </form>
  43.  
  44. <input type="Submit" onClick="window.close()" value="Close Window">
  45. <?
  46.  
  47. //Update
  48. $ud_ID=$_POST['ud_ID'];
  49. $ud_Client=$_POST['ud_Client'];
  50. $ud_Number=$_POST['ud_Number'];
  51. $ud_Address=$_POST['ud_Address'];
  52. $ud_Issue=$_POST['ud_Issue'];
  53. $ud_Notes=$_POST['ud_Notes'];
  54. $ud_Status=$_POST['ud_Status'];
  55.  
  56. //Print "ID: $ID<br>";
  57. //Print "ud_ID: $ud_ID<br>";
  58.  
  59. 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' ");
  60.  
  61. ?>
  62.  
  63.  
  64. </html>
  65.  
  66.  
Mar 17 '10 #18
computerfox
276 100+
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

Expand|Select|Wrap|Line Numbers
  1.  
  2. <div id=icon style="position:absolute;left:900">
  3.  
  4. <img src="http://www.1fixcomputermedic.com/Pictures/Site/ambulance.jpg">
  5.  
  6. </div>
  7.  
  8. <div id=cm style="position:absolute;">
  9.  
  10. <img src="/Pictures/Site/work_order.jpg"><br>
  11.  
  12. <br>
  13.  
  14. </div>
  15.  
  16. <div id=data style="position:absolute;top:300;">
  17. <form id="workOrder" method="POST">
  18. <input type="hidden" name="ID" value="<? echo $ID; ?>">
  19. Client:<input type="text" name="Client" value="<? echo $Client; ?>"><br>
  20. Number:<input type="bigint(20)" name="Number" value="<? echo $Issue; ?>"><br>
  21. Address:<input type="text" name="Address" value="<? echo $Issue; ?>"><br>
  22. Issue: <input type="longtext" name="Issue" value="<? echo $Issue; ?>"><br>
  23. Notes: <input type= "longtext" name="Notes" value= "<? echo $Notes; ?>"><br>
  24. Status: <input type="tinyint(1)" name="Status" value="<? echo $Status; ?>"><br>
  25. Charge: <input type="tinyint(1)" name="Charge" value="<? echo $Charge; ?>"><br>
  26.  
  27. <input type="Submit" value="Submit">
  28. </form>
  29. </div>
  30.  
  31. <?
  32. //Get data from form
  33. $ID=$_POST['ID'];
  34. $Client=$_POST['Client'];
  35. $Number=$_POST['Number'];
  36. $Address=$_POST['Address'];
  37. $Issue=$_POST['Issue'];
  38. $Notes=$_POST['Notes'];
  39. $Status=$_POST['Status'];
  40. $Charge=$_POST['Charge'];
  41.  
  42. //Insert new data into inprogress
  43.  
  44. $query="INSERT INTO inprogress (Client,Time,Number,Address,Issue,Notes,Status,Charge) VALUES ('$Client', NOW(), '$Number', '$Address','$Issue','$Notes','$Status','$Charge') ";
  45.  
  46. mysql_query($query);
  47.  
  48. mysql_close();
  49. ?>
  50.  
  51.  
  52. </html>
  53.  
  54.  
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?
Mar 17 '10 #19
Atli
5,058 Expert 4TB
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:
Expand|Select|Wrap|Line Numbers
  1. <?php
  2. // Check if the updated values have been sent.
  3. if(isset($_POST['id'], $_POST['newValue1'], $_POST['newValue2']))
  4. {
  5.     // Fetch the updated values, making sure they
  6.     // are safe to use in the SQL query.
  7.     $id = (int)$_POST['id'];
  8.     $newValue1 = mysql_real_escape_string($_POST['newValue1']);
  9.     $newValue2 = mysql_real_escape_string($_POST['newValue2']);
  10.  
  11.     // Create and execute the SQL query
  12.     $sql = "UPDATE `table` SET
  13.                 `value1` = '$newValue1',
  14.                 `value2` = '$newValue2'
  15.             WHERE
  16.                 `id` = $id
  17.             LIMIT 1";
  18.     $result = mysql_query($sql);
  19.  
  20.     // Make sure the SQL query was successful
  21.     if($result) {
  22.         echo "Updated!";
  23.     }
  24.     else {
  25.         echo "Error! " . mysql_error();
  26.     }
  27. }
  28. else
  29. {
  30.     // Get and "id" from the URL, or default to 1
  31.     ($id = @$_GET['id']) or $id = 1;
  32.  
  33.     // Fetch values
  34.     $sql = "SELECT `value1`, `value2` 
  35.             FROM `table` 
  36.             WHERE `id` = $id";
  37.     $result = mysql_query($sql) or die("Failed to fetch values. " . mysql_error());
  38.     $row = mysql_fetch_assoc($result);
  39.  
  40.     // Prepare values for being printed into HTML
  41.     $oldValue1 = htmlentities($row['value1'], ENT_QUOTES, 'UTF-8');
  42.     $oldValue2 = htmlentities($row['value2'], ENT_QUOTES, 'UTF-8');
  43.  
  44.     // Print the HTML
  45.     echo <<<HTML
  46. <form action="{$_SERVER['PHP_SELF']}" method="post">
  47.     <input type="hidden" name="id" value="{$id}">
  48.     Value1: <input type="text" name="newValue1" value="{$oldValue1}"><br>
  49.     Value2: <input type="text" name="newValue2" value="{$oldValue2}"><br>
  50.     <input type="submit" value="Update">
  51. </form>
  52. HTML;
  53.  
  54. }
  55. ?>
Mar 17 '10 #20
computerfox
276 100+
I did what you said, and it's still having problems updating :(

This is how my script looks now:

Expand|Select|Wrap|Line Numbers
  1.  
  2. <html>
  3. <?
  4.  
  5. if(isset($ud_ID,$ud_Client, $ud_Number,$ud_Address,$ud_Issue,$ud_Notes,$ud_Status,$ud_Charge)){
  6.  
  7. //Update
  8. $ud_ID=$_POST['ud_ID'];
  9. $ud_Client=$_POST['ud_Client'];
  10. $ud_Number=$_POST['ud_Number'];
  11. $ud_Address=$_POST['ud_Address'];
  12. $ud_Issue=$_POST['ud_Issue'];
  13. $ud_Notes=$_POST['ud_Notes'];
  14. $ud_Status=$_POST['ud_Status'];
  15. $ud_Charge=$_POST['ud_Charge'];
  16.  
  17. $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 ";
  18.  
  19.  
  20.  mysql_query("$query");
  21. }
  22.  
  23. else{
  24. $ID=$_GET['ID'];
  25. $username="*************";
  26. $password="**************";
  27. $database="***************";
  28. mysql_connect("**************",$username,$password);
  29. mysql_select_db($database);
  30. $query="SELECT*  FROM inprogress ";
  31. $result=mysql_query($query);
  32.  
  33.  
  34. //Print output
  35. $row=mysql_fetch_assoc($result);
  36.  
  37. $ID=$row['ID'];
  38. $Time=$row['Time'];
  39. $Client=$row['Client'];
  40. $Number=$row['Number'];
  41. $Address=$row['Address'];
  42. $Issue=$row['Issue'];
  43. $Notes=$row['Notes'];
  44. $Status=$row['Status'];
  45. $Charge=$row['Charge'];
  46. }
  47. ?>
  48.  
  49.  
  50. <form method="POST">
  51. <input type="hidden" name="ud_ID" value="<? echo $ID; ?>">
  52. Client: <input type="text" name="ud_Client" value="<? echo $Client; ?>"><br>
  53. Address: <input type="text" name="ud_Address" value="<? echo $Address; ?>"><br>
  54. Number: <input type="bigint(20)" name="ud_Number" value="<? echo $Number; ?>"><br>
  55. Issue: <input type="longtext" name="ud_Issue" value="<? echo $Issue; ?>"><br>
  56. Notes: <input type="longtext" name="ud_Notes" value="<? echo $Notes; ?>"><br>
  57. Status: <input type="tinyint(1)" name="ud_Status" value="<? echo $Status; ?>"><br>
  58. Charge: <input type="double" name="ud_Charge" value="<? echo $Charge; ?>"><br>
  59. <input type="Submit"  value="Update">
  60. </form>
  61.  
  62. <input type="Submit" onClick="window.close()" value="Close Window">
  63.  
  64. <?
  65.  
  66.  
  67.  
  68.  
  69. //mysql_close();
  70.  
  71. ?>
  72.  
  73.  
  74. </html>
  75.  
  76.  
Also, when would I use a "@" and I recently bought an SSL, so would that effect anything with how the script works?
Mar 17 '10 #21
Atli
5,058 Expert 4TB
Your code is not structured correctly. This is the basic idea of what you should be doing:
Expand|Select|Wrap|Line Numbers
  1. if ( the data has been posted ) {
  2.     fetch the posted data,
  3.     create the UPDATE query,
  4.     and execute the query.
  5. }
  6. ELSE {
  7.     fetch an ID from the URL or use 1 if there is no ID in the URL
  8.     fetch the row data from MySQL
  9.     insert the data into the HTML form and print it
  10. }
There are a number of problems in your code:
  1. 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.)
  2. 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.
  3. 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.
  4. 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.)
  5. 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:
Expand|Select|Wrap|Line Numbers
  1. ($id = @$_GET['id']) or $id = 1;
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.
Mar 17 '10 #22
computerfox
276 100+
Expand|Select|Wrap|Line Numbers
  1.  
  2. <?
  3.  
  4. 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'])){
  5.  
  6. //Update
  7. $ud_ID=mysql_real_escape_string($_POST['ud_ID']);
  8. $ud_Client=mysql_real_escape_string($_POST['ud_Client']);
  9. $ud_Number=mysql_real_escape_string($_POST['ud_Number']);
  10. $ud_Address=mysql_real_escape_string($_POST['ud_Address']);
  11. $ud_Issue=mysql_real_escape_string($_POST['ud_Issue']);
  12. $ud_Notes=mysql_real_escape_string($_POST['ud_Notes']);
  13. $ud_Status=mysql_real_escape_string($_POST['ud_Status']);
  14. $ud_Charge=mysql_real_escape_string($_POST['ud_Charge']);
  15. //16
  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 ";
  17.  
  18. $username="";
  19. $password="";
  20. $database="";
  21. mysql_connect("",$username,$password);
  22. mysql_select_db($database);
  23. $query="SELECT*  FROM inprogress ";
  24. $result=mysql_query($query);
  25. //28
  26.  mysql_query("$query");
  27. }
  28.  
  29. else{
  30.  
  31. $ID=@$_GET['ID'] or $ID=1;
  32. $username="";
  33. $password="";
  34. $database="";
  35. mysql_connect("",$username,$password);
  36. mysql_select_db($database);
  37. $query="SELECT*  FROM inprogress WHERE ID=$ID ";
  38. $result=mysql_query($query);
  39.  
  40. $row=mysql_fetch_assoc($result);
  41.  
  42.     $Client = htmlentities($row['Client'], ENT_QUOTES, 'UTF-8');
  43.     $Address = htmlentities($row['Address'], ENT_QUOTES, 'UTF-8');
  44.     $Number = htmlentities($row['Number'], ENT_QUOTES, 'UTF-8');
  45.     $Isse = htmlentities($row['Issue'], ENT_QUOTES, 'UTF-8');
  46.     $Notes = htmlentities($row['Notes'], ENT_QUOTES, 'UTF-8');
  47.     $Charge = htmlentities($row['Charge'], ENT_QUOTES, 'UTF-8');
  48.     $Status = htmlentities($row['Status'], ENT_QUOTES, 'UTF-8');
  49.  
  50.  echo <<<HTML;
  51.  
  52. <form action="{$_SERVER['PHP_SELF']}" method="POST">
  53. <input type="hidden" name="ud_ID" value="<? echo $ID; ?>">
  54. Client: <input type="text" name="ud_Client" value="<? echo $Client; ?>"><br>
  55. Address: <input type="text" name="ud_Address" value="<? echo $Address; ?>"><br>
  56. Number: <input type="bigint(20)" name="ud_Number" value="<? echo $Number; ?>"><br>
  57. Issue: <input type="longtext" name="ud_Issue" value="<? echo $Issue; ?>"><br>
  58. Notes: <input type="longtext" name="ud_Notes" value="<? echo $Notes; ?>"><br>
  59. Status: <input type="tinyint(1)" name="ud_Status" value="<? echo $Status; ?>"><br>
  60. Charge: <input type="double" name="ud_Charge" value="<? echo $Charge; ?>"><br>
  61. <input type="Submit"  value="Update">
  62. </form>
  63.  
  64.  
  65. //<input type="Submit" onClick="window.close()" value="Close Window">
  66.  
  67. HTML;
  68.  
  69. }
  70.  
  71. ?>
  72.  
This give me an error. What did I do wrong?
Mar 18 '10 #23
Atli
5,058 Expert 4TB
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:
Expand|Select|Wrap|Line Numbers
  1. Client: <input type="text" name="ud_Client" value="{$Client}"><br>
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.)
Mar 18 '10 #24
computerfox
276 100+
I changed it, but now it's saying that the fetch isn't valid on line 39.... WTF?

Expand|Select|Wrap|Line Numbers
  1. <?
  2.  
  3. 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'])){
  4.  
  5. //Update
  6. $ud_ID=mysql_real_escape_string($_POST['ud_ID']);
  7. $ud_Client=mysql_real_escape_string($_POST['ud_Client']);
  8. $ud_Number=mysql_real_escape_string($_POST['ud_Number']);
  9. $ud_Address=mysql_real_escape_string($_POST['ud_Address']);
  10. $ud_Issue=mysql_real_escape_string($_POST['ud_Issue']);
  11. $ud_Notes=mysql_real_escape_string($_POST['ud_Notes']);
  12. $ud_Status=mysql_real_escape_string($_POST['ud_Status']);
  13. $ud_Charge=mysql_real_escape_string($_POST['ud_Charge']);
  14.  
  15. $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 ";
  16.  
  17. $username="";
  18. $password="";
  19. $database="";
  20. mysql_connect("",$username,$password);
  21. mysql_select_db($database);
  22. $query="SELECT*  FROM inprogress WHERE ID=$ID";
  23. $result=mysql_query($query);
  24.  
  25.  mysql_query($query);
  26. }
  27.  
  28. else{
  29.  
  30. $ID=@$_GET['ID'] or $ID=1;
  31. $username="";
  32. $password="";
  33. $database="";
  34. mysql_connect("",$username,$password);
  35. //mysql_select_db($database);
  36. $query="SELECT*  FROM gancsosa_crystalworks.inprogress WHERE ID=$ID ";
  37. $result=mysql_query($query);
  38.  
  39. $row=mysql_fetch_assoc($result);
  40.  
  41.     $Client = htmlentities($row['Client'], ENT_QUOTES, 'UTF-8');
  42.     $Address = htmlentities($row['Address'], ENT_QUOTES, 'UTF-8');
  43.     $Number = htmlentities($row['Number'], ENT_QUOTES, 'UTF-8');
  44.     $Isse = htmlentities($row['Issue'], ENT_QUOTES, 'UTF-8');
  45.     $Notes = htmlentities($row['Notes'], ENT_QUOTES, 'UTF-8');
  46.     $Charge = htmlentities($row['Charge'], ENT_QUOTES, 'UTF-8');
  47.     $Status = htmlentities($row['Status'], ENT_QUOTES, 'UTF-8');
  48.  
  49.  echo <<<HTML
  50.  
  51. <form action="{$_SERVER['PHP_SELF']}" method="POST">
  52. <input type="hidden" name="ud_ID" value="{$ID}">
  53. Client: <input type="text" name="ud_Client" value="{$Client}"><br>
  54. Address: <input type="text" name="ud_Address" value="{$Address}"<br>
  55. Number: <input type="bigint(20)" name="ud_Number" value="{$Number}"><br>
  56. Issue: <input type="longtext" name="ud_Issue" value="{$Issue}"><br>
  57. Notes: <input type="longtext" name="ud_Notes" value="{$Notes}"><br>
  58. Status: <input type="tinyint(1)" name="ud_Status" value="{$Status}"><br>
  59. Charge: <input type="double" name="ud_Charge" value="{$Charge}"><br>
  60. <input type="Submit"  value="Update">
  61. </form>
  62.  
  63.  
  64. <input type="Submit" onClick="window.close()" value="Close Window">
  65.  
  66. HTML;
  67.  
  68. }
  69.  
  70. ?>
  71.  
Mar 18 '10 #25
computerfox
276 100+
123456789123456789123456789
Mar 18 '10 #26
computerfox
276 100+
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
Mar 18 '10 #27
computerfox
276 100+
any ideas how i can do that?
Mar 18 '10 #28
computerfox
276 100+
Expand|Select|Wrap|Line Numbers
  1.  
  2. Print '<th><a href="https://www.1fixcomputermedic.com/update.php?id " target="_blank">Edit</a></th>';
  3.  
  4.  
any ideas why this doesn't bring in id as a variable?
Mar 18 '10 #29
Atli
5,058 Expert 4TB
The "id" in the URL of that link has no value. Where is the value supposed to be coming from?
Mar 18 '10 #30
computerfox
276 100+
Expand|Select|Wrap|Line Numbers
  1.  
  2. $id="_GET['ID']";
  3.  
  4. while($info = mysql_fetch_array( $data )) 
  5.  
  6. Print "<tr>";
  7. Print "<th>ID: ".$info['ID'] . "</th> "; 
  8. Print "<th>Time: " .$info['Time'] . "</th>";
  9. Print "<th>Name: ".$info['Client'] . " </th>"; 
  10. Print "<th>Number: " .$info['Number'] . "</th>";
  11. Print "<th>Address: " .$info['Address'] . "</th>";
  12. Print "<th>Issue: " .$info['Issue'] . "</th>";
  13. Print "<th>Notes: " . $info['Notes'] . "</th>";
  14. Print "<th>Status: " .$info['Status'] . "</th>";
  15. Print "<th>Charge: $" .$info['Charge'] . "</th>";
  16. Print '<th><a href="https://www.1fixcomputermedic.com/update.php?ID="$id" " target="_blank">Edit</a></th>';
  17. Print '<th><a href="" target="_blank">Print</a></th>';
  18. Print "</tr>";
  19.  
  20.  
Mar 18 '10 #31
Atli
5,058 Expert 4TB
Ok. If you want the link to include the ID, you need to put the ID into the URL, in the same way you do with the lines above it. - The links should look like: http://example.com/page.php?name=value where "name" is the name of the variable you want to pass ("id" in your case) and "value" is the value of it. - Your URL is just missing the "value" part.
Mar 18 '10 #32
computerfox
276 100+
is there some way i can make the value part a variable as in so that i can put it into a loop?
Mar 18 '10 #33
Atli
5,058 Expert 4TB
Explain this looping a little better.
Do you want to display multiple values on a single page, or do you want to do something like... updating one row, and be automatically sent to the next row when done?

P.S.
Expand|Select|Wrap|Line Numbers
  1. $id="_GET['ID']";
  2. // Should be
  3. $id=$_GET['ID'];
See Strings and Superglobals in the manual.
Mar 18 '10 #34
computerfox
276 100+
this is where i print out my data, well, at least my inprogress data.

then i have a link for the update page, which i'm having trouble with.

after the data has been updated, i have a script where it puts it into the correct table.

i want the link to have a variable instead of having ?id=1, instead, i want to make it something like ?ID=$id, where $id is the id of the row, which i will be updating. in other words, it should be a link to the unique update page.
Mar 18 '10 #35
computerfox
276 100+
Question:

login.php-login information

checklogin.php:

{ retrieve cookie if possible

else

set cookie
}

update.php

the action for the form is set to go back to checklogin.php, but it doesn't save the username and password. why would that be?

ALSO

i still need help making that id link compatible with my loop in the script. any help would be greatly appreciated.
Mar 19 '10 #36

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Rani | last post: by
15 posts views Thread by http://www.visual-basic-data-mining.net/forum | last post: by
5 posts views Thread by meetalps | last post: by
OuTCasT
reply views Thread by OuTCasT | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.