Page 1 of 1

problem inserting/updating with PHP/MySQL

Posted: Sat Nov 15, 2003 2:51 am
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.

Posted: Sat Nov 15, 2003 3:05 am
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]

Posted: Sat Nov 15, 2003 3:54 am
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.

Posted: Sat Nov 15, 2003 3:31 pm
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?

Posted: Sat Nov 15, 2003 3:47 pm
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.
}
?>

Posted: Sat Nov 15, 2003 6:38 pm
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;

Posted: Sun Nov 16, 2003 3:35 am
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.

Posted: Sun Nov 16, 2003 6:20 am
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.