Solved

updating table, then adding new row if new account

Posted on 2003-11-26
7
361 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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
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…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
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…

813 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

16 Experts available now in Live!

Get 1:1 Help Now