The marriage of heaven (common elements) and hell (code writing code).
June 26, 2008 Posted by Emre S. Tasci
So, as Blake had said, "Energy is Eternal Delight." or in other words, let’s merge the preceding two entries to write a code for a function that produces MySQL queries that will fetch the common elements involved with the given elements:
function fetch_elements($el,$order,$total)
{
// produces the query to select the elements that have binaries with the $el
// $order and $total are used to adjust the tab positions and designating the sets
$abc = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
$tab = str_repeat("\t",($total-$order));
$lom = <<<LOM
$tab SELECT symbol_A AS symb, val1 AS val$abc[$order]
$tab FROM `dbl014`
$tab WHERE symbol_B = "$el"
$tab GROUP BY Symbol_A
$tab UNION
$tab SELECT symbol_B AS symb, val1 AS val$abc[$order]
$tab FROM `dbl014`
$tab WHERE (
$tab symbol_A = "$el"
$tab AND symbol_B != ""
$tab )
$tab GROUP BY Symbol_B
LOM;
return $lom;
}
function merge_queries($str1,$str2,$or1,$or2,$tab)
{
// merges two node (or node group) queries (formed via fetch_elements() function)
// using the INNER JOIN function
return "$tab SELECT *\n$tab FROM (\n".$str1."\n$tab ) AS $or1\n$tab INNER JOIN (\n".$str2."\n$tab ) AS $or2\n$tab USING (symb)\n$tab ";
}
function fetch_common_elements($ar_el)
{
// produces the query that selects the common elements that have binaries with all of the elements given in the $ar_el array (using their symbols)
$total = sizeof($ar_el);
$abc = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
//$tab = str_repeat("\t",($total-$order-1));
$str[0] = fetch_elements($ar_el[0],0,$total-1);
$str[1] = fetch_elements($ar_el[1],1,$total);
$stri = merge_queries($str[0],$str[1],"A","B",str_repeat("\t",($total-2)));
for($i=2;$i<$total;$i++)
{
$str[$i] = fetch_elements($ar_el[$i],$i,$total);
$stri = merge_queries($str[$i],$stri,$abc[$i],$abc[($i+1)],str_repeat("\t",($total-$i-1)));
}
return $stri;
}
Now, we can call it with, for example:
echo fetch_common_elements(Array("Ga","Fe","Gd","Y","Ti","Ge","Ce"));
Which will output:
SELECT *
FROM (
SELECT symbol_A AS symb, val1 AS valG
FROM `dbl014`
WHERE symbol_B = "Ce"
GROUP BY Symbol_A
UNION
SELECT symbol_B AS symb, val1 AS valG
FROM `dbl014`
WHERE (
symbol_A = "Ce"
AND symbol_B != ""
)
GROUP BY Symbol_B
) AS G
INNER JOIN (
SELECT *
FROM (
SELECT symbol_A AS symb, val1 AS valF
FROM `dbl014`
WHERE symbol_B = "Ge"
GROUP BY Symbol_A
UNION
SELECT symbol_B AS symb, val1 AS valF
FROM `dbl014`
WHERE (
symbol_A = "Ge"
AND symbol_B != ""
)
GROUP BY Symbol_B
) AS F
INNER JOIN (
SELECT *
FROM (
SELECT symbol_A AS symb, val1 AS valE
FROM `dbl014`
WHERE symbol_B = "Ti"
GROUP BY Symbol_A
UNION
SELECT symbol_B AS symb, val1 AS valE
FROM `dbl014`
WHERE (
symbol_A = "Ti"
AND symbol_B != ""
)
GROUP BY Symbol_B
) AS E
INNER JOIN (
SELECT *
FROM (
SELECT symbol_A AS symb, val1 AS valD
FROM `dbl014`
WHERE symbol_B = "Y"
GROUP BY Symbol_A
UNION
SELECT symbol_B AS symb, val1 AS valD
FROM `dbl014`
WHERE (
symbol_A = "Y"
AND symbol_B != ""
)
GROUP BY Symbol_B
) AS D
INNER JOIN (
SELECT *
FROM (
SELECT symbol_A AS symb, val1 AS valC
FROM `dbl014`
WHERE symbol_B = "Gd"
GROUP BY Symbol_A
UNION
SELECT symbol_B AS symb, val1 AS valC
FROM `dbl014`
WHERE (
symbol_A = "Gd"
AND symbol_B != ""
)
GROUP BY Symbol_B
) AS C
INNER JOIN (
SELECT *
FROM (
SELECT symbol_A AS symb, val1 AS valA
FROM `dbl014`
WHERE symbol_B = "Ga"
GROUP BY Symbol_A
UNION
SELECT symbol_B AS symb, val1 AS valA
FROM `dbl014`
WHERE (
symbol_A = "Ga"
AND symbol_B != ""
)
GROUP BY Symbol_B
) AS A
INNER JOIN (
SELECT symbol_A AS symb, val1 AS valB
FROM `dbl014`
WHERE symbol_B = "Fe"
GROUP BY Symbol_A
UNION
SELECT symbol_B AS symb, val1 AS valB
FROM `dbl014`
WHERE (
symbol_A = "Fe"
AND symbol_B != ""
)
GROUP BY Symbol_B
) AS B
USING (symb)
) AS D
USING (symb)
) AS E
USING (symb)
) AS F
USING (symb)
) AS G
USING (symb)
) AS H
USING (symb)
and when fed to the database will yield:
thank y’all.. 8)
(Un)Common?
June 24, 2008 Posted by Emre S. Tasci
They say that, it is the opposite (unsimilar) couples that make up for much stabler relationships… If you have only 2 different values, (+) and (-), spin up and down, man and woman, then the unsimilar of a thing that is unsimilar to you equals to a thing that is similar to you. Although it is generally not valid for elements, it gives you an insight nonetheless.. So if Y, Gd, Fe and Ga play a similar part in dearest ZrO2‘s life, let’s check for their other common "partners" that are known to have some connection/involvement this or that way…
The table we’re interested in today holds the formula and the constituent elements as in the example below:
(Actually the table contains way more properties than the formula and the constituent elements but I’ve focused only to the relevant props)
symbol_A and symbol_B are position oriented wrt the written formula, meaning that for an AxBy binary, you have A for symbol_A and B for symbol_B whereas if it is BxAy, then they are reversed.
We will be looking for elements that form binaries with the aforementioned 4 elements, namely: Yttrium, Gadolinium, Iron and Gallium.
We can search for each of these 4 via the following query and then add it up:
SELECT symbol_A as symb,val1 FROM `dbl014`
WHERE symbol_B="Gd"
GROUP BY Symbol_A
UNION
SELECT symbol_B as symb, val1 FROM `dbl014`
WHERE (symbol_A="Gd" AND symbol_B!="")
GROUP BY Symbol_B
For the intersection of two elements, we glue the two like the above one via the INNER JOIN function:
SELECT * FROM
(
SELECT symbol_A as symb,val1
FROM `dbl014`
WHERE symbol_B="Gd"
GROUP BY Symbol_A
UNION
SELECT symbol_B as symb, val1
FROM `dbl014` WHERE (symbol_A="Gd" AND symbol_B!="")
GROUP BY Symbol_B
) AS A
INNER JOIN
(
SELECT symbol_A as symb,val1
FROM `dbl014` WHERE symbol_B="Y"
GROUP BY Symbol_A
UNION
SELECT symbol_B as symb, val1
FROM `dbl014` where (symbol_A="Y" AND symbol_B!="")
GROUP BY Symbol_B
) AS B
USING (symb)
ORDER BY symb
And for the ultimate quattro, it gets even exciting! :
SELECT *
FROM (
SELECT symbol_A AS symb, val1 AS val
FROM `dbl014`
WHERE symbol_B = "Fe"
GROUP BY Symbol_A
UNION
SELECT symbol_B AS symb, val1 AS val
FROM `dbl014`
WHERE (
symbol_A = "Fe"
AND symbol_B != ""
)
GROUP BY Symbol_B
) AS A
INNER JOIN (
SELECT *
FROM (
SELECT symbol_A AS symb, val1 AS valA
FROM `dbl014`
WHERE symbol_B = "Gd"
GROUP BY Symbol_A
UNION
SELECT symbol_B AS symb, val1 AS valA
FROM `dbl014`
WHERE (
symbol_A = "Gd"
AND symbol_B != ""
)
GROUP BY Symbol_B
) AS A
INNER JOIN (
SELECT *
FROM (
SELECT symbol_A AS symb, val1 AS valB
FROM `dbl014`
WHERE symbol_B = "Ga"
GROUP BY Symbol_A
UNION
SELECT symbol_B AS symb, val1 AS valB
FROM `dbl014`
WHERE (
symbol_A = "Ga"
AND symbol_B != ""
)
GROUP BY Symbol_B
) AS A
INNER JOIN (
SELECT symbol_A AS symb, val1 AS valC
FROM `dbl014`
WHERE symbol_B = "Y"
GROUP BY Symbol_A
UNION
SELECT symbol_B AS symb, val1 AS valC
FROM `dbl014`
WHERE (
symbol_A = "Y"
AND symbol_B != ""
)
GROUP BY Symbol_B
) AS B
USING ( symb )
) AS C
USING ( symb )
) AS D
USING ( symb )
ORDER BY symb
and voila!
a total of 42 all in all. The "val" columns are there for verificational purposes only – since after applying the GROUP BY function, they all lose their meanings.
Pettifor Map 2000 Edition
April 9, 2008 Posted by Emre S. Tasci
Suppose you have the formulas, structures and temperature and pressure of the measuring environment stored in your database. Here are some steps to draw a 2000 version of the Pettifor Map.
First of all, here is what my formula table in the database looks like:
The information in molA and molB columns are populated from formulas using the following MySQL commands:
AB
UPDATE dbl014 SET molA=1, molB=1 WHERE val1 NOT REGEXP "[0-9]"
AB[0-9]
UPDATE `dbl014` SET molA=1 WHERE val1 NOT REGEXP "[0-9][A-Z]" AND molA IS NULL
A[0-9]B
UPDATE `dbl014` SET molB=1 WHERE val1 NOT REGEXP "[0-9]$" AND molB IS NULL
A2B
UPDATE `dbl014` SET molA=2 WHERE val1 REGEXP "[A-z]2[A-z]" AND molB =1
AB2
UPDATE `dbl014` SET molB=2 WHERE val1 REGEXP "[A-z]2$" AND molA =1
But we will be focusing only to the AB compositon today.
In addition, I have another table where I store information about elements:
So I can go between Symbol to Atomic Number to Mendeleev Number as I please..
Here is what I did:
* Retrieved the complete list of AB compositions.
* Retrieved the reported structures and the temperature and pressure of the entries for each composition.
* Filtered the ones that weren’t measured/calculated at ambient pressure
* Also recorded each component’s Mendeleev number, so at the end I obtained the following table where formula and structure information is enumerated (for instance, the values you see in the formula column are nothing but the ids of the formulas you can see in the first figure):
* Next, selected those that were measured in ambient temperature (something like SELECT * FROM db.table GROUP BY structure, formula WHERE temp=298)
* Builded a histogram for structure populations, took the first 10 most populated structures as distinct and joining the rest as "other" (assigned structure number of "0"). By the way, here are the top 20 structures for AB compositions with the first number in parentheses showing its rank among binary compounds while the second is the number AB compositions registered in that structure type:
1 — NaCl,cF8,225 (4) (347)
2 — CsCl,cP2,221 (7) (306)
3 — TlI,oS8,63 (14) (126)
4 — FeB-b,oP8,62 (32) (69)
5 — NiAs,hP4,194 (16) (62)
6 — ZnS,cF8,216 (9) (51)
7 — CuAu,tP2,123 (44) (42)
8 — CuTi,tP2,123 (76) (34)
9 — Cu,cF4,225 (3) (34)
10 — FeAs,oP8,62 (51) (30)
11 — ZnO,hP4,186 (23) (28)
12 — W,cI2,229 (2) (26)
13 — FeSi,cP8,198 (57) (25)
14 — Mg,hP2,194 (5) (25)
15 — NaPb,tI64,142 (207) (14)
16 — ZrCl,hR12,166 (365) (14)
17 — NaO,hP12,189 (208) (13)
18 — AuCd,oP4,51 (121) (10)
19 — WC,hP2,187 (138) (10)
20 — DyAl,oP16,57 (149) (10)
* Wrote these data into a file ("str_wo_temp.txt" for further references) with [a] | [b] | [structure] being the columns as in :
79 100 11
26 93 11
24 93 11
73 92 11
78 100 11
27 64 0
66 92 0
12 97 0
53 101 0
61 86 4
31 84 4
23 85 4
32 85 4
33 85 4
27 85 4
so it was piece of a cake (not that easy, actually 8) to plot this using gnuplot :
set term wxt
set palette rgb 33,13,10
set view map
splot "str_wo_temp.txt" with points palette pt 5 ps 0.6
set xtics rotate by 90
set xtics("He" 1, "Ne " 2, "Ar" 3, "Kr " 4, "Xe" 5, "Rn " 6, "Fr" 7, "Cs " 8, "Rb" 9, "K " 10, "Na" 11, "Li " 12, "Ra" 13, "Ba " 14, "Sr" 15, "Ca " 16, "Yb" 17, "Eu " 18, "Sc" 19, "Lu " 20, "Tm" 21, "Er " 22, "Ho" 23, "Dy " 24, "Y" 25, "Tb " 26, "Gd" 27, "Sm " 28, "Pm" 29, "Nd " 30, "Pr" 31, "Ce " 32, "La" 33, "Lr " 34, "No" 35, "Md " 36, "Fm" 37, "Es " 38, "Cf" 39, "Bk " 40, "Cm" 41, "Am " 42, "Pu" 43, "Np " 44, "U" 45, "Pa " 46, "Th" 47, "Ac " 48, "Zr" 49, "Hf " 50, "Ti" 51, "Ta " 52, "Nb" 53, "V " 54, "W" 55, "Mo " 56, "Cr" 57, "Re " 58, "Tc" 59, "Mn " 60, "Fe" 61, "Ru " 62, "Os" 63, "Co " 64, "Rh" 65, "Ir " 66, "Ni" 67, "Pt " 68, "Pd" 69, "Au " 70, "Ag" 71, "Cu " 72, "Mg" 73, "Hg " 74, "Cd" 75, "Zn " 76, "Be" 77, "Tl " 78, "In" 79, "Al " 80, "Ga" 81, "Pb " 82, "Sn" 83, "Ge " 84, "Si" 85, "B " 86, "Bi" 87, "Sb " 88, "As" 89, "P " 90, "Po" 91, "Te " 92, "Se" 93, "S " 94, "C" 95, "At " 96, "I" 97, "Br " 98, "Cl" 99, "N " 100, "O" 101, "F " 102, "H" 103)
set ytics("He" 1, "Ne " 2, "Ar" 3, "Kr " 4, "Xe" 5, "Rn " 6, "Fr" 7, "Cs " 8, "Rb" 9, "K " 10, "Na" 11, "Li " 12, "Ra" 13, "Ba " 14, "Sr" 15, "Ca " 16, "Yb" 17, "Eu " 18, "Sc" 19, "Lu " 20, "Tm" 21, "Er " 22, "Ho" 23, "Dy " 24, "Y" 25, "Tb " 26, "Gd" 27, "Sm " 28, "Pm" 29, "Nd " 30, "Pr" 31, "Ce " 32, "La" 33, "Lr " 34, "No" 35, "Md " 36, "Fm" 37, "Es " 38, "Cf" 39, "Bk " 40, "Cm" 41, "Am " 42, "Pu" 43, "Np " 44, "U" 45, "Pa " 46, "Th" 47, "Ac " 48, "Zr" 49, "Hf " 50, "Ti" 51, "Ta " 52, "Nb" 53, "V " 54, "W" 55, "Mo " 56, "Cr" 57, "Re " 58, "Tc" 59, "Mn " 60, "Fe" 61, "Ru " 62, "Os" 63, "Co " 64, "Rh" 65, "Ir " 66, "Ni" 67, "Pt " 68, "Pd" 69, "Au " 70, "Ag" 71, "Cu " 72, "Mg" 73, "Hg " 74, "Cd" 75, "Zn " 76, "Be" 77, "Tl " 78, "In" 79, "Al " 80, "Ga" 81, "Pb " 82, "Sn" 83, "Ge " 84, "Si" 85, "B " 86, "Bi" 87, "Sb " 88, "As" 89, "P " 90, "Po" 91, "Te " 92, "Se" 93, "S " 94, "C" 95, "At " 96, "I" 97, "Br " 98, "Cl" 99, "N " 100, "O" 101, "F " 102, "H" 103)
set xtics font "Helvatica,8"
set ytics font "Helvatica,8"
set xrange [0:103]
set yrange [0:103]
#set term postscript enhanced color
#set output "pettifor.ps"
replot
and voila (or the real one with the accent which I couldn’t manage to 8) ! So here is the updated Pettifor Map for you (and for the AB compositons 8) But mind you, it doesn’t contain possible entries after year 2000.
You can plot the "same" map with Octave somewhat as follows (since I’m a newbie in Octave myself, I’ll take you as far as I could go – from there on, you’re on your own 8)
First, load the data file into a matrix:
load str_wo_temp.txt
then map this matrix contents into another matrix with the first (a) and second (b) columns being the new matrix’s column and row number and the third column (structure) being the value:
for i = 1:length(str_wo_temp)
A(str_wo_temp(i,1),str_wo_temp(i,2)) = str_wo_temp(i,3);
endfor
now, finally we can plot the density matrix:
imagesc(A)
which produces:
Oh, don’t be so picky about the labels and that thing called "legend"! 8) (By the way, if you know how to add those, please let me know, too – I haven’t started googling around the issue for the moment..)
While I was at it, I also plotted a 3-D Pettifor Map with the z-axis representing the temperature at which the structure was determined. This time, instead of saving all the data into one file, I saved the [a] [b] [temp] values into "structure name" files. Then, I picked up the most populated structure types (i.e., the ones contating the most lines) and moved the remaining into a temporary folder where I issued the
cat *.txt > others.txt
command. Then plotted this 3D map via gnuplot with the following command:
splot "NaCl,cF8,225.txt", "CsCl,cP2,221.txt", "TlI,oS8,63.txt", "FeB-b,oP8,62.txt", "NiAs,hP4,194.txt", "ZnS,cF8,216.txt", "FeAs,oP8,62.txt", "Cu,cF4,225.txt", "W,cI2,229.txt", "CuTi,tP2,123.txt", "CuAu,tP2,123.txt", "ZnO,hP4,186.txt", "Mg,hP2,194.txt", "FeSi,cP8,198.txt", "GeS,oP8,62.txt", "NaPb,tI64,142.txt", "AuCd,oP4,51.txt", "ZrCl,hR12,166.txt", "NaO,hP12,189.txt", "CuI,cF20,216.txt", "DyAl,oP16,57.txt", "InBi,tP4,129.txt", "PbO,tP4,129.txt", "CoO,tI4,139.txt", "WC,hP2,187.txt", "MoC,hP8,194.txt", "NaTl,cF16,227.txt", "GeTe,hR6,160.txt", "Sn,tI4,141.txt", "HgIn,hR6,166.txt", "In,tI2,139.txt", "PdBi,mP16,4.txt", "Nd,hP4,194.txt", "CuTi,tP4,129.txt", "NaP,oP16,19.txt", "HgS,hP6,152.txt", "CoSn,hP6,191.txt", "KGe,cP64,218.txt", "HgCl,tI8,139.txt", "AgI,cI38,229.txt", "others.txt"
Here are two snapshots:
I’m planning to include a java version of this 3D map where you can more easily read what you are looking…
Protected: mP12, oP12, tP6
January 14, 2008 Posted by Emre S. Tasci
Less is More MySQL
January 9, 2008 Posted by Emre S. Tasci
(-Right now, I’m going to define a new category: the MySQL category!-)
Suppose that, you have a database which contains a huge number of entries about the materials (like, for instance, the Pauling Database). Let it have 163 different properties we can query about. It is optimized for queries, so, the values are enumerated and the labels for these are kept in "pauling.dblxxx" tables, which may be something like this for the "Chemical System" property:
The values are kept in "pauling.valxxx" tables:
.jpg)
(Where the first one is the EntryCode, the PRIMARY key that relates all the tables and the second value is the enumeration for the value. For example, 1422 for the 13. property is actually Ho-Ir 🙂
and there is one more set of tables, the paulingv2.valxxx tables which are stored and keyed in val order, so:
- if we want to find the EntryCodes corresponding to a given property, we query the paulingv2.valxxx tables
- if we want to find the property that is corresponding to a given EntryCode we query the pauling.valxxx tables
- if we want to "translate" the property’s enumeration, we query the pauling.dblxxx tables.
Here is the thing: Let’s say that we want the Structure Types that have an Atomic Enviroment Type (AET) of a rhombic dodecahedron , with a/b ratio 1, alpha=beta=90o. The property numbers for these are:
Structure Type : 32
AET : 86
a/b ratio : 44
alpha : 41
beta : 42
Since the last three properties are numeric, we don’t enumerate them and the enumeration corresponding to the rhombic dodecahedron for AET is 6. So, fasten your seat belts, we are about to lift off! :
SELECT id,pauling.dbl032.val FROM pauling.dbl032
INNER JOIN
(
SELECT DISTINCT val FROM val032
INNER JOIN
(
SELECT v AS EntryCode FROM
(
(
SELECT val001.val AS v FROM val001
INNER JOIN paulingv2.val086
USING (EntryCode)
WHERE paulingv2.val086.val=6
) AS A
INNER JOIN
(
(
SELECT val001.val AS v FROM val001
INNER JOIN paulingv2.val044
USING (EntryCode)
WHERE paulingv2.val044.val=1
) AS B
INNER JOIN
(
# 41=90 && 42= 90
(
SELECT val001.val AS v FROM val001
INNER JOIN paulingv2.val041
USING (EntryCode)
WHERE paulingv2.val041.val=90
) AS C
INNER JOIN
(
SELECT val001.val AS v FROM val001
INNER JOIN paulingv2.val042
USING (EntryCode)
WHERE paulingv2.val042.val=90
) AS D
USING (v)
)
USING (v)
)
USING (v)
)
) AS G
USING (EntryCode)
)
AS Q ON (id = Q.val) ORDER BY val;
Later addition: Assuming 86=6; we don’t really need the other constraints 44=1, 42=90, 41=90 – do we?… So it’s just the boring:
SELECT id FROM pauling.dbl032
INNER JOIN
(
SELECT DISTINCT val FROM val032
INNER JOIN
(
SELECT v AS EntryCode FROM
(
(
SELECT val001.val AS v FROM val001
INNER JOIN paulingv2.val086
USING (EntryCode)
WHERE paulingv2.val086.val=6
) AS A
)
) AS G
USING (EntryCode)
)
AS Q ON (id = Q.val) ORDER BY Q.val;
The result of this is something like this:
To be honest, it is actually something like this :
The strange symbols are the price we pay for using non-standard charsets! 😉
So, to tidy up, I import this to a table with the following structure:
CREATE TABLE IF NOT EXISTS `bcc` (
`id` smallint(5) unsigned NOT NULL default ‘0’,
`val` varchar(254) NOT NULL default ”,
`usagecount` smallint(5) unsigned NOT NULL default ‘0’,
`val1` varchar(30) NOT NULL default ‘0’,
`val2` varchar(6) default NULL,
`val3` varchar(3) default NULL,
`val1t` varchar(30) NOT NULL,
`val2sp` smallint(5) unsigned NOT NULL,
`SG` tinyint(3) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `val` (`val`),
KEY `val1` (`val1`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
You have already met the id and val columns. "usagecount" will be imported from the pauling.dbl013 table; val1, val2, val3 are the seperated structure type information (ie, for "CuTi,tp4,129", val1="CuTi", val2="tp4" and val3="129"); val1t is the "translated" version of val1 which is the readeable one (ie, "(Ag¡•¦§Zn¡•¥¥)©Zn" is translated as "(Ag0.56Zn0.44)8Zn"). The translation is done via the following simpe php function:
function translate_symbols($string,$f_tr2symb=true)
{
$symbol_array = array("•","¡","¢","£","¤","¥","¦","§","¨","©","ª");//subscript values
$transl_array = array(".","0","1","2","3","4","5","6","7","8","9");//subscript values
if(!$f_tr2symb)return str_replace($transl_array,$symbol_array,$string);
else return str_replace($symbol_array,$transl_array,$string);
}
"val2sp" is the sliced numeric value from the Pearson Symbol stored in the val2 column.
To tidy up:
$query = "SELECT id, val1, val2 FROM bcc";
$qresult = mysql_query($query);
while($result = mysql_fetch_array($qresult))
{
$query2 = "UPDATE bcc SET val1t=\"".translate_symbols($result["val1"])."\", val2sp=SUBSTRING(val2,3,20) WHERE id = ".$result["id"]." LIMIT 1";
//echo $query2."\n";
$q2result = mysql_query($query2);
echo mysql_error();
}
You can refer to my previous entry for slicing up the "val" column. I had already done this while constructing the pauling.dblxxx tables, so in fact the actual view of the pauling.dbl032 table is the following one
meaning, I can just import them using the id’s of my new table:
UPDATE bcc, pauling.dbl032 SET
bcc.val = pauling.dbl032.val,
bcc.usagecount = pauling.dbl032.usagecount,
bcc.val1 = pauling.dbl032.val1,
bcc.val2 = pauling.dbl032.val2,
bcc.val3 = pauling.dbl032.val3
WHERE bcc.id = pauling.dbl032.id
Now we have something like:
We still have some work to do. Let’s take the two structures Ni2Al and Ni2In. Say that we are looking for superstructures, which have the property that the "atoms occupy atomic positions according to the parent crystal structure". The AET for Ni2Al is given as 14-b;14-b;14-b; whereas the AET for Ni2In is given as 11-a;11-a;14-b; . We want every atom to have the parent crystal structure (14-b – the rhombic dodecahedron for bcc), so we will eliminate those ones that have other AET.
mysql_query("USE pauling");
$ids_q = mysql_query("SELECT id FROM s07pt.bcc");
while($ids = mysql_fetch_row($ids_q))
{
$id= $ids[0];
$query = "
SELECT COUNT(DISTINCT val) FROM pauling.val032
INNER JOIN
(
SELECT v AS EntryCode FROM
(
SELECT pauling.val001.val AS v FROM pauling.val001
INNER JOIN paulingv2.val032
USING (EntryCode)
WHERE paulingv2.val032.val = ".$id."
) AS A
INNER JOIN
(
SELECT pauling.val001.val AS v FROM pauling.val001
INNER JOIN paulingv2.val086
USING (EntryCode)
WHERE paulingv2.val086.val != 6
) AS B
USING (v)
) as C USING (EntryCode)";
$query = mysql_query($query);
$result = mysql_fetch_row($query);
$result = mysql_result($query,0);
$result = ($result+1)%2;
$query = "UPDATE s07pt.bcc SET incl_theo = $result where id = $id LIMIT 1";
$j++;
echo $j.".\t".$query."\n";
mysql_query($query);
}
"incl_theo" is the column which is equal to 1 if the structure in question contains no AET other than 14-b, 0 otherwise. This gives us smt. like:
