PHP/MySQL INSERT INTO Multiple Tables from Forms

PHP
1

hi guys,

am trying to make a simple PHP form submission and insert some data to MySQL, my tables are:

category:
-id
-category_name

table1:
-category_id(FK)
-title
-description

table2:
-table1_id(FK)
-filetype
-filesize
-filedate
-filename

Form:
-Date
-Title
-description
-category(drop down)
-upload file (get the file info like type,ext,size,filename)

to make it short, heres the code:

Form:


<strong>Fill up the form/strong><br /><br>                

    <form enctype="multipart/form-data" action="upfileone.php" method="POST">
        Date: <?php echo date("d-M-Y") ?>

            <p>Title:
                <input type="text" name="title" value="<?php echo $sel_filepage['file_title']; ?>" id="file_title" />
            </p>

            <p>Descriptionbr>
                <textarea name="description" rows="4" cols="24">
                <?php echo $sel_filepage['content']; ?></textarea>
            </p>

        Category:
        <select name="select_cat">
        <?php $cat_set = get_all_categs();
            while($category = mysql_fetch_array($cat_set)){
                $catname = $category['cat_name'];
                echo '<option value="'.$catname.'">'.$catname.'</option>';
            }
        ?>
        </select>
        <br><br>
        <label for="file">Choose File to Upload/label>
        <input type="file" name="upfile" id="upfile" > <br /><br />
        <input type="hidden" name="MAX_FILE_SIZE" value="1000000" />
        <input type="hidden" name="filepage" value="<?php echo $_GET['filepage']?>">
        <input type="submit" name="upload" value="Add" class="pure-button pure-button-success">
        <a href="content.php?filepage=<?php echo $sel_filepage['id']; ?>" class="pure-button">Cancel</a>
    </form> <!-- END FORM -->
Action FILE:

<?php
require_once("includes/functions.php");

// directory to be saved
$target = "server/php/files/";
$target = $target . basename($_FILES['upfile']['name']);

// gets info from FORM
$currentDate = date("Y-m-d");
$file_title = $_POST['title'];
$content = $_POST['description'];
$category = $_POST['select_cat'];
$upfile = ($_FILES['upfile']['name']);

// connects to db
$con = mysqli_connect("localhost", "root", "password", "database");
if(mysqli_connect_errno())
{
    echo "error connection" . mysqli_connect_error();
}

// insert to database
$sql = "INSERT INTO filepages (category_id,file_title,content)
VALUES ('$category','$file_title','$content')";

/*$sql2 = "BEGIN
            INSERT INTO filepages (category_id, file_title, content)
                VALUES ('$category','$file_title','$content')
            INSERT INTO fileserv (file_date)
                VALUES ($currentDate)
            COMMIT";*/

if(!mysqli_query($con, $sql))
{
    die('Error ' . mysqli_error());
}
echo "1 File Added <br>";

    if (file_exists("server/php/files/" . $_FILES["upfile"]["name"]))
      {
      echo $_FILES["upfile"]["name"] . " already exists. ";
      }
    else
      {
        insertFile( $_POST['filepage'], 
                    $_FILES["upfile"]["type"], 
                    ($_FILES["upfile"]["size"] / 1024), 
                    $_FILES["upfile"]["name"]);       
        move_uploaded_file($_FILES["upfile"]["tmp_name"],"server/php/files/" . $_FILES["upfile"]["name"]);

        echo "The FILE " . basename($_FILES['upfile']['name']) . " has been uploaded.<br>";
        echo "Stored in: " . "server/php/files/" . $_FILES["upfile"]["name"] . "<br>";
        echo "Upload: " . $_FILES["upfile"]["name"] . "<br>";
        echo "Type: " . $_FILES["upfile"]["type"] . "<br>";
        echo "Size: " . ($_FILES["upfile"]["size"] / 1024) . " kB<br>";
        echo "Temp file: " . $_FILES["upfile"]["tmp_name"] . "<br>";

      }


?>

It does insert to both tables, now my main problem is:

how to get the ID from drop down menu of category and insert to Filepages.category_id
how to get Filepages.ID data and insert to Fileserve
Thanks!

2

Not sure what you need. You got all your other input, why can’t you do these inputs?

3 

<select name="select_cat">
<?php $cat_set = get_all_categs();
    while($category = mysql_fetch_array($cat_set)){
        $catname = $category['cat_name'];
        echo '<option value="'.$catname.'">'.$catname.'</option>';
    }
?>
</select>

Here, where you’re building your category menu, you’re using cat_name as the value of the option… if you set this to the category ID instead, it’ll be passed through when you submit your form.

After you insert a new row to the Filepages table, you can use the [fphp]mysqli_insert_id[/fphp] function to get the ID of the new row, and then use that to do an insert into the Fileserve table:

$new_id = mysqli_insert_id($con);
4

@fretburner

  1. cat_name is the field under table1, name of category, and category_id is its corresponding ID

  2. will it work if i use only mysql INSERT? like this, actually this gives an error

$sql2 = "INSERT INTO filepages (category_id,file_title,content)
		SELECT id, cat_name FROM categs
		WHERE categs.id = filepages.category_id";

by your advise, i can place this ($new_id = mysqli_insert_id($con); ) after $sql query is done?

thanks

5

I’m not sure I understand what you mean. From the information you already posted, it seems that your DB tables look like this:


filepages:
-id // Automatic ID from DB
-category_id(FK)
-title
-description

fileserv:
-id //Automatic ID from DB
-filepages_id(FK) //ID from filepages
-filetype
-filesize
-filedate
-filename

Is that correct?

Also, if I understand you correctly, this is what you’re trying to do:


$currentDate = date("Y-m-d");
$file_title  = mysqli_real_escape_string($_POST['title']);
$content     = mysqli_real_escape_string($_POST['description']);
$category    = mysqli_real_escape_string($_POST['select_cat']);
$upfile      = ($_FILES['upfile']['name']);

// insert to database
$sql = "INSERT INTO filepages (category_id, file_title, content)
VALUES ('$category', '$file_title', '$content')";

if(!mysqli_query($con, $sql))
{
    die('Error ' . mysqli_error());
}

$new_id = mysqli_insert_id($con);

$sql2 = "INSERT INTO fileserv (filepages_id, filetype, filesize, filedate, filename)
VALUES ($new_id, '{$_FILES["upfile"]["type"]}', '{$_FILES["upfile"]["size"]}', '$currentDate', '{$_FILES["upfile"]["name"]}')";

if(!mysqli_query($con, $sql2))
{
    die('Error ' . mysqli_error());
}

For $sql2 I’ve just made a guess, as you haven’t given much information about that query.

6

It looks like the DB is incorrect, relook the code you wrote for it.

7

@fretburner

its correct, the DB Table

categs:
-id // auto increment
-category_name (dropdown menu)

filepages:
-id // auto increment
-category_id (FK, automatic when dropdown selected) NOT WORKING
-title
-description

fileserv: (automatic insert file properties info here)
-id // auto increment
-filepage_id (FK, automatic get ID from filepages) NOT WORKING
-filetype
-filesize
-filedate
-filename

status: 2 data not working,

$sql2, i just tried if it works with 2 statements, i did tried yours but some problem in real escape string, needs parameter. but anyways, i think i have a simple problem, either mysql statement or handling of php data.

please help me how.
Thanks.