[eluser]Leon Stafford[/eluser]
Hi,
I have hacked together some code to do what I want, but it is totally inefficient and I would be so happy if someone could help me optimize it using JOINS or whatever works (my brain tends to melt with DB stuff...)
Code:
###################
//My db setup
+----------------------+
| my tables |
+----------------------+
| kg_compounds |
| kg_progress |
+----------------------+
mysql> describe kg_progress;
+-------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| user | varchar(45) | NO | | | |
| kanji | varchar(6) | NO | | | |
| progress | tinyint(4) | NO | | 0 | |
| compound | int(11) | NO | | | |
| compound_progress | tinyint(4) | NO | | | |
+-------------------+------------------+------+-----+---------+----------------+
mysql> describe kg_compounds;
+------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| compound | varchar(50) | NO | | | |
| reading | varchar(200) | YES | | NULL | |
| meaning | varchar(200) | YES | | NULL | |
| grade | varchar(30) | YES | | NULL | |
| usefulness | tinyint(4) | NO | | | |
+------------+------------------+------+-----+---------+----------------+
The kg_progress.compound corresponds to kg_compounds.id
The kg_progress.compound_progress is set to 0 or 1
Here is the code which is WORKING, just very slowly and without allowing me to add a proper LIMIT...
Code:
function get_word_list($level = 1, $isShuffled = false)
{
$user = $this->db_session->userdata('id');
if ($isShuffled)
{
$query = $this->db->query("
SELECT *
FROM kg_compounds
WHERE grade='$level'
ORDER BY RAND()");
}
else
{
$query = $this->db->query("
SELECT *
FROM kg_compounds
WHERE grade='$level'
");
}
$compound_list = array();
foreach ($query->result() as $row)
{
$query2 = $this->db->query("
SELECT * FROM kg_progress
WHERE user = '$user' AND
compound = '$row->id' AND
compound_progress = 1
");
if ($query2->num_rows() < 1)
{
$compound_list[] = array(
'id' => $row->id,
'compound' => $row->compound,
'reading' => $row->reading,
'meaning' => $row->meaning,
'grade' => $row->grade,
);
}
}
return $compound_list;
}