Josee

First, your database, or at least the column where you store "Carré" must use utf8 and the appropriate collation (probably utf8_general_ci). If you change the character set after populating the database, you must convert the existing data to utf8: ALTER TABLE tablename CONVERT TO CHARACTER SET utf8; Of course, you must apply the conversion only to one column if only one column in the database should use utf8. Second, you must add to your database connexion file one of these two lines: mysqli_set_charset($dbc, 'utf8'); OR mysqli_query($dbc, 'SET NAMES utf8'); I use the first, in my PHP connexion file, just after the connexion itself. This makes sure PHP uses utf8 too to send data to the db. From the command-line, you must use CHARSET UTF8; immediately after choosing the database if you want the diacritics to display correctly.

The mysqli_real_escape_string() function is used for an INSERT or an UPDATE — or similar queries that involve inserting data into the database — so as to avoid problematic characters. You don't need it to retrieve data with a SELECT query. Were you thinking of prepared statements that avoid the use of the mysqli_real_escape_string() function? (That's covered in chapter 12, "Preventing SQL Injection Attacks".)

I'm glad it's working for you. And of course I didn't invent this; I just adapted it from one of the 'recipes' in the O'Reilly CSS Cookbook.

I'm not familiar with "KEY" on its own, and I haven't used the E-commerce book so far. So I'll let someone else answer!

The first occurrence is just the index name. You could write UNIQUE KEY some_other_name (category) if it's clearer for you. I've often seen index names such as category_unq or user_id_pk or some other other suffix that makes it immediately clear what kind of index it is.

I don't think you can embed a <span> inside another one. But you could use <strong class="something" (or id="something" if that makes more sense)>, or <em class="something">, so as to redefine them in your css file (or just use the hierarchy to isolate these <strong> or <em> elements). It would make sense, semantically, since you want to emphasize these messages.

For joins and so on, I find another Peachpit Press Visual Quickstart Guide very useful: it's SQL by Chris Fehily. I've got the third edition but have no idea if that's the most recent. I very much like Larry's MySQL examples in the three books you mention because they are real-life examples, they show how to use PHP and MySQL together, and they explain the logic for choosing this or that solution; so I go back to these regularly to try and learn more from them. But I also use the SQL Quickstart Guide when I want to check the different possibilities offered by MySQL. It's usually my first step before going to the MySQL manual online. It's not the kind of book you read from beginning to end in an orderly fashion; at least, I don't! It's rather a simplified version of the manual that concentrates on the queries that are used most often, with quite a lot of examples that get gradually more complex. I usually start with quite a precise idea of what I want to obtain and use the guide to check for the different ways of achieving this result. I've always found it very helpful for joins, grouping or filtering data.

I spent quite a lot of time trying to do things with mb_convert_encoding() and other functions, also considering Paul's suggestion of converting to HTML entities (but I had no idea how complex it would be starting from multibyte strings) and various other possibilities that all seemed bound to fail if I also wanted/needed to use functions like stripslashes() or trim()… and you know what works? Using exactly the same variable/text in both columns in my database, one with a UTF8 character set and the corresponding collation, the other with a latin1 character set and the corresponding collation. (So my INSERT query is something like: INSERT INTO flexions (flexion, conversion) VALUES ($string, $string); no other work needed.) These two columns are called "flexion" and "conversion", and here are the results, depending on which of the two columns I use for the WHERE condition: // SELECTion based on a latin1 column (conversion) SELECT flexion FROM flexions WHERE conversion LIKE 'aima%' ORDER BY flexion Rows: 13 flexion aima aimai aimaient aimais aimais aimait aimant aimas aimasse aimassent aimasses aimassiez aimassions // SELECTion based on a UTF8 column (flexion) SELECT flexion FROM flexions WHERE flexion LIKE 'aima%' ORDER BY flexion; Rows: 16 flexion aima aimai aimaient aimais aimais aimait aimâmes aimant aimas aimasse aimassent aimasses aimassiez aimassions aimât aimâtes So I can have the best of both encoding worlds with no headaches!

Thank you for the suggestion, Paul. Since my last post, I've been thinking that I could create a new column in the database with the same text as in the Unicode column, and just a different character set (latin1) and collation (latin1_general_ci), so that I could benefit both from utf8 and from latin1. There's a multibyte function called mb_convert_encoding() that seems promising. But if it doesn't work, I'll certainly try htmlentities() as you suggest.

Thanks, HartleySan. Doing part of the work in PHP could be a solution sometimes, but I think that for my project there are numerous cases where it wouldn't be very helpful. What makes me pessimistic about existing solutions is that Google, for instance, is quite bad for searches in French (or German, or any language using diacritics [accents, and so forth], I suppose). For instance, "élève" is a noun and means a student in French, and "élevé" is a past participle and means "raised up", or "high up", and many other things, but it's just not the same word as "élève". Google is unable (or unwilling) to differentiate between the two. So Google searches usually give thousands of answers where there could be only a few dozens if real spelling was taken into account. If it's midnight for you in Japan, it was tea time for me in Europe, and I've been thinking of another solution while drinking my tea: for all tables I have to rely on for searches, I can add a column with a numeric equivalent of the words; fortunately, as far as I can see right now all these tables would have single words in the columns I'm interested in. So creating a php script that automatically creates numerical equivalents of the words before I enter both forms into the database should be rather easy. This way, I could keep the convenience of Unicode for alphabetical sorting, and be able to retrieve the spellings I want right from the database. I'll play around with this possibility in the next few days!

Thank you, HartleySan. I had seen this page indeed… which is what sent me to the one I quoted, that only confirmed what I understood from the page you refer to. It's really, really annoying. What's the use of being able to store the data in whatever language you choose if you can't retrieve it in a way that's meaningful in that language? That's a rhetorical question, of course, since none of us can change a thing about it. I think I'll have to change the default character set and collation for this project, which also means having a separate configuration file with a separate connexion file, separate functions, separate templates since the html files won't be using the same encoding… Really despairing!

Thanks, HartleySan. I've at last found the explanation (at least I think so), and it's discouraging: http://dev.mysql.com/doc/refman/5.0/en/charset-collation-effect.html If I understand rightly (and I'll test to confirm) I would be better off with some latin1_xxx character set than with Unicode, which really seems odd to me since I thought the aim of Unicode was to take into account the peculiarities of each language. I don't (much) care about sorting, but I really care about distinguishing between words such as "a" and "à", which are not at all the same in French (the first is a verb, the second is a preposition).

Hello, I've already spent quite a long time trying to find the right syntax for a very simple SELECT query, but to no avail. I've also been reading chapter 9, on Globalization, from the MySQL 5.1 Reference Manual, but I still don't understand what's happening (or, rather, I don't want to believe that most MySQL functions are not multi-byte safe!). The three SELECT queries I've tried are the following: SELECT flexion FROM flexions WHERE flexion LIKE 'aimer%'; SELECT flexion FROM flexions WHERE flexion LIKE 'aimer%' COLLATE utf8_swedish_ci; SELECT flexion FROM flexions WHERE MATCH (flexion) AGAINST ('aimer*' IN BOOLEAN MODE); Whatever the query, the results always include "aimèrent"… which they shouldn't! Does anyone have any idea of how to force MySQL to distinguish between 'e' and 'è'? Functions like SUBSTRING() explicitly state that they are multi-byte safe. I've read no such thing in the manual about LIKE or about MATCH. Are there other functions I could use instead? With thanks for your help,

I had the same problems with the command-line to start with, and as HartleySan suggests, using phpMyAdmin can be a more friendly way of getting familiar with SQL. Another way round is to prepare your command in a text editor, and once you are sure you haven't made any mistakes, just paste it in Terminal or another client, and hit return. I've got used to the command-line but I still use a text editor first when I want to use Terminal for a long command, as correcting typos can really be hard work!

I'm not sure I understood exactly what you want to do, but if you want for instance to give a different background color to the link corresponding to the current page, here is one way of doing it. It's less advanced and less dynamic than what Jonathon and Stuart propose, but it works too. — The opening <body> tag of the html pages is in my header.html file. I added an "id" attribute to the <body> tag, represented by a variable. <body id="<?php echo $body_id ?>"> — On each separate page, I give a different value to the $body_id variable. For instance: $body_id = "index_page"; $body_id = "login_page"; — In the navigation menu, each link also has a unique "id" attribute: <a href="/index.php" id="index_link">Home</a> <a href="/login.php" id="login_link">Log In</a> — Then, in my css file, I give different background colors to the links according to their status. • Ordinary links: #navigation li a, #navigation li a:visited { background: #dcdcdc; color: #10190e;} #navigation li a:hover, #navigation li a:focus, #navigation li a:active { background: #a7ccf7; color: #10190e; } • Link for the current page: body#index_page a#index_link, body#login_page a#login_link { background: #627dc6; color: #ffffff; } Which means: the <a> tag with an "index_link" id attribute on the page where <body> has an "index_page" id attribute and the <a> tag with a "login_link" id attribute on the page where <body> has a "login_page" id attribute have a blue background (instead of grey for ordinary links). I hope this helps,

I edited my post since Jonathon had answered meanwhile.

I'm really no expert with regular expressions, but if you are using UTF-8, you should have a look at the multibyte functions, such as mb_ereg, mb_ereg_match and so on. Also, if you are using UTF-8, you should be aware that none of the most common functions such as substr() will work on a French word using Unicode. You must use their multibyte equivalents. And you must go UTF-8 all along the line. What I mean is your html files must be in UTF-8, your database (or at least the tables where you have French) must be in UTF-8, and so on.

That's cheating, Larry! Thanks very much to both of you. I think this trick will help me many times during this new project.

Hello, When I use an INSERT query inside a loop, like this: while ($genres_entree = mysqli_fetch_array($result2, MYSQLI_NUM)) { $query5 = "INSERT INTO referents_genders (id_referent, id_gramm_gender) VALUES ($id_referent, $genres_entree[0])"; $result5 = @mysqli_query($bdd, $query5); } what PHP function should I use to know the total number of rows affected by $query5? If I use mysqli_affected_rows(), the result is always 1, since the counter is apparently set back to 0 at the end of every loop. So just now I'm getting the total number of affected rows with a SELECT query: $query6 = "SELECT id_referent FROM referents_genders WHERE id_referent=$id_referent"; $result6 = @mysqli_query($bdd, $query6); $nb_genres_referent = mysqli_num_rows($result6); but I have a feeling I could be much more efficient! With thanks for your help,

Yes. I base my configuration file on Larry's explanations in chapter 16 from PHP 6 and MySQL 5 for Dynamic Web Sites, with a "LIVE" constant based on server information and constants for the different base-URLs and base-URIs. That makes things very simple. You just have to think once about the different paths on your own computer and on the web server, and when you want to re-use files in another project, very few changes are needed for paths to work whatever the project.

In case this can be of any help (I used to make this mistake): remember that every time you collect several answers, from checkboxes or from a multiple-select dropdown menu, for instance, what you (should) get is an array. So you must use a foreach loop (not an "if" conditional) to get the results, and to check the boxes if you want to make them sticky.

Hello, You must have taken that line from page 167 rather than 169, and it goes with the previous line: foreach ($books as $key => $value) { print "<p>$key: $value</p>\n"; } It means more or less: for each element from the $books array, the key will be represented by the $key variable (you could name it $k or $x if you prefer), and the value will be represented by the $value variable (you could name it $v or $y if you prefer). Since the content of $value is itself an array, this will just print: If you want to print the content of the $books array and of each of the three arrays it contains (i.e. the title of each book, and below the title of each chapter), use the script on page 169: foreach ($books as $title => $chapters) { print "<p>$title"; foreach ($chapters as $number => $chapter) { print "<br />Chapter $number is $chapter"; } print '</p>'; } You see that here altogether different names are used for the variables. You could write just as well: foreach ($books as $key => $value) { print "<p>$key"; foreach ($value as $k => $v) { print "<br />Chapter $k is $v"; } print '</p>'; } The result would be exactly the same. If you want more examples, have a look at the "foreach" page in the PHP manual: http://www.php.net/manual/en/control-structures.foreach.php I hope this helps,

Thank you very much for your answer, Larry. This column is for literary quotations, and I limited the UNIQUE index to the first 80 characters (out of a maximum of 1000 characters). Is that still very inefficient? Would it be better to use PHP to check that I haven't already entered this quotation in the database? With thanks for your help,

Thank you for your research and the link, HartleySan. I read the corresponding pages but haven't got round to testing it in my script yet.

Hello Larry, On page 180 you write: Am I right in thinking this is not always the case, and that you can for instance both have a FULLTEXT index and a UNIQUE index on the same column? I looked at the 5.1 MySQL manual ("create-index" page and "How MySQL Uses Indexes" page) and didn't find anything on this subject, so I probably didn't look in the right place! But I tried creating a UNIQUE index on a VARCHAR(1000) column that already had a FULLTEXT index, and I got no error message. mysql> SHOW INDEX FROM citations +-----------+--------+-----------+---------+----------+-----------+-----------+------+--------+------+----------+ | Table | Non_ | Key_name | Seq_in_ | Column_ | Collation | Cardinal- | Sub_ | Packed | Null | Index_ | | | unique | | index | name | | ity | part | | | type | +-----------+--------+-----------+---------+----------+-----------+-----------+------+--------+------+----------+ | citations | 0 | PRIMARY | 1 | id_ | A | 16 | NULL | NULL | | BTREE | | | | | | citation | | | | | | | | citations | 0 | citation_ | 1 | citation | A | 16 | 80 | NULL | | BTREE | | | | unique | | | | | | | | | | citations | 1 | citation_ | 1 | citation | NULL | NULL | NULL | NULL | | FULLTEXT | | | | fulltext | | | | | | | | | +-----------+--------+-----------+---------+----------+-----------+-----------+------+--------+------+----------+ 3 rows in set (0,00 sec) Can I keep it this way, or shall I have problems with this table if I keep both indices? With thanks for your help,