CODEWITHSUNDEEP
phpmysql
Namo
Namo

Reputation: 157

Unable to update table using PHP Script

I have written a php-script to update a table. Please refer to the the following code: 

<?php

/*
 * Following code will update view-status of seen queries
 * limit value is read from HTTP Post Request
 */

// array for JSON response
$response = array();

// check for required fields
if (isset($_POST['limit'])) 
{

    $limit = $_POST['limit'];

    //echo "limit= " . $limit;
    // include db connect class
    require_once __DIR__ . '/db_connect.php';

    // connecting to db
    $db = new DB_CONNECT();

    $con = mysqli_connect(DB_SERVER, DB_USER, DB_PASSWORD, DB_DATABASE);

    if (mysqli_connect_errno()) 
    {
       echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }
    // mysql Updating view_status
    $result=mysqli_query($con,"UPDATE tbl_query_master t1 INNER JOIN (SELECT query_id FROM tbl_query_master WHERE view_status=0 ORDER BY query_date ASC LIMIT '".$limit."') as t2 on t1.query_id = t2.query_id SET t1.view_status=1");

    if($result)
    {
      // Record updated successfully
      $response["success"] = 1;
      $response["message"] = "Updated successfully.";
    }
   else
   {
        // Record updated successfully
      $response["success"] = 0;
      $response["message"] = "Oops! An error occurred.";
   }

     // echoing JSON response
     echo json_encode($response);
}
else 
{
    // required field is missing
    $response["success"] = 0;
    $response["message"] = "Required field(s) is missing";

    // echoing JSON response
    echo json_encode($response);
}
?>

But for some unknown reason my update query above is not working and is giving me the following error: 

"{"success":0,"message":"Oops! An error occurred."}"

However, if I execute following query manually then it works perfectly.

  UPDATE tbl_query_master t1 
  INNER JOIN (SELECT query_id 
  FROM tbl_query_master WHERE view_status=0 
  ORDER BY query_date ASC LIMIT 2) as t2 
  on t1.query_id = t2.query_id SET t1.view_status=1;

Upvotes: 0

Views: 70

Answers (3)

Manwal
Manwal

Reputation: 23816

Use bind_param

$stm=mysqli_prepare($con,"UPDATE tbl_query_master t1 INNER JOIN (SELECT query_id FROM tbl_query_master WHERE view_status=0 ORDER BY query_date ASC LIMIT ?) as t2 on t1.query_id = t2.query_id SET t1.view_status=1");
                                                                                                                                                         ^
$stmt->bind_param('d', $limit);

/* execute prepared statement */
$stmt->execute();

printf("%d Row updated.\n", $stmt->affected_rows);

Upvotes: 0

kubik_rubic
kubik_rubic

Reputation: 133

Try to change string

$result=mysqli_query($con,"UPDATE tbl_query_master t1 INNER JOIN (SELECT query_id FROM tbl_query_master WHERE view_status=0 ORDER BY query_date ASC LIMIT '".$limit."') as t2 on t1.query_id = t2.query_id SET t1.view_status=1");

to

$result=mysqli_query($con,"UPDATE tbl_query_master t1 INNER JOIN (SELECT query_id FROM tbl_query_master WHERE view_status=0 ORDER BY query_date ASC LIMIT ".$limit.") as t2 on t1.query_id = t2.query_id SET t1.view_status=1");

You don't need single quotes around $limit value.

Upvotes: 1

Jangya satapathy
Jangya satapathy

Reputation: 906

Have not tested it.but hope it will help you it seems to be change on

 $result=mysqli_query('your query',$con);

Upvotes: 0

Related Questions