Solved

updating table, then adding new row if new account

Posted on 2003-11-26
7
362 Views
Last Modified: 2006-11-17

Hi,

I need a script that updates the 'status' column in a mysql table if the account number is found, like this:

mysql_query("UPDATE user SET status = '$cust_data[5]' WHERE account = '$cust_data[2]'");

and....   if the account number is not found, inserts a new row with the new account information:

mysql_query("INSERT INTO user VALUES
('$cust_data[0]','$cust_data[1]','$cust_data[2]','$cust_data[3]','$cust_data[4]','$cust_data[5]')");

How do I do that?

Thanks,

Chris

0
Comment
Question by:ChrisAndrews
  • 4
  • 3
7 Comments
 
LVL 13

Accepted Solution

by:
lozloz earned 125 total points
ID: 9831794
hi,

simple if clause based on the relevant details being selected from a database

<?
$query = "SELECT COUNT(id) AS num FROM user WHERE account='$cust_data[2]'"; // find the row
$result = mysql_query($query) or die("Error in query - " . mysql_error());
$row = mysql_fetch_assoc($result);
if($row["num"]) > 0) { // if there is a row with that account number...
  $query2 = "UPDATE user SET status = '$cust_data[5]' WHERE account = '$cust_data[2]'"; // update it
  $result2 = mysql_query($result2) or die("Error in query - " . mysql_error());
  print "User details updated";
} else {
  $query2 = "INSERT INTO user VALUES ('$cust_data[0]','$cust_data[1]','$cust_data[2]','$cust_data[3]','$cust_data[4]','$cust_data[5]')"; // otherwise add it
  $result2 = mysql_query($query2) or die("Error in query - " . mysql_error());
  print "User's details added";
}
?>

cheers,

loz
0
 

Author Comment

by:ChrisAndrews
ID: 9834856

Hi Loz,

Thank you, I think this is going to work out great.  Though the script is more advanced than I could write, I can kinda follow along with what's happening.

But when I run it I get a 'parse error' on this line:

if($row["num"]) > 0) {

Do you see what might be causing that?

Thanks again for your help,

Chris
0
 
LVL 13

Expert Comment

by:lozloz
ID: 9834886
sorry that should be

if($row["num"]) > 0)) {

loz
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:ChrisAndrews
ID: 9835007

opps, actually:

if($row["num"] > 0 )

figured that out after you added the second one :)

Now I get:

Error in query - Unknown column 'id' in 'field list'

Chris
0
 
LVL 13

Expert Comment

by:lozloz
ID: 9835141
hah my fault for being a little tired..

yeh you need to change the first query to have a field counted (i usually use the id field) but you can use account:

$query = "SELECT COUNT(account) AS num FROM user WHERE account='$cust_data[2]'"; // find the row
0
 

Author Comment

by:ChrisAndrews
ID: 9835218

found and fixed one more bug, now it's working  :)

$result2 = mysql_query($result2) or die("Error in query2 - " . mysql_error());

changed to:

$result2 = mysql_query($query2) or die("Error in query2 - " . mysql_error());

Just noted in case anyone else accesses this question (or I come back to it later).

Thanks again loz, this helps me out a lot,

Chris
0
 
LVL 13

Expert Comment

by:lozloz
ID: 9835236
heh no problem, sorry for the errors i'm being a bit dopey

loz
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
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…
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…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

680 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