Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

updating table, then adding new row if new account

Posted on 2003-11-26
7
Medium Priority
?
366 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
[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
  • 4
  • 3
7 Comments
 
LVL 13

Accepted Solution

by:
lozloz earned 500 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
Independent Software Vendors: 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

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.

Question has a verified solution.

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

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
This article discusses how to create an extensible mechanism for linked drop downs.
The viewer will learn how to dynamically set the form action using jQuery.
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…

722 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