Change string before writing into mysql database

Question

0.00/5 (No votes)

See more:

Hello CodeProject,

currently i'm writing an app for an landlord/lesser.
I have created a table which have a column "Muell", defined as decimal(6,2).
I'm using an Inputfield with

SQL

SELECT distinct CONCAT(e.Miete, " €") AS Miete,

as default content. If the user changes the field, and leaves the "€" i'm running into an error.

I'm using that statement:

SQL

UPDATE tbl_nebenkosten
    SET Muell = '$Muell',
        Wasser = '$Wasser',
        Versicherung = '$Versicherung',
        Strom = '$Strom',
        Hausverwaltung = '$Hausverwaltung',
        Heizung = '$Heizung'
    WHERE MieterID = '${state.MieterID}'

I read MySQL SUBSTRING_INDEX() function - w3resource[^]. On that page just a example with "SELECT" is given.

In my case i need UPDATE instead.
Sadly i just started programming with SQL, so many is new for me.

What I have tried:

SQL

UPDATE tbl_nebenkosten
    SET Müll = SELECT SUBSTRING_INDEX('$Muell',' ',1)
        Wasser = '$Wasser',
        Versicherung = '$Versicherung',
        Strom = '$Strom',
        Hausverwaltung = '$Hausverwaltung',
        Heizung = '$Heizung'
    WHERE MieterID = '${state.MieterID}'

That one gives me an error.

Posted 23-Apr-18 0:51am

Sascha Manns

Updated 23-Apr-18 2:07am

v3

Add a Solution

2 solutions

Solution 1

Validate the values (and optionally clean them up like removing a currency symbol) before passing them to the database. That allows you also to show specific error messages upon invalid input. Otherwise you are stuck to SQL error messages which are not meaningful for most users.

How to do the validation depends on the used language and how data are passed (e.g. from a form or importing from a file).

You should also use parametrised SQL queries. With those data are passed as objects and not as strings which avoids SQL injection - Wikipedia[^].

Posted 23-Apr-18 1:14am

Jochen Arndt

Comments

Sascha Manns 23-Apr-18 7:28am

In general the Inputfield is connected to a data capture container. There the value will be used in a variable and then i'm trying to save it.
The Inputfield itself uses a database query (the mentioned CONCAT command) to get the "old" price from the database. So the user can see what value are stored and it is visible in the Inputfield. If the user changes nothing, the variables will be written into the database.
What would be a good statement to remove the "€" if exists?

Jochen Arndt 23-Apr-18 7:50am

That can be only answered when knowing the used programming language or framework.

Sascha Manns 23-Apr-18 7:52am

I'm using a composer like this https://www.engomo.com/de/home/

Jochen Arndt 23-Apr-18 8:05am

Then you should ask there for support. Input validation and clean up should be done by the framework, not by the database. There will be some framework used by the composer. There may be options to access that, options for input validation, or nothing. But only they would know.

In any case you can move the display of the Euro symbol out of the input box by placing it right of it or within the label.

Sascha Manns 23-Apr-18 8:08am

Hi Jochen. I found a solution (See below). That works as expected.

Jochen Arndt 23-Apr-18 8:20am

Fine.

But that will not help if someone enters "3.O1" (that is the letter O and not zero 0) or removes the space in front of the Euro symbol (tip: remove that from your triming because a trailing space should not harm). That is why I suggested to do the validation before passing to the database. And I did not know that you used a composer when answering.

That is the drawback of such tools:
You are stuck to what they can do.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sascha Manns · Accepted Answer · 2018-04-23T02:08:00

I found it out. That snipped works as expected:

SQL

UPDATE tbl_nebenkosten
    SET Müll = TRIM(TRAILING ' €' FROM '$Muell'),
        Wasser = TRIM(TRAILING ' €' FROM '$Wasser'),
        Versicherung = TRIM(TRAILING ' €' FROM '$Versicherung'),
        Strom = TRIM(TRAILING ' €' FROM '$Strom'),
        Hausverwaltung = TRIM(TRAILING ' €' FROM '$Hausverwaltung'),
        Heizung = TRIM(TRAILING ' €' FROM '$Heizung')
    WHERE MieterID = '${state.MieterID}'

TRIM does a great job :-)