Concat () function or alternative solution in mysql query

Question

I am trying to add a character before/after value in mysql query. but I can't make it work. This is the part that doesn't work in my case:

$query = "select CONCAT ('.', DuRpt) as DuRpt, DaRpt from DDtb order by DATE DESC";

You can see the full code below. Any ideas why it doesn't work or can I get an alternative solution, please. thanks.

<div class="container">
<div class="left">
<?php

include ("etc/config.php");

$query = "select concat ('.', DuRpt) as DuRpt, DaRpt from DDtb order by DATE DESC"; 
$result = mysqli_query($link, $query); 
if (!$result) { 
    $message = 'ERROR:' . mysqli_error($link); 
    return $message; 
} else { 
    $i = 0; 
    echo '<form name="select" action="" method="GET">';
    echo '<select name="mySelect" id="mySelect"  size="44" onchange="this.form.submit()">'; 
    while ($i < mysqli_field_count($link)) { 
        $meta =  
            mysqli_fetch_field_direct($result, $i); 
        echo '<option>' . $meta->name . '</option>'; 
        $i = $i + 1; 
    } 
    echo '</select>';
    echo '</form>';
}

    ?>

   </div>
  <div>

  <?php

if(isset($_GET['mySelect'])) {
    $myselect = $_GET['mySelect']; 
    $sql = "SELECT `$myselect` as mySelect from DDtb order by DATE DESC";   
    $result = mysqli_query($link, $sql);   

    if ($result->num_rows > 0) {

        $table_row_counter = 3;
        echo '<table>';

        while($row = $result->fetch_assoc()) 
        {
            $table_row_counter++;
            if ($table_row_counter % 30 == 1) {
                echo '</table>';
                echo '<table>';
            }

            echo "<tr><td>" . $row["mySelect"] . "</td></tr>";

        }
    }
} 

echo '</table>';
mysqli_close($link);
?> 

</div>
</div>

Do you get any error? What does the format of your DDtb look like? — khartnett
– khartnett, Commented Jul 27, 2018 at 22:58
I don't get any error. and the strange thing is the results are output without the character (.), I only get the plain results. — Max
– Max, Commented Jul 27, 2018 at 23:02
Where would you expect the result to be output? I don't see DuRpt anywhere besides your query... — khartnett
– khartnett, Commented Jul 27, 2018 at 23:18
DuRpt is displayed in selectbox, and when clicked the results are displayed. DuRpt becomes myselect in this case. — Max
– Max, Commented Jul 27, 2018 at 23:20
ah, I see, so $myselect gets the value of 'DuRpt', and your 2nd query just ends up being "SELECT DuRpt as mySelect from DDtb order by DATE DESC" — khartnett
– khartnett, Commented Jul 27, 2018 at 23:27

khartnett · Accepted Answer · 2018-07-27 23:57:42Z

For the 2nd half of your code, you can do this: note you won't need to concat anything in your initial query

if(isset($_GET['mySelect'])) {
    // configure every option here, if there's not pre/postfix, use a blank string
    $prepostfixes = [
        'DuRpt' => ['.', '.'],
        'DaRpt' => ['', ''],
    ];
    $myselect = $_GET['mySelect'];
    if (!isset($prepostfixes[$myselect])) {
        die ('Unknown Select'); // this will prevent sql injection
    }
    $sql = "SELECT `$myselect` as mySelect from DDtb order by DATE DESC";
    $result = mysqli_query($link, $sql);

    if ($result->num_rows > 0) {

        $table_row_counter = 3;
        echo '<table>';
        $prefix = $prepostfixes[$myselect][0];
        $postfix = $prepostfixes[$myselect][1];

        while($row = $result->fetch_assoc())
        {
            $table_row_counter++;
            if ($table_row_counter % 30 == 1) {
                echo '</table>';
                echo '<table>';
            }

            echo "<tr><td>" . $prefix . $row["mySelect"] . $postfix . "</td></tr>";

        }
    }
}

user2203703 · Accepted Answer · 2018-07-27 23:24:27Z

0

Just update your code and remove the duplicate of DuRpt from it.

$query = "select concat ('.', DuRpt) as DuRpt from DDtb order by DATE DESC";

answered Jul 27, 2018 at 23:24

user2203703

2,0254 gold badges23 silver badges36 bronze badges

10 Comments

Max Over a year ago

I'm sorry, I don't understand what you mean with the duplicate of DuRpt. I tried your suggestion in the code, it didn't work.

user2203703 Over a year ago

are you using phpmyadmin?

user2203703 Over a year ago

phpmyadmin -> your database, click on SQL (beside the Structure) and execute select concat ('.', DuRpt) as DuRpt from DDtb order by DATE DESC and let me know the result.

Max Over a year ago

in phpmyadmin, it works. I mean the character (.) is added to the field value. but it's not working in the code.

user2203703 Over a year ago

if it works from phpmyadmin, then it should work within your code and give you the same result.

|

Collectives™ on Stack Overflow

Concat () function or alternative solution in mysql query

2 Answers 2

Comments

10 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

10 Comments

Related