Solved

updating table, then adding new row if new account

Posted on 2003-11-26
7
364 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 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
Secure Your WordPress Site: 5 Essential Approaches

WordPress is the web's most popular CMS, but its dominance also makes it a target for attackers. Our eBook will show you how to:

Prevent costly exploits of core and plugin vulnerabilities
Repel automated attacks
Lock down your dashboard, secure your code, and protect your users

 

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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
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…
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.

623 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