0

I have an HTML form with a <select> list. This list has many options (over 200), but let's say, for simplicity, there are four.

<input type="text" name="country" placeholder="&nbsp;your name">

<input type="email" name="email" placeholder="&nbsp;your e-mail">

<select>
  <option value="skateboard">skateboard</option>
  <option value="scooter">scooter</option>
  <option value="parkour">parkour</option>
  <option value="bmx">bmx</option>
</select>

User data is currently processed with PHP and dropped into a MySQL database accessed via phpMyAdmin.

This works fine. One table gathers all user data in three columns – name, e-mail and option.

The problem is that each option represents a different division of the company and having all options dropped into the same table requires manual sorting.

An employee sorts through the table, cuts out all "skateboard" entries and sends them to the proper team. Same cut/paste process for "scooter", "parkour" and "bmx". So this one table is being manually divided into four parts.

My goal is to remove the manual sorting component and automate the entire process.

I'm thinking we could instead have four different tables, one dedicated to each option. When an entry is received for "skateboard", the data is dropped into table 1, "scooter" goes into table 2, etc. No more human intervention required.

I can envision a clunky and bloated PHP script handling the job, but I'm thinking MySQL provides a more efficient solution.

Unfortunately, my experience with MySQL is quite limited. Looking for "the right" starting point in the MySQL Reference Manual has so far been fruitless. What rules or programs am I looking for?

Improve this question
8
  • 1
    I don't understand the need to sort similar data into different tables when WHERE statements exists. Commented Jul 31, 2018 at 21:52
  • What is the reason these entries need to go into different tables? You can easily filter the main table by option using select name, e-mail from table where option = 'bmx' or something (and build separate php pages for those) Commented Jul 31, 2018 at 21:53
  • What happens when you have 20 options? or 200 options? Are you really going to have 200 tables? One table per option doesn't make sense. Commented Jul 31, 2018 at 21:54
  • 1
    Also phpmyadmin should never be the end users view into the data. phpmyadmin should be reserved for projdeveloper/administrator only. Commented Jul 31, 2018 at 21:56
  • Thanks all for the feedback. Already quite useful. Commented Jul 31, 2018 at 21:58

2 Answers 2

Reset to default
2

You could use the phpMyAdmin search function to limit result to what you require and export them if needed to CSV, However i think the issue is that you are using phpMyAdmin to look at your data. phpMyAdmin is really a database administration tool. its okish at best for viewing data and out right dangerous at worse. (to easy to delete something you really shouldn't)

ideally what you want here is a php script that locates the entries in the database and list them in a html table based on certain criteria then you could allow what ever dept you wanted to view only the data they should.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

The PHP script seems to be the way to go. I just thought there may be a simpler and more direct way with MySQL (which there may be -- I still don't know -- but PHP is simple enough for my purposes at the moment).
Here's what I'm thinking: create db tables for each option, then use this PHP logic: if option="skateboard" then connect to table 1 , if option="parkour" then connect to table 2 , etc.
Part from being bad practice, what happens if later you want to add some more activities, are you going to make a more tables and alter the PHP code? i would do this. Use one table, add fields in the table for the data and make sure you have a field for the activity, then add an index to the activity column to make searching faster. Then use PHP code to find the data, something like 'select * from mytable where activity = "skateboard", this will allow you to use only one table and return only the skateboard activity.
Yes, that's a good alternative. The truth is, I need one database with four tables max (or, as you say, one table with an index key). Thank you.
1

Hm, in this case I would create two logical, that is three relational tables inside the MySQL DBMS.

In logical sense: there is a n:m relation between two entities, User and Option (since a user can have multiple options if I understood it correctly, and of course an option can be dedicated to multiple users).

In relational sense: since there is a n:m relation, you need to split it to a third table in between with foreign and primary keys being those of the options and users.

So in the end you can have three tables (example):

Options:

  • PK: option_id
  • M: name
  • M: date

Users:

  • PK: user_id
  • name

Options_Users:

  • PK & FK: options_id
  • PK & FK: users_id

PK: primary key M: Mandary FK: Foreign key

You can then get all the data that you need through the PHP, by using the WHERE and JOIN commands (look them up for examples). phpMyAdmin is pretty much used only for inspecting the data and potentially manually creating the tables. All the real administrative operations can and should be handled via the script (in this case, PHP).

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.