Page 1 of 1

Insert into database PHP problem

Posted: Thu Feb 26, 2009 6:25 am
by mrjtfool
Hi

I'm currently writing a PHP script to insert some data into a database I've created. The data is taken from a HTML form and is passed to this script which in turn inserts it into the relevant tables of my database. Very simple indeed...

Here is the code:

Code: Select all

<?php
$mysqli = mysqli_connect("localhost", "my_username", "my_password", "my_database");
 
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
} else {
    $sql = "INSERT INTO salesperson (`salesman_no`, `branch_no`, `first_name`, `last_name`)
    VALUES ('".$POST["salesman_no"]."',\'001\','".$POST["firstname"]."','".$POST["lastname"]."');
    INSERT INTO hhc (`hhc_asset_no`, `salesman_no`)
    VALUES ('".$POST["hhc_no"]."','".$POST["salesman_no"]."')
    INSERT INTO printer (`printer_asset_no`, `salesman_no`)
    VALUES ('".$POST["printer_no"]."','".$POST["salesman_no"]."')";
    $res = mysqli_query($mysqli, $sql);
    
    if ($res === TRUE) {
        echo "A record has been inserted.";
    } else {
        printf("Could not insert a record: %s\n, mysqli_error($mysqli));
    }
    
    mysqli_close($mysqli);
}
?>
Once I submit the form on the other page this script runs but I always get this error message:

Parse error: syntax error, unexpected $end in C:\Users\Jamie\Desktop\xampp\htdocs\xampp\Asset Tracking System\insert_Antrim.php on line 24

Does anyone know why I'm getting this? I've been looking at this same code for ages but just cant work it out.

Thanks for any help.

Re: Insert into database PHP problem

Posted: Thu Feb 26, 2009 6:31 am
by requinix

Code: Select all

printf("Could not insert a record: %s\n, mysqli_error($mysqli));
Get yourself an editor that can do syntax highlighting.

Re: Insert into database PHP problem

Posted: Thu Feb 26, 2009 7:29 am
by mrjtfool
Ha thanks I knew it would be something simple. Can anyone recommend any editors?

Also is this the correct SQL syntax to insert data into multiple tables?

Re: Insert into database PHP problem

Posted: Thu Feb 26, 2009 9:42 am
by mrjtfool
Sorry about the multiple posts. I've changed the above code to:

Code: Select all

<?php
$mysqli = mysqli_connect("localhost", "my_username", "my_password", "my_database");
 
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
} else {
    $sql = "INSERT INTO salesperson( salesman_no, branch_no, first_name, last_name ) 
        VALUES ('".$_POST["salesman_no"]."', 
        '001', 
        '".$_POST["firstname"]."',
        '".$_POST["lastname"]."'
        )";
    $sql .= "INSERT INTO hhc( salesman_no, hhc_asset_no )
        VALUES ('".$POST["salesman_no"]."',
        '".$_POST["hhc_no"]."'
        );";
    $res = mysqli_query($mysqli, $sql);
    
    if ($res === TRUE) {
        echo "<p>A record has been inserted.</p>
        <b></b>
        <a href='Antrim.php'>Go Back</a>";
    } else {
        printf("Could not insert a record: %s\n", mysqli_error($mysqli));
    }
    
    mysqli_close($mysqli);
}
?>
It now tells me there is an SQL syntax error when the second INSERT command is read, although I can't see anything wrong with it. Im still searching the net for answers but haven't seen anything wrong with my code...

Re: Insert into database PHP problem

Posted: Thu Feb 26, 2009 9:46 am
by papa

Code: Select all

 
#    $sql .= "INSERT INTO hhc( salesman_no, hhc_asset_no )
#         VALUES ('".$POST["salesman_no"]."',
#         '".$_POST["hhc_no"]."'
#         );";
Should be

Code: Select all

 
  $sql .= "INSERT INTO hhc( salesman_no, hhc_asset_no )
         VALUES ('".$POST["salesman_no"]."',
        '".$_POST["hhc_no"]."'
       )";

Re: Insert into database PHP problem

Posted: Thu Feb 26, 2009 4:02 pm
by mrjtfool
Thanks for the reply.

I've tried what you said and it still brings me back an error message. It says there was an unexpected " on one of the lines.

It seems to be where I am using variables containing data from an HTML form. It doesn't like the way I have set it out. Although I'm almost 100% sure this is the correct syntax???

Thanks.

Re: Insert into database PHP problem

Posted: Thu Feb 26, 2009 4:06 pm
by John Cartwright
You are trying to append 2 queries into a single string, however mysqli_query() only supports a single query per execution.

Re: Insert into database PHP problem

Posted: Thu Feb 26, 2009 4:54 pm
by requinix
The error is because you didn't separate the two queries with a semicolon. But even if you did, what John said is still correct.

Re: Insert into database PHP problem

Posted: Thu Feb 26, 2009 5:09 pm
by mrjtfool
OK, so best thing to do is two seperate myqli_query() calls with one query in each?

Re: Insert into database PHP problem

Posted: Thu Feb 26, 2009 5:11 pm
by John Cartwright
Yes

Re: Insert into database PHP problem

Posted: Fri Feb 27, 2009 5:43 am
by webnhance
The best editor is editplus

editplus.com

you'll love it

Re: Insert into database PHP problem

Posted: Sat Feb 28, 2009 12:35 pm
by mrjtfool
Thanks using the seperate mysqli_query calls worked!

I'll check that editor out as well. Currently I'm using Crimson Editor which seems quite nice but doesn't check your code 100%.