Hi.
I have one Db with 40 tables and more 25 fields for each table.
Is lots of variables to assign.
I have make all forms.
But know is the time to put all variables working.
Like:
$table_1_field_name=$mysqli_real_escape_string ($link, $_POST[‘name’])
But do this stuff by hand for more than 1200 variables is a crazy thing.
Any way to do it dynamic???
You would use array(s). Arrays are for sets of data.
If you are still using the xxx_escape_string() function, the php mysqli extension, and discrete logic to handle errors, switch to use prepared queries, the much simpler php PDO extension, and use exceptions to handle errors. Doing this will result in the simplest php code and the simplest sql syntax.
You can also dynamically validate the input data as a set and dynamically build any INSERT or UPDATE query by having an array defining the expected/permitted fields, with validation attributes and query attributes, then loop over this defining array to perform the validation using general purpose code and to build the sql statements with the appropriate fields.
Another comment on this subject.
The $_POST[ ] is itself a variable array. Therefore, you can loop thru it and pull out data without the need
to assign each name manually. You can loop thru it using something loosely like this:
foreach ($_POST as $variable_name=>$variable_data) { }
If you use prepared statements and set up your database field names the same as you use in your form,
you can use a simple loop like this to build the query for updating data. You can validate each field inside
the loop and if no errors use the loop to build the query for you. No need to code 1200 items that way.
Just a few lines to indicate which fields should be text, numeric or Boolean… Saves a lot of time!
Never do it before, let me try it! Im a beginner.
I have old program database for my business complete obsolete in paradox tables,
I migrate all tables with success to mysql and Im trying to built my own web app.
Well, let’s discuss this further. Before you get a database set up with so many tables, it might be better to start by redesigning the database. Why would you have 40 tables with 25 fields? My guess is that you never designed it logically. If your 40 tables have the exact same 25 fields, you should just create one and only one table. Just add a master field that would indicate which table it is linked to.
Can you give us any more information about the current 40 tables? Do they use the same fields for all?
If so, create one table for all with the 25 fields in place. Add one filed and call it “table_name” and then
insert all your data from the 40 tables using the table name as a master index. Then, it would be easier to use in the rest of your project. I am not a database designer, but, it seems incorrect to have so many tables.
This smells of a bad DB design. Post an SQL dump of your DB schema for us to review. There is no point writing code against a bad DB design if that is the case.
I am a Database Architect and I concur with @ErnieAlex.
The database is portuguese. I could put a dump. But is a database for insurance broker.
The common fields is id (number of cliente), number of insurance company, number of product of Company ( like allianz health number 50), number of the policy.
Well, the question is why so many tables? Take just the first two tables. What is different between them. Are the fields all the same? Just different table names? If so, just combine them into one larger table using the table name as a field. Then, pull out data for any one table name as needed.
This way you are just dealing with 25 fields, not 1200… Does this make sense? If not, @benanamen can help you design it more correctly…
We don’t necessarily need the data so there is no privacy issues, just the table SQL dump. You can also PM it to us.
how to send it in PM?
It gives error, I cannot send message to user»
I am leaving for a few hours. Click on benanamen’s ICON and you will see the MESSAGE button.
Click on that and send it to him. He is better at databases than me, so he will help you out!
No message button
Odd! Your image did not show. I can’t view it. Leaving for 3 or four hours… Will check back in them…
No problem . I will try again later.
You could sent to PM and I reply it .
I just bumped your level. See if you see the message button now.
Thank You. I have sent the db dump by Pm.
Let us all know if you find a solution to this…
I´m trying to found. I start today the first test.
I´m close to build the query but is insecure.
And I have a problem with last variable it writes the comma after it.
Im trying to check if is last of the array and not write the comma.
<?
echo "\$sql= INSERT INTO apolices(";
foreach ($_POST as $variable_name=>$variable_data) {
if($variable_name=="submit"){
$variable_name="";
}else{
echo($variable_name).',';
}
}
echo ")" . "Values (";
foreach ($_POST as $variable_name=>$variable_data) {
if($variable_data==""){
$variable_data="NULL";
}
echo($variable_data).',';
}echo ")";
?>Please don’t unconditionally loop over all the submitted $_POST data and blindly use it in your code. Hackers love this, because they submit 100s of their own fields and data values in the hope that you are using variable variables or extract (without using a prefix) so they can override your program variables or if you are putting the external data directly into emails or sql query statements so that they can produce their own emails and sql queries.
Your server-side code must be in control of what data it operates on and how it uses it, which is why someone wrote doing this by -
If I have time, I will post an example for producing INSERT/UPDATE queries using the php PDO extension (the ridiculous mysqli code needed to dynamically do this is overly complicated and slow and not worth wasting any time trying.)