recordsets disappear from menu when adding mysql_query("SET NAMES 'utf8'"); below the connection in php

I hope someone can help with this. I'm trying to create a dynamic site using
dreamweaver CS3, with php5 and mysql5 in the background but I'm having
difficulty with recordsets disappearing from the server behaviour menu when
tweaking the code to allow set characters or set names for utf8.

For a while, I've been having a problem getting my dynamically generated
master list pages (eg lists of jobs) to display international characters like
an accented french e (and other languages) and for these to display correctly
when viewed in phpmyadmin also. So, I've placed headers at the top of the page
for utf8 and created the content metatag for utf8 in the page code. Where
needed I've added ENT_QUOTES utf etc for htmlentities and added accept-charset
utf-8 in the <form> action area.

(one thing I haven't done in the form action area is add
<enctype="multipart/form-data">, but I'm not sure if this will make any
difference)

In the backend, the innoDB database is collated and built using
utf8_unicode_ci. I'm on a shared host -so can't directly access php.ini or
adjust the connection settings through phpmyadmin. But I can write .htaccess
files (currently with the defaultcharset set to utf8 also).

At this point - all list pages display the correct characters when retrieved
from the database - indicating that the headers are working. But - in
phpmyadmin the letters are garbled, and garbled on return on update forms
(content management). I solved this by adding the &#xxxx; equivalents in insert
and update forms to display the text correctly on the list pages - but again
it's not convenient to see all of this in phpmyadmin, as I'd like to be able to
read it there as it should be, and to dispense with the need to add &#xxxx
references on the insert and update forms.

So...a bit of searching, and I read that in order for the international
characters to display in phpmyadmin correctly, I needed to amend my php page
code so that for all instances where I see the connection to the database,
before the SELECT, INSERT and DELETE queries, I need to add either
mysql_set_charset('utf8', $connection) or use three lines of code referencing
mysql_query SET NAMES, SET CHARACTER SET and SET COLLATION_CONNECTION for utf8.

I did that, and it removed all of my recordsets in the sidebar menu! Not too
good. I'm left with a task of rebuilding pages now.

So I placed the mysql_set_charset line in the connection.php file in the DW
Connections folder below the connection there instead. [I didn't precede the
mysql etc line with a $ sign - I wonder if that's the issue?]

With this change I could use the Insert forms to add the international
characters directly into the database and see through phpmyadmin that all was
hunky dory. But then the problems came with first loads of the master list
page.

So, I can get international characters into the database now (which is a step
in the right direction for me), using an insert form on the website, but I
still can't get the international characters to display on the returned page on
first loading eg on a list of jobs. Note that the detail pages display fine
when clicking the master links, and when I return from the detail page back to
the master list - the characters are ok. They just don't seem to display when
the master page loads for the first time which is really perplexing me.

I suspect that I need to add the mysql_set_charset line below the $connection
and before the selects, inserts etc at all instances on the webpage, but this
just removes the recordset from the menu, so this doesn't seem to be a good
option.

Can anyone point me in the right direction as I'm been stuck on this for ages.
It may be something simple like adding $ in front of the mysql_query, but then
again maybe not.

I've tried other forums - but people seem to be content with viewing but not
offering assistance. I don't believe that what I'm doing here hasn't been done
before....all I want is for my pages to display international characters and
for these to display correctly in phpmyadmin so that when I insert, update and
delete records using webforms I don't have to resort to using &#xxx inserts.

Help please!

..oO(08Green)

So...a bit of searching, and I read that in order for the international
characters to display in phpmyadmin correctly, I needed to amend my php page
code so that for all instances where I see the connection to the database,
before the SELECT, INSERT and DELETE queries, I need to add either
mysql_set_charset('utf8', $connection) or use three lines of code referencing
mysql_query SET NAMES, SET CHARACTER SET and SET COLLATION_CONNECTION for utf8.

Usually you only need to send a SET NAMES 'utf8' right after the
connection to the DB has been established. That way all data that is
transferred between the DB and your script will be handled as UTF-8.

I did that, and it removed all of my recordsets in the sidebar menu! Not too
good. I'm left with a task of rebuilding pages now.

So I placed the mysql_set_charset line in the connection.php file in the DW
Connections folder below the connection there instead. [I didn't precede the
mysql etc line with a $ sign - I wonder if that's the issue?]

I've never used mysql_set_charset().

Here in my applications it always works like this:

* the default charset in my InnoDB tables is ISO-8859-1, I only use
UTF-8 in the columns that really need it (text data, but not INT or
DATE columns)
* SET NAMES 'utf8' at the beginning
* all files (HTML and PHP) are encoded as UTF-8 without BOM
* all pages delivered as UTF-8 (my pages are script-generated, so I use
an appropriate header() call to define the content-type and encoding)

That's it. Oh, and I don't use phpMyAdmin ...

With this change I could use the Insert forms to add the international
characters directly into the database and see through phpmyadmin that all was
hunky dory. But then the problems came with first loads of the master list
page.

In the first part of your posting it sounded as if it worked almost
correctly, before you tried to fix the broken phpMyAdmin appearance.
Is that correct?

So, I can get international characters into the database now (which is a step
in the right direction for me), using an insert form on the website, but I
still can't get the international characters to display on the returned page on
first loading eg on a list of jobs.

They did display correctly first, didn't they? So it seems that you
broke your script just in order to fix some PMA problem. Revert that.
Make your script work for normal input/output from/to a website first
and don't worry about PMA for now.

What do you need PMA for BTW? It may have many other issues as well and
should only be used if really necessary or if you're not familiar with
the command line interface. The only thing that we are using it is to
get a dump from a remote DB, where I don't have shell access to call
mysqldump directly (yes, I could also write a little PHP script for
that, it's already somewhere on my TODO). But here on my local system I
always use the MySQL command prompt if I have to directly work on some
tables or to import the dump from remote.

Anyway, if you need PMA, your might try their group or forum. I'm sure
there's an FAQ as well. Maybe it's just a configuration issue.

I've tried other forums - but people seem to be content with viewing but not
offering assistance. I don't believe that what I'm doing here hasn't been done
before....all I want is for my pages to display international characters and
for these to display correctly in phpmyadmin so that when I insert, update and
delete records using webforms I don't have to resort to using &#xxx inserts.

The bug here is PMA itself. ;-)

Micha

Thanks Micha,
I need PMA as I'm on a shared host, so it's the only way I can query the
database. I don't think PMA is the cause of the error (it's XAMPP) as I can
get info into the database in the way I want it, but not on output (once set
names is used). The output doesn't display correctly only on first loading of
the page.....returning back from a detail page makes the characters display
properly...so I think it must be to do with the way the page connects on first
loading.

Before I used SET NAMES, input into the database was originally through
webforms (CMS) and using &#xxx entries for international characters. The web
pages were displaying fine with all characters as they should be. Only the
database had &#xxxx entries making reading it tricky.

Once and only once I started using SET NAMES (and I placed this below the
connection in the dreamweaver connection.php file rather than on the php
webpage), did the characters appear in the database correctly. I could then
used insert forms and update/delete forms with the characters rather than using
&#xxx entries. On the face of it this allowed the data to be correctly entered
into the database and viewed correctly in PMA and recalled in the right format
for all CMS pages (updates, deletes) and detail pages....

....BUT not (for some reason) the master list pages like a list of jobs on
first loading. As mentioned it's only a problem on first load - clicking from
the list of jobs to the detail about one, and then back again makes the
characters appear, but clearly it's not too good to have garbled letters on
screen in the first instance.

I've a feeling that I may be placing SET NAMES in the wrong place. Is the
connection file in Dreamweaver the best place for it? Perhaps someone could
post an example of how it should be placed in code. You mention it should be
placed after the connection but is this directly after (next line) before the
SELECT, INSERT query, or at the end of that particular run of script (after the
recordset script perhaps).

Still floundering here I'm afraid, and thanks for giving it a go.

Solved it. The issue is to do with previewing a TMP file/page, rather than a
web page accessed through links off the nav bar.

In short clearing cache and previewing eg a jobs page with international
characters in it on first load will present garbled characters. This is because
the page is a TMP file. Simply renaming it in the address bar to its proper
file name will present the characters as they should be.

This can be double checked by loading up the home page and accessing that page
through the nav bar - again the characters display fine, even on first use.

So if you have the same problem i had now you know...it's a simple fix, but
unless you know, it'll save a few hair pulling moments.