3 insert commands not working properly

[patk570]

Hello, I am trying to update 3 tables that i have in my db. the second and third INSERT commands there is a specific field that needs to have the ID of the first table last row inserted.

    first table, admin:
    +--------------------+
    |id username passcode|
    |1  admin    test    |
    +--------------------+

    second table custcomp
    +--------------------+
    |id cid cpuname sn   |
    |1  1   test    test |
    +--------------------+

    third table computerdb
    +--------------------+
    |id cid cpid ticket  |
    |1  1   1    123456  |
    +--------------------+

Now the CID needs to be the same in all fields which is the id(auto_increment) of the user in admin.

The CPID needs to be the id(auto_increment) of custcomp table

In my code i have $con->insert_id which works for the first one, but the CPID

Here is my input codes:

$con = mysqli_connect("localhost","ROOT","ROOT","MY_DB");
                       if (mysqli_connect_errno())
                               {
                                      echo "Failed to connect to MySQL:" . mysqli_connect_error();
                               }
                               $query = "INSERT INTO admin(name, email, phone, username, passcode, access_level) VALUES ('".$_POST['name']."','".$_POST['email']."','".$_POST['phone']."','".$username."','".$db_password."', '1')";
                               if (!mysqli_query($con,$query))
                        {
                             die('Error: ' . mysqli_error($con));
                        }else{

                        $sql2 = "INSERT INTO custcomp (cid, cpuname, sn, model, brand, hdd, memory, type )
                       VALUES ('".$con->insert_id."','Pending Update','N/A', 'Pending Update', 'Pending Update', 'Pending Update', 'Pending Update', 'Pending Update')";
                       if (!mysqli_query($con,$sql2))
                        {
                             die('Error: ' . mysqli_error($con));
                        }else{
                       $sql = "INSERT INTO computerdb (cpid, cid, ticket, computerpass, status, notes )
                       VALUES ('".$con->insert_id."','".$con->insert_id."','".$_POST['ticketnumber']."','".$_POST['password']."', 'Pending', '".date('m/d/Y H:i:s'). ": Work has started on your computer')";
                       if (!mysqli_query($con,$sql))
                        {
                             die('Error: ' . mysqli_error($con));
                        }else{

                            //more code to email info:

I think I might have gotten in over my head on this...
any help or ideas would be great!

[diafol]

1. You are inserting raw input data to the SQL query. This could make you vulnerable to SQL injection - so if using mysql_* functions, use mysql_real_escape_string(). However, you should consider using mysqli or PDO with parameterized binding, which obviates the need for escaping functions.

2. You seem to be inserting into the user/admin table regardless of whether a record exists or not. I'm assuming that this table holds 'customer' or general user data. If so, a single user may end up with multiple accounts, so a check may be required, e.g. on email to allow login instead of registration.

3. Your second table is storing 'Pending Update'. I suggest using NULL or just leaving off those fields from the SQL query altogether. Filtering and sorting become more difficult than they need to be when you store this type of 'default' value. Anyway, default values are usually designated within MySQL table itself, unless you have multiple scenarios each with different 'defaults'.

4. How is the ticketnumber set in the first place? Wouldn't this be the 'id' of the table? I assume that this should be assigned automatically.

5. You are running 3 separate insert queries. You could write a stored procedure transaction which would do the lot in one go with a rollback on failure. However, thinking about it, you'd still want queries 1 and 2 to work even if 3 failed, so it's probably easier to keep your procedure pretty much as it is, but with a custom error message is something goes wrong. At the moment you're providing a vanilla mysql_error() in each case - not useful for knowing which statement failed.

[patk570]

I understand I am using $_POST and not mysqli_real_escape_string(con, $_POST['fname']) right now this is not in production as of yet. On the form there is a login box that if they already been in once they get a username and password generated automatically and sent in the email. what I sent was just the input statements that I cannot figure out. THe ticket number is a randomly generated number that is 6 digits long and gets submitted to them so they can check the status online.

I am just wondering if it is possible to get A) the id's to match up like I need because the CID is what links their data together so they dont have multiple username and such. B) if the method I am using can be shortened?

Thanks,

[patk570]

$cid = $con->insert_id;

is all i needed to do for them to work...

[diafol]

I don't see why you need CID in the third table. The only relationship should be the CPID as CID is redundant.

You can't run a single SQL query to do the lot - for that you'd need a stored procedure as mentioned previously.

Why is the ticketnumber randomly generated? If you want your ticket numbers to start at a high number e.g. 564738 - so it doesn't look like you've just started out - then you can declare that in your MySQL table. Example table creation:

CREATE TABLE `test` (
  `id` INT(6) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
  `varfield` VARCHAR(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=564738 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Notice the AUTO_INCREMENT=100

This look about right though... but not tested

$con = mysqli_connect("localhost","ROOT","ROOT","MY_DB");
if (mysqli_connect_errno())
{
    echo "Failed to connect to MySQL:" . mysqli_connect_error();
}
$query = "INSERT INTO admin(...) VALUES (...)";
if (!mysqli_query($con,$query))
{
    die('Error on admin insert: ' . mysqli_error($con));
}else{
    $query = "INSERT INTO custcomp (cid, ...) VALUES ($con->insert_id, ...)";
    if (!mysqli_query($con,$query))
    {
        die('Error on custcomp: ' . mysqli_error($con));
    }else{
        $query = "INSERT INTO computerdb (cpid, ...) VALUES ($con->insert_id, ...)"; //no need for cid here
        if (!mysqli_query($con,$query))
        {
            die('Error on computerdb: ' . mysqli_error($con));
        }else{
            //...
        }
    }
}

[patk570]

Thanks, Sorry it took so long to get back.