I am using joshcam/ PHP MySQLi class and currently having problem on inserting foreign character in the table.  I tried to update using rawQuery to identify if the problem lies with the class itself.  $db->rawQuery("UPDATE table SET fieldname=''龙岗区中心" WHERE . ..... ");  The query executes with no problem but it saves as ?????? in the table.  If i use mysql_query update on the same table , the characters insert in the table with no problem.  Has anyone experienced this? The charset is set as UTF-8.  Any help would be much appreciated.  Regards  Ash
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.
19638
I am trying to INSERT INTO a table using the input from another table. Although this is entirely feasible for many database engines, I always seem to struggle to remember the correct syntax for the SQL engine of the day (, , , , and ).  Is there a silver-bullet syntax coming from an SQL standard (for example, ) that would allow me to insert the values without worrying about the underlying database?
94051
I want to do something like this:    create table app_users (     app_user_id smallint(6) not null auto_increment primary key,     api_key     char(36) not null default uuid() );    However this results in a error, is there a way to call a function for a default value in mysql?  thanks.
108095
Who can explain why   select case when '' = ' ' then 1 else 0 end, LEN(''), LEN(' '), DATALENGTH(''), DATALENGTH(' ');   yields  ----------- ----------- ----------- ----------- ----------- 1           0           0           0           1   The funny consequence of this is that in   create table test ( val varchar(10) ); insert into test values( '' ); update test set val = ' ' where val = '';   the update will indeed replace the empty string by a blank, but the where clause keeps to be true and repeated executions of the update statement tell  (1 row(s) affected)
46938
I am trying to execute my PHP code, which calls two MySQL queries via mysqli, and get the error "Commands out of sync; you can't run this command now".  Here is the code I am using  &lt;?php $con = mysqli_connect("localhost", "user", "password", "db"); if (!$con) {     echo "Can't connect to MySQL Server. Errorcode: %s\n". Mysqli_connect_error();     exit; } $con-&gt;query("SET NAMES 'utf8'"); $brand ="o"; $countQuery = "SELECT ARTICLE_NO FROM AUCTIONS WHERE upper(ARTICLE_NAME) LIKE % ? %"; if ($numRecords = $con-&gt;prepare($countQuery)) {     $numRecords-&gt;bind_param("s", $brand);     $numRecords-&gt;execute();     $data = $con-&gt;query($countQuery) or die(print_r($con-&gt;error));     $rowcount = $data-&gt;num_rows;     $rows = getRowsByArticleSearch("test", "Auctions", " ");     $last = ceil($rowcount/$page_rows); }  else {  print_r($con-&gt;error); } foreach ($rows as $row) {     $pk = $row['ARTICLE_NO'];     echo '&lt;tr&gt;' . "\n";     echo '&lt;td&gt;&lt;a href="#" onclick="updateByPk(\'Layer2\', \'' . $pk . '\')"&gt;'.$row['USERNAME'].'&lt;/a&gt;&lt;/td&gt;' . "\n";     echo '&lt;td&gt;&lt;a href="#" onclick="updateByPk(\'Layer2\', \'' . $pk . '\')"&gt;'.$row['shortDate'].'&lt;/a&gt;&lt;/td&gt;' . "\n";     echo '&lt;td&gt;&lt;a href="#" onclick="deleterec(\'Layer2\', \'' . $pk . '\')"&gt;DELETE RECORD&lt;/a&gt;&lt;/td&gt;' . "\n";     echo '&lt;/tr&gt;' . "\n"; } function getRowsByArticleSearch($searchString, $table, $max) {     $con = mysqli_connect("localhost", "user", "password", "db");     $recordsQuery = "SELECT ARTICLE_NO, USERNAME, ACCESSSTARTS, ARTICLE_NAME, date_format(str_to_date(ACCESSSTARTS, '%d/%m/%Y %k:%i:%s'), '%d %m %Y' ) AS shortDate FROM AUCTIONS WHERE upper(ARTICLE_NAME) LIKE '%?%' ORDER BY str_to_date(ACCESSSTARTS, '%d/%m/%Y %k:%i:%s')" . $max;     if ($getRecords = $con-&gt;prepare($recordsQuery)) {         $getRecords-&gt;bind_param("s", $searchString);         $getRecords-&gt;execute();         $getRecords-&gt;bind_result($ARTICLE_NO, $USERNAME, $ACCESSSTARTS, $ARTICLE_NAME, $shortDate);         while ($getRecords-&gt;fetch()) {             $result = $con-&gt;query($recordsQuery);             $rows = array();             while($row = $result-&gt;fetch_assoc()) {                 $rows[] = $row;             }             return $rows;         }     } }   I have tried reading up on this, but I am unsure of what to do. I have read about store result and free result, however these have made no difference when using them. I am unsure at exactly which point this error is being caused, and would like to know why it is being caused, and how to fix it.  Going by my debug statements, the first if loop for countQuery is not even being entered, because of an error in my sql syntax near near '% ? %'. However if I just select * instead of trying to limit based on a LIKE clause, I still get the command out of sync error.
121895
I have a trigger and a class that seem to be causing an insert fail in Dataloader.  I am trying to insert 200 records to see if an error is generated before moving these to production. I'm just inserting the mandatory fields such as Name, Owner, Market, Customer_Success_Manager__c, and Business Unit.   Each time I get an error:     UpdateCSMTeam:execution of AfterInsertcaused by: System.DmlException:   Insert failed. First exception on row 0 with id01MJ0000009gXthMAE;   first error: INVALID_FIELD_FOR_INSERT_UPDATE, cannot specify Id in an   insert call: [Id]Class.UpdateCSM.updateTeamMem . I've listed the class   and trigger below.   APEX CLASS  public class UpdateCSM {             public static void updateTeamMember(List&lt;Account&gt; accounts) {                     List&lt;AccountShare&gt; ashareLIST = new List&lt;AccountShare&gt;();                     List&lt;AccountTeamMember&gt; ATM = new List&lt;AccountTeamMember&gt;();                    Set&lt;String&gt; rmMember = new Set&lt;String&gt;();                     Map&lt;ID, ID&gt; acctToBRepMap = new Map&lt;ID, ID&gt;();                      for(Account a:accounts){                              AccountShare caSharingRule = new AccountShare();                                caSharingRule.AccountId = a.Id;                                caSharingRule.OpportunityAccessLevel = 'Edit';                                caSharingRule.CaseAccessLevel = 'Edit';                                caSharingRule.AccountAccessLevel = 'Edit';                                caSharingRule.UserOrGroupId = a.Customer_Success_Manager__c;                                ashareLIST.add(caSharingRule);                                    if(a.Customer_Success_Manager__c!= null){                                   AccountTeamMember Teammemberad=new AccountTeamMember();                 Teammemberad.AccountId=a.id;                 Teammemberad.UserId=a.Customer_Success_Manager__c;                 Teammemberad.TeamMemberRole = 'Customer Success Manager';                                   ATM.add(Teammemberad);                             if(!ATM.isEmpty()){                                    insert ATM;                                              insert ashareLIST;                                                         if(a.Customer_Success_Manager__c==null){                                            list&lt;AccountTeamMember&gt; ratm = [SELECT UserId                                                                             FROM AccountTeamMember                                                                             WHERE (TeamMemberRole = 'Customer Success Manager')                                                          AND AccountId =:a.id];                                             if(ratm.size()&gt;0)                                                 delete ratm;                                        }                                    }                                 }                            }                         }                    } ...   APEX TRIGGER  trigger UpdateCSMTeam on Account (After update, After insert){         UpdateCSM.updateTeamMember(trigger.new); }
124164
For example, I have a table for user registration:  CREATE TABLE IF NOT EXISTS test_user (      user_id int(10) unsigned NOT NULL AUTO_INCREMENT,      user_name varchar(50) NOT NULL,      PRIMARY KEY (user_id),   ) ENGINE=InnoDB DEFAULT CHARSET=utf8   I want the user_id field ID numbers to be continuous, without gaps.    Failed transactions will occupy some ID numbers, is there any way around this?
69645
Our production environment just froze* this morning for a while when altering a table, adding a column actually.   Offending SQL:ALTER TABLE cliente ADD COLUMN topicos character varying(20)[];  * Login into our system requires a select from that very same table, so no one could login during the alter table. We actually had to kill the process to allow the system resume normal operations.    Table Structure:   CREATE TABLE cliente (   rut character varying(30) NOT NULL,   nombre character varying(150) NOT NULL,   razon_social character varying(150) NOT NULL,   direccion character varying(200) NOT NULL,   comuna character varying(100) NOT NULL,   ciudad character varying(100) NOT NULL,   codigo_pais character varying(3) NOT NULL,   activo boolean DEFAULT true,   id serial NOT NULL,   stock boolean DEFAULT false,   vigente boolean DEFAULT true,   clase integer DEFAULT 1,   plan integer DEFAULT 1,   plantilla character varying(15) DEFAULT 'WAYPOINT'::character varying,   facturable integer DEFAULT 1,   toolkit integer DEFAULT 0,   propietario integer DEFAULT 0,   creacion timestamp without time zone DEFAULT now(),   codelco boolean NOT NULL DEFAULT false,   familia integer DEFAULT 0,   enabled_machines boolean DEFAULT false,   enabled_canbus boolean DEFAULT false,   enabled_horometro boolean DEFAULT false,   enabled_comap boolean DEFAULT false,   enabled_frio boolean DEFAULT false,   enabled_panico boolean DEFAULT false,   enabled_puerta boolean DEFAULT false,   enabled_rpm boolean DEFAULT false,   enabled_supervisor integer DEFAULT 0,   demo boolean,   interno boolean,   mqtt_enable boolean NOT NULL DEFAULT false,   topicos character varying(20)[],   CONSTRAINT pk_cliente PRIMARY KEY (rut),   CONSTRAINT fk_cliente_familiaid FOREIGN KEY (familia)       REFERENCES cliente_familia (id) MATCH SIMPLE       ON UPDATE NO ACTION ON DELETE NO ACTION,   CONSTRAINT pk_pais FOREIGN KEY (codigo_pais)       REFERENCES pais (codigo) MATCH SIMPLE       ON UPDATE NO ACTION ON DELETE NO ACTION,   CONSTRAINT unique_id_cliente UNIQUE (id) ) WITH (   OIDS=FALSE ); ALTER TABLE cliente   OWNER TO waypoint; GRANT ALL ON TABLE cliente TO waypoint; GRANT ALL ON TABLE cliente TO waypointtx; GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE cliente TO waypointtomcat; GRANT SELECT ON TABLE cliente TO waypointphp; GRANT SELECT ON TABLE cliente TO waypointpphppublic; GRANT ALL ON TABLE cliente TO waypointsoporte; GRANT SELECT, INSERT ON TABLE cliente TO waypointsalesforce; GRANT SELECT ON TABLE cliente TO waypointadminuser; GRANT SELECT ON TABLE cliente TO waypointagenda; GRANT SELECT ON TABLE cliente TO waypointmachines; GRANT SELECT ON TABLE cliente TO waypointreports; GRANT SELECT ON TABLE cliente TO readonly;  CREATE INDEX index_cliente   ON cliente   USING btree   (rut COLLATE pg_catalog."default");  CREATE INDEX index_cliente_activo   ON cliente   USING btree   (activo);  CREATE INDEX index_cliente_id_activo   ON cliente   USING btree   (id, activo);  CREATE INDEX index_cliente_rut_activo   ON cliente   USING btree   (rut COLLATE pg_catalog."default", activo);   CREATE TRIGGER trigger_default_admin   AFTER INSERT   ON cliente   FOR EACH ROW   EXECUTE PROCEDURE crea_default_admin();  CREATE TRIGGER trigger_default_grupo   AFTER INSERT   ON cliente   FOR EACH ROW   EXECUTE PROCEDURE crea_default_clientegrupo();     Should I disable CONSTRAINTS, TRIGGERS, or something else?  Perhaps any DB Tuning?  What else should I provide for further analysis?  Version: PostgreSQL 9.4.5 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit
18011
Is there a way to get the raw SQL string executed when calling PDOStatement::execute() on a prepared statement? For debugging purposes this would be extremely useful.
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().
10256
How do I use the json_encode() function with MySQL query results? Do I need to iterate through the rows or can I just apply it to the entire results object?
62223
In MySQL we can create queries with or without the backtick (`) symbol. Example:   SELECT * FROM TEST; SELECT * FROM `TEST`;   Both works fine in mysql-console.  Is there any technical difference between them?  Is there any benefit using (`) over over simple queries?
17325
I have a MySQL table with utf8 general ci collation. In the table, I can see two entries:  abad abád  I am using a query that looks like this:  SELECT *  FROM `words` WHERE `word` = 'abád'   The query result gives both words:  abad abád  Is there a way to indicate that I only want MySQL to find the accented word? I want the query to only return    abád    I have also tried this query:  SELECT *  FROM `words` WHERE BINARY `word` = 'abád'   It gives me no results. Thank you for the help.
76738
I have a webserver administration question. In this website:   All PHP generated pages contain the following line:  &lt;meta http-equiv="Content-Type" content="text/html; charset=iso-8859-2" /&gt;   But this is somehow disregarded probably via the php-apache processing and the page displays in browsers with an UTF-8 header. As a result of that question marks (�) are shown in the page text instead of accented characters (éáöőóüűúí). I tested this in Firefox, IE, Chrome and Seamonkey.  The strangest in this phenomenon, that this symptom started only yesterday, after I upgraded my server to Debian 8.0 Jessie from 7.0 Wheezy. During the upgrade I also upgraded all other packages as well, including apache, php, and so on, and selected "yes" for overwriting config files with factory default ones. After this I fine-tuned my config files to have everything the way I like, but I did not find a way to fix this. Before the upgrade, the page displayed just fine.  Here is a screenshot, where you can see that Firefox sees the "charset=iso-8859-2" definition, but still displays the page with UTF-8 encoding.    My suspicion is, that this is a server configuration issue, but it could also be, that one part of the processing component (Apache, php) changed due to the upgrade in some way, resulting this strange behavior. The problem is, I can't pinpoint, what could possibly cause this problem.  Can anyone solve this mistery? What could be possibly going wrong during the processing of the page?
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?
50152
Consider the following text (incidentally, part of a MySQL dump):   CREATE TABLE `table` (   `id` int(10) NOT NULL auto_increment,   `name` varchar(100) NOT NULL default '',   `description` text NOT NULL,   PRIMARY KEY  (`id`),   FULLTEXT KEY `full_index` (`name`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */;   I would like to remove the FULLTEXT key, and I also want to remove the trailing comma on the line above so that the SQL remains valid.  Can anyone come up with (and explain) a sed recipe to do this?
31743
I am converting some module from d5 to d7, and in old code there is an update query like this:  &lt;?php db_query("UPDATE {a_table} SET count = count + 1 WHERE field1 = %d", $some_value);   As we see, it is incrementing a field value (AFAIK it is a mysql feature). But, if I use new style of update it won't work.  &lt;?php db_update('a_table')     -&gt;fields(array('count' =&gt; 'count + 1',))     -&gt;condition('field1', $some_value)     -&gt;execute();   Are there any workaround within new style of db update without running an extra query to get existing field value to increment it?