Internship, Day 26 (25 May 2012)

(Photo: Handling two computers at once…)

สรุปว่าส่วนที่ต้องทำจริงๆ คือ การเก็บพวก session การใช้งานต่างๆ ลงไปพักไว้ในฐานข้อมูลก่อน โอเคครับ ไม่ยาก แต่นาน 555+ ที่ต้องเก็บลงฐานข้อมูลเป็นเพราะว่ามันนานเกินกว่าจะเก็บไว้ด้วย PHP session ครับ

So it’s just storing sessions into the database. It’s not that hard but will take some time to code. The reason for this is because the said session is a real-world session which is longer than a PHP session.

SQL: Insert with respect to reference table

Since there’s not much in the blog today, let’s write another knowledge scrap. A reference table is an enumerated table with IDs against “facts” in a one-to-one manner such as:

 
userclass(id INT, name VARCHAR(20));
+------+----------+
| id   | name     |
+------+----------+
|    1 | normal   |
|    2 | silver   |
|    3 | gold     |
|    4 | platinum |
+------+----------+

usertype(id INT, name VARCHAR(20));
+------+------------+
| id   | name       |
+------+------------+
|    1 | individual |
|    2 | sme        |
|    3 | corporate  |
+------+------------+

Say, if we want to insert a “silver sme” user into a table, rather than, with PHP variables, using

 
// Get the reference ID from two tables
$q1 = "SELECT id FROM userclass WHERE name='$class'";
$q2 = "SELECT id FROM usertype WHERE name='$type'";

$r1 = mysql_query($q1);
$r2 = mysql_query($q2);

$s1 = mysql_fetch_assoc($r1);
$s2 = mysql_fetch_assoc($r2);

$classid = $s1 ['id'] ;
$typeid = $s2 ['id'] ;

// Now insert into new table
$q3 = "INSERT INTO user (uname, uclass, utype) VALUES('$username', $classid, $typeid)";
$r3 = mysql_query($q3) or die(mysql_error());

we have a “PHP-clean but more SQL-complex” way to do it:

 
$q = "INSERT INTO user (uname, uclass, utype)
       (SELECT '$username', userclass.id, usertype.id
        FROM userclass, usertype
        WHERE userclass.name = '$class'
          AND usertype.name = '$type'
       )";
$r = mysql_query($q) or die(mysql_error());

It does not hurt to study SQL. You can in fact extend this to as many reference tables as necessary. This is a generalized version of the SQL code:

 
INSERT INTO target_table (
  fixed_col_1, fixed_col_2, ..., fixed_col_m,
  ref_col_1, ref_col_2, ..., ref_col_n)
  (SELECT fixed_input_1, fixed_input_2, ..., fixed_input_m,
          ref_id_1, ref_id_2, ..., ref_id_n
   FROM ref_table_1, ref_table_2, ..., ref_table_n
   WHERE
     ref_table_1.ref_val_1 = ref_input_1 AND
     ref_table_2.ref_val_2 = ref_input_2 AND
     ... AND
     ref_table_n.ref_val_n = ref_input_n
  );

Where:

fixed_col_i are target columns you want your fixed inputs to go into
ref_col_j are target columns you want your inputs to be referenced into IDs and go into
fixed_input_i is the data itself. Usually a string or integer or something else.
ref_id_j is the id column from each table j. For brevity I’ll force this column to be int.
ref_table_j is the table itself. Each table is defined by ref_table_j (ref_id_j INT, ref_val_j SOME_DATA_TYPE)
ref_val_j is the value (or “name”) column from each table j.
ref_input_j is the user input, usually supplied from PHP or something else.

It will take some time to understand all this due to my bad English, but I think the code can be studied more easily due to the fact that the code does not belong to any human language — they are just symbols derived from English.

LunaticNeko