update and insert a record depending on row existence

[mythri]

I have 2 tables, access_level and pages. In pages table i will have all the details of page like page_id, code, herf, pagename.. and i will select few from this table and store it in access_level table depending on the department and position. 

Now while editing, i will display all the pages which is stored in access_level pages with a particular page code along with those pages from pages table. if the page_id exists in access_level table, its has to get updated, if its not present then it should get inserted into access_level table. 

These things am doing with checkbox and <li>. 

my access_level table

 

 

and my pages table

 

 

here is my code

$s1 = mysql_query("SELECT pages.page_id as pid, pages.code, pages.page, 
            pages.href, access_level.aid, access_level.page_id as pgid, 
            access_level.department, access_level.position, 
            access_level.active 
        FROM pages
        LEFT JOIN access_level ON (pages.page_id=access_level.page_id 
            AND access_level.department=".$department." 
            AND access_level.position=".$position.") WHERE pages.code='snor die(mysql_error());
				while($s2 = mysql_fetch_array($s1)) {  ?>
				<tr><td><li><?php echo $s2['page']; ?>  </td><td><input type="checkbox" name="sn[]" value="<?php echo $s2['pid']; ?>" <?php if($s2['pgid'] === $s2['pid']) echo 'checked="checked"';?> />
                <input type="hidden" value="<?php echo $s2['pid']; ?>" name="page_id[<?php echo $s2['pgid']; ?>]">

while submittings i am not getting the logic, how can be done. Please somebody suggest me

[mythri]

Can somebody please help?

[ginerjm]

How about re-describing your intentions without using your current design details?  Not understanding your goals from your prior write-up

[Psycho]

I'm not going to try any decipher your code and provide a solution. But, from your explanation, what you want is to use a single INSERT query with the ON DUPLICATE KEY UPDATE clause. This allows you to execute one query which will either insert a record if no unique constraints exist or update an existing record if there are unique constraints.

 

You stated

 

 

if the page_id exists in access_level table, its has to get updated, if its not present then it should get inserted into access_level table.

 

So,the page_id field in the table should be set as a unique field in the DB schema. You can then run a query such as this

 

INSERT INTO access_level
   (page_id, department, position, active)
VALUES
   (5, 3, 4, 1)
ON DUPLICATE KEY UPDATE
    department=VALUES(department),
    position=VALUES(position),
    active=VALUES(active)

 

The part after the "ON DUPLICATE KEY UPDATE" may be confusing. Let me explain:

 

department=VALUES(department)

 

This basically says set the value of the field department to the value that was defined for department in the VALUES clause. This way, if you were building a query, you don't have to insert the same variable data multiple times.