0

I'll admit, I'm an average query coder so I'm pretty sure my issue is "operator error"

I have 2 db tables. The columns are in ()

  • table1 (form_id, form_name, field_order)

  • table2 (field_id, field_name)

Table1 contains all my forms. The field_order column contains the ID's for all the field_names in table2. So the field_order column in table 1 looks like (682,1,2,341,72,72,73)

These numbers correspond to the id's for the fields in table 2.

Where my problem is, is that in my query using the IN clause, the form fields results are listed in ASC order by field_id in table2. I need the field_id and field_name to be listed in the field_order in table1.

Any help is appreciated. I think I'm missing something simple.

My code is below.

<?php   
$sqlForms = "select t1.form_id, t1.form_name, t1.field_ids from table1 as t1 where t1.form_id != '10' order by form_name asc";

$resultsSQL = $this->EE->db->query($sqlForms);
    if ($resultsSQL->num_rows() > 0)
    {
        echo "<table border=1>";

        foreach($resultsSQL->result() as $row)
        {
        echo "<tr>";
        echo "<td>".$row->form_id."</td><td>".$row->form_name."<br />";

        $formID = str_replace("|", ",", "$row->field_ids");

        $sqlFields = "select t2.field_id, t2.field_name
                        from table2 as t2
                        where t2.field_id IN (".$formID.")";

            $resultsFields = $this->EE->db->query($sqlFields);
                if ($resultsFields->num_rows() > 0)
                {
                    echo "<table border=2>";
                        foreach($resultsFields->result() as $rowFields)
                        {
                            echo "<tr><td>".$rowFields->field_id."</td><td>".$rowFields->field_name."</td></tr>";
                        }
                    echo "</table>";
                }

        echo "</td>";
        echo "</tr>";
        }
        echo "</table>";
    }
?>
0

4 Answers 4

1

I'm an idiot! All I needed to do was add ORDER BY FIELD(id,5,2,6,8,12,1...) to my code. I like it when the answer is an easy one. I just wish it wouldn't take me so long to figure out!

1

Assuming that the table1 has form_id as the PRIMARY KEY, you can combine both queries into one. No need for all these complications (i.e. first run query A, then assemble the returned IDs using PHP, then use the assembled list to run query B with ORDER BY FIELD().

You can simply run one query:

select t2.field_id, t2.field_name
from table2 as t2
  join table1 as t1
    on t2.field_id = t1.form_id
where t1.form_id <> 10 
order by t1.form_name asc ;
1
WHERE                x IN (682,1,2,341,72,72,73)
ORDER BY FIND_IN_SET(x,   "682,1,2,341,72,72,73")
0

You will need to add a column for Field Sequence some where in this mix.

I suggest adding another table for like this table3 (form_ID, field_id, field_sequence)

Then removing field_order from table one

 $sqlFields = "select t2.field_id, t2.field_name
                    from table2 as t2
                    where t2.field_id IN (".$formID.")";

becomes:

 $sqlFields = "select t3.field_id
                    from table3 as t3
                    where t3.form_ID IN (".$formID.") order by t3.field_sequence";

you can join table3 with table1 and table2 if you need the acutal n

3
  • Would field_squence be the field order?
    – user1469
    Commented Nov 2, 2015 at 18:39
  • Do you mean create a temp table in that in table3, each row would consist of the form is, field id and field id sequence? So that if form 33 had 24 fields, there would 24 rows for that form in table3?
    – user1469
    Commented Nov 2, 2015 at 19:16
  • Makes sense, I just need to figure out how to do that. The problem is that I'm late to the game on this. As I have several forms so I would be kind of working backwards.
    – user1469
    Commented Nov 2, 2015 at 19:16

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.