Check of existence
Posted: Wed Sep 23, 2009 12:37 am
Dear reader,
I'm now using the following code:
But I want to check easily if the line which will be added already exists in the database. If this is true, he shouldnt INSERT the query and get the id of the existing line.
I've used the SELECT query above, but didnt work fine for me and now trying to do it with a UNIQUE index. I used this:
ADD UNIQUE `ladetype` ( `ladetype` , `houtsoort` , `oppervlak` , `breedte` , `diepte` , `hoogte` , `bodem` , `binnenlade` , `greep` , `bestek` , `front_verst` ) (via PHPMYAdmin)
But still i get the same inserts:
http://yfrog.com/0bdatabasshj
Any1 can help me?
I'm now using the following code:
Code: Select all
<?
class ProductController
{
private $db;
public function __construct(PDO $db)
{
$this->db = $db;
}
public function orderProduct(array $post)
{
$nvt = array('ladeGreep', 'ladeFrontVerst', 'loperBlumotion', 'loperKg', 'loperLengte', 'loperTipon', 'ladeBinnen');
foreach($nvt as $aanpas)
{
if(!isset($post[$aanpas]))
{
$post[$aanpas] = 'n.v.t.';
}
}
return $this->checkProduct($post);
}
protected function checkProduct(array $post)
{
// Arrays voor namen
$aLade = array('ladeType','ladeSoort','ladeOpp','ladeBreedte','ladeDiepte','ladeHoogte','ladeBodem','ladeBinnen','ladeGreep','ladeBestek','ladeFrontVerst');
$aLoper = array('loperType','loperBlumotion','loperKg','loperLengte','loperTipon');
// Kijken of al bestaat
try
{
$sql = "SELECT
id
FROM
lade
WHERE
ladetype = '".$post['ladeType']."' AND houtsoort = '".$post['ladeSoort']."' AND oppervlak = '".$post['ladeOpp']."' AND breedte = '".$post['ladeBreedte']."' AND diepte = '".$post['ladeDiepte']."' AND hoogte = '".$post['ladeHoogte']."' AND bodem = '".$post['ladeBodem']."' AND binnenlade = '".$post['ladeBinnen']."' AND greep = '".$post['ladeGreep']."' AND bestek = '".$post['ladeBestek']."' AND front_verst = '".$post['ladeFrontVerst']."'";
$stmt = $this->db->prepare($sql);
$stmt->execute();
$rows = $stmt->rowCount();
}
catch(PDOException $e)
{
echo '<pre>';
echo 'Regel: '.$e->getLine().'<br>';
echo 'Bestand: '.$e->getFile().'<br>';
echo 'Foutmelding: '.$e->getMessage();
echo '</pre>';
}
if($rows == 0)
{
if((isset($post['loperType']) && $post['loperType'] == 'geen') || !isset($post['loperType']))
{
// Lade invoegen
try
{
// Query samenstellen
$sql = 'INSERT INTO lade VALUES (NULL';
foreach($aLade as $index){
$sql .= ', ';
if(isset($post[$index]) && ($post[$index] != 'n.v.t.')){
$post[$index] = $this->db->quote($post[$index]);
$sql .= $post[$index];
} else $sql .= 'NULL';
}
$sql .= ')';
$stmt = $this->db->prepare($sql);
$stmt->execute();
}
catch(PDOException $e)
{
echo '<pre>';
echo 'Regel: '.$e->getLine().'<br>';
echo 'Bestand: '.$e->getFile().'<br>';
echo 'Foutmelding: '.$e->getMessage();
echo '</pre>';
}
// Winkelwagen genereren
$ladeId = $this->db->lastInsertId();
$_SESSION['cart'][] =
array
(
'ladeId' => ''.$ladeId.'',
'Stuks' => ''.$post['ladeStuks'].'',
'mode' => 1
);
}
else
{
try
{
// Lade & loper invoegen
$this->db->beginTransaction();
// Query (lade) samenstellen
$sql = 'INSERT INTO lade VALUES (NULL';
foreach($aLade as $index){
$sql .= ', ';
if(isset($post[$index]) && ($post[$index] != 'n.v.t.')){
$post[$index] = $this->db->quote($post[$index]);
$sql .= $post[$index];
} else $sql .= 'NULL';
}
$sql .= ')';
// Query (loper) samenstellen
$sql2 = 'INSERT INTO loper VALUES (NULL';
foreach($aLoper as $index){
$sql2 .= ', ';
if(isset($post[$index]) && ($post[$index] != 'n.v.t.')){
$post[$index] = $this->db->quote($post[$index]);
$sql2 .= $post[$index];
} else $sql2 .= 'NULL';
}
$sql2 .= ')';
// ID's verkrijgen
$this->db->query($sql);
$ladeId = $this->db->lastInsertId();
$this->db->query($sql2);
$loperId = $this->db->lastInsertId();
$this->db->commit();
}
catch(PDOException $e)
{
if(isset($db))
{
$db->rollBack();
}
echo '<pre>';
echo 'Regel: '.$e->getLine().'<br>';
echo 'Bestand: '.$e->getFile().'<br>';
echo 'Foutmelding: '.$e->getMessage();
echo '</pre>';
}
/* Debug */
echo $ladeId; echo $loperId;
// Winkelwagen genereren
$_SESSION['cart'][] =
array
(
'ladeId' => ''.$ladeId.'',
'Stuks' => ''.$post['ladeStuks'].'',
'loperId' => ''.$loperId.'',
'mode' => 2
);
}
}
}
}
?>I've used the SELECT query above, but didnt work fine for me and now trying to do it with a UNIQUE index. I used this:
ADD UNIQUE `ladetype` ( `ladetype` , `houtsoort` , `oppervlak` , `breedte` , `diepte` , `hoogte` , `bodem` , `binnenlade` , `greep` , `bestek` , `front_verst` ) (via PHPMYAdmin)
But still i get the same inserts:
http://yfrog.com/0bdatabasshj
Any1 can help me?