Advertisement
Guest User

Untitled

a guest
Aug 8th, 2017
89
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 5.61 KB | None | 0 0
  1. column1 | column2 | column3
  2. value1 | value1 | -------
  3. value2 | value2 | ------- <=== this column3 rows does not have value in database yet so it will be blank when I retrieved the data
  4.  
  5. $(document).ready(function(){
  6. $("#RetrieveList").on('click',function() {
  7. var status = $('#status').val();
  8. var date = $('#Date').val();
  9. var date1 = $('#Date1').val();
  10. $.post('retrieve.php',{status:status, date:date, date1:date1}, function(data){
  11. $("#results").html(data);
  12. });
  13. return false;
  14. });
  15.  
  16. <?php
  17. $servername = "localhost";
  18. $username = "root";
  19. $password = "";
  20. $dbname = "sample_db";
  21.  
  22. // check data before use it and convert from string to expected type, use try, not like here:
  23. $date = $_POST['date'];
  24. $date1 = $_POST['date1'];
  25. // use valid data to select rows
  26. try {
  27. //1. connect to MySQL database
  28. $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
  29.  
  30. //2. set the PDO error mode to exception
  31. $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  32.  
  33. //3. create query string (here is answer on your question)
  34. $sql = 'SELECT column1, column2, column3 FROM sample_table WHERE scheduled_start_date BETWEEN :d1 AND :d2';
  35.  
  36. //4. prepare statement from query string
  37. $stmt = $conn->prepare($sql);
  38.  
  39. //5. bind optional parameters
  40. //if ($status != 'All') $stmt->bindParam(':st', $status);
  41.  
  42. //6. bind parameters
  43. $stmt->bindParam(':d1', $date);
  44. $stmt->bindParam(':d2', $date1);
  45.  
  46. //7. execute statement
  47. $stmt->execute();
  48.  
  49. //8. returns an array containing all of the result set rows
  50. $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
  51.  
  52. //get count of rows
  53. $numrow = count($result);
  54.  
  55. //print array - there is many solution to print array,
  56. //to debug you can do:
  57. //print_r($result);
  58.  
  59. } catch(PDOException $e) {
  60. echo "Error: " . $e->getMessage();
  61. }
  62. $conn = null;
  63.  
  64. if($numrow == 0)
  65. echo "No results found.";
  66. else
  67. echo "Count: $numrow</br>";
  68. {
  69.  
  70. echo "<table width='auto' cellpadding='1px' cellspacing='0px' border=1 align='center'>
  71. <tr>
  72. <!--<th align='center'><input id=checkall name=checkall id=checkall type=checkbox value='' /></th>-->
  73. <th align='center'><strong>Column1</strong></th>
  74. <th align='center'><strong>Column2</strong></th>
  75. <th align='center'><strong>Column3</strong></th>
  76. </tr>";
  77.  
  78. foreach ($result as $row => $info) {
  79. echo "<form action='crqretrieve_status.php' method='post'>";
  80. echo"<tr>";
  81. echo "<td align='center'>" . $info['column1'] . "<input type=hidden name=column1 value=" . $info['column1'] . " </td>";
  82. echo "<td align='center'>" . $info['column2'] . "<input type=hidden name=column2 value=" . $info['column2'] . " </td>";
  83. echo "<td align='center'>" . "<input name=column3 value='' </td>";
  84. echo "</tr>";
  85. echo "</form>";
  86. }
  87. }
  88. echo "</table>";
  89.  
  90. ?>
  91.  
  92. // if the 'id' variable is set in the URL, we know that we need to edit a record
  93. if (isset($_GET['id']))
  94. {
  95. // if the form's submit button is clicked, we need to process the form
  96. if (isset($_POST['submit']))
  97. {
  98. // make sure the 'id' in the URL is valid
  99. if (is_numeric($_POST['id']))
  100. {
  101. // get variables from the URL/form
  102. $id = $_POST['id'];
  103. $column1 = $_POST['column1'];
  104. $column2 = htmlentities($_POST['column2'], ENT_QUOTES);
  105. $column3 = htmlentities($_POST['column3'], ENT_QUOTES);
  106. $column4 = htmlentities($_POST['column4'], ENT_QUOTES);
  107.  
  108. // check that fields are not empty
  109. if ($column1 == '' || $column2 == '' || $column3 == ''|| $column4 == '')
  110. {
  111. // if they are empty, show an error message and display the form
  112. $error = 'ERROR: Please fill in all required fields!';
  113. renderForm($column1, $column2, $column3, $column4, $error, $id);
  114. }
  115. else
  116. {
  117. // if everything is fine, update the record in the database
  118. if ($stmt = $mysqli->prepare("UPDATE sample_table SET column1 = ?, column2 = ?, column3 = ?, column4 = ?
  119. WHERE id=?"))
  120. {
  121. $stmt->bind_param("ssssi", $column1, $column2, $column3, $column4, $id);
  122. $stmt->execute();
  123. $stmt->close();
  124. }
  125. // show an error message if the query has an error
  126. else
  127. {
  128. echo "ERROR: could not prepare SQL statement.";
  129. }
  130.  
  131. // redirect the user once the form is updated
  132. header("Location: list.php");
  133. }
  134. }
  135. // if the 'id' variable is not valid, show an error message
  136. else
  137. {
  138. echo "Error!";
  139. }
  140. }
  141. // if the form hasn't been submitted yet, get the info from the database and show the form
  142. else
  143. {
  144. // make sure the 'id' value is valid
  145. if (is_numeric($_GET['id']) && $_GET['id'] > 0)
  146. {
  147. // get 'id' from URL
  148. $id = $_GET['id'];
  149.  
  150. // get the record from the database
  151. if($stmt = $mysqli->prepare("SELECT column1, column2, column3, column4 FROM sample_table WHERE id=?"))
  152. {
  153. $stmt->bind_param("i", $id);
  154. $stmt->execute();
  155.  
  156. $stmt->bind_result($column1, $column2, $column3, $column4);
  157. $stmt->fetch();
  158.  
  159. // show the form
  160. renderForm($column1, $column2, $column3, $column4, NULL, $id);
  161.  
  162. $stmt->close();
  163. }
  164. // show an error if the query has an error
  165. else
  166. {
  167. echo "Error: could not prepare SQL statement";
  168. }
  169. }
  170. // if the 'id' value is not valid, redirect the user back to the view.php page
  171. else
  172. {
  173. header("Location: list.php");
  174. }
  175. }
  176. }
  177.  
  178. // close the mysqli connection
  179. $mysqli->close();
  180.  
  181. $column1Values = array();
  182. $column1Values[42] = "abc"; // key is the id in the database
  183. $column1Values[306] = "def";
  184. // ...
  185.  
  186. $column2Values = array();
  187. $column2Values[42] = "ghi";
  188. $column2Values[306] = "jkl";
  189. // ...
  190.  
  191. if ($stmt = $mysqli->prepare("UPDATE sample_table SET column1 = ?, column2 = ? WHERE id=?"))
  192. {
  193. $stmt->bind_param("ssi", $column1, $column2, $id);
  194. for ($i=0; $i<count($column1Values); $i++) {
  195. $column1 = $column1Values[$ids[$i]];
  196. $column2 = $column2Values[$ids[$i]];
  197. $id = $ids[$i];
  198. $stmt->execute();
  199. }
  200. $stmt->close();
  201. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement