problem inserting/updating with PHP/MySQL

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
cityinnards
Forum Newbie
Posts: 3
Joined: Sat Nov 15, 2003 2:51 am
Location: RVA

problem inserting/updating with PHP/MySQL

Post by cityinnards »

Hi everybody. I'm a PHP/MySQL newb and am having trouble with a bit of code. After reading from a tab-deliniated text file, I would like to either insert new data from the file into the db, or if the data is already there, update it if need be:

Code: Select all

<?php
$AlreadyThereCheck="select * from Users where SourceId='$Array[0]'";
$AlreadyThere=mysql_query($AlreadyThereCheck);

if ($AlreadyThere){
$AlreadyThereCounter++;
$Update="Update Users SET FirstName='$Array[1]', LastName='$Array[2], MailingAddy=.......where SourceId="$Array[0]'";
$UpdateResult=mysql_query($Update);

}else{

if (!$AlreadyThere){
$Insert="Insert into Users (FirstName, LastName, MailingAddy,.....
VALUES ('$Array[1]', '$Array[2]',,.........'$Array[0]')";
$InsertCounter++;
$InserResult=mysql_query($Update);
}
?>
Working with this code, I can insert into the db, but when the db is populated and I try again hoping for an update (which should be none since the file is unchanged), it just inserts duplicate records. If I switch the arguments between the two IF statements, I can update the db, but if it's empty, it won't insert.
I'm also pretty sure I'm reading from the file correctly.

Any suggestions are appreciated.
Quietus
Forum Newbie
Posts: 16
Joined: Sat Nov 15, 2003 1:59 am

Post by Quietus »

I'm sorry if this is a stupid question, but have you checked that the strings:

$AlreadyThereCheck="select * from Users where SourceId='$Array[0]'";
$AlreadyThere=mysql_query($AlreadyThereCheck);

produce the expected values?

[edit]
Further - the quote structure in:
$Update="Update Users SET FirstName='$Array[1]', LastName='$Array[2], MailingAddy=.......where SourceId="$Array[0]'";
Doesn't look right to me. Check your numbers of opening and closing ' and "
[/edit]
Last edited by Quietus on Sat Nov 15, 2003 4:06 am, edited 1 time in total.
User avatar
m3mn0n
PHP Evangelist
Posts: 3548
Joined: Tue Aug 13, 2002 3:35 pm
Location: Calgary, Canada

Post by m3mn0n »

Are you using proper IF syntax?

Code: Select all

<?php
if($AlreadyThere)
{
 // code
} 
   elseif(!$AlreadyThere)
{
  // code
} 
   else 
{
  // code
} 
?>
Also, try using [php_man]mysql_fetch_row[/php_man] to pinpoint if that user sourceid exists.
Only for SELECT,SHOW,DESCRIBE or EXPLAIN statements, mysql_query() returns a new result identifier that you can pass to mysql_fetch_array() and other functions dealing with result tables.
cityinnards
Forum Newbie
Posts: 3
Joined: Sat Nov 15, 2003 2:51 am
Location: RVA

Post by cityinnards »

If I echo $AlreadyThere, I get:

Select * from Users where SourceOfThisRecord = 'J1818J'

As far as the IF syntax goes, I have:

IF ($AlreadyThere)
{
//code
}
else
{
if (!$AlreadyThere)
{
//code
}

Should I use elseif instead of nesting another IF statement?
Paddy
Forum Contributor
Posts: 244
Joined: Wed Jun 11, 2003 8:16 pm
Location: Hobart, Tas, Aussie
Contact:

Post by Paddy »

Code: Select all

<?php
if ($AlreadyThere)
{
//This code will execute is $AlreadyThere is not empty
}
else
{
//The opposite of $AlreadyThere is !$AlreadyThere. The ! stands for not. So this else statement will execute if !$AlreadyThere. It is true that you could have used an if else statement but that would mean that the server would have to make another boolean decision. So although it would work, it is bad programming.
}
?>
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

you should maybe have a call that checks to associated rows if you want to get the data found that matches the query statement, such as

Code: Select all

$bob = mysql_fetch_assoc($alreadythere);

echo $bob;
cityinnards
Forum Newbie
Posts: 3
Joined: Sat Nov 15, 2003 2:51 am
Location: RVA

Post by cityinnards »

Is there a way that, with the current code, I can feed the IF statement something that will evaluate a boolean statement? I think it's getting invalid parameters ($AlreadyThereCheck), and I need to alter the SQL update statement somehow.
Quietus
Forum Newbie
Posts: 16
Joined: Sat Nov 15, 2003 1:59 am

Post by Quietus »

I'm sure you've already done this, but for my peace of mind, could you echo out the value of all the variables used within the if statement after the if statement?

From the code example given, the if statement looks valid, so I believe the bug is coming in after the if evaluation.
Post Reply