Solved

find a column value in mysql and update using php

Posted on 2007-11-24
8
673 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
[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
  • 2
  • 2
  • +1
8 Comments
 
LVL 49

Expert Comment

by:Roonaan
ID: 20343009
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
ID: 20343047
$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
ID: 20343054
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 17

Expert Comment

by:nplib
ID: 20343075
thank you.
0
 

Author Comment

by:nhay59
ID: 20343155
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
ID: 20343167
$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
ID: 20343186
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
ID: 31410774
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

Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
The viewer will learn how to count occurrences of each item in an array.
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.

734 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