Mysql table looks like this:  The actual contents are like this:  I tried these:  in model.php  if (mysqli_num_rows($result) &gt; 0) {         //iterate         while($row = mysqli_fetch_assoc($result)){             //get username             $disease = mb_convert_encoding($row['disease'], &quot;UTF-8&quot;, &quot;auto&quot;);             $food = mb_convert_encoding($row['food'], &quot;UTF-8&quot;, &quot;auto&quot;);             $en_name = mb_convert_encoding($row['en_name'], &quot;UTF-8&quot;, &quot;auto&quot;);             $health_effect = mb_convert_encoding($row['healthEffect'], &quot;UTF-8&quot;, &quot;auto&quot;);             $metabollite = mb_convert_encoding($row['metabollite'], &quot;UTF-8&quot;, &quot;auto&quot;);             //$citation = $row['citation'];             $next_row = array(&quot;Disease&quot;=&gt;$disease, &quot;Food&quot;=&gt;$food,                 &quot;Name&quot;=&gt;$en_name, &quot;Health Benefits&quot;=&gt;$health_effect, &quot;Metabollite&quot;=&gt;$metabollite);                 //&quot;Sources&quot;=&gt;$citation);             $matches_arr[] = $next_row;         }     }     $utfArr = array_map(&quot;utf8_encode&quot;, $matches_arr);//this does not work for associate arrays I think  back in controller.php I tried again  //encode in UTF8  foreach($matches_arr as $col =&gt; $val){  $utf8_val = mb_convert_encoding($val, &quot;UTF-8&quot;, &quot;auto&quot;);  $matches_arr[$col] = $utf8_val;  } //send it back to view header('Content-Type: application/json'); $disease_json = json_encode($matches_arr, JSON_UNESCAPED_UNICODE); echo $disease_json;  but it outputs question marks for table columns in Japanese:  I saw this:  But I think my case is different  because mysql database is already encoded in utf8mb4_bin and not an obscure Japanese encoding. Anyways from that answer I tried putting this tag at the top of controller.php and model.php &lt;meta http-equiv=&quot;Content-Type&quot; content=&quot;text/html; charset=utf-8&quot; /&gt;&quot;   and check that my view.php has the charset:     &lt;meta charset=&quot;utf-8&quot;&gt;     &lt;meta name=&quot;viewport&quot; content=&quot;width=device-width, initial-scale=1, shrink-to-fit=no&quot;&gt;     &lt;meta name=&quot;description&quot; content=&quot;&quot;&gt;     &lt;meta name=&quot;author&quot; content=&quot;Mark Otto, Jacob Thornton, and Bootstrap contributors&quot;&gt;     &lt;meta name=&quot;generator&quot; content=&quot;Jekyll v4.1.1&quot;&gt;     &lt;title&gt;Dashboard Bioactive Compounds&lt;/title&gt;     &lt;!-- boostrap comes first --&gt;     &lt;link rel=&quot;stylesheet&quot; href=&quot;css/bootstrap.css&quot;&gt;     &lt;meta charset=&quot;utf-8&quot;&gt;  Actually it had it twice. I saw that the query result in model.php is already just question marks in phpstorm debugger console when I inspect the query result variable. How can I fix this to show Japanese on the view page?
326
I'm setting up a new server and want to support UTF-8 fully in my web application. I have tried this in the past on existing servers and always seem to end up having to fall back to ISO-8859-1.  Where exactly do I need to set the encoding/charsets? I'm aware that I need to configure Apache, MySQL, and PHP to do this — is there some standard checklist I can follow, or perhaps troubleshoot where the mismatches occur?  This is for a new Linux server, running MySQL 5, PHP, 5 and Apache 2.
72846
I have fname and lname in my database, and a name could be stored as JOHN DOE or john DOE or JoHN dOE, but ultimately I want to display it as John Doe  fname being John and lname being Doe
29604
I want to pipe the output of a "template" file into MySQL, the file having variables like ${dbName} interspersed. What is the command line utility to replace these instances and dump the output to standard output?
120116
Problem:  Is there any way of calculating number of bytes occupied by the table creation, i know that you can get some information from information_schema.tables but that information is not accurate enough.             What actually required is the number of bytes according to the definition of the table for innodb only and collation could also be considered as utf-8-general-ci  For example a table test is as following     create table test   (   col1 varchar(25),   col2 int,   col3 varchar(3),   col4 char(15),   col5 datetime   );     Now would require to know the total row size that can be accumulated in one row according to the types of columns in the table.  Found a some sort of similar solution in MSSQL but need its MySQL version    Any help is much appreciated.
67515
Ok, here is my dilemma I have a database set up with about 5 tables all with the exact same data structure. The data is separated in this manner for localization purposes and to split up a total of about 4.5 million records.  A majority of the time only one table is needed and all is well. However, sometimes data is needed from 2 or more of the tables and it needs to be sorted by a user defined column. This is where I am having problems.  data columns:  id, band_name, song_name, album_name, genre   MySQL statment:  SELECT * from us_music, de_music where `genre` = 'punk'   MySQL spits out this error:  #1052 - Column 'genre' in where clause is ambiguous   Obviously, I am doing this wrong. Anyone care to shed some light on this for me?
115935
I have a form with a textarea. Users enter a block of text which is stored in a database.  Occasionally a user will paste text from Word containing smart quotes or emdashes. Those characters appear in the database as: â€“, â€™, â€œ ,â€  What function should I call on the input string to convert smart quotes to regular quotes and emdashes to regular dashes?   I am working in PHP.  Update: Thanks for all of the great responses so far. The page on Joel's site about encodings is very informative:   Some notes on my environment:  The MySQL database is using UTF-8 encoding. Likewise, the HTML pages that display the content are using UTF-8 (Update:) by explicitly setting the meta content-type.  On those pages the smart quotes and emdashes appear as a diamond with question mark.  Solution:  Thanks again for the responses. The solution was twofold:   Make sure the database and HTML files were explicitly set to use UTF-8 encoding. Use htmlspecialchars() instead of htmlentities().
60461
What is a fast way to select a random row from a large mysql table?  I'm working in php, but I'm interested in any solution even if it's in another language.
23902
In MySql's interpreter, it's very easy to dump a table to the screen along with its field names.   There seems to be no simple way to export a table to a tab-delimted or CSV outfile including its column headers.  I'm trying to do this using only SQL or the Linux command line, without writing a program in another language.  Thank you
2806
My schema 'test' has 3 tables: 'user', 'post' and 'revision'.  CREATE TABLE IF NOT EXISTS `test`.`user` (   `iduser` INT UNSIGNED NOT NULL AUTO_INCREMENT,   `name` VARCHAR(45) NOT NULL,    PRIMARY KEY (`iduser`)) ENGINE = InnoDB;  CREATE TABLE IF NOT EXISTS `test`.`post` (   `idpost` INT UNSIGNED NOT NULL AUTO_INCREMENT,   `iduser` INT UNSIGNED NOT NULL,   `content` VARCHAR(4000) NOT NULL,   `revision_count` INT NOT NULL DEFAULT 0,    PRIMARY KEY (`idpost`),    INDEX `fk_post_user1_idx` (`iduser` ASC),    CONSTRAINT `fk_post_user1`      FOREIGN KEY (`iduser`)      REFERENCES `test`.`user` (`iduser`)      ON DELETE NO ACTION      ON UPDATE NO ACTION) ENGINE = InnoDB;  CREATE TABLE IF NOT EXISTS `test`.`revision` (   `idrevision` INT UNSIGNED NOT NULL,   `iduser` INT UNSIGNED NOT NULL,   `idpost` INT UNSIGNED NOT NULL,   `content` VARCHAR(4000) NOT NULL,    UNIQUE INDEX `index1` (`idpost` ASC, `idrevision` ASC),    PRIMARY KEY (`idrevision`, `iduser`),    INDEX `fk_revision_user1_idx` (`iduser` ASC),    CONSTRAINT `fk_revision_post`      FOREIGN KEY (`idpost`)      REFERENCES `test`.`post` (`idpost`)      ON DELETE NO ACTION      ON UPDATE NO ACTION,    CONSTRAINT `fk_revision_user1`      FOREIGN KEY (`iduser`)      REFERENCES `test`.`user` (`iduser`)      ON DELETE NO ACTION      ON UPDATE NO ACTION) ENGINE = InnoDB;   And there is the script creates data:  INSERT INTO `test`.`user` (`iduser`, `name`) VALUES (1, 'Ann'); INSERT INTO `test`.`user` (`iduser`, `name`) VALUES (2, 'Bob');  INSERT INTO `test`.`post` (`idpost`, `iduser`, `content`, `revision_count`) VALUES (1, 1, 'this is first post', 1);  INSERT INTO `test`.`revision` (`idrevision`, `iduser`, `idpost`, `content`) VALUES (1, 1, 1, 'this is old content of first post');   I create 2 sessions work concurrently. Session 1 works with query:  BEGIN; INSERT INTO revision (idrevision, idpost, iduser, content) VALUES     ((SELECT max_idrevision + 1      FROM (SELECT max(idrevision) AS max_idrevision           FROM revision WHERE idpost = 1) AS anon_1),     1, 2, 'abc xyz');   Now session 2 works:   BEGIN; UPDATE revision SET idrevision=3 WHERE idpost=1 AND iduser =1; -- will hang by lock  -- but with this update UPDATE revision SET content='nwe asdfs' WHERE idpost=1 AND iduser=1; -- not hang   I don't know what's lock mysql used with query insert in session 1? Is it row lock or table lock or anything else ? If it's row lock, why does second update query in session 2 not hang ?
66743
I would like to have a table that looks like the image below.  \documentclass{article} \begin{document}     \begin{table*}         \begin{tabular}{|l|l|l|l|l|l|l|} \hline              &amp;\multicolumn{3}{c|}{A} &amp;\multicolumn{3}{c|}{B} \\ \cline{2-7}            1 &amp; a &amp; b &amp; c &amp; d &amp; e &amp; f \\ \hline           2 &amp; g &amp; h &amp; i &amp; j &amp; k &amp; l \\ \hline          \end{tabular}     \end{table*} \end{document}   However, I cannot obtain the table with an entire empty row, like the cold cache, written in its center. Is there some way to do so?    I also want the caption below the table.
1770
Consider a database table holding names, with three rows:  Peter Paul Mary   Is there an easy way to turn this into a single string of Peter, Paul, Mary?
97492
I have a MySQL database table with nearly 23 million records. This table has no primary key, because nothing is unique. It has 2 columns, both are indexed. Below is its structure:    Below is some of its data:    Now, I ran a simple query:  SELECT `indexVal` FROM `key_word` WHERE `hashed_word`='001'   Unfortunately, this took more than 5 seconds to retrieve the data and show them to me. My future table will have 150 billion records, so this time is very very high.  I ran the Explain command to see what's going on. Result is below.    Then I ran the Profile using below command.  SET profiling=1; SELECT `indexVal` FROM `key_word` WHERE `hashed_word` = '001'; SHOW profile;   Below is the result of the profiling:    Below is some more information about my table:    So, why this is taking so long? They are indexed too! In future, I have to run lot of LIKE commands, so this is taking too much time. What has gone wrong?
55771
Anyone know how to combine PHP prepared statements with LIKE? i.e.   "SELECT * FROM table WHERE name LIKE %?%";
3156
Here are the args for my query :  $args = array(     'post_type' =&gt; 'news',     'meta_query' =&gt; array(         array(             'key' =&gt; 'topics',             'value' =&gt; 'sports',         )     ) );   This works when topics is a string, but not when it is an array. I'd like this query to work when topics is for example array( 'sports', 'nonprofit', etc. )  Is there a way to build meta queries with arrays as a meta_key ?
80030
I'm working on a project in which I'm creating a custom post type and custom data entered via meta boxes associated with my custom post type. For whatever reason I decided to code the meta boxes in such a way that the inputs in each metabox are part of an array. For instance, I'm storing longitude and latitude:  &lt;p&gt;      &lt;label for="latitude"&gt;Latitude:&lt;/label&gt;&lt;br /&gt;      &lt;input type="text" id="latitude" name="coordinates[latitude]" class="full-width" value="" /&gt;  &lt;/p&gt;  &lt;p&gt;          &lt;label for="longitude"&gt;Longitude:&lt;/label&gt;&lt;br /&gt;      &lt;input type="text" id="longitude" name="coordinates[longitude]" class="full-width" value="" /&gt;  &lt;/p&gt;   For whatever reason, I liked the idea of having a singular postmeta entry for each metabox. On the save_post hook, I save the data like so:  update_post_meta($post_id, '_coordinates', $_POST['coordinates']);   I did this because I have three metaboxes and I like just having 3 postmeta values for each post; however, I've now realized a potential issue with this. I may want to use WP_Query to only pull out certain posts based these meta values. For instance, I may want to get all posts that have latitude values above 50. If I had this data in the database individually, perhaps using the key latitude, I would do something like:  $args = array(     'post_type' =&gt; 'my-post-type',     'meta_query' =&gt; array(         array(             'key' =&gt; 'latitude',             'value' =&gt; '50',             'compare' =&gt; '&gt;'         )     )  ); $query = new WP_Query( $args );   Since I have the latitude as part of the _coordinates postmeta, this would not work.  So, my question is, is there a way to utilize meta_query to query a serialized array like I have in this scenario?
97745
I have a database setup with the following schema:   CREATE TABLE siteInfo (     siteID             SERIAL  NOT NULL,     siteName           TEXT    NOT NULL,     siteHost           TEXT    NOT NULL,     notes              TEXT    NOT NULL,     allowableCountries TEXT    NOT NULL,     displayChance      INT     NOT NULL,     imagePath          TEXT    NOT NULL,     PRIMARY KEY (siteID) );  CREATE TABLE siteLinks (     siteID             SERIAL  NOT NULL,     URL                TEXT    NOT NULL,     FOREIGN KEY (siteID) REFERENCES siteInfo(siteID) ON DELETE CASCADE );  CREATE TABLE siteDemographicLinks (     siteID             SERIAL  NOT NULL,     URL                TEXT    NOT NULL,     country            TEXT    NOT NULL,     chance             INT     NOT NULL,     FOREIGN KEY (siteID) REFERENCES siteInfo(siteID) ON DELETE CASCADE );  CREATE TABLE linkVisits (     siteID             SERIAL  NOT NULL,     rawVisit           BIGINT  NOT NULL,     redirects          BIGINT  NOT NULL,     FOREIGN KEY (siteID) REFERENCES siteInfo(siteID) ON DELETE CASCADE );   Data from my form is in the following format:  array(7) { ["siteName"]=&gt;string(9) "Site Name" ["siteHost"]=&gt;string(9) "SIte Host" ["notes"]=&gt;string(5) "Notes" ["links"]=&gt;array(2) {[0]=&gt;string(6) "Link 1" [1]=&gt;string(6) "Link 2"} ["countries"]=&gt;string(4) "None" ["displayChance"]=&gt;string(2) "15" ["extraLink"]=&gt;array(2) {    [0]=&gt;array(3) {       ["url"]=&gt;string(7) "dSite 1"       ["countries"]=&gt;string(4) "NONE"       ["chance"]=&gt;string(2) "11"       }    [1]=&gt;array(3) {       ["url"]=&gt;string(6) "dSite2"       ["countries"]=&gt;string(4) "None"       ["chance"]=&gt;string(2) "13"       }    } }   How would I go abound inserting data into the many type tables so they reference tuples inserted into the one type table?  I have this query constructed:  INSERT INTO siteInfo (siteName, siteHost, notes, allowableCountries, displayChance, imagePath) VALUES ('Sample', 'sample.com', 'note', 'NONE', '50', '../var');   I would then need to insert data into the siteLinks table, which is related to the entry already in the siteInfo table.  INSERT INTO siteLinks (siteID, URL) VALUES ((SELECT siteid FROM siteinfo WHERE sitename='Sample'), 'test');   Is there a better way of doing this?
73925
Earlier today a question was asked regarding .  The top answer, at time of writing, suggests in PHP just using htmlspecialchars and mysql_real_escape_string.   My question is: Is this always enough? Is there more we should know? Where do these functions break down?