Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

MYSQL Update with result

Posted on 2014-02-21
8
Medium Priority
?
580 Views
Last Modified: 2014-02-25
I have a simple mysql update query with an IF statement in it:

function questionResponseToStack()
{
	$id = $_POST['id'];
	
	$sql = "UPDATE questionresponses SET questionResponseDateUpdated = CURDATE(), questionResponseTimeUpdated = CURTIME(), questionResponseToStack = IF(questionResponseToStack=1, 0, 1) WHERE questionResponseID = '$id'";
	$result = mysql_query($sql);

	//handle your query.
	if ($result) {
		 $ajaxResponse['status'] = "success";
	} else {
		 $ajaxResponse['status'] = "failure";
		 $ajaxResponse['message'] = "There was a problem: " . mysql_error();
	}
	echo json_encode($ajaxResponse);
}

Open in new window


I want to be able to report back in the json if the record was changed to a 1 or 0. Can this be done easily and efficiently?
0
Comment
Question by:Steve Tinsley
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39877386
I'm not sure without making some changes to the SQL.  The reason I say that is that UPDATE queries may find and match rows, but unless there is an actual change in the contents of the row, the UPDATE did not occur and the number of affected rows will not increase.  So you may want to think through what is a success vs a failure.  MySQL will return TRUE whether any rows were updated or not.

In a semi-related matter, you've got a data base conversion ahead of you.  This article explains why PHP is eliminating the MySQL extension and what you must do to keep your scripts running.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html
0
 

Author Comment

by:Steve Tinsley
ID: 39877440
Oh! Any chance you have any pointers to help with the query?

Yeah I know the MySQL / PHP change but haven't got time to make change yet. The software runs locally offline on my webserver so I have no risk in it changing overnight.
0
 
LVL 34

Expert Comment

by:Slick812
ID: 39877584
greetings  sjtinsley83, , As far as I have seen, there are few (if any) effective ways to "Combine" an UPDATE query and a SELECT query into one Query that updates AND returns a table column value,
So, What I would do for a simple update like this, is to do a SELECT first maybe as -
"SELECT questionResponseToStack FROM questionresponses  WHERE questionResponseID = '$id'";
and after sql query get the value as
$questionRes = $row['questionResponseToStack'];
$oppositeRes = 1;
if ($questionRes)  $oppositeRes = 0;

do your UPDATE query and then in the return message have -

if ($result) {
    $ajaxResponse['status'] = "success";
    $ajaxResponse['message'] = 'questionResponseToStack was '.$questionRes.' and is now '.$oppositeRes;
    }
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39877971
Help me understand the intent of the query a little bit, please.  Can you just say in English what the query is supposed to do?  Also, what is the reason for separating the date and time columns?  Thanks.
0
 

Author Comment

by:Steve Tinsley
ID: 39878112
Basically its is toggling a button on or off... And I wanted to pass back the state of the button each time rather than relying on javascript to keep the color of the button in sync.

The reason for separating the date time..... erm.... because  I wanted to?? :)
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39878200
OK, that makes more sense to me now.  I'm assuming that the id field is UNIQUE.   Here are the conditions that can exist:

1. There is no record to match the request
2. There is a record with a zero
3. There is a record with a one

If you have an index on each of id and questionResponseToStack, you can run an amazingly fast SELECT query that will tell you which of the three conditions exists.  You can save that information for your JSON response.  Then you can run either an INSERT or an UPDATE query, depending on whether you're looking at 1, 2, or 3.  The speed of these queries is likely to be such that no amount of tinkering with them will yield a meaningful improvement in script performance.
http://xkcd.com/1205/

It may not particularly matter, but for me, it's easier to think about sorting with the DATETIME information in just one column.
0
 
LVL 34

Accepted Solution

by:
Slick812 earned 1500 total points
ID: 39878526
OK, I saw about your reason as "toggling a button on or off." so you can add an element to the ajaxResponse array for the button, OR you can do the select query After the update and use the returned value.

if ($result) {
    $ajaxResponse['status'] = "success";
    $ajaxResponse['button'] = $oppositeRes;
    }

BUt maybe you do not consider my suggestion will work? I am not sure from reading your question again, that the code you have posted already works for you? ? , , , OR you have not tried it and wanted some other code?
0
 

Author Closing Comment

by:Steve Tinsley
ID: 39886749
Looks like the best way to do this is to have an insert query, and a select query.
thanks for your help.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this article, we’ll look at how to deploy ProxySQL.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question