PHP/Mysql Add Category to Materia

Question

Good staff never had much interest in the back-end area, but for greater reasons I’m having to learn.

So far I can do the basics, which are insert, delete, update, view the data using php/mysql.

Now I came across a problem that I could not do using the handouts, which is to register a subject in a certain category.

I tried to do an INSERT followed by a SELECT but I was not successful, because the material does not change the id_cat, it is with the value "0" and not with the value "1" that would reference the category.

If anyone could give me an example of what INSERT + SELECT would look like.

Thank you very much.

(NOTE: I’m not using OO..., I’m doing it the old way with mysql_, which is the easiest way to learn.)

Full code, I added id_cat but it keeps generating id 0, to check if it was at least identifying SELECT I changed the int id_cat to varchar and the same record the category name in id_cat normally.

<?php
if(isset($_POST['enviar'])) {

    $id_cat = $_POST['cat_lancamentos'];
    $titulo = $_POST['titulo'];
    $original = $_POST['original'];
    $capa = $_FILES["capa"] ["name"];
    $lancamento = $_POST['lancamento'];
    $genero = $_POST['genero'];
    $diretor = $_POST['diretor'];
    $sinopse = $_POST['sinopse'];
    $midia = $_POST['video'];

    $caminho_img = "../capas/";    
    if(move_uploaded_file($_FILES["capa"]["tmp_name"],$caminho_img."/".$capa)) {    
    $mysql_path = $caminho_img."/".$capa;

            $sql=mysql_query("INSERT INTO lancamentos  VALUES('', '$id_cat', '$titulo', '$original', '$capa', '$diretor', '$lancamento', '$genero', '$midia', '', '', '$sinopse')");
            echo "INSERT INTO lancamentos VALUES('', '$id_cat', '$titulo', '$original', '$capa', '$diretor', '$lancamento', '$genero', '$midia', '', '', '$sinopse')";
            if($sql)

            {
                header("location: lancamentos-gerenciar.php");
        }
    }
}
?>

<form method="post" action="" enctype="multipart/form-data">
<pedido>
<botaoexcluir><a href="#"><font color="red">Cancelar</font></a></botaoexcluir> <botaoadicionar><input type="submit" name="enviar" value="Cadastrar"></botaoadicionar>
<capa><img id="uploadPreview" width="152px" height="214px" /> </capa>
<campopedido><strong>Titulo:</strong> <input type="text" name="titulo" id="titulo" size="50"></campopedido>
<campopedido><strong>Titulo Original:</strong> <input type="text" name="original" id="original" size="40"></campopedido>
<campopedido><strong>Lançamento:</strong> <input type="text" name="lancamento" id="lancamento" size="40"></textarea></campopedido>
<campopedido><strong>Genero:</strong> <input type="text" name="genero" id="genero" size="40"></campopedido>
<campopedido><strong>Categoria:</strong> 
<select name="cat_lancamentos" size="1" id="cat_lancamentos">
<option value="">Selecionar</option>
<?php
$sql = "SELECT * FROM cat_lancamentos";
$resultado = mysql_query($sql) or die('Erro ao selecionar a categoria: ' .mysql_error());

while($linhas = mysql_fetch_array($resultado))
{
    $selected = ($linhas['nome'] == $_POST['cat_lancamentos'])?'selected':'';
?>
    <option value="<?php echo $linhas['nome'];  ?>" <?php echo $selected; ?>>
        <?php echo $linhas['nome']; ?>
    </option>
<?php 
}
?>
</select>
</campopedido>
<campopedido><strong>Dirigido por:</strong> <input type="text" name="diretor" id="diretor" size="40"></campopedido>
<campopedido><strong>Selecionar capa:</strong> <input id="uploadImage" type="file" name="capa" onchange="PreviewImage();" /></campopedido>

<sinopse>
<strong>Sinopse</strong><br />
<textarea name="sinopse" id="sinopse" size="85" cols="40" rows="5" style="width:620px; height:80px; margin-top:10px;"></textarea>
</sinopse>
</pedido>

<titulo> Midia</titulo>
<pedido>
<codigo>
<p>Codigo de adicição de midia</p>
<textarea name="video" id="video" size="85" cols="40" rows="5" style="width:600px; height:120px; margin-top:-20px;margin-bottom:10px;">

</textarea>

</codigo>
</pedido>
</form>

My Mysql Tables

CREATE TABLE `cat_lancamentos` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `nome` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM CHARSET=utf8 AUTO_INCREMENT=1 ;

CREATE TABLE `lancamentos` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id_cat` bigint(20) NOT NULL,
  `titulo` varchar(255) NOT NULL,
  `original` varchar(255) NOT NULL,
  `capa` varchar(100) NOT NULL,
  `diretor` varchar(50) NOT NULL,
  `lancamento` varchar(50) NOT NULL,
  `genero` varchar(255) NOT NULL,
  `midia` varchar(255) NOT NULL,
  `temporada` varchar(50) NOT NULL,
  `episodios` varchar(20) NOT NULL,
  `sinopse` text NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM CHARSET=utf8 AUTO_INCREMENT=1 ;

Answer 1

Look in your table dung where this:

`id_cat` bigint(20) NOT NULL, // aqui você diz que não pode ser null

Look at your php where you have the Insert:

$sql = mysql_query("INSERT INTO lancamentos (id, id_cat, titulo, original, capa, diretor, lancamento, genero, midia, temporada, episodios, sinopse)  VALUES('', '', '$titulo', '$original', '$capa', '$diretor', '$lancamento', '$genero', '$midia', '', '', '$sinopse')

You are passing null in id_cat

Another thing, you made the structure for a FOREIGN KEY, but did not relate the tables, that is to say FK (id_cat) this not guaranteeing the integrity of the data, would have to do the relationship so:

ALTER TABLE `lancamentos` 
ADD CONSTRAINT `fk_lancamentos`
FOREIGN KEY (`id_cat`)
REFERENCES `cat_lancamentos` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION;

EDIT: In order for id_cat to be registered correctly, it must be this logic:

1. Have at least one record on cat_lancamentos
2. on the screen the user will choose this release, then the id of it shall be kept for the next step
3. Record in the table releases the id cat_lancings on id_cat and its other attributes

Answer 2

I did not understand very well what you were wanting, from what I understood, first you must do the Internet and then select, and the select together you wanted would be this?

select l.id, ct_l.nome, l.titulo, l.original, l.capa, l.diretor, l.lancamento, l.genero, l.midia, l.temporada, l.episodios, l.sinopse from lancamentos l
inner join cat_lancamentos ct_l on ct_l.id = l.id_cat;