Results 1 to 14 of 14

Thread: how to delete a line from mysql

  1. #1
    Squeeler Vini's Avatar
    Join Date
    Jul 2003
    Location
    Sheffield
    Posts
    1,769
    Thanks
    44
    Thanked
    8 times in 8 posts

    how to delete a line from mysql

    PHP Code:
    <html>
    <head><title>Contacts</title>
    <style type="text/css">
         cell {
                    background: #f0f0f0;
                    font: 12px normal Verdana, Arial, sans-serif;}
        th#tableheader, tr#tableheader {
                    background: #3F789A;
                    border: 1px solid #000000;
                    font: 12px normal Verdana, Arial, sans-serif;
                    font-weight: bold;
                    color: #ffffff;}
        td#tabledata {
                    border: 1px solid #000000;
                    font: 12px normal Verdana, Arial, sans-serif;
                    color: #336699;}

    </style>
    </head>
    <body bgcolor="#f0f0f0">
    <b><font face="Verdana" size="2"><font color="#c0c0c0">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;UK Dialling Code: 00 44 114</font></b></center><br><br>
    <?
        $DBhost 
    'localhost';
        
    $DBuser 'vini';
        
    $DBpass 'moosevanderpasswordthief';
        
    $DBName 'contacts';
        
    $table 'uk';
        
    mysql_connect($DBhost,$DBuser,$DBpass) or die('Unable to connect to database');
        @
    mysql_select_db($DBName) or die('Unable to select database '.$DBName);


        
    $sqlquery "SELECT * FROM $table ORDER BY name";
        
    $result mysql_query($sqlquery);

        if (
    mysql_num_rows($result) < 1)
        {
            echo 
    '<center><p>There Were No Results for Your Search</center>';
        } else {
            echo 
    '<div class=contact><table width="600" align="center">';
                        echo 
    '<tr>
                        <th id="tableheader">Name</th><th colspan="2" id="tableheader">Office</th><th id="tableheader">Mobile</th><th id="tableheader">Skype</th>'
    ;
                while (
    $row mysql_fetch_array($result))
            {

                   echo
    '<tr><td id="tabledata"><b>'.$row['name'].'</b></td>
                        <td align="center" id="tabledata"><b>'
    .$row['office'].'</b></td>
                        <td align="center" id="tabledata"><font color="#FF0000"><b>'
    .$row['ext'].'</b></font></td>
                        <td align="center" id="tabledata"><b>'
    .$row['mobile'].'</b></td>
                        <td align="center" id="tabledata"><a href="skype:'
    .$row['skype'].'?add"><b>'.$row['skype'].'</b></a></td>
                        </tr>'
    ;
            }
            echo 
    '</table></div>';
        }
    ?>

    <!--<br><center><b><font face="Verdana" size="2"><a href="/contacts/">
    <font color="#3F789A">Add New Contact</font></a></font></b></center><br>-->
    <br><br>
    </body></html>
    how do i add a new column with a delete function

    after the skype bit, i want something like

    <td align="center" id="tabledata"><a href="$sql.delete"><b>X</b></a></td>

  2. #2
    HEXUS.net Webmaster
    Join Date
    Jul 2003
    Location
    UK
    Posts
    3,108
    Thanks
    1
    Thanked
    0 times in 0 posts
    You'll need a URL with a parameter in it that is then trapped and runs some SQL. I strongly suggest that you don't delete any records and merely flag them as deleted using an extra column in your database that way you can get stuff back in the event of user error.

    So your a href would be something like http://example.com?action=delete&id=1234 and then you have some code that looks for $_GET['action'] and if that's equal to delete then you run a SQL query to update the record with ID 1234 to set a column called deleted to yes or 1 or something similar

  3. #3
    Squeeler Vini's Avatar
    Join Date
    Jul 2003
    Location
    Sheffield
    Posts
    1,769
    Thanks
    44
    Thanked
    8 times in 8 posts
    Quote Originally Posted by Iain
    You'll need a URL with a parameter in it that is then trapped and runs some SQL. I strongly suggest that you don't delete any records and merely flag them as deleted using an extra column in your database that way you can get stuff back in the event of user error.

    So your a href would be something like http://example.com?action=delete&id=1234 and then you have some code that looks for $_GET['action'] and if that's equal to delete then you run a SQL query to update the record with ID 1234 to set a column called deleted to yes or 1 or something similar
    that sounds perfect, but. being the newb that i am, have no clue on how to code that? is there a tut anyhwere?



    is what im looking for.
    Last edited by Vini; 02-11-2005 at 10:09 AM.

  4. #4
    TiG
    TiG is offline
    Walk a mile in other peoples shoes...
    Join Date
    Jul 2003
    Location
    Questioning it all
    Posts
    6,213
    Thanks
    45
    Thanked
    48 times in 43 posts
    Code:
            
    counter = 0
     while ($row = mysql_fetch_array($result))
            {
     counter ++;
                   echo'<td align="center" id="tabledata"><a href=delete.php?id=counter"><b>X</b></a></td>';
    
            }
            echo '</table></div>';
    I'm no web developer. but the way i'd do it is have the above. A counter loop to tag each row, then on the page it calls, have a sql delete statement referencing the ID. Then just re-direct to the page that you had originally?.

    TiG
    -- Hexus Meets Rock! --

  5. #5
    HEXUS.net Webmaster
    Join Date
    Jul 2003
    Location
    UK
    Posts
    3,108
    Thanks
    1
    Thanked
    0 times in 0 posts
    replace counter with the row ID from the database and TiG's code is the right way to do it.

  6. #6
    Squeeler Vini's Avatar
    Join Date
    Jul 2003
    Location
    Sheffield
    Posts
    1,769
    Thanks
    44
    Thanked
    8 times in 8 posts
    im gonna pass i think, i canne get it working.

    Code:
    <html>
    <head><title>Contacts</title>
    <style type="text/css">
         cell {
                    background: #f0f0f0;
                    font: 12px normal Verdana, Arial, sans-serif;}
        th#tableheader, tr#tableheader {
                    background: #3F789A;
                    border: 1px solid #000000;
                    font: 12px normal Verdana, Arial, sans-serif;
                    font-weight: bold;
                    color: #ffffff;}
        td#tabledata {
    		        border: 1px solid #000000;
    		        font: 12px normal Verdana, Arial, sans-serif;
                    color: #336699;}
    
    </style>
    <link rel="stylesheet" type="text/css" href="../../css/gripple.css">
    </head>
    <body bgcolor="#f0f0f0">
    <?
        $DBhost = 'localhost';
        $DBuser = 'vini';
        $DBpass = 'fffffff';
        $DBName = 'suppliers';
        $table = 'gripple';
        mysql_connect($DBhost,$DBuser,$DBpass) or die('Unable to connect to database');
        @mysql_select_db($DBName) or die('Unable to select database '.$DBName);
    
    
        $sqlquery = "SELECT * FROM $table ORDER BY name";
        $result = mysql_query($sqlquery);
    
        if (mysql_num_rows($result) < 1)
        {
            echo '<center><p>There Were No Results for Your Search</center>';
        } else {
            echo '<div class=main><table width="600" align="center">';
                        echo '<tr>
                        <th id="tableheader">Name</th><th id="tableheader">Telephone</th><th id="tableheader">Account No.</th><th id="tableheader">Supplier Contact</th><th id="tableheader">Engineer Contact</th>';
                while ($row = mysql_fetch_array($result))
            {
    
                   echo'<tr><td id="tabledata"><a href="'.$row['web'].'"><b>'.$row['name'].'</b></a></td>
                        <td align="center" id="tabledata"><b>'.$row['phone'].'</b></td>
                        <td align="center" id="tabledata"><b>'.$row['account'].'</b></td>
                        <td align="center" id="tabledata"><a href="mailto:'.$row['email'].'"><b>'.$row['contact'].'</b></a></td>
                        <td align="center" id="tabledata"><b>'.$row['engineer'].'</b></td>
                        </tr>';
    
            counter = 0
    			 while ($row = mysql_fetch_array($result))
    			        {
    			 counter ++;
    			               echo'<td align="center" id="tabledata"><a href=delete.php?id=counter"><b>X</b></a></td>';
    }
            echo '</table></div>';
        }
    
    
    ?>
    
    <!--<br><center><b><font face="Verdana" size="2"><a href="/contacts/">
    <font color="#3F789A">Add New Contact</font></a></font></b></center><br>-->
    <br><br>
    </body></html>

  7. #7
    TiG
    TiG is offline
    Walk a mile in other peoples shoes...
    Join Date
    Jul 2003
    Location
    Questioning it all
    Posts
    6,213
    Thanks
    45
    Thanked
    48 times in 43 posts
    Vini my code was Psuedo code, its not a cut and replace job at all.

    Code:
    <html>
    <head><title>Contacts</title>
    <style type="text/css">
         cell {
                    background: #f0f0f0;
                    font: 12px normal Verdana, Arial, sans-serif;}
        th#tableheader, tr#tableheader {
                    background: #3F789A;
                    border: 1px solid #000000;
                    font: 12px normal Verdana, Arial, sans-serif;
                    font-weight: bold;
                    color: #ffffff;}
        td#tabledata {
    		        border: 1px solid #000000;
    		        font: 12px normal Verdana, Arial, sans-serif;
                    color: #336699;}
    
    </style>
    <link rel="stylesheet" type="text/css" href="../../css/gripple.css">
    </head>
    <body bgcolor="#f0f0f0">
    <?
        $DBhost = 'localhost';
        $DBuser = 'vini';
        $DBpass = 'fffffff';
        $DBName = 'suppliers';
        $table = 'gripple';
        mysql_connect($DBhost,$DBuser,$DBpass) or die('Unable to connect to database');
        @mysql_select_db($DBName) or die('Unable to select database '.$DBName);
    
    
        $sqlquery = "SELECT * FROM $table ORDER BY name";
        $result = mysql_query($sqlquery);
    
        if (mysql_num_rows($result) < 1)
        {
            echo '<center><p>There Were No Results for Your Search</center>';
        } else {
            echo '<div class=main><table width="600" align="center">';
                        echo '<tr>
                        <th id="tableheader">Name</th><th id="tableheader">Telephone</th><th id="tableheader">Account No.</th><th id="tableheader">Supplier Contact</th><th id="tableheader">Engineer Contact</th>';
                while ($row = mysql_fetch_array($result))
            {
    
                   echo'<tr><td id="tabledata"><a href="'.$row['web'].'"><b>'.$row['name'].'</b></a></td>
                        <td align="center" id="tabledata"><b>'.$row['phone'].'</b></td>
                        <td align="center" id="tabledata"><b>'.$row['account'].'</b></td>
                        <td align="center" id="tabledata"><a href="mailto:'.$row['email'].'"><b>'.$row['contact'].'</b></a></td>
                        <td align="center" id="tabledata"><b>'.$row['engineer'].'</b></td>
                        <td align="center" id="tabledata"><a href=delete.php?id='.$row['RowID'].'"><b>Delete</b></a></td>'
                        </tr>';
    
    }
            echo '</table></div>';
        }
    
    
    ?>
    
    <!--<br><center><b><font face="Verdana" size="2"><a href="/contacts/">
    <font color="#3F789A">Add New Contact</font></a></font></b></center><br>-->
    <br><br>
    </body></html>
    However this is again not perfect PHP code, as i'm no PHP developer, i don't know the syntax but you are going to have to call a page (which you don't have yet) called delete.php which retrieves the ID you pass it which is the ROW ID from the data (Note you have to have this ROW ID field setup which you might not have yet either)

    You then need to have the SQL code to do
    Delete from Table where ROW ID = passedROW ID

    Once this completes return to the page you came from.

    Hope this helps
    TiG
    -- Hexus Meets Rock! --

  8. #8
    Squeeler Vini's Avatar
    Join Date
    Jul 2003
    Location
    Sheffield
    Posts
    1,769
    Thanks
    44
    Thanked
    8 times in 8 posts
    ok, well i have time now, so im playing, i have delete.php which is:

    PHP Code:
    <?
        $DBhost 
    'localhost';
        
    $DBuser 'vini';
        
    $DBpass '444';
        
    $DBName 'suppliers';
        
    $table 'gripple';
        
    mysql_connect($DBhost,$DBuser,$DBpass) or die('Unable to connect to database');
        @
    mysql_select_db($DBName) or die('Unable to select database '.$DBName);


    $result mysql_query("DELETE FROM $table WHERE id=") or print ("Can't delete entry.<br />" $sql "<br />" .
    mysql_error());

    ?>

    ive tried all sorts of expressions to delete but, the WHERE id= isnt being read from the main page.

    PHP Code:
    {

                   echo
    '<tr><td id="tabledata"><a href="'.$row['web'].'"><b>'.$row['name'].'</b></a></td>
                        <td align="center" id="tabledata"><b>'
    .$row['phone'].'</b></td>
                        <td align="center" id="tabledata"><b>'
    .$row['account'].'</b></td>
                        <td align="center" id="tabledata"><a href="mailto:'
    .$row['email'].'"><b>'.$row['contact'].'</b></a></td>
                        <td align="center" id="tabledata"><b>'
    .$row['engineer'].'</b></td>
                        <td align="center" id="tabledata"><a href=contacts/suppliers/delete.php?id='
    .$row['id'].'><b>Delete</b></a></td>
                        </tr>'
    ;
            } 
    any ideas?

    if i run the delete.php on its own with a manually specified 'id' it works fine.

  9. #9
    HEXUS.net Webmaster
    Join Date
    Jul 2003
    Location
    UK
    Posts
    3,108
    Thanks
    1
    Thanked
    0 times in 0 posts
    It can't read from the main page, you need to pass it thorugh as a GET parameter. So the first page needs to redirect to delete.php?id=123 and the your SQL refers to $_GET['id'] which in that example is 123

  10. #10
    Squeeler Vini's Avatar
    Join Date
    Jul 2003
    Location
    Sheffield
    Posts
    1,769
    Thanks
    44
    Thanked
    8 times in 8 posts
    WOOHOO!

    "DELETE FROM $table WHERE id='{$_GET['id']}'"

    wicked gents, thank you very very very very much!

  11. #11
    Seething Cauldron of Hatred TheAnimus's Avatar
    Join Date
    Aug 2005
    Posts
    17,164
    Thanks
    803
    Thanked
    2,152 times in 1,408 posts
    what happens if id is set to an SQL comment, line termination, and then DROP TABLE *;

    you need to validate id first.

    a way to do this in this case is with the intval function, which takes a variable and returns the integer (number only) value.

    http://uk.php.net/intval
    throw new ArgumentException (String, String, Exception)

  12. #12
    Squeeler Vini's Avatar
    Join Date
    Jul 2003
    Location
    Sheffield
    Posts
    1,769
    Thanks
    44
    Thanked
    8 times in 8 posts
    if it was proper important sql stuff going on the webby for the world to see, it would need to be a lot better than it is now, but its internal and no-one here could do anything to it. so it should be ok, many thanks for everyones help

  13. #13
    Seething Cauldron of Hatred TheAnimus's Avatar
    Join Date
    Aug 2005
    Posts
    17,164
    Thanks
    803
    Thanked
    2,152 times in 1,408 posts
    i think your missing the point, its one extra function call, if your going to be let near a comptuer you should start doing things like that without thinking. It shouldn't be a thought to oh, make this secure, it should be i only want a number here, lets force it to be a number. This is why i've grown to hate PHP so much (when i used to love it so!)
    throw new ArgumentException (String, String, Exception)

  14. #14
    Squeeler Vini's Avatar
    Join Date
    Jul 2003
    Location
    Sheffield
    Posts
    1,769
    Thanks
    44
    Thanked
    8 times in 8 posts
    if it was easy for me to do i would, but reading that link made no sense to me.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Nero vision express saying:'Burn process failed'
    By johnnr892 in forum Help! Quick Relief From Tech Headaches
    Replies: 15
    Last Post: 11-12-2005, 11:43 PM
  2. BT Line Length problems
    By CocoPops in forum Networking and Broadband
    Replies: 7
    Last Post: 16-08-2005, 02:29 PM
  3. Nero or Burner ?
    By Foxile in forum Help! Quick Relief From Tech Headaches
    Replies: 30
    Last Post: 04-04-2005, 07:31 AM
  4. Dodgy DVD-r's ?
    By starside in forum Help! Quick Relief From Tech Headaches
    Replies: 12
    Last Post: 27-03-2005, 06:11 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •