Board index   FAQ   Search  
Register  Login
Board index php forum :: php coding PHP coding => Mail

deleting records into two tables... pls help... thanks

the mail() function

Moderators: macek, egami, gesf

deleting records into two tables... pls help... thanks

Postby halli16 » Thu Jun 21, 2012 12:45 am

Hi! I'm working on a 'Record Management System' for my thesis. I had a problem with deleting two records into two tables. When I delete one record in 'new_dockets', the duplicate in 'all_dockets' should be delete also.

SQL:

TABLE1: 'all_dockets'

id, classification, tcc_number, tcc_date, company_name, status_request, time_date_created

TABLE2: 'new_dockets'

id, classification, tcc_number, tcc_date, time_date_created



here's the code:

new_docket.php
Code: Select all
    <div align="center" class="formarea"> 
                 
            <div> 
            <br /> 
                    <font face="Verdana" style="font-size: 36px; font-style:normal; color: #333333"><b>NEW DOCKET</b></font> 
                    <br /> 
                    <font face="Verdana" style="font-size: 22px; font-style:normal; color: #000000">DOCKET INFORMATION</font> 
                    <br /><br /> 
         
    <?php 
     
            include('db_connect.php'); 
             
            $per_page = 10; 
             
            $result = mysql_query("SELECT * FROM new_dockets ORDER BY time_date_save DESC"); 
            $total_results = mysql_num_rows($result); 
            $total_pages = ceil($total_results / $per_page); 
     
            if (isset($_GET['page']) && is_numeric($_GET['page'])) 
            { 
                    $show_page = $_GET['page']; 
                     
                    if ($show_page > 0 && $show_page <= $total_pages) 
                    { 
                            $start = ($show_page -1) * $per_page; 
                            $end = $start + $per_page;   
                    } 
                    else 
                    { 
                            $start = 0; 
                            $end = $per_page;   
                    }                 
            } 
            else 
            { 
                    $start = 0; 
                    $end = $per_page;   
            } 
             
             
            echo "<p><a href='viewallnewdockets.php'>View First</a> | <b>View Page:</b> "; 
            for ($i = 1; $i <= $total_pages; $i++) 
            { 
                    echo "<a href='viewallnewdockets.php?page=$i'>$i</a> "; 
            } 
            echo "</p>"; 
                     
     
            echo "<table border='1' cellpadding='10'>"; 
            echo "<tr><th>Delete</th> <th>Date Created</th> <th>TCC Number</th> <th>Company Name</th> <th>Index Number</th> <th>View</th> <th>Edit</th> </tr>"; 
     
            for ($i = $start; $i < $end; $i++) 
            { 
     
                    if ($i == $total_results) { break; } 
             
     
                    echo "<tr>"; 
                    echo '<td align="center"><a href="deleteNewDocket.php?id=' . mysql_result($result, $i, 'id') . '" onclick="return confirm(\'Are you sure you want to delete this?\');"><img src="images/x.jpg" /></a></td>'; 
                    echo '<td>' . mysql_result($result, $i, 'time_date_save') . '</td>'; 
                    echo '<td><b>' . mysql_result($result, $i, 'tcc_number') . '</b></td>'; 
                    echo '<td>' . mysql_result($result, $i, 'company_name') . '</td>'; 
                    echo '<td><b>' . mysql_result($result, $i, 'cabinet_row'); 
                    echo '<font>-</font>' . mysql_result($result, $i, 'cabinet_name'); 
                    echo '<font>-</font>' . mysql_result($result, $i, 'cabinet_layer') . '</b></td>'; 
                    echo '<td align="center"><a href="viewnewdocket.php?id=' . mysql_result($result, $i, 'id') . '" ><img src="images/view1.jpg" /><a></td>'; 
                    echo '<td align="center"><a href="editnewdocket.php?id=' . mysql_result($result, $i, 'id') . '" ><img src="images/edit.jpg" /></a></td>'; 
                    echo "</tr>";   
            } 
             
            echo "</table>";   
                     
    ?> 
                <p align="center"><a href="reports.php"><img src="images/back2.jpg"/></a></p> 
            </div> 
        </div> 



Code: Select all
delete.php

    <?php 
     
        include('db_connect.php'); 
       
        if (isset($_GET['id']) && is_numeric($_GET['id'])) 
        { 
            $id = $_GET['id']; 
       
            $result = mysql_query("DELETE FROM new_dockets WHERE id = $id"); 
       
            header("Location: new_docket.php"); 
        } 
       
        else 
        { 
            header("Location: new_docket.php"); 
        } 
     
    ?> 




they have different ID's...
thanks....
halli16
New php-forum User
New php-forum User
 
Posts: 1
Joined: Thu Jun 21, 2012 12:39 am

Re: deleting records into two tables... pls help... thanks

Postby freshnet » Sun Jun 24, 2012 6:40 am

I think you're missing some of your code. I only see one delete query.

I would suggest that if you have two related tables and the PKs are different, that you make a third table to relate the two. This will let you look up one id from the other, which will make running the second delete query easier. If you create a foreign key relationship between this new table and the other tables, you can set the records to 'ON DELETE CASCADE', which will automatically remove the child rows. Alternatively, you could just add a third delete query to delete from the new table.
User avatar
freshnet
php-forum Active User
php-forum Active User
 
Posts: 278
Joined: Tue Feb 22, 2011 8:19 am
Location: Canada


Return to PHP coding => Mail

Who is online

Users browsing this forum: No registered users and 1 guest

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.