Problem inserting rows if it doesn't exist already

[maganv0x]

Ok what I'm doing with my script is parsing data from a text file into my database, which all works fine, but I decided to put a if statement in tonight to check to see if the user already exists in the table. What I'm trying to do is check and see if the $name variable (obtained via fscanf) is already present in the database via a SELECT statement. Problem is, when I run the script it does not insert the values into the database whether they are present or not. I figured maybe someone could find the flaw in my code, as I'm a not so seasoned developer when it comes to PHP or SQL for that matter. Below is the code snippet:

while ($userinfo = fscanf ($handle, "%[^\t]\t%[^\t]\t%[^\t]\t%[^\n]\n")){
    list ($name, $team, $country) = $userinfo;
    $check = mssql_query("SELECT name FROM index_chessbrain WHERE name LIKE '$name'");
    while ($row = mssql_fetch_array($check)) {
        if ($row[0] == "$name"){
            $uID = $uID + 1;

    }
    else if ($row[0] != "$name"){
        mssql_query("INSERT INTO index_chessbrain (uID, name, team, country) VALUES ($uID, '$name', '$team', '$country')");
        $uID = $uID + 1;
    }
}
}
fclose($handle);

[bad76]

Hi,
the code seems to be right.

You must try some debug test:

1) test if while ($row = mssql_fetch_array($check)) is always false;
2)test ($row[0] == "$name") value
3)test the INSERT statement

To do this just add some echo "i'm here" or otehr...

[maganv0x]

Using SQL profiler, I verifyed that the SELECT statement was working correctly.

tested insert command by taking it out of the if statement and it inserts fine. No signs of any attempts in SQL Profiler to do a INSERT command when in the if statement.

I added a echo "$name" directly before the check state
and a echo "$row[0]" directly after the while ($row = mssql_fetch_array($check)) { line. Both give identical outputs.

[bad76]

😕
Always identical outputs ? even if $name not exist?

[kevinsequeira]

try this

if ($row[0] == $name){
$uID = $uID + 1;
}else{
mssql_query("INSERT INTO index_chessbrain (uID, name, team, country) VALUES ($uID, '$name', '$team', '$country')");
$uID = $uID + 1;
}

reg
kevin

[bad76]

May be, but
why php return ($row[0]==$name) and ($row[0]!=$name) both false? It's a bug?

[maganv0x]

if ($row[0] == $name){ 
$uID = $uID + 1; 
}else{ 
mssql_query("INSERT INTO index_chessbrain (uID, name, team, country) VALUES ($uID, '$name', '$team', '$country')"); 
$uID = $uID + 1; 
}

I wouldn't mind doing that, but there is definitly a problem in the if statement. If $row[0] == $name turns out to be false all the time whether the select statement returns a value or not, then I'll have many duplicate entries from the trailing else statement. I haven't fooled with it any more since early this morning, because I'm at work, but I'll be sure to get back to you guys this evening with results of using the else statement instead of an else if. I wanted to stay away from using an else statement, I feel they have a better chance to be buggy, but I'll give it a shot.

===========================================

Last night I did decide to try to the script with a hard-coded name that was not obtained through the fscanf method and it worked as it should. I inserted the information for Supp in the database and executed this simpler script to make sure the data stored in the array could be evaluated against a variable containing a string.

$name = "Supp"
$check = mssql_query("SELECT name FROM index_chessbrain WHERE name LIKE 'Supp'"); 
while ($row = mssql_fetch_array($check)) { 
if ($row[0] = $name){
echo "$name";
}
} 
fclose($handle);

As it expected it passed the if statement and echoed his name. When I removed his record from the database, it failed and nothing was done. Then I went back to the original script and put an

echo "$name";

statement directly following the

while ($userinfo = fscanf ($handle, "%[^\t]\t%[^\t]\t%[^\t]\t%[^\n]\n")){ 
list ($name, $team, $country) = $userinfo; 

and it correctly displayed each name that was parsed into the $name variable on each pass of the while loop.

/me scratches his head in dismay
I'm lost for words

[maganv0x]

Returned home and switched up the if/else statement every which way I could think and still no go. The code as it stands at this moment is:

$uID = 1;
while ($userinfo = fscanf ($handle, "%[^\t]\t%[^\t]\t%[^\t]\t%[^\t]\t%[^\t]\t%[^\t]\t%[^\n]\n")){
    list ($name, $team, $country, $nodes_processed, $jobs_accepted, $jobs_processed, $cpu_time) = $userinfo;
    $check = mssql_query("SELECT name FROM index WHERE name LIKE '$name'");
    while ($row = mssql_fetch_array($check)) {
        if ($row[0] != "$name"){
            mssql_query("INSERT INTO index(uID, name, team, country) VALUES ($uID, '$name', '$team', '$country')");
            $uID = $uID + 1;
        }
        else {
            $uID = $uID + 1;
        }
    }
}
fclose($handle);

[maganv0x]

Since I'm trying to find the root of the problem I wrote a mini script that works on the same principles, but easier to diagnose.

while ($userinfo = fscanf ($handle, "%[^\t]\t%[^\t]\t%[^\t]\t%[^\t]\t%[^\t]\t%[^\t]\t%[^\n]\n")){
    list ($name, $team, $country, $nodes_processed, $jobs_accepted, $jobs_processed, $cpu_time) = $userinfo;
    $check = @mssql_query("SELECT name FROM index WHERE name LIKE '$name'");
    while ($row = @mssql_fetch_array($check)) {
        if ($row[0] == "$name"){
             echo "$name exists in database<br>";
        }
        else if ($row[0] != "$name") {
              echo "$name does not exist in database<br>";
        }
    }
}

Upon executing this code it will show the the names that do exist in the database (the ones that pass the if statement), but it won't display anything else. Even if I use

else

statement instead of

else if

it will not show the other message. Anyone have any clue in the world why it would do that?

[bad76]

Just moment!
If the name isn't in the DB the while fail !

The rigth code may be like this:

$row = @mssql_fetch_array($check) 
if (!$row) {
              echo "$name does not exist in database<br>";
} else if ($row[0] == "$name"){
   echo "$name exists in database<br>";
}    

or better:

if ($row = @mssql_fetch_array($check) )
{
   echo "$name exists in database<br>";
} else {
   echo "$name does not exist in database<br>";
}    

[maganv0x]

Actualy you are right, someone pointed it out to me yesterday and I got it going now 😃

$row = mssql_fetch_array($check);
if ($row[0] == null){
       mssql_query("INSERT INTO index (uID, name, team, country) VALUES ($uID, '$name', '$team', '$country')");
       mssql_query("INSERT INTO entry (uID, nodes_processed, np_yesterday, np_today, jobs_accepted, jobs_processed, cpu_time, timestamp) VALUES ($uID, $nodes_processed, 0, $nodes_processed, $jobs_accepted, $jobs_processed, $cpu_time, getDate())");
       $uID = $uID + 1;
    }
    else {
       $np_yesterday = $row[1];
       mssql_query("UPDATE entry SET nodes_processed = $nodes_processed, np_today = $nodes_processed, np_yesterday = $np_yesterday, jobs_accepted = $jobs_accepted, jobs_processed = $jobs_processed, cpu_time = $cpu_time WHERE uID = $uID");
       $uID = $uID + 1;
    }

Works like a charm now 🙂