PHP check if value exists

Question

I’m trying to write a if code, but can't figure out how.

In my database table i have the primary key id. In the table i have objekt_nr & element_nr. Now, before i call an INSTERT i need to check if objekt_nr with element_nr already exist. If objekt_nr With element_nr exist. Then UPDATE instead of INSTERT. Only id can be unique.

example of table:

id......objekt_nr......element_nr

1.......1...............1

2.......1...............2

3.......1...............3

4.......2...............1

5.......2...............2

PHP:

if(isset($_POST["Import"])){ echo '<a href=".php">Fortsätt...</a><br />'; echo $path=$_FILES["file"]["tmp_name"]; //Load file into PHPExcel $objPHPExcel = PHPExcel_IOFactory::load($path); //Loop threw file to get data foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) { $worksheetTitle = $worksheet->getTitle(); $highestRow = $worksheet->getHighestRow(); // e.g. 10 $highestColumn = 'J'; //$worksheet->getHighestColumn(''); // e.g 'F' $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); $nrColumns = ord($highestColumn) - 64; //Echo file info echo "<br>The worksheet ".$worksheetTitle." has "; echo $nrColumns . ' columns (A-' . $highestColumn . ') '; echo ' and ' . $highestRow . ' row.'; echo '<br>Data: <table border="1"><tr>'; //Loop threw colum, rows and cells for ($row = 2; $row <= $highestRow; ++ $row) { echo '<tr>'; for ($col = 0; $col < $highestColumnIndex; ++ $col) { $cell = $worksheet->getCellByColumnAndRow($col, $row); $val = $cell->getCalculatedValue(); //$dataType = PHPExcel_Cell_DataType::dataTypeForValue($val); echo '<td>' . $val . '<br></td>'; } echo '</tr>'; } echo '</table>'; } for ($row = 2; $row <= $highestRow; ++ $row) { $val=array(); for ($col = 0; $col < $highestColumnIndex; ++ $col) { $cell = $worksheet->getCellByColumnAndRow($col, $row); $val[] = $cell->getCalculatedValue(); } // Prepare Query $query = "INSERT INTO phpexcel( objekt_nr, objekt_rev, element_nr, element_hojd, element_typ, element_langd, element_oppningar, element_vikt, element_ritare, element_status) VALUES ( :objekt_nr, :objekt_rev, :element_nr, :element_hojd, :element_typ, :element_langd, :element_oppningar, :element_vikt, :element_ritare, :element_status )"; // Security measures $query_params = array( ':objekt_nr' => $val[0], ':objekt_rev' => $val[1], ':element_nr' => $val[2], ':element_hojd' => $val[3], ':element_typ' => $val[4], ':element_langd' => $val[5], ':element_oppningar' => $val[6], ':element_vikt' => $val[7], ':element_ritare' => $val[8], ':element_status' => $val[9] ); try { $stmt = $db->prepare($query); $result = $stmt->execute($query_params); } catch(PDOException $ex){ die("Failed to run query: " . $ex->getMessage()); } //echo $query."\n"; } }

INSERT INTO ... ON DUPLICATE KEY UPDATE ? Assuming objeckt_nr is the key. dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html — ʰᵈˑ
– ʰᵈˑ, Commented Oct 28, 2014 at 9:13
@ʰᵈˑ definitely the easiest solution, but it's worth pointing out that it is a MySQL extension (non-standard sql) — Tim Seguine
– Tim Seguine, Commented Oct 28, 2014 at 9:16
@TimSeguine this is possible with MySQL - I'm unsure what you're saying. — ʰᵈˑ
– ʰᵈˑ, Commented Oct 28, 2014 at 9:17
@ʰᵈˑI am saying that it is only possible with MySQL. Depending on what the OP needs, this lack of portability to other implementations may or may not be acceptable. — Tim Seguine
– Tim Seguine, Commented Oct 28, 2014 at 9:18
@TimSeguine I understand. However, OP has tagged mysql and not mentioned the answer should have portability in mind. Nevertheless, a good point. — ʰᵈˑ
– ʰᵈˑ, Commented Oct 28, 2014 at 9:19

ConnectedSystems · Accepted Answer · 2014-10-28 11:55:42Z

Two options here.

1) Make "objekt_nr" field a PRIMARY or UNIQUE key and use REPLACE instead of INSERT (http://dev.mysql.com/doc/refman/5.0/en/replace.html). This is pretty much @hd 's answer in the comments.

2) Check for an existing "objekt_nr" and "element_nr" and run the appropriate query

$check_duplicate_query = "SELECT COUNT(*) FROM phpexcel WHERE objekt_nr = ':objekt_nr' AND element_nr = ':element_nr'"; $stmt = $db->prepare($check_duplicate_query); $result = $stmt->execute($query_params); $rows = $stmt->fetch(PDO::FETCH_NUM); if($rows[0] > 0) { //UPDATE } else { //INSERT } $stmt = $db->prepare($query); $result = $stmt->execute($query_params);

EDIT:

Just thought of a 3rd option

Create another field "objekt_element_nr" and set this field to unique. This field gets assigned objekt and element number combination, which from your example should be unique.

e.g. 1_1, 1_2, 1_3, 2_1, 2_2, etc.

Then simply use the REPLACE function I linked to above.

Your $query_params would look like

$query_params = array( ':objekt_nr' => $val[0], ':objekt_rev' => $val[1], ':element_nr' => $val[2], ':objekt_element_nr' => $val[0].'_'.$val[2] ':element_hojd' => $val[3], ':element_typ' => $val[4], ':element_langd' => $val[5], ':element_oppningar' => $val[6], ':element_vikt' => $val[7], ':element_ritare' => $val[8], ':element_status' => $val[9] );

@ConnectedSystemes I Can't make objekt_nr unique because two element_nr having the same objekt_nr. element_nr can be unique. But i need to match.. if the objekt_nrwith the element_nrexist, then update it.
@ConnectedSystemes Thank you! I will try this and come back! I've also updated my explaining question! (I was wrong with the unique of elemenr_nr. ONLY id can be unique!)
I also recommend using @ʰᵈˑ's answer in the comment instead of the REPLACE command (dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html) because REPLACE will delete then insert new row, whereas INSERT ON DUPLICATE UPDATE will update existing row. Better performance wise.
@ConnectedSystsmes I really like your 3rd solution! I go with this, and it worked exactly how i wanted! Made my day! Thank U!

Collectives™ on Stack Overflow

PHP check if value exists

1 Answer 1

6 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Related