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, Apr 28 '14 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? — , Apr 28 '14 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, Apr 28 '14 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, Apr 29 '14 at 00:17
@user3583216 I did not say you **did not** need 2, I said you **DID** need 2 queries; this is because you are performing 2 different operations to the database, `INSERT` and `DELETE`. Also, you should also consider using [MySQLi over MySQL](http://stackoverflow.com/questions/8891443/when-should-i-use-mysqli-instead-of-mysql). — Sunny Patel, Apr 29 '14 at 00:19
i see, well i didnt work :/, it does not delete row "Option1" on table "dos2" but it does INSERT into table "dos" — user3583216, Apr 29 '14 at 00:24
What columns do you have in "dos" and "dos2"? Also, I saw that you edited your original question and removed your original code which changes the question and **added personal information**. — Sunny Patel, Apr 29 '14 at 00:26
woop, yah i update with the new code, i have "lista", "justifica", "si" as a colums on table "dos" and "lista2" as a column on table "dos2" i want to insert the options on table "dos" column "lista" and delete "the option i pick" on table "dos2" and column "lista2" — user3583216, Apr 29 '14 at 00:31
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, Apr 29 '14 at 00:35
@user3583216 Your variables in their $_POST in SQL must have quotes, `$_POST['lista']` — Lucas Henrique, Apr 29 '14 at 01:38
@MikeBrant ill try to use one of those methods, thanks mate! — user3583216, Apr 29 '14 at 02:58

Linuxx · Accepted Answer · 2014-04-29T04:03:43.087

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 '14 at 04:03

answered Apr 29 '14 at 00:46

Linuxx

261
1
8

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 – user3583216 Apr 29 '14 at 02:57
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. – Linuxx Apr 29 '14 at 03:02
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? – user3583216 Apr 29 '14 at 03:21
I had some bad code (some of my template code) within Dos2. Recopy Dos2. Sorry. – Linuxx Apr 29 '14 at 03:45
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 :/ – user3583216 Apr 29 '14 at 03:58
The SQL Statement is failing ($this->objDB->prepare). You will need to run it by hand and see why it's failing, or you can do some try, catch. – Linuxx Apr 29 '14 at 04:02
I had a typo. The table is called "lista2", not "lista5". – Linuxx Apr 29 '14 at 04:04

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

1 Answers1