What is the Correct way to sql, return and format all array data?

Question

I would appreciate some help on this one. i have achieved what i wanted but i don't know if this is the best way to go about it.

1st I queried the db.

mysql_select_db($database);
    $sql_many_sections =" 
    SELECT sections.subject, sections.section_number, sections.classroom, 
    sections.level_id, sections.course, sections.campus, sections.times, 
    sections.shift
    FROM sections
    LEFT JOIN section_teacher 
    ON sections.section_number = section_teacher.section_number
    LEFT JOIN profile 
    ON profile.teacher_number = section_teacher.teacher_number
    WHERE profile_id ='{$colname_teacher}'
    ORDER BY sections.section_number ASC";

    // bring out the results as an array so I can loop through them later                   
    $result = mysql_query($sql_many_sections);
    confirm_query($result);
    while ($teaching_sections = mysql_fetch_array($result)) {
      $teaches[] = $teaching_sections;
    }

Then I print_r the results which was.

Array
(
[0] => Array
    (
        [0] => English
        [subject] => English
        [1] => 32346
        [section_number] => 32346
        [2] => G635
        [classroom] => G635
        [3] => 2
        [level_id] => 2
        [4] => 236
        [course] => 236
        [5] => Male Science
        [campus] => Male Science
        [6] => 8 - 9:40 am, 10 - 11:40 am
        [times] => 8 - 9:40 am, 10 - 11:40 am
        [7] => AM
        [shift] => AM
    )

[1] => Array
    (
        [0] => English
        [subject] => English
        [1] => 49493
        [section_number] => 49493
        [2] => 1328
        [classroom] => 1328
        [3] => 2
        [level_id] => 2
        [4] => 236
        [course] => 236
        [5] => Male Humanities
        [campus] => Male Humanities
        [6] => 8 - 9:40 am, 10 - 11:40 am
        [times] => 8 - 9:40 am, 10 - 11:40 am
        [7] => AM
        [shift] => AM
    )

)

When I want to now return this data like this

•   Section: 32346
•   Subject: English
•   Classroom: G635
•   Level: 2
•   Course: 236
•   Campus: Male Science
•   Shift: AM
•   Shift times: 8 - 9:40 am, 10 - 11:40 am

The only way i could get it to work was by doing the below and that was an accident. What is the correct way to achieve these same results. Thanks

<?php 
foreach($teaches as $value)
{ ?>
<ul>
<li>Section: <? echo  $value[1];?></li> 
<li>Subject: <? echo $value[0];?></li> 
<li>Classroom: <? echo $value[2];?></li> 
<li>Level: <? echo $value[3];?></li> 
<li>Course: <? echo $value[4];?></li> 
<li>Campus: <? echo $value[5];?></li> 
<li>Shift: <? echo $value[7];?></li> 
<li>Shift times: <? echo $value[6];?></li> 
</ul>
<?
} 
?>

Please, don't use mysql_* functions to write new code. They are no longer maintained and the community has begun deprecation process. See the red box? Instead you should learn about prepared statements and use — NullPoiиteя
– NullPoiиteя, Commented Oct 22, 2012 at 12:59
either PDO or MySQLi. If you can't decide which, this article will help you. If you pick PDO, here is good tutorial. Also see Why shouldn't I use mysql functions in PHP? — NullPoiиteя
– NullPoiиteя, Commented Oct 22, 2012 at 13:00
The way you have done it is probably optimal already. It is often better to loop over as you have and accumulate rows into an array (unless you have many thousands of rows), then to use the array when creating output rather than mixing the fetch logic into your output code. — Michael Berkowski
– Michael Berkowski, Commented Oct 22, 2012 at 13:03
Only recommended change is to use mysql_fetch_assoc() instead of mysql_fetch_array() and then use the associative keys when outputting, as in <? echo $value['subject'] ?> — Michael Berkowski
– Michael Berkowski, Commented Oct 22, 2012 at 13:04
Thanks all for the help I see the difference with the assoc and not array. One thing i'm confused about now is i've been learning for 2 months from some tutorials from a well know company and this is what they taught in the basic series (re the mysql_*). would oop solve the issues mentioned by "NullPointer" as that was what i wanted to learn next? thanks — user1740151
– user1740151, Commented Oct 22, 2012 at 13:22

jaco · Accepted Answer · 2012-10-22 13:06:26Z

2

You could use this way:

foreach ($teaches as $teach) {
    echo '<ul>';
    foreach ($teach as $name => $value) {
        $name = str_replace('_', ' ', $name);
        $name = ucfirst($name);
        echo '<li>' . $name . ': ' . $value . '</li>';
    }
    echo '</ul>';
}

And obviously I suggest you to use mysql_fetch_assoc() so you can get only the array key instead of its index.

answered Oct 22, 2012 at 13:06

jaco

3,5265 gold badges28 silver badges43 bronze badges

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

Collectives™ on Stack Overflow

What is the Correct way to sql, return and format all array data?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related