Unable to update table using PHP Script

Question

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;

Have you checked whether the connection succeeds? Instead of printing your own error message try out with the actual error message — Linga
– Linga, Commented Aug 12, 2014 at 7:21
@ling.s Yes..! I have checked it.. there is no problem in connection. I think problem is on query line.. — Namo
– Namo, Commented Aug 12, 2014 at 7:23
Could you please var_dump out $result straight after the query is executed and also add the following line var_dump(mysqli_error()) and return it's output too, so it looks like this gist. This will give us better insight into the issue . — Jujhar Singh
– Jujhar Singh, Commented Aug 12, 2014 at 7:25

kubik_rubic · Accepted Answer · 2014-08-12 07:31:04Z

1

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.

edited Aug 12, 2014 at 7:31

answered Aug 12, 2014 at 7:27

kubik_rubic

1336 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jangya satapathy · Accepted Answer · 2014-08-12 07:29:42Z

0

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

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

answered Aug 12, 2014 at 7:29

Jangya satapathy

9069 silver badges20 bronze badges

Comments

Manwal · Accepted Answer · 2014-08-12 07:34:56Z

0

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);

answered Aug 12, 2014 at 7:34

Manwal

23.9k15 gold badges65 silver badges95 bronze badges

Collectives™ on Stack Overflow

Unable to update table using PHP Script

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related