Insert & Update MySQL Table

Question

I'm trying to put some code together that inserts data, whilst checking whether the data already exists into a mySQL table.

The data originates as an xml file which I've managed to extract and put into a PHP format but I'm struggling to get the 'Insert' and 'Checking' functionality working.

This is the code that I've got at the moment.

<? 

  $objDOM = new DOMDocument(); 
  $objDOM->load("xmlfile.xml"); 

  $Details = $objDOM->getElementsByTagName("Details"); 

  foreach( $Details as $value ) 
  { 

    $ListEntry = $value->getElementsByTagName("ListEntry"); 
    $listentry  = $ListEntry->item(0)->nodeValue;

    $SiteType = $value->getElementsByTagName("SiteType"); 
    $sitetype  = $SiteType->item(0)->nodeValue;

    $SiteDescription = $value->getElementsByTagName("SiteDescription"); 
    $sitedescription  = $SiteDescription->item(0)->nodeValue;

    $Siteosgb36lat = $value->getElementsByTagName("Siteosgb36lat"); 
    $siteosgb36lat  = $Siteosgb36lat->item(0)->nodeValue;

    $Siteosgb36lon = $value->getElementsByTagName("Siteosgb36lon"); 
    $siteosgb36lon  = $Siteosgb36lon->item(0)->nodeValue;


  } 

require("phpfile.php");

// Opens a connection to a MySQL server
$connection = mysql_connect ("hostname", $username, $password);
if (!$connection) {
  die('Not connected : ' . mysql_error());
}

// Set the active MySQL database
$db_selected = mysql_select_db($database, $connection);
if (!$db_selected) {
die ('Can\'t use db : ' . mysql_error());
}

mysql_query("INSERT INTO sitestable(listentry, sitetype, sitedescription, siteosgb36lat, siteosgb36lon) VALUES($listentry, $sitetype, $sitedescription, $siteosgb36lat, $siteosgb36lon ) ") 
or die(mysql_error());  

echo "Data Inserted!";

?>

I'd like the query to check to see whether there is an entry in the 'listentry' column that matches data in the file I'm wanting to add, if there is, then I want it to check all the fields for that record and update accordingly into my table.

Could someone perhaps take a look at this and let me know where I'm going wrong please.

Kind regards

UPDATED CODE

<? 

  $objDOM = new DOMDocument(); 
  $objDOM->load("xmlfile.xml"); 

  $Details = $objDOM->getElementsByTagName("Details"); 

  foreach( $Details as $value ) 
  { 

    $listentry = $value->getElementsByTagName("listentry"); 
    $listentrys  = $listentry->item(0)->nodeValue;

    $sitetype = $value->getElementsByTagName("sitetype"); 
    $sitetypes  = $sitetype->item(0)->nodeValue;

    $sitedescription = $value->getElementsByTagName("sitedescription"); 
    $sitedescriptions  = $sitedescription->item(0)->nodeValue;

    $siteosgb36lat = $value->getElementsByTagName("siteosgb36lat"); 
    $siteosgb36lats  = $siteosgb36lat->item(0)->nodeValue;

    $siteosgb36lon = $value->getElementsByTagName("siteosgb36lon"); 
    $siteosgb36lons  = $siteosgb36lon->item(0)->nodeValue;

    //echo "$listentrys :: $sitetypes :: $sitedescriptions :: $siteosgb36lats :: $siteosgb36lons <br>"; 


  } 

require("phpfile.php");

//Opens a connection to a MySQL server
$connection = mysql_connect ("hostname", $username, $password);
if (!$connection) {
 die('Not connected : ' . mysql_error());
}

// Set the active MySQL database
$db_selected = mysql_select_db($database, $connection);
if (!$db_selected) {
die ('Can\'t use db : ' . mysql_error());
}

mysql_query("INSERT IGNORE INTO sitestable (listentry, sitetype, sitedescription, siteosgb36lat, siteosgb36lon) VALUES('$listentrys','$sitetypes','$sitedescriptions','$siteosgb36lats','$siteosgb36lons') ")  
or die(mysql_error());  


echo "Data Inserted!"; 

?>

score 3 · Answer 1 · answered Oct 20 '11 at 15:11

3

If the listentry attribute is a primary key of your table, or has a unique index defined, you can use the INSERT ... ON DUPLICATE KEY UPDATE command, for more details see http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html.

answered Oct 20 '11 at 15:11

Jan-Henk

4,864
1
24
38

mattcan · Answer 2 · 2011-10-22T17:21:31.217

As mentioned earlier INSERT .. ON DUPLICATE KEY UPDATE is your best bet. For your particular query, it would look like this:

INSERT INTO sitetable
    (listentry, sitetype, sitedescription, siteosgb36lat, siteosgb36lon)
VALUES
    ($listentry, $sitetype, $sitedescription, $siteosgb36lat, $siteosgb36lon)
ON DUPLICATE KEY UPDATE
    listentry = VALUES(listentry),
    sitetype = VALUES(sitetype),
    sitedescription = VALUES(sitedescription),
    siteosgb36lat = VALUES(siteosgb36lat),
    siteosgb36lon = VALUES(siteosgb36lon);

This assumes you want to use all the new values, which is what it sounds like and that one of your new values is used in a UNIQUE column.

EDIT It should be noted that the query above includes your PHP variables.

UPDATE

You have a couple of problems here.

First, your plural variables (like listentrys) are not actually arrays. Because of this, when you do your insert, you are only keeping the last value from the for loop.

Secondly, you can't insert an array into a query in the manner you are expecting. You need to loop each individual value.

My suggestion would be to use an array of arrays so that you can keep track of how many records you want and then loop your query statement.

foreach( $Details as $value ) 
{ 

  $listentry = $value->getElementsByTagName("listentry"); 
  $sitetype = $value->getElementsByTagName("sitetype"); 
  $sitedescription = $value->getElementsByTagName("sitedescription"); 
  $siteosgb36lat = $value->getElementsByTagName("siteosgb36lat"); 
  $siteosgb36lon = $value->getElementsByTagName("siteosgb36lon"); 

  $new_rows[] = array(
      'listentry' => $listentry->item(0)->nodeValue,
      'sitetype' => $sitetype->item(0)->nodeValue,
      'sitedescription' => $sitedescription->item(0)->nodeValue,
      'siteosgb36lat' => $siteosgb36lat->item(0)->nodeValue,
      'siteosgb36lon' => $siteosgb36lon->item(0)->nodeValue
  );

} 

//now loop your query
foreach ($new_rows as $row)
{
  $listentry = $row['listentry'];
  $sitetype = $row['sitetype'];
  $sitedescription = $row['sitedescription'];
  $siteosgb36lat = $row['siteosgb36lat'];
  $siteosgb36lon = $row['siteosgb36lon'];

  //Your query here
}

Hi, many thanks for replying to my post. I did try the code, but unfortunately couldn't get it to run. However on some further research I think that a 'INSERT IGNORE' would be enough, but I'm having a problem with this. Basically the code runs, but it only inserts the last record. I've made an update to my original post. I just wondered if you could take a look at it please and let me know where I'm going wrong. Kind regards — IRHM, Oct 21 '11 at 15:07
Hi @IRHM, are you sure `INSERT IGNORE` is what you want? If there is a duplicate key the new data will not be inserted. [There is an SO answer](http://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update/548570#548570) by Bill Karwin that explains it better. I have also updated my answer to reflect your question. — mattcan, Oct 22 '11 at 16:38

score 1 · Accepted Answer · answered Oct 20 '11 at 15:16

1

try this code

mysql_query("INSERT INTO sitestable(listentry, sitetype, sitedescription, siteosgb36lat, siteosgb36lon) VALUES('".$listentry."','".$sitetype."','".$sitedescription."','".$siteosgb36lat."','".$siteosgb36lon."') ") 
or die(mysql_error());

answered Oct 20 '11 at 15:16

Vaibhav Naranje

78
1
1
5

score -1 · Answer 4 · answered Oct 20 '11 at 15:19

-1

Have you checked out the Merge statement? Merge on Msdn

answered Oct 20 '11 at 15:19

Orry

2,578
2
18
14

1

This is about mySQL, not T-SQL – Pekka Nov 01 '11 at 12:33

Insert & Update MySQL Table

4 Answers4