Insert/update based on 2 DB criteria

Databases
1

Hello

I’m trying to create a page view for a project I’m working on and I can’t
seem figure out the MySQL statement. Any assistent you can provide would be greatly appreciated.

I have 2 critaries:
IP address (db field: unique)
Project ID

The script itself works if I take out the when clause. It updates the IPaddres field if not updates the count field.

What I’m looking for:
If a record meets the criataria (projectID & IPaddress) increment the count field. (returning user viewing the same project)
else
update the table with projectID, IPaddress and increment the count field ( this could be a new user or a returning user whos viewing a different project)

  $testUserIP =  '192.168.0.17';
 $testCount = 1;  
 $ProjectID = 35;  
 try{
      $SMT1=$dbcon->prepare("INSERT INTO tbl_testTB (test_visitedIP, test_count, test_prjID)
                            VALUES(:userIP, :countIP, :PrjID)
                            ON DUPLICATE KEY UPDATE
                            test_count = test_count + 1
                            WHERE test_prjID <> $ProjectID ");
     $SMT1->bindParam(':userIP',  $testUserIP);
     $SMT1->bindParam(':countIP', $testCount);  
     $SMT1->bindParam(':PrjID',   $ProjectID);   
     $SMT1->execute();   
} catch(PDOException $e) { echo 'Unable to update DB : ' . $e->getMessage();   }

how can i fix this issue?

2

the WHERE clause is not allowed in that syntax

i think what you need is a unique composite key consisting of IP and project

that way, the same user (IP) looking at a new project will force a new INSERT

1 Like
3

Hi, thanks for the reply back.

MySQL skills are a bit weak so how do I achieve that or re-write the above script?

Thanks

4

could you please do this and post the results –

SHOW CREATE TABLE tbl_testTB
5

Hi r937,

You asked the same question on another post for table information but I wasn’t able to give that information due my MYSQL knowledge but this time you provided the know how thanks. :slight_smile:

as per the make up of the table:

tbl_testTB
CREATE TABLE `tbl_testTB` (
`test_index` int(11) NOT NULL AUTO_INCREMENT,
`test_prjID` int(11) NOT NULL,
`test_historyDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`test_visitedIP` varchar(100) NOT NULL,
`test_count` int(11) NOT NULL,
PRIMARY KEY (`test_index`),
UNIQUE KEY `test_visitedIP` (`test_visitedIP`),
UNIQUE KEY `test_visitedIP_2` (`test_visitedIP`),
UNIQUE KEY `test_prjID` (`test_prjID`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4

thanks again.

6

[code]ALTER TABLE tbl_testTB
DROP KEY test_visitedIP ;

ALTER TABLE tbl_testTB
DROP KEY test_visitedIP_2 ;

ALTER TABLE tbl_testTB
DROP KEY test_prjID ;

ALTER TABLE tbl_testTB
ADD UNIQUE ( test_visitedIP , test_prjID ) ;[/code]

that should fix your issues

1 Like
7

Hi r937,

Thanks for the assistant. your reply seem to have fixed my problems… thanks again.

8

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.