Update database value from 0 to 1 based on a selected option after form send

Question

my problem seems simple but I do not know much about PHP.

I have two tables:

Table 1: ** certificates ** / Table2: ** keys **

They don't have the same order or amount of columns. Table2 has predeterminated values while table1 is clean, receiving data inserted by the user.

The user uses a form to insert data into table1. In this form, there's a select populated with data from table2.

The select options are keys, like those:

1112, 1113, 1114, etc.

What I need is when the user insert one of the keys in table1, the status of the key in table2 is updated from 0 to 1. (so the key will not be shown in the select).

Here's the code that inserts the data into table1

<?php
if ($registro != "") {
    include("conexao-certificado.php");
    $conexao = @mysql_connect($host, $user, $pass)
        or die("Cadastro");
    $query = "INSERT INTO certificados VALUES ('$id','$registro','$aluno','$professor','$escola','$publicado_por','$registrado_por','$ano','$curso','$cpf','$grade', now() );";
    mysql_query($query, $conexao);
    $url = basename($_SERVER['PHP_SELF']);
    echo "<script>window.location='$url'</script>";
} else {

}

?>

And here's the code that populates the select:

<?php
include("conexao-certificado.php");
$conexao = @mysql_connect($host, $user, $pass);
if (!$conexao)
    die("Nao foi possivel conectar no servidor MySQL. Erro: " . mysql_error());
mysql_select_db($db) or die("Nao foi possivel usar o banco de dados. Erro: " .
        mysql_error());
$palavra = str_replace(" ", "%", $HTTP_POST_VARS[palavra]);
$qr = "SELECT idchave FROM chaves WHERE (publicado_por LIKE '%" . $_SESSION['nome'] . "%" . $_SESSION['sobrenome'] . "%' AND status LIKE '0' )  ORDER BY idchave ASC;";
$sql = mysql_query($qr);

?>
<select name="registro" id="registro">
    <?php
    echo '<option value="">Selecione a chave</option>';
    while ($r = mysql_fetch_array($sql)) {
        echo '<option value="' . $r['idchave'] . '">' . $r['idchave'] . '</option>';
    }

    ?>
</select>

I tried puting this after the INSERT, but it only works if i specifie the key like "idchave=1112".

$atualiza = "UPDATE chaves SET status=1 WHERE idchave=$registro;";
mysql_select_db($db);
mysql_query($query,$conexao);
mysql_query($atualiza,$conexao);

I found a solution. So here's what I did. First a created a column in table1 with status always being 1 while on table2 the status are always 0.

After that I just added a INNER JOIN in my code.

Here's the code:

$atualiza = "UPDATE chaves c1
INNER JOIN certificados c2 ON c1.registro = c2.registro
SET c1.status = c2.status;";
mysql_query($atualiza,$conexao);

Thank you for all the help.

FYI, [you shouldn't use `mysql_*` functions in new code](http://stackoverflow.com/questions/12859942/). They are no longer maintained [and are officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). See the [red box](http://php.net/manual/en/function.mysql-connect.php)? Learn about [*prepared statements*](https://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://php.net/manual/en/mysqlinfo.api.choosing.php) will help you decide which one is best for you. — John Conde, May 09 '17 at 00:00
Your script is at risk of [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Have a look at what happened to [Little Bobby Tables](http://bobby-tables.com/) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). — John Conde, May 09 '17 at 00:00
Don't use the error suppression operator (@) as it hides error messages that may be helpful in debugging your code. You should also always write your code so it does not generate any PHP errors including notices. — John Conde, May 09 '17 at 00:00
At [so] you are expected to try to **write the code yourself**. After **[doing more research](//meta.stackoverflow.com/questions/261592)** if you have a problem you can **post what you've tried** with a **clear explanation of what isn't working** and providing a [**Minimal, Complete, and Verifiable example**](//stackoverflow.com/help/mcve). I suggest reading [ask] a good question and [the perfect question](http://codeblog.jonskeet.uk/2010/08/29/writing-the-perfect-question/). Also, be sure to take the [tour] and read **[this](//meta.stackoverflow.com/questions/347937/)**. — John Conde, May 09 '17 at 00:01

score 0 · Answer 1 · answered May 09 '17 at 03:39

0

To achieve your goal,

after your insert query on table1, create an update query for your table2 that will update the status to 1, after updating your table2,

add a condition on your dropdown selection query, to display only fields with status0 on your table2,

so that it wont be displayed on the selection anymore

answered May 09 '17 at 03:39

apelidoko

782
1
7
23

Thank you for your reply. I already have a condition that only display the fields with 0. I found a solution using INNER JOIN and edited the question explaining what I did. – Alan Franco May 09 '17 at 03:52

Update database value from 0 to 1 based on a selected option after form send

1 Answers1