How to delete and insert simultaneously using a dropdown menu on PHP and MySQL

Question

I'm having troubles trying to insert and delete using a dropdown menu at the same time

<div id="blabla">
    <div style="position: relative;">
        <p style="position: absolute; top: 185px; left: 58px;">  
            <select name="lista">
                <option selected="selected"></option>
                <optgroup label="Selecciona">
                    <option> Option 1</option>
                    <option> Option 2</option>
                    <option> Option 3</option>
                    <option> Option 4</option>
                    <option> Option 5</option>
                </optgroup>
            </select>

and this is the ADD.php code

   <html>
<head>
</head>
<body>

 <?php
if(isset($_POST['justifica']) && !empty($_POST['justifica']) &&
isset($_POST['si']) && !empty($_POST['si']) &&
isset($_POST['lista']) && !empty($_POST['lista']))
{
mysql_connect("123", "123", "p123") or die(mysql_error()) ; 
mysql_select_db("123") or die(mysql_error()) ; 

mysql_query("INSERT INTO dos (lista,justifica,si) VALUES ('$_POST[lista]','$_POST[justifica]','$_POST[si]')");
mysql_query("DELETE FROM dos2 WHERE lista5='$_POST[lista]')");
if ($_POST['value'] === '') {
    $_POST['value'] = null; // null en mayuscula si es SQL
}
echo "<img src=imagenes/Satisf.jpg>";
}else{
echo "<img src=imagenes/Error.jpg>";
}
?>

</body>
</html>

I want to INSERT into table name "dos" and, at the same time, DELETE from table name "dos2" when I press the Submit button

On form "Option1" will insert "Option1" on table "dos" and, at the same time, delete "Option1" from table "dos2".

The answer to this question is just too obvious. It won't help anyone in the future. — developerwjk
– developerwjk, Commented Apr 28, 2014 at 23:33
@developerwjk it is? i have no idea what the answer is, guess i'm dumb. user3583216 whats the relationship between dos and dos2, how do we know what id you want to delete? — user557846
– user557846, Commented Apr 28, 2014 at 23:41
Are you trying to INSERT a row into "dos" with "Option 1" as lista and DELETE a row in "dos2" that has "Option 1" as lista? I know it may sound redundant, but I wanted to clarify your English. If this IS the case, then you do need 2 queries like you have, and the second one will just be: DELETE FROM dos2 WHERE lista2='$_POST[lista]' — Sunny Patel
– Sunny Patel, Commented Apr 28, 2014 at 23:49
@LaughDonor Yeah, thats what i want!, why do u mean i dont need 2 querys? do i need to combine them or something? — user3583216
– user3583216, Commented Apr 29, 2014 at 0:17
You would likely need to do this in one of three ways: 1) do both query operations in a transactions to make the act atomically 2) create a trigger on the insert table to delete the row from the delete table automatically 3) write a stored procedure to wrap the insert and delete operations. — Mike Brant
– Mike Brant, Commented Apr 29, 2014 at 0:35

Linuxx · Accepted Answer · 2014-04-29 04:03:43Z

1

I would use MySQLi to do this, and I prefer OOP...

<?php

class MyDBHandle
{
    public $objDB;

    //Creates the DB Object
    public function Init($strHost, $strUsername, $strPassword, $strDB)
    {
        $this->objDB = new mysqli($strHost, $strUsername, $strPassword, $strDB);

    }

    //SQL Query 1
    public function Dos($strLista, $strJustifica, $strSi)
    {
        $objStatement = $this->objDB->prepare("INSERT INTO dos ('lista', 'justifica', 'si') VALUES (?, ?, ?)");
        $objStatement->bind_param("sss",
                $strLista, 
                $strJustifica, 
                $strSi);

        $objStatement->execute();
        $objStatement->free_result();
    }

    //SQL Query 2
    public function Dos2($strLista)
    {

        $objStatement = $this->objDB->prepare("DELETE FROM dos2 WHERE lista2=?");
        $objStatement->bind_param("s",
                $strLista);

        $objStatement->execute();
        $objStatement->free_result();
    }

    //disconnect from the db
    public function Disconnect()
    {
        $this->objDB->Close();
    }
}

//db variables
$dbHost = "xxx";
$dbUser = "xxx";
$dbPass = "xxx";
$dbDB = "xxx";

//initialize and connect to the DB
$objDB = new MyDBHandle();
$objDB->Init($dbHost, $dbUser, $dbPass, $dbDB);

//here is a moded version of your code to execute the new methods
if(isset($_POST['justifica']) && !empty($_POST['justifica']) &&
isset($_POST['si']) && !empty($_POST['si']) &&
isset($_POST['lista']) && !empty($_POST['lista']))
{
    $objDB->Dos($_POST['lista'], $_POST['justifica'], $_POST['si']);
    $objDB->Dos2($_POST['lista']);
}
$objDB->Disconnect();


?>

edited Apr 29, 2014 at 4:03

answered Apr 29, 2014 at 0:46

Linuxx

2591 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

user3583216 Over a year ago

Call to a member function bind_param() on a non-object (line 23, the first bind)<- , im undersating ur code and i know by now that bind_param is substitute for those (?) but i didnt find a way to make it work

Linuxx Over a year ago

If you look, all of the DB code is written within the "class MyDBHandle { ... }". You must have that written above your initialization of the object with "$objDB = new MyDBHandle()". Just like you have to write a function above the area you use it.

user3583216 Over a year ago

yes is written like that (same as the code u posted, the only thing i changed was the db variables).. you mean i have to add some more code or something?

Linuxx Over a year ago

I had some bad code (some of my template code) within Dos2. Recopy Dos2. Sorry.

user3583216 Over a year ago

Still give me an error, Call to a member function bind_param() on a non-object on line 24, i cant figure it out what the problem is :/

|

Collectives™ on Stack Overflow

How to delete and insert simultaneously using a dropdown menu on PHP and MySQL

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related