PHP function with multiple mySQL queries

Question

I have a series of functions that was written by a previous developer and now I am trying to optimize them.

function translateAdmSection() {
$lang = $_REQUEST['lang'];

if($lang == '') {
    $content = 'content';
}
if($lang == 'en') {
    $content = 'content';
}
if($lang == 'es') {
    $content = 'es_content';
}
if($lang == 'fr') {
    $content = 'fr_content';
}
if($lang == 'ko') {
    $content = 'kr_content';
}
if($lang == 'mdn') {
    $content = 'mdn_content';
}
if ($lang == 'pt') {
    $content = 'pt_content';
}

$title_query = ("SELECT adm_name, ".$content." as content FROM adm_sect WHERE adm_name = 'title';");
$title_result = mysql_query($title_query);
$title_row = mysql_fetch_assoc($title_result);

$sub_head_query = ("SELECT adm_name, ".$content." as content FROM adm_sect WHERE adm_name = 'Sub-headline';");
$sub_head_result = mysql_query($sub_head_query);
$sub_head_row = mysql_fetch_assoc($sub_head_result);

$content_query = ("SELECT adm_name, ".$content." as content FROM adm_sect WHERE adm_name = 'content';");
$content_result = mysql_query($content_query);
$content_row = mysql_fetch_assoc($content_result);


$section_name = $title_row['content'];
$section_sub_head = $sub_head_row['content'];
$section_content =   $content_row['content'];


  echo '<div class="section-title">
            <h2>'.$section_name.'</h2>
            <h3>'.$section_sub_head.'</h3>
        </div>

        <div class="row">
            <div class="column column-1-2">'.$section_content.'</div><!-- .column-1-2 -->

            <div class="column column-1-2">
                <div class="contact-info">
                    <h4>Main Campus Address</h4>
                    <p>
                        Oral Roberts University<br />
                        Office of Admissions<br />
                        <span class="locality">
                            7777 South Lewis Avenue<br />
                            Tulsa, Oklahoma 74171<br />
                            United States of America
                        </span>
                    </p>
                    <h4>Call or Email Undergraduate Admissions</h4>
                    <p>
                        <span class="tel">+1 800.678.8876<br /></span>
                        <span class="tel">+1 918.495.6518<br /></span>
                        <span class="email">E: <a href="mailto:&#x61;&#x64;&#x6D;&#x69;&#x73;&#x73;&#x69;&#x6F;&#x6E;&#x73;&#x40;&#x6F;&#x72;&#x75;&#x2E;&#x65;&#x64;&#x75;">admissions@oru.edu</a></span>
                    </p>
                    <h4>Call or Email ORU&#x27;s Coordinator of Chinese Intiatives</h4>
                    <p>
                        <span class="tel">+1 918.495.6540<br /></span>
                        <span class="email">E: <a href="mailto:&#x79;&#x66;&#x61;&#x6E;&#x67;&#x40;&#x6F;&#x72;&#x75;&#x2E;&#x65;&#x64;&#x75;">yfang@oru.edu</a></span>
                    </p>
                </div><!-- .contact-info -->
            </div><!-- .column-1-2 -->
        </div><!-- .row -->

        <div class="row">
            <div class="column column-1-2 centered">
                <h3><a class="button arrow-button" href="#">Request More Information</a></h3>
            </div><!-- .column-1-2 -->
            <div class="column column-1-2 centered">
                <h3><a class="button arrow-button" href="#">APPLY TO ORU TODAY</a></h3>
            </div><!-- .column-1-2 -->
        </div><!-- .row -->';

}

The table being referenced is set up as follows:

| ID | adm_name          | content | es_content |
-------------------------------------------------
| 1  | title             | content | translated |
| 2  | content           | content | translated |
| 3  | address_info      | content | translated |
| 4  | RFI Button Text   | content | translated |
| 5  | RFI Link          | content | translated |
| 6  | Apply Button Text | content | translated |
| 7  | Apply Link        | content | translated |
| 8  | Sub-headline      | content | translated |

The $lang through top IF statements determine the page language and select the DB content column based on the language. So that if $lang == 'es' then the $content table header would be es_content

My question(s) are as follows:

1) How can I separate, if possible, the $lang parameter call at the beginning of the function so that I can duplicate this function for "adm_sect", "finaid_sect" and "onl_learning_sect"

2) Can I simplify this down to 1 query since the only difference is the "adm_name" variable?

3) How can I generate the individual column values so that I can put them all in one export value? Or is this possible since they are based on individual sql queries?

The end goal is to have one query that generates the values for each translated portion from the DB. Is this possible and if so recommendations on how to accomplish this?

Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Feb 10 '16 at 18:39
"One query" here should be easy. Can you share what you've tried? — Jay Blanchard, Feb 10 '16 at 18:41
@Jay - I am not a PHP programmer and didn't write the code above so I am learning as I go, hence my request for advice. I have tried putting each "adm_name" in a variable and query but it only returns one variable unless I have a for results loop. — Jonathan Anspaugh, Feb 10 '16 at 18:46
This old dangerous code. Use PDO, or mysqli and use prepared statements. This code is a SQL Injection honeypot. — digitai, Feb 10 '16 at 18:52
Fair enough. Typically SO is a "one question at a time" kind of place. I only say that because you're likely to receive down votes and close votes because multiple questions make the post "too broad". Additionally folks here like to see some effort and may want to know why that effort failed. As to your questions 1.) What are these? Sections of the website? 2.) Yes, this can likely be simplified if you show us what your desired output is. 3.) Will likely be in the answer to 2. — Jay Blanchard, Feb 10 '16 at 18:53
It also looks like a bad table setup / database design. Typically you would have all of those rows as columns and then you would have a table for each language type that you could join with the id of the content info and the content. I know it is a lot to take in, but you may have to endure some pain to get to a better system. — Jay Blanchard, Feb 10 '16 at 18:58
This is one of the control functions for a website found at web.oru.edu/dev/oruglobal/index-test.php If you add `?lang=es` or "ko or pt or mdn" you can see how the page translates. The system is functional currently but the multiple queries are slowing down the load times and I feel like this should be easier to accomplish than my former developer attempted. I am learning a lot but still have a lot to learn. I need the function to produce the `echo` statement at the bottom of the function outlined above. — Jonathan Anspaugh, Feb 10 '16 at 18:59
The DB is actually over 50 tables structured like this due to the multiple translations available. I agree this isnt the optimal structure but it isn't in the production timeline to rebuild the entire DB right now — Jonathan Anspaugh, Feb 10 '16 at 19:00
All those `if` statements should be `elseif`, since the cases are mutually exclusive. Or you could use `switch/case`. — Barmar, Feb 10 '16 at 19:13

Barmar · Accepted Answer · 2016-02-10T19:27:34.507

1) You can put that code in a function in a separate file, that you load with require.

2) You can do this as a single query. Put all the values in an associative array.

$values = array();

$query = "SELECT adm_name, $content AS content FROM adm_sect
          WHERE adm_name IN ('title', 'Sub-headline', 'content')";
$result = mysql_query($query);
while ($row = mysql_fetch_assoc($result)) {
    $values[$row['adm_name']] = $row['content'];
}

Now instead of $title_row you use $values['title'].

3) I don't understand the question. What do you mean by putting the columns in one export value? Unless the array in my above code is what you're talking about.

yes #3 is what you did in the array. Thank you very much. I will give this a try. — Jonathan Anspaugh, Feb 10 '16 at 19:26

PHP function with multiple mySQL queries

1 Answers1