Solved

Best practice for checking value of a field in php/mysql?

Posted on 2011-09-06
2
240 Views
Last Modified: 2012-06-27
Hi,
in my app I need to check the value of a field and based on that, insert new value or not.
What I'm currently doing is:

 
$result = mysql_query("SELECT subscribestatus FROM callers WHERE FromCaller = '$From'");
	while ($row = mysql_fetch_assoc($result)) {
	$status = $row["subscribestatus"];
	}

Open in new window


Then once its assigned as a variable, I use a switch/case statement to check the value compared to the 3 different ones it may be. Then if it matches one of them, I do an update statement something like this:

 
mysql_query("UPDATE callers SET subscribestatus = 'Subscribed' WHERE FromCaller = '$From' AND subscribestatus = 'Pending'");?>

Open in new window


So my question is: is that the best way to do it? Seems like a lot of work to do a comparison of one value. I just get a nagging suspicion there is a better way.

Thanks tj
0
Comment
Question by:tjyoung
2 Comments
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 250 total points
Comment Utility
Regarding this: $status = $row["subscribestatus"];

You do not need to do that.  $row["subscribestatus"] is already its own variable.  There is no value added by proliferating variables.  You can use switch / case on the original element of the $row array

Your design pattern makes sense to me as you have described it here.  The things I would be concerned about include the origin and sanitization of the $From variable, and the absence of the LIMIT clause in your queries.

HTH, ~Ray
0
 
LVL 26

Expert Comment

by:skullnobrains
Comment Utility
you can perform a single SQL query using an if or case statement to update conditionnally to the existing or another value. something like this :

"UPDATE callers SET subscribestatus =
  CASE WHEN (SELECT subscribestatus FROM ...) = 'something_meaning you do not want to update'
  THEN subscribestatus
  ELSE 'Subscribed'
  END WHERE FromCaller = '$From' AND subscribestatus = 'Pending'"

this is already a little better but it will still perform the lookup and lock the row in the callers table

note this will also work if you stick the extra comparison in the where clause

... WHERE ... AND  (select ...) = 'whatever_triggers_the_update'

otherwise you need to go for a transaction : perform the select
SELECT @var:=subscribestatus FROM ...;

CASE when @var == whatever
then UPDATE ...

i'm not completelyy sure about the syntax for the transaction part. feel free to ask if you need more help
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
echo button 13 45
Re-imbursement Claim System 3 21
Page showing diff display 4 19
MSSQL 2008 with mySQL webservers 7 23
Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
This article discusses how to create an extensible mechanism for linked drop downs.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
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 …

772 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now