MySQL vs PhP which is better for the work?

Databases
1

Hi

I have some data stored in key values in a list on my website (say a simple HTML select). The data can be matched with key value pairs as numbers or strings in the database like :

Php array (
1=>high school
2=>university
Etc

Then stored in a database.

Or

High school => High school
University => university
Etc

Now this is repeated throughout my site.

The question is if I store the key as numbers in the database, php will have to match back with some form of switch statement.

So which is the best way to optimize my site - have numbers in the database (which makes for I understand a faster database) and have php match back to values?

Or just store the correct string in the database in the first place (which I understand is slower for database)?

The keys are going to be reasonably static in this instance. But this question keeps popping up as I go through my site.

I guess the question comes down to which tool is best and is a helpful for understanding.

Any ideas or suggestions are greatly appreciated!

Thanks!

2

You should be storing the key in the db. It is not clear what the actual problem is you are asking about.

If you are trying to populate a dropdown from the DB you would just select the id and description field and then foreach over the results to populate the dropdown.

SELECT colum_id, description FROM lookup_table

If you are trying to display the stored result you would us a JOIN to, well, join the description to the id.

3

no… you would store the descriptions in the database as well

4

Sorry if I was unclear : my question do I store the key to the select HTML (not the primary key) as an integer or just directly as a string in the database ?
Which makes for the fastest website?

Thanks

5

Column type would be Int

6

this is pretty unclear as well

why would the key to the select html ~not~ be the PK of the descriptions stored in the database?

1 Like
7

Yeah OP, you are unclear. There are two instances where the ID is “stored”.

  1. The lookup table, id should be auto-increment int.
  2. The user inserted/updated data table, id should be int with a foreign key to the lookup table int
1 Like
8

I don’t understand why you would store key/value pairs in an array and the database.
One makes the other redundant.
You would use one or the other, not both.

9

My question is

I have two tools php and MySQL.

I understand that integers are faster in databases than strings

I have some HTML select fields. The HTML can be option 1:

HTML
<select name=“education”>
    <option value=1>High school</option>
   <option value=2>University</option>
</select>

PHP
$education = $_Post[‘education]

If $education = 1 print “high school” else (switch) etc if 2 print …

MySQL
This education value is stored in a MySQL database column with he title education. Here the column type is Int and high school is selected the value is 1.

MySQL:

education int = 1.

I then insert the key into the database as 1 or 2 and then use a php if type tool to print the value to screen later. If 1 print ‘high school’ if education = 2 print university.

OR

Option 2:

HTML

<select name=“education”>
    <option value=“high school”>High school</option>
   <option value=“university”>University</option>
</select>

PHP

$education = $_POST[‘education’];

No PHP if type statement

Here MySQL column type is varchar and the value is stored as a string.

education varchar = ‘high school’

Then my php does not have to do an if type statement but merely prints out the value directly from the database.

My question is a conceptual one - which option is better(?) faster easier etc for websites using php MySQL.

Do people usually use integers (make dB faster?) or strings?

Thanks!

10

If I were going to take the path of using a database, which I may if the data were likely to change and be updated with any degree of frequency, I would do as suggested, have a PK as an integer and a text string value. These could be fetched as key pairs via PDO::FETCH_KEY_PAIR.

If the data was unlikely to change and not too many rows, it could be hard coded into the PHP, I may use an array of key/value pairs.

$eduPlace = array(
    1 => 'high school',
    2 => 'university',
);

Then instead of if or switch use:-

$education = $eduPlace[$_POST['education']] ;

This suggests that you will be hard coding the pairs anyway.

So the choice is for me:-

  1. I don’t mind hard coding it all, no database required.
  2. Too much data to hard code, updated too frequently, do use a database to store both keys and values.
11

It is the storage of my users select option response into the database - not a cross reference of the values.

The user on my website will choose a select value and their choice Will be stored with their customer profile.

Do I store the users select option as a 1 or a ‘high school’ in the dB.

… or did you answer that above (sorry!)

12

In that case, definitely use a database and store the value as an integer.
The text string would be in another table with its PK (ID) matching the integer you store with the student ID.

So you have one table “education” with columns id and place with values like 1 and High School
Then a table matching students to education with a student_id and education_id as integers.

It may be that you just add the education (int) column to your student table, if they will only ever have one value for that.

1 Like
13

Got it praise the Trees!! :sunny: LOL

Thanks for your endless patience — this is why I use this forum. Others when an idea is explained by beginners it is often just downvoted or gets a snarky reply :(!

Thanks again!
Karen

15

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.