Delete a row in mysql with php

Question

I'm doing a little php + mysql for deleting rows in my database, the problem that when I press the button and nothing happen. I would appreciate your help.

This PHP shows a list of courses that a type of employee has to do, the delete button should delete the course of the list that you select it.

for example

cargocurso.php?id=1

shows the type of employee, for example:

Sales Manager

and in a table the courses related with that type of empleyee:

1 | Sales | (delete button)

<?php

$link = mysql_connect("localhost", "root","123456"); 
mysql_select_db("ecl", $link); 

if (isset($_GET['id'])) {

    $id=$_GET['id'];;

    $sql = mysql_query("select k.idcargo, c.fullname, k.nombre_cargo, d.cursoid, d.id from mdl_course as c 
                        inner join mdl_user_cursos_asoc as d on c.id = d.cursoid
                        inner join mdl_user_cargo as k on d.cargoid = k.idcargo where k.idcargo=$id", $link);

    if (isset($_POST['delete'])) {
    $idasoc = $_POST['idasoc'];
    $sql2 = "DELETE FROM mdl_user_cursos_asoc WHERE id = '.$idasoc.'";
    $result = mysql_query($sql2);

        echo "has been deleted.";
    }   

    if(mysql_num_rows($sql) == 0) {

        echo "No hay cursos asociados";

    } else {

    $cargnombre = mysql_fetch_array($sql);

        echo $cargnombre[2]."</br></br>";

    echo "<form action='".$_SERVER['PHP_SELF']."' method='post'>";
    echo "<table border='1px'>";

    while ($row = mysql_fetch_array($sql)) {
                echo "<tr>";
                echo "<input type='hidden' name='idasoc' value = '.$row[4].'>";
                echo "<td>$row[1]</td>";
                echo "<td><input type='submit' name='delete' value='Eliminar'></td>";
                echo "</tr>";
            }
    echo "</table>";
    echo "</form>";


    }

    } 
?>

Thanks for you help!

You shouldn't use the deprecated `mysql_*` functions. Use mysqli or PDO instead and your code is wide open to SQL-injections. Always escape your inputs! (If you use mysqli or PDO you should use prepared statements for this). — M. Eriksson, Jan 21 '16 at 23:01
Have you checked if the POST variable $idasoc has any values using isset($_POST['idasoc'])???? — gomesh munda, Jan 21 '16 at 23:01
Consult these following links http://php.net/manual/en/function.mysql-error.php and http://php.net/manual/en/function.error-reporting.php and apply that to your code and you'll see the error you made. — Funk Forty Niner, Jan 21 '16 at 23:13

Professor Abronsius · Accepted Answer · 2016-01-21T23:06:07.207

There is a minor error in the sql statement for deleting the record - there are spurious periods . in there. Change to:

$sql2 = "DELETE FROM mdl_user_cursos_asoc WHERE id = '{$idasoc}'";

Also, others may note lso - stop using the now deprecated mysql_* functions as they are generally now considered unsafe.

In addition to the misuse of quotes and periods in the sql there was another in the loop that outputs the contents of the table.

<?php
    $link = mysql_connect("localhost", "root","123456"); 
    mysql_select_db("ecl", $link); 

    if( isset( $_GET['id'] ) ) {

        $id=$_GET['id'];

        $sql = mysql_query("select k.idcargo, c.fullname, k.nombre_cargo, d.cursoid, d.id from mdl_course as c 
                            inner join mdl_user_cursos_asoc as d on c.id = d.cursoid
                            inner join mdl_user_cargo as k on d.cargoid = k.idcargo where k.idcargo=$id", $link );

        if ( isset( $_POST['delete'], $_POST['idasoc'] ) ) {/* Combine `isset` to test multiple POST vars are set */
            $idasoc = $_POST['idasoc'];
            $sql2 = "DELETE FROM mdl_user_cursos_asoc WHERE id = '{$idasoc}'";/* changed here */
            $result = mysql_query( $sql2 );

            echo "has been deleted.";
        }   

        if( mysql_num_rows( $sql ) == 0 ) {
            echo "No hay cursos asociados";
        } else {
            $cargnombre = mysql_fetch_array( $sql );
            echo $cargnombre[2]."</br></br>";


        echo "
        <form action='".$_SERVER['PHP_SELF']."' method='post'>
            <table border='1px'>";

            while( $row = mysql_fetch_array( $sql ) ) {
                    echo "
                    <tr>
                        <td>
                            <input type='hidden' name='idasoc' value = '{$row[4]}'><!-- changed here -->
                            {$row[1]}
                        </td>
                        <td><input type='submit' name='delete' value='Eliminar'></td>
                    </tr>";
                }
        echo "
            </table>
        </form>";
        }
    } 
?>

If you are not going to change to mysqli or PDO with prepared statements ( which mitigate against sql injection ) then at least ensure you filter any data received from your users - for instance mysql_real_escape_string offers at least some protection.

I have a kind of other problem, when you press delete button, It deleted the last row not the one you select. Any idea what can be wrong? — rfcabal, Jan 28 '16 at 01:19
yes. in general - change the button type from `submit` to `button`, add a `data-id=$id` attribute and use javascript to submit the form via an eventlistener attached to the button — Professor Abronsius, Jan 28 '16 at 07:41

Delete a row in mysql with php

1 Answers1