Query echo works in phpmyadmin, but not php script

[foundherent]

Good Afternoon Team,

Am sitting with something simple using the language below. If I copy the echo output of my query as included below it works perfectly in phpmyadmin but doesn't work on a website. Variables all seem to echo consistently/correctly and POST checks seem to verify this is working correctly as well. I worry the error comes with the syntax I used in combining the sql queries. That, or perhaps LAST_INSERT_ID does not work in the php script as well as it does in phpmyadmin. All help appreciated.

if(isset($_POST[`region_id`])) {
    $competition_id = htmlentities($_POST[`competition_id`],ENT_QUOTES,`UTF-8`);
    $division_name = htmlentities($_POST[`division_name`],ENT_QUOTES,`UTF-8`);
    $region_id = htmlentities($_POST[`region_id`],ENT_QUOTES,`UTF-8`);
    $division_edit_submit = "INSERT INTO division_table (competition_id,division_name) VALUES (`$competition_id`,`$division_name`);
    INSERT INTO region_assignment_table (division_id,region_id) VALUES ((LAST_INSERT_ID()),`1`)";    
    mysqli_query($connection,$division_edit_submit);
    echo $division_edit_submit;
    }

 

[requinix]

mysqli_query() can't do multiple queries at once.

You can use mysqli_multi_query(), or do what I would do which is just run two separate queries.

However I suspect you'll want to know that new division ID, in which case you'll have to do two queries anyways.

[foundherent]

54 minutes ago, requinix said:

mysqli_query() can't do multiple queries at once.

You can use mysqli_multi_query(), or do what I would do which is just run two separate queries.

However I suspect you'll want to know that new division ID, in which case you'll have to do two queries anyways.

This explanation makes perfect sense but the concept of scope makes me very worried that creating two queries will not properly include the LAST_INSERT_ID correctly. Will test when I'm back on my system but if I'm not overthinking this aspect any elaboration will help prevent any further newbie posts. Thanks again for all the help.

[benanamen]

htmlentities  is an output function and for HTML output. Why are you using it on your input? What if you need to use the data that is not for HTML Output? Also, you need to use Prepared Statements. Never ever put variables in your query.

 

[requinix]

2 hours ago, foundherent said:

This explanation makes perfect sense but the concept of scope makes me very worried that creating two queries will not properly include the LAST_INSERT_ID correctly. Will test when I'm back on my system but if I'm not overthinking this aspect any elaboration will help prevent any further newbie posts. Thanks again for all the help.

LAST_INSERT_ID() is set according to the connection, so doing an INSERT in one query and using LAST_INSERT_ID in the next is safe. As long as you aren't reconnecting between queries, of course. That would be silly.

[Barand]

Caveat:

LAST_INSERT_ID() can be a problem if you are inserting, say, a master record and several detail records and you want the detail records to contain the id of the master.

In this case, the first detail record would be OK but subsequent details would contain the id of the previous detail record. That is not what is required.