More efficient MySQL / PHP code

Question

I'm building a dynamic drop down box for an HTML form using PHP and a MySQL DB (sugarcrm) to populate said box.

So far for this one i have hard-coded a lot of it - but there has to be a better way.

There must be a much more efficient way to write this code than what I'm doing. Any input is welcome:

function services(){

    mysql_connect('myhost', 'myname', 'mypass');
    mysql_select_db('spaladon_sugar');
    $sqlF = "SELECT id, type, name FROM serv1_services WHERE type LIKE 'facial'";
    $resultF = mysql_query($sqlF);
    $sqlT = "SELECT id, type, name FROM serv1_services WHERE type LIKE 'treatments'";
    $resultT = mysql_query($sqlT);
    $sqlS = "SELECT id, type, name FROM serv1_services WHERE type LIKE 'salon'";
    $resultS = mysql_query($sqlS);
    $sqlWax = "SELECT id, type, name FROM serv1_services WHERE type LIKE 'waxing'";
    $resultWax = mysql_query($sqlWax);
    $sqlWell = "SELECT id, type, name FROM serv1_services WHERE type LIKE 'wellness'";
    $resultWell = mysql_query($sqlWell);
    $sqlH = "SELECT id, type, name FROM serv1_services WHERE type LIKE 'haircutting'";
    $resultH = mysql_query($sqlH);
    $sqlM = "SELECT id, type, name FROM serv1_services WHERE type LIKE 'makeup'";
    $resultM = mysql_query($sqlM);
    $sqlC = "SELECT id, type, name FROM serv1_services WHERE type LIKE 'color'";
    $resultC = mysql_query($sqlC);

    echo "<select name='services'>";
    echo "<option value=''> - Facials - </option>";
    while ($row = mysql_fetch_array($resultF)) {
        echo "<option value='" . $row['name'] . "'>". $row['name'] . "</option>";
    }
    echo "<br /><option value=''> - Medical Spa Treatments - </option>";
    while ($row = mysql_fetch_array($resultT)) {
        echo "<option value='" . $row['name'] . "'>". $row['name'] . "</option>";
    }
    echo "<option value=''> - Salon - </option>";
    while ($row = mysql_fetch_array($resultS)) {
        echo "<option value='" . $row['name'] . "'>". $row['name'] . "</option>";
    }
    echo "<option value=''> - Waxing - </option>";
    while ($row = mysql_fetch_array($resultWax)) {
        echo "<option value='" . $row['name'] . "'>". $row['name'] . "</option>";
    }
    echo "<option value=''> -  Wellness - </option>";
    while ($row = mysql_fetch_array($resultWell)) {
        echo "<option value='" . $row['name'] . "'>". $row['name'] . "</option>";
    }
    echo "<option value=''> - Haircutting - </option>";
    while ($row = mysql_fetch_array($resultH)) {
        echo "<option value='" . $row['name'] . "'>". $row['name'] . "</option>";
    }
    echo "<option value=''> - Makeup - </option>";
    while ($row = mysql_fetch_array($resultM)) {
        echo "<option value='" . $row['name'] . "'>". $row['name'] . "</option>";
    }
    echo "<option value=''> - Color and Highlight - </option>";
    while ($row = mysql_fetch_array($resultC)) {
        echo "<option value='" . $row['name'] . "'>". $row['name'] . "</option>";
    }
    echo "</select>";
}

you should seperate the data from the view

Ryan
– Ryan

2012-05-15 02:17:04 +00:00
Commented May 15, 2012 at 2:17 — Ryan
– Ryan, Commented May 15, 2012 at 2:17

mauris · Accepted Answer · 2012-05-15 02:20:35Z

5

A rough improvement:

function services(){

    mysql_connect('myhost', 'myname', 'mypass');
    mysql_select_db('spaladon_sugar');
    $types = array(
        'facial' => 'Facials',
        'treatments' => 'Treatments',
        // and more typename => Type Label
    );

    echo "<select name='services'>";
    foreach($types as $name => $label){
        $sql = sprintf('SELECT id, type, name FROM serv1_services WHERE type LIKE \'%s\'', $name);
        $result = mysql_query($sq;);
        echo '<option value=""> - ' . $label . ' - </option>';
        while ($row = mysql_fetch_array($result)) {
            echo "<option value='" . $row['name'] . "'>". $row['name'] . "</option>";
        }
    }
    echo "</select>";
}

answered May 15, 2012 at 2:20

mauris

43.6k16 gold badges102 silver badges132 bronze badges

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

Comments

bitoshi.n · Accepted Answer · 2012-05-15 03:46:24Z

4

Just query once.

function services(){

    mysql_connect('myhost', 'myname', 'mypass');
    mysql_select_db('spaladon_sugar');
    $sql = "SELECT id, type, name FROM serv1_services WHERE type IN ('facial', 'treatment', 'salon') ORDER BY type"; //you can add another filter on WHERE type IN (...)
    $result = mysql_query($sql);
    $type = '';

    echo "<select name='services'>";
    while ($row = mysql_fetch_array($result)) {
        if($type!=$row['type'])
        {
           if($type!='')
              echo "</optgroup>";
           switch($row['type'])
           {
              case 'facials':                     
                 echo "<optgroup label=' - Facials - '>";
                 break;
              // etc for another case...
           }
           $type=$row['type'];
        }
        echo "<option value='" . $row['name'] . "'>". $row['name'] . "</option>";
    }    
    echo "</select>";
}

edited May 15, 2012 at 3:46

answered May 15, 2012 at 2:30

bitoshi.n

2,3381 gold badge16 silver badges16 bronze badges

3 Comments

Frank Farmer Over a year ago

+1. Since it's possible serv1_services might include many rows beyond those we're interested in, it might be good to add a WHERE clause, e.g. WHERE type IN ('facial', 'treatment', 'salon'... There may also be ordering issues to consider.

Hazy McGee Over a year ago

Works great - Im looking around for how to have a white space after each category as well - if anything pops to mind let me know :D THANKS A LOT!

bitoshi.n Over a year ago

@HazyMcGee, Look at my edited post or <optgroup> reference

Collectives™ on Stack Overflow

More efficient MySQL / PHP code

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related