mysqli and php: inserting into multi tables from one form

[#1]

I am not totally sure if this is the right place for this as this is to do with php code and inserting into multiple tables.

However, I have been struggling with this for some time now and decided to try my luck.

I have a php page with a rather large from. As I have been getting error message in my phpadmin for exceeding the field limit etc., I have decided to split the inputs into three groups and insert them into three separate tables,

I know haw to insert into multiple tables with mysqli_multi_query if the values I am inserting are hardcode values (i.e. not variables coming from the FORM but just strings). Unfortunately, the moment I involve the form's posted variables in the concatenated single query, it only inserts into just one of the tables and that is it. The other to are left out.

I would really appreciate if anyone come across this problem and know how to solve it OR is there another way I can do this.

Many thanks

please find the mysqli_multi_query section coppied below:

========================================================

mysqli_select_db($connection, 'database');
include("phpinclude/var_declare.php");
include("phpinclude/Eng1_Array_Check.php");
$sql="INSERT INTO online_mock1_eng_mark1 (q1, q2, q3A, q3B, q3C, q3D, q3E, q3F, q4A, q4B, q4C, q4D, q4E, q5A, q5B, q5C, q5D, q5E, q5F, q5G, onmock_1_eng_scr1)
VALUES ('$q1',$q2,'$q3A','$q3B','$q3C','$q3D','$q3E','$q3F','$q4A','$q4B','$q4C','$q4D','$q4E','$q5A','$q5B','$q5C','$q5D','$q5E','$q5F','$q5G','".round(count($arrCorAns1))."');";
$sql .="INSERT INTO online_mock1_eng_mark2 (q6A, q6B, q6C, q6D, q6E, q6F, q6G, q6H, q6I, q6J, q6K, q6L, q7, q8, q9, q10, q11, onmock_1_eng_scr2) VALUES ('$q6A','$q6B','$q6C','$q6D','$q6E','$q6F','$q6G','$q6H','$q6I','$q6J','$q6K','$q6L','$q7','$q8','$q9','$q10','$q11','".round(count($arrCorAns2))."') WHERE fk_userid='".$uid."';";
if (mysqli_multi_query($connection, $sql)) {
echo "New records created successfully";
} else {
echo "Error: " . $sql . "<br>" . mysqli_error($connection);
}

mysqli_close($connection);

[#2]

@SorNet117 You might want to print your $sql variable to the console just to make sure it's sane, and values quoted where expected etc, and do it for both statements. Also, if the data in the second table relies on the first you are best to do this as two distinct inserts protected by a transaction. That way if the statements fail, for whatever reason, you can roll back to keep your database consistent.

[#3]

Hi Mook

'Thanks for coming back.

I have been involve to a certain degree with writing my own PHP code etc.

But, compared to people like you I am still a novice. So, my apologies if at times I sound like an idiot.

Indeed, here we go: not familiar with 'print your $sql variable to the console'

Data in the second statement does rely on the first.

The form inputs are to be split into three groups and inserted into 3 tables. The actual ID is common for all though if that matters.

[#4]

Sorry misprint.

I meant to saydata in the second statement does not rely on the first one at all.

[#5]

Moderators Note

This topic has been moved from Everything Else to Tech Help

[#6]

Apologies for the delay in replying @SorNet117 I'm not a PHP developer so I don't know the exact syntax but to 'print your $sql variable to the console' or better log this is what you need. https://www.php.net/manual/en/function.error-log.php
 

I see you already have a variation of this in your else statement*. What does it tell you?

With this you will be able to see exactly what SQL is generated from your code e.g. :

INSERT INTO online_mock1_eng_mark1 (X, Y, Z, ...) ;

If the input is for three groups, and by that you mean tables then I only see two INSERT statements above. Also where does the common ID come from?

By using the printed SQL above you can use it directly in your Database Manager Console, and it will show you what errors there are, if any, assuming you're not seeing them as part of your else condition.

* Printing information like you do to the body of the HTML page in not recommended as it has the potential to leak information about your database to the user or hacker! You should use the aforementioned function to keep it on the server side.

[#7]

Thank you very much for your assistance Mook. I have  been trying numerous options without success and I finally gave up as my tests are getting quite close now.

I have actually reverted back to inserting into a single table which sorted out the problem.

However, as I did before, I now get 'Warning: a form on this page has more than 1000 fields. On submission some of the fields might be ignored, due to PHP's max_input_vars configuration.'.

How do I access the php.ini file to change this limit and more importantly if I do, would it in any way trigger other issues which would be a disaster at the moment?

I am worried about this, because when I actually decided to update to PHP 7.1 version I hit a brick wall and everything save HTML pages and tables became inaccessible. Thankfully Bob sorted it out for me.

Does this warning mean  my table could become unusable?

[#8]

For what little I did do @SorNet117 you are welcome. How to access the configuration file, that I can't answer, but someone else will.

Having over 100 columns in a table implies that the database hasn't been normalised and could benefit from it, if you are able to do this. By changing the max value in your .ini file you are only treating the symptom, not the cause and I suggest you take the time out to review the table structure and attempt to normalise it. By normalising I mean splitting it into other tables with fewer columns. If you want to do this and need help with it please ask.

[#9]

php.ini depends on your hosting setup. IF you're on a shared host you might not even have access to it - on the other hand you might have a local php.ini file OR the ability to create one. Every setup varies.

If you have hosting on your own machine then php.ini will be in the php folder somewhere - make changes and restart apache.

As for printing the sql statement, a typical way of debugging is to print the variable to the screen - eg supposing your statement is like:

$Query = "insert into <table> (`columns`) values ($var)";

then you would do this:

print $Query;

Copy whats printed and then paste it into phpmyadmin's query page to see if it runs successfully - if not, you'll get an error.

Also note that mysqli is an old extension now and i beleive that was removed from php7? (I could be wrong but if not it will be deprecated). You're better off switching to PDO which is a database interface for many different types of databases such as mysql, microsoft sql, access, maria db etc. It's slightly more complex but like all things the more you use it the more natural it becomes. It's also more secure.

On that note, don't use variables in your SQL statement, use prepared statements (whether you use mysqli or pdo) as the database creates the sql template internally and then treats the variables differently - if you use $vars directly in the sql string then they can be hacked a lot easier.