Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- column1 | column2 | column3
- value1 | value1 | -------
- value2 | value2 | ------- <=== this column3 rows does not have value in database yet so it will be blank when I retrieved the data
- $(document).ready(function(){
- $("#RetrieveList").on('click',function() {
- var status = $('#status').val();
- var date = $('#Date').val();
- var date1 = $('#Date1').val();
- $.post('retrieve.php',{status:status, date:date, date1:date1}, function(data){
- $("#results").html(data);
- });
- return false;
- });
- <?php
- $servername = "localhost";
- $username = "root";
- $password = "";
- $dbname = "sample_db";
- // check data before use it and convert from string to expected type, use try, not like here:
- $date = $_POST['date'];
- $date1 = $_POST['date1'];
- // use valid data to select rows
- try {
- //1. connect to MySQL database
- $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
- //2. set the PDO error mode to exception
- $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
- //3. create query string (here is answer on your question)
- $sql = 'SELECT column1, column2, column3 FROM sample_table WHERE scheduled_start_date BETWEEN :d1 AND :d2';
- //4. prepare statement from query string
- $stmt = $conn->prepare($sql);
- //5. bind optional parameters
- //if ($status != 'All') $stmt->bindParam(':st', $status);
- //6. bind parameters
- $stmt->bindParam(':d1', $date);
- $stmt->bindParam(':d2', $date1);
- //7. execute statement
- $stmt->execute();
- //8. returns an array containing all of the result set rows
- $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
- //get count of rows
- $numrow = count($result);
- //print array - there is many solution to print array,
- //to debug you can do:
- //print_r($result);
- } catch(PDOException $e) {
- echo "Error: " . $e->getMessage();
- }
- $conn = null;
- if($numrow == 0)
- echo "No results found.";
- else
- echo "Count: $numrow</br>";
- {
- echo "<table width='auto' cellpadding='1px' cellspacing='0px' border=1 align='center'>
- <tr>
- <!--<th align='center'><input id=checkall name=checkall id=checkall type=checkbox value='' /></th>-->
- <th align='center'><strong>Column1</strong></th>
- <th align='center'><strong>Column2</strong></th>
- <th align='center'><strong>Column3</strong></th>
- </tr>";
- foreach ($result as $row => $info) {
- echo "<form action='crqretrieve_status.php' method='post'>";
- echo"<tr>";
- echo "<td align='center'>" . $info['column1'] . "<input type=hidden name=column1 value=" . $info['column1'] . " </td>";
- echo "<td align='center'>" . $info['column2'] . "<input type=hidden name=column2 value=" . $info['column2'] . " </td>";
- echo "<td align='center'>" . "<input name=column3 value='' </td>";
- echo "</tr>";
- echo "</form>";
- }
- }
- echo "</table>";
- ?>
- // if the 'id' variable is set in the URL, we know that we need to edit a record
- if (isset($_GET['id']))
- {
- // if the form's submit button is clicked, we need to process the form
- if (isset($_POST['submit']))
- {
- // make sure the 'id' in the URL is valid
- if (is_numeric($_POST['id']))
- {
- // get variables from the URL/form
- $id = $_POST['id'];
- $column1 = $_POST['column1'];
- $column2 = htmlentities($_POST['column2'], ENT_QUOTES);
- $column3 = htmlentities($_POST['column3'], ENT_QUOTES);
- $column4 = htmlentities($_POST['column4'], ENT_QUOTES);
- // check that fields are not empty
- if ($column1 == '' || $column2 == '' || $column3 == ''|| $column4 == '')
- {
- // if they are empty, show an error message and display the form
- $error = 'ERROR: Please fill in all required fields!';
- renderForm($column1, $column2, $column3, $column4, $error, $id);
- }
- else
- {
- // if everything is fine, update the record in the database
- if ($stmt = $mysqli->prepare("UPDATE sample_table SET column1 = ?, column2 = ?, column3 = ?, column4 = ?
- WHERE id=?"))
- {
- $stmt->bind_param("ssssi", $column1, $column2, $column3, $column4, $id);
- $stmt->execute();
- $stmt->close();
- }
- // show an error message if the query has an error
- else
- {
- echo "ERROR: could not prepare SQL statement.";
- }
- // redirect the user once the form is updated
- header("Location: list.php");
- }
- }
- // if the 'id' variable is not valid, show an error message
- else
- {
- echo "Error!";
- }
- }
- // if the form hasn't been submitted yet, get the info from the database and show the form
- else
- {
- // make sure the 'id' value is valid
- if (is_numeric($_GET['id']) && $_GET['id'] > 0)
- {
- // get 'id' from URL
- $id = $_GET['id'];
- // get the record from the database
- if($stmt = $mysqli->prepare("SELECT column1, column2, column3, column4 FROM sample_table WHERE id=?"))
- {
- $stmt->bind_param("i", $id);
- $stmt->execute();
- $stmt->bind_result($column1, $column2, $column3, $column4);
- $stmt->fetch();
- // show the form
- renderForm($column1, $column2, $column3, $column4, NULL, $id);
- $stmt->close();
- }
- // show an error if the query has an error
- else
- {
- echo "Error: could not prepare SQL statement";
- }
- }
- // if the 'id' value is not valid, redirect the user back to the view.php page
- else
- {
- header("Location: list.php");
- }
- }
- }
- // close the mysqli connection
- $mysqli->close();
- $column1Values = array();
- $column1Values[42] = "abc"; // key is the id in the database
- $column1Values[306] = "def";
- // ...
- $column2Values = array();
- $column2Values[42] = "ghi";
- $column2Values[306] = "jkl";
- // ...
- if ($stmt = $mysqli->prepare("UPDATE sample_table SET column1 = ?, column2 = ? WHERE id=?"))
- {
- $stmt->bind_param("ssi", $column1, $column2, $id);
- for ($i=0; $i<count($column1Values); $i++) {
- $column1 = $column1Values[$ids[$i]];
- $column2 = $column2Values[$ids[$i]];
- $id = $ids[$i];
- $stmt->execute();
- }
- $stmt->close();
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement