[RESOLVED] If row exists, UPDATE?

[trendMM]

I'm working on a script where the client will upload his inventory in a text file once a week to the database. Later on I will build a page for the clients to log into where they can view their individual based on their account number, etc. that is stored in the database.

Where I'm lost at is the upload part. Right now I have it working so you just upload it and it adds all the rows to the db that are in the text file. Well what I want to do is make it so if the account number, PO number, and rite number in the text file already exist in a row together in the database to have it update the other fields in that row. The 3 have to be together in the same row though. And if these aren't found to have it post a new row? Some clients might have the same account number but multiple fields for po numbers and right numbers. So we have to only update if the 3 fields are together in a row. Get what I mean?

Here's my code I'm using right now. Works great but I want I don't want it adding new rows everytime. I want it to update rows that have those 3 fields in them already...

<?php

// Define database connection details
$dbHost = "localhost";
$dbUser = "xxx";
$dbPass = "xxx";
$dbName = "xxx";
$table = "inv";


// Attempt to connect to MySQL server
$link = @mysql_connect($dbHost, $dbUser, $dbPass);

// If the connection was unsuccessful...
if (!$link)
{
    // Report error to browser and exit
    print ("Could not connect to server");
    exit;
}

// Attempt to select database. If unsuccessfull...
if (!@mysql_select_db($dbName))
{
    // Report error to the browser and exit
    print ("Could not select $dbName database");
    exit;
}

// Fetch the current time
$posted = time();

$lines = file ('InventoryUpload.txt');
foreach ($lines as $line_num => $line)
 {
$pieces = explode("|",  $line);

// Build Query
$query = "INSERT INTO inv (client, anum, qnty, uom, pon, rnu, des, ware, posted)
          VALUES('$pieces[0]', '$pieces[1]', '$pieces[2]', '$pieces[3]', '$pieces[4]', '$pieces[5]', '$pieces[6]', '$pieces[7]', $posted)";

// Execute Query
$result = @mysql_query($query) or die ("Error: $query" . mysql_error());

}

if ($result)
    echo ' Data Inserted Into The Database.<br>';

?>

Thanks in advance!

[raspberryh]

foreach ($lines as $line_num => $line)
 {
$pieces = explode("|",  $line);

$query = "select * from inv where anum=$pieces[1] and pon=$pieces[4] and rnu=$pieces[5]";
$existing_record = mysql_query($query);

// if record exists already, then do an UPDATE
if(mysql_num_rows($existing_record)){
  $existing_record = mysql_fetch_array($existing_record);
  // update
  $query = "update inv set ... where id='" . $existing_record["id"] . "'"; 
  mysql_query($query);
}

// otherwise, record does not exist - do an INSERT
else{
$query = "INSERT INTO inv (client, anum, qnty, uom, pon, rnu, des, ware, posted)
          VALUES('$pieces[0]', '$pieces[1]', '$pieces[2]', '$pieces[3]', '$pieces[4]', '$pieces[5]', '$pieces[6]', '$pieces[7]', $posted)";

// Execute Query
$result = @mysql_query($query) or die ("Error: $query" . mysql_error());
}

} 

[trendMM]

Thanks a lot for the help. I was actually trying to figure it out myself and then you posted. Helped me out a lot. I got it all up and now it's spitting out an error.

Here's the code after I edited the UPDATE piece...

 foreach ($lines as $line_num => $line) 

 { 

$pieces = explode("|",  $line); 

$query = "SELECT * FROM inv WHERE anum=$pieces[1] AND pon=$pieces[4] AND rnu=$pieces[5]"; 
$existing_record = mysql_query($query); 

// if record exists already, then do an UPDATE 
if(mysql_num_rows($existing_record)){ 
  $existing_record = mysql_fetch_array($existing_record); 

  // update 
  $query = "UPDATE inv SET client = '$pieces[0]', qnty = '$pieces[2]', uom = '$pieces[3]', des = '$pieces[6]', ware = '$pieces[7]', posted = '$posted' WHERE invID='" . $existing_record["invID"] . "'";  

  mysql_query($query); 

} 

// otherwise, record does not exist - do an INSERT 
else{ 

$query = "INSERT INTO inv (client, anum, qnty, uom, pon, rnu, des, ware, posted) 
          VALUES('$pieces[0]', '$pieces[1]', '$pieces[2]', '$pieces[3]', '$pieces[4]', '$pieces[5]', '$pieces[6]', '$pieces[7]', $posted)"; 

// Execute Query 
$result = @mysql_query($query) or die ("Error: $query" . mysql_error()); 
} 

}

And here's the error I'm receiving at the moment from it.

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/evanbart/public_html/test.php on line 45

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/evanbart/public_html/test.php on line 45

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/evanbart/public_html/test.php on line 45

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/evanbart/public_html/test.php on line 45

Got any clues on how I could fix that? Thanks again for your all your help!

[Sayian]

Post the entire code ...... i have no clue what line 45 is

[trendMM]

Here you go..

<?php 

// Define database connection details 
$dbHost = "localhost"; 
$dbUser = "xxx"; 
$dbPass = "xxx"; 
$dbName = "xxx"; 
$table = "inv"; 


// Attempt to connect to MySQL server 
$link = @mysql_connect($dbHost, $dbUser, $dbPass); 

// If the connection was unsuccessful... 
if (!$link) 
{ 
    // Report error to browser and exit 
    print ("Could not connect to server"); 
    exit; 
} 

// Attempt to select database. If unsuccessfull... 
if (!@mysql_select_db($dbName)) 
{ 
    // Report error to the browser and exit 
    print ("Could not select $dbName database"); 
    exit; 
} 

// Fetch the current time 
$posted = time(); 

$lines = file ('InventoryUpload.txt');

 foreach ($lines as $line_num => $line) 

 { 

$pieces = explode("|",  $line); 

$query = "SELECT * FROM inv WHERE anum=$pieces[1] AND pon=$pieces[4] AND rnu=$pieces[5]"; 
$existing_record = mysql_query($query); 

// if record exists already, then do an UPDATE 
if(mysql_num_rows($existing_record)){ 
  $existing_record = mysql_fetch_array($existing_record); 

  // update 
  $query = "UPDATE inv SET client = '$pieces[0]', qnty = '$pieces[2]', uom = '$pieces[3]', des = '$pieces[6]', ware = '$pieces[7]', posted = '$posted' WHERE invID='" . $existing_record["invID"] . "'";  

  mysql_query($query); 

} 

// otherwise, record does not exist - do an INSERT 
else{ 

$query = "INSERT INTO inv (client, anum, qnty, uom, pon, rnu, des, ware, posted) 
          VALUES('$pieces[0]', '$pieces[1]', '$pieces[2]', '$pieces[3]', '$pieces[4]', '$pieces[5]', '$pieces[6]', '$pieces[7]', $posted)"; 

// Execute Query 
$result = @mysql_query($query) or die ("Error: $query" . mysql_error()); 
} 

}  


?>

[trendMM]

Alright I got it working. All I changed was,

$query = "SELECT * FROM inv WHERE anum=$pieces[1] AND pon=$pieces[4] AND rnu=$pieces[5]";  

$existing_record = mysql_query($query); 

to

$query = "SELECT * FROM inv WHERE anum = '$pieces[1]' AND pon = '$pieces[4]' AND rnu = '$pieces[5]'"; 
$existing_record = mysql_query($query) or die(mysql_error());

Works good and is uploading to the DB correctly. Only creates a new row when it's not present in the DB already. Updates the row if it's already in there.

Only thing that isn't working right now is the messages. Like if it works it doesn't say "Data inserted into the database." etc. I don't know why they're not working correctly.

Any ideas?

[trendMM]

Ok I'm an idiot, I forgot the if ($result) messages. Wow. Works great from what I can see now. I'll mark this thing resolved. Thanks for all of your help!

[raspberryh]

Anytime 🙂