Solved

find a column value in mysql and update using php

Posted on 2007-11-24
8
669 Views
Last Modified: 2013-12-12
Hi,

I have a simply table with three columns as follows,

id, tag, count

I know how to update the column 'count' using php, but I need to first check whether the value exists in the column. If it exists the value is updated, if not a new value is inserted. However, how would I check first that the value exists, and then use either the insert or update query based on whether the value exists?

Any help appreciated.

Thanks
0
Comment
Question by:nhay59
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 49

Expert Comment

by:Roonaan
Comment Utility
You could request the mysql_affected_rows() function just after your update. If 0 rows are updated, then you need to update the row. I think this is more efficient then calling to see if the record exist before updating.
0
 
LVL 17

Expert Comment

by:nplib
Comment Utility
$myvar = 1;
$sql = "select * from mytable where count = $myvar;"
$rs = mysql_query($sql);
if (mysql_num_rows($rs) >= 1{
     $does_exist = true;
}
if ($does_exist == true) {
    $sql = "update .....";
} else {
    $sql = "insert into .....";
}
0
 
LVL 49

Expert Comment

by:Roonaan
Comment Utility
nplib,

The implementation you provided is functional, but can be improved.

When you wánt to do a select, then please add limit 0,1, or change the query into a count(somefield) query.

-r-
0
 
LVL 17

Expert Comment

by:nplib
Comment Utility
thank you.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:nhay59
Comment Utility
Hi,

Thanks to both of you for the help. I'm using this with a baisc textfield and submit button, but I get an error with the included code. The error is as follows,

Parse error: syntax error, unexpected T_VARIABLE in.....

and this refers to the line,

$rs = mysql_query($sql);

Any ideas why this is happening?

Thanks for the help




mysql_connect($db_host, $db_user, $db_pass) or die(mysql_error());

mysql_select_db($db_name);
 

if(isset($_POST['add']))

{

$tag = $_POST['tag'];
 

$sql = "select * from tags where tag='$tag;'"

$rs = mysql_query($sql);

if (mysql_num_rows($rs) >= 1{

     $does_exist = true;

}

if ($does_exist == true) {

    $sql = "UPDATE tags SET `count`=`count`+1, tag ='$tag' WHERE tag='$tag'";

} else {

    $sql = "INSERT INTO tags SET `count`=`count`+1, tag ='$tag';";

}
 

echo "Keyword tag and value updated";

}

Open in new window

0
 
LVL 17

Expert Comment

by:nplib
Comment Utility
$sql = "select * from tags where tag='$tag;'" <------ you forgot the ;
$rs = mysql_query($sql);

add ; to the end of you $sql statement

anytime you see that error, it is usually a syntax mistake from the line before.
0
 
LVL 20

Accepted Solution

by:
steelseth12 earned 500 total points
Comment Utility
You could simply add a unique index on your tag column

ALTER TABLE  tags  ADD UNIQUE (tag);

and you can run a single  query without the php code

INSERT INTO tags SET `count`=`count`+1, tag ='$tag' ON DUPLICATE KEY UPDATE `count`=`count`+1;
0
 

Author Closing Comment

by:nhay59
Comment Utility
Hi,

Thanks for all the help.

Yes, it was much easier to just add the unique index to the tag column.

Thanks for all the help. It is much appreciated.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
This article discusses four methods for overlaying images in a container on a web page
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

771 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

11 Experts available now in Live!

Get 1:1 Help Now