Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 367
  • Last Modified:

updating table, then adding new row if new account


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
ChrisAndrews
Asked:
ChrisAndrews
  • 4
  • 3
1 Solution
 
lozlozCommented:
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
 
ChrisAndrewsAuthor Commented:

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
 
lozlozCommented:
sorry that should be

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

loz
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
ChrisAndrewsAuthor Commented:

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
 
lozlozCommented:
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
 
ChrisAndrewsAuthor Commented:

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
 
lozlozCommented:
heh no problem, sorry for the errors i'm being a bit dopey

loz
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now