mysqli binding with blank spaces

Question

I have an website, wich shows information through an SQL Query. This is working just fine, except when one of the parameters is a string with blank spaces.

echo $seminar = $_POST['Seminar'];
echo $start = $_POST['DatumGruppe'];

$prep_stmt = $mysqli->prepare("SELECT `idtermine` FROM `termine`, seminar WHERE termine.seminar = seminar.idseminar AND seminar.name = ? AND termine.start= ? ");
$prep_stmt->bind_param('ss', $seminar, $start);
if (!$prep_stmt->execute())
{
    echo 'Error executing!';
}

else
{
$prep_stmt->store_result();
$prep_stmt->bind_result($termin);

    if ($prep_stmt->num_rows != 1)
    {
    echo '<p class="error">Kein passendes Seminar gefunden</p>';
    }

    while ($prep_stmt->fetch())
    {
        echo 'ID: '.$termin.'<br>';
    }
}

Like i said: when $seminar is something like "Entspannung" its perfectly working. But "Metakognitives Training - Umgang mit Grübeln und Sorgen" isn't working - theres no mistake in the query, it just doesn't find a fitting row. There are no mistakes in the writing, and the same query is working on another website. So I really don't get the problem.

I tried using quotes

echo $seminar = "'".$_POST['Seminar']."'";

which didn't helped eather. I hope you can help.

The space should not be an issue. More likely the "ü" is messing up due to encoding mis-handling. — deceze, Oct 10 '17 at 12:46
Need to add `utf-8` support in query due to special character `ü` in string!! — Saty, Oct 10 '17 at 12:47
I think the problem lies not in the use of blank spaces, but in the use of the Umlaute. Try "_...Gruebeln..._". Or just try something which is not saved with umlaute. Then you see further, e.g. convert db and tables and fields to the `utf8_general_ci` charset. And the php.ini settings should be set for UTF-8. — , Oct 10 '17 at 12:49
Are both servers using the same DB? Have you output the query on both servers and confirmed it is actually the same (while viewing the source, not the browser rendered)? e.g. if querying with `&252;` and DB has `ü` you won't get results. Also don't quote, the driver does that, the extra quotes will result in a non-match. — chris85, Oct 10 '17 at 12:51
@Saty using $mysqli->set_charset("utf8") doesn't change anything — Joseliese, Oct 10 '17 at 13:00
@chris85 there's only one DB, so yes. But what do you mean by "output the query"? — Joseliese, Oct 10 '17 at 13:06
`echo "SELECT idtermin FROM termine, seminar WHERE termine.seminar = seminar.idseminar AND seminar.name = ? AND termine.start= ? ";` and output the bindings, `var_dump($seminar, $start)`. — chris85, Oct 10 '17 at 13:08
@Joseliese Question 1: If you save a string with spaces, but WITHOUT Umaute, in the db and then try to fetch the value, is the value correctly fetched? — , Oct 10 '17 at 13:33
@Joseliese Question 3: If the two websites are using the same db and same web server, then a good not-working-reason is that you are using a virtual host for each website. Therefore, each one has its own settings package. Could it be this? — , Oct 10 '17 at 13:34
@aendeerei 1) no it isn't. still not giving the correct result. 2) The site is hosted on strato, can't describe which webserver in detail 3) both sides are using the same domain. So no, no different setting packages. — Joseliese, Oct 10 '17 at 13:41
@Joseliese Ok... Please indulge me: Create a new php page (UTF-8) and copy the whole code to it. Then try again. — , Oct 10 '17 at 13:48
@Joseliese Oh, and remove the `echo` from `echo $seminar = ...` and `echo $start = ...`! Then try again. — , Oct 10 '17 at 13:52
@Joseliese Replace double quotes (") with single qoutes (') around the sql statement. Then try again. Trust me, this can be the reason, too. I had encounter this situation and I even made more tests: this little thing had a great negative effect regarding correctly fetching. — , Oct 10 '17 at 13:56

score 0 · Answer 1 · 2017-10-10T13:07:30.367

0

As said, the Umlaute are the problem. In order to be able to use them, set:

php.ini:

default_charset = "UTF-8"

Web server's cofig file (here setting for Apache's httpd.conf file):

AddDefaultCharset "UTF-8"

my.ini (or my.cnf):

See this answer

Database:

Encoding: utf8
Collation: utf8_general_ci

Each table:

Encoding: UTF-8 Unicode (utf8)
Collation: utf8_general_ci

Each field (varchar, char, text, etc):

Encoding: UTF-8 Unicode (utf8)
Collation: utf8_general_ci

Theoretically, the fields inherit the encoding and the collation from the table.

Also, see this answer for some other instructions and modes of changing the corresponding settings.

Good luck!

edited Oct 10 '17 at 13:07

answered Oct 10 '17 at 13:02

How is this even possible? the side is located on the same Webspace as the side which doesn't have any problems with the "Umlaute". They are using the same DB, which is already encoded in UTF-8 – Joseliese Oct 10 '17 at 13:09
1

Practically you don't need to do all this. The only things which are important are: 1. Is the data in the database *correct*, i.e. is it actually storing "ü" and not some other garbage? The column encoding doesn't even matter. 2. Are you receiving an "ü" from the web browser, and not some other garbage? 3. Are you sending an "ü" to the database, i.e. the correct bytes for the character "ü" in the connection encoding you declared when connecting to the database? – deceze Oct 10 '17 at 13:13
I just replaced "ü" with ue, the problem still exists.... – Joseliese Oct 10 '17 at 13:16
@Joseliese Then other parts of that string aren't matching… – deceze Oct 10 '17 at 13:17
@deceze, the data is correct, I am receiving "ü" and I am sending "ü" to the database. checked all that – Joseliese Oct 10 '17 at 13:18
Thanks for the comment, @deceze. Your advice is 100% correct. In my answer I added the settings which always ensure me no problems whatsoever with the use of Umlaute in my mysql dbs. The steps you are recommended I followed a long time ago. After a lot of researches on that time, I found my permanent solution, mirrored in my answer. – Oct 10 '17 at 13:20
@deceze: After checking all different kinds of possible mistakes, the only one seems to be the blank spaces. I checked the query in phpmyadmin, it is working. And it is working on another website on the same server. I'm quite clueless right now :/ – Joseliese Oct 10 '17 at 13:21

mysqli binding with blank spaces

1 Answers1