PHP SELECT loop

Question

I have a School database with two courses, each course has 12 possible subject fields (co_subj...) that can contain the subject ID or NULL, in this case course ID 1 has 3 subjects, and course 2 has only 1:

Courses

I need PHP to create a <div> for each subject found on a course, and don't create nothing in any NULL case. Querys:

$select = mysql_query("SELECT * FROM course_conf JOIN course_type ON ct_id=co_fk_ct_id ORDER BY co_name");

And then a while makes PHP check every course's field:

while($registroBbdd = mysql_fetch_array($select))
                {
                    $class="";
                    $courseId=$registroBbdd['co_id'];
                    $courseName=$registroBbdd['co_name'];
                    $courseType=$registroBbdd['ct_name'];

The doubt comes right now, trying to solve the most efficient way the <div> creating I mentioned before. The only way I find to solve this is creating a conditional "IF" structure printing the if a value is found, and nothing if NULL is found, like this:

if($registroBbdd['co_subj1'] != NULL){
     echo "<div>'.$registroBbdd['co_subj1'].'</div>}
else if ($registroBbdd['co_subj2'] != NULL){
     echo "<div>'.$registroBbdd['co_subj2'].'</div>}
.............}

Is there any looping way to make this? In order to avoid the whole "if" structure creation.

AMS · Accepted Answer · 2014-04-03 15:53:01Z

2

What about trying a via a loop with isset($registroBbdd['co_subj'+$i])

answered Apr 3, 2014 at 15:53

AMS

4492 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

RobP · Accepted Answer · 2014-04-03 15:54:59Z

1

You could have the SQL statement do the work. For example:

SELECT *, (CASE WHEN co_subj1 IS NOT NULL THEN co_subj1 ELSE co_subj2 END) AS subject 
FROM course_conf JOIN course_type ON ct_id=co_fk_ct_id ORDER BY co_name
WHERE co_subj1 IS NOT NULL OR co_subj2 IS NOT NULL

answered Apr 3, 2014 at 15:54

RobP

9,5923 gold badges22 silver badges33 bronze badges

9 Comments

spencer7593 Over a year ago

or, just IFNULL(co_subj1,co_subj2) AS subject

RobP Over a year ago

IFNULL is nice and concise. I lose track if which SQL versions and dialects support what, but that one's ubuquitous, right? @Biomehanika, I think it's faster, more maintainable, with less code to let the SQL statement prepare the results the way you want. Maybe give it a try!

RobP Over a year ago

the purpose of the CASE statement (or the IFNULL in @spencer's improved version) is to give you a new field in your query called "subject" which contains either co_subj1 or co_subj2 as necessary. So in your loop, don't use an if-else, always do this: echo "<div>'.$registroBbdd['subject'].'</div>

RobP Over a year ago

you're welcome. Much simpler looking code now, isn't it? Not that it's always OK to push complexity down into the SQL, but often the SQL engine is way more optimized than a PHP loop through the results would be.

RobP Over a year ago

IFNULL() works with 2 arguments, but COALESCE() works with as many as you like

|

Collectives™ on Stack Overflow

PHP SELECT loop

2 Answers 2

Comments

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related