Hi,

I have a new database with currently only one record.

The database has 125+ fields.

I cannot add or records without an Internal Server Error, and have noticed that the page display gets all funky (inputs overlap etc).

All other functions seem fine, and I have used the program with databases containing fewer fields (say 30) without any problems.

BTW, Primary and Uniques are set.
Any thoughts?

That sounds like a lot of fields to have in one table. While MySQL can handle a lot of fields, it is best to create "linked tables" of data for easier management.

Regards,

Eliot Lee

I am just now getting into MySQL and am rapidly seeing the advantages over flatfiles!!!

Actually, there are a variety of reasons why I would like to do this for the MySQL program I am setting up (A Membership site with user profile/resumes)

The reason for most of the fields is much of the db is resume job info for up to 9 jobs.
('job1','Company1','Supervisor1') etc.
I could go as far as breaking up the tables into table1(UserInfo), table2(job1), table3(job2), etc. But for now would like to have the user info (maybe 20 fields) and the resume part (maybe 100 fields) seperated, I would then be faced with how to pull those into the html output of my scripts which currently pull from one table (I believe)
-----------------------------------------------------
Anyway, 1st things 1st, I am not sure how to link the tables, can you direct me to a resource for this?

Thanks

http://www.mysql.com
http://www.sql.com

Also, I would highly recommend buying some books about SQL Databases.

Here are some simple and helpful tips:

1) You must normalize your database (tables) with appropriate field types and also setting up the appropriate primary keys in your tables. This includes not having fields with multiple values.

2) To have N => M connected table (One record to Many records), you need to have unqiue keys setup in your tables, and then create a foreign key in the linked table.

Example:

(PK = Primary Key)
(FK = Foreign Key)

User table (PK = Userid)
Resume table (PK = Resumeid, FK = Userid)

3) If you want to have a N = N connected tables (One to One), you will need to have primary keys and foreign keys created in both tables.

Example:

(PK = Primary Key)
(FK = Foreign Key)

User table (PK = Userid, FK = Resumeid)
Resume table (PK = Resumeid, FK = Userid)

Regards,

Eliot Lee

Thank you very much for that useful definition and instruction.

So does this mean that a query to the user table requesting fields from the "linked" (same as "joined", yes?) resume table would render all to the output, or must I make seperate queries in the script?

And to digress I would like to clerify one thing.

In order to Is it necessary to have the number of fields equal to those in the database? or is just naming the fields you are querying sufficient?

Sort of...similar concept.

In flat files, you have to identify the column number of the record (i.e., field) to read and manipulate the data....in SQL, you simply use the field name.

And before you INSERT data, you should PREPARE the table to pull the appropriate row of data before inserting. You should be able to see examples of this in the Links SQL or DBMAN SQL.

Here are some generic codes:



This ensures that you identify the correct row of data or "record". Trust me...I have made a lot of mistakes with not pulling the correct record and forgeting to use the WHERE function. I am also playing with Cold Fusion, SQL 7 and Access. And during one of my test scripts, I neglected to put the WHERE function and the whole database was updating with the inputed values (parameters). Thank goodness I ran a backup of my files before attempting to UPDATE the table.

Of course with Perl, you have to use more functions and commands.

Regards,

Eliot Lee

First I must explain that I do not have shell access so I must depend on programs like DBMANSQL and my server's MySQL minimal interface.

Second I asked the question about the number of fields, because the perl scripts I am working with seem to be reading the database and otherwise functioning ok, but for some reason, whenever I try to add or edit a record (seperate scripts - add.cgi, modify.cgi), the database does not update.

I hacked this program (which was a dating script) and replace almost all the fields.

I know the database functions because I successfully inserted a record using the control panel.

But as I recall, I had to have every field represented (both NULL and NOT NULL).

For the fields that I didn't have data, I just left empty IE:


('ID','UserName','EmailAddress','','','Field6')

So I guess that in the script where I am using the $query = "INSERT INTO dataprofiles line, I need to do the same.....

Correct?

Actually, I would like to speak to you privately about this program, and have misplaced your email.

If this is ok, let me know at bsimon47@veriomail.com

Not really...depending how you are parsing the parameters, you can easily put the parameters into hashes and simply use $db->add_record ( $rec, $in );. Of course, you would have to have sub add_record defined that basically translates the paramters inputed from forms into hashes that are then inserted into tables.

Why do you want to contact me privately? I don't have my email address posted in my profile for a reason.

Regards,

Eliot Lee

I believe that is covered in:
So can you think of another reason that the add.cgi executes all of it's functions EXCEPT writing to the database?

I was suspecting that it was in the:
line, as it was missing host=userhost as I have seen in other MySQL programs I have, but when I added it, I got a 500 error!

$$$

If you are using Links SQL, you need to use strict codes...Replace $FORM{'FieldName'} with:



And you don't need &form_parse.

Regards,

Eliot Lee

I strongly considered using Links SQL for this program (mostly because I know and like the admin functions), but decided on converting a Dating Program as is seemed to be more closely set up for the user functions I was looking for.

If the Links2 "MyLinks" addon functions could be set up with Links SQL, I probably would have gone that way.

We've used the same script that you are in the past, but have so severly modified it that its nolonger even close to resembling the original, and is pretty much completely rewritten with better coding.

We also experienced the problem that you have, and the solution was that one of our fields was left out of the add.cgi file, so when it went to add it to the database, mysql said "no way jose" and didn't write the data, even though we were shown the page that tells you that the data has been accepted and written to the database.

So, check your fields, in your form, and make sure that you have exactly what your database is expecting, and that they are named exactly the same, one little character off on a field name means no go.

Now, as to whats going on with your form page not displaying properly, that's a matter of system resources, even though you might be able to see it, keep in mind that others may not, what will happen with very large forms, is that selection lists get thrown all over the page, checkboxes and radio buttons disappear. Make your forms minimalist, and use one of the existing scripts provided by that guy at SS, to break up your forms into multiple pages and have the same cgi script parse the forms page by page, this is realitively simple to do with hidden fields, and hidden values of something like page1
then in the form parsing, you would have something like:

$page = $FORM'page'
if $page = page1 then do this, where this is a special subroutine to gather the data into the script and output the next page with another hidden tag of page2, follow along these lines until you get to the end result, then write it to the database.

Cordially,
Harrison


"I've got if's pretty good, but that's about it"

Thank you for your input
Actually, I ended up splitting much of the data into a separate table and using a foreign key to associate the tables.

This has opened up a meriad of possibilities for me.

AND had resolved the problem with MySQLMan not being able to display so many fields.

Thanks Again!