Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 23

Displaying posts with tag: PHP/MySQL/MSSQL (reset)

How To – Configure MySQL to Use UTF-8
+1 Vote Up -1Vote Down

Background Knowledge


Using the character set UTF-8 allows for the use of any language, can represent every character in the Unicode character set and is backward compatibility with ASCII. Not to mention is can handle any platform and be sent through many different systems without corruption. With such advantages this is why so many are making the switch.

The following instructions were done on Debian Squeeze v6.04 AMD64 operating system using MySQL v14.14 Distrib 5.1.61.

Solution – Server Configuration


At present MySQL is configured by default to use “latin1″ character set. Here’s how to change MySQL configuration to use UTF-8 character set and collation.

  • Check MySQL’s current configuration, run the following two SQL statements.
    1
    2
    
    SHOW VARIABLES LIKE '%collation%'; 
    SHOW
  •   [Read more...]
    How To – Fix MySQL Option Without Preceding Group
    +0 Vote Up -0Vote Down

    Background Knowledge


    You try starting, stopping or connecting to MySQL and receive the following error, “Error: Found option without preceding group in config file: /etc/mysql/conf.d/char_collation_set.cnf at line: 1″. The error message my vary but comes to the same issue. MySQL may not start or might experience connectivity issues.

    This issue was experienced on Debian Squeeze v6.04 AMD64 system with MySQL v14.14 Distrib 5.1.61.

    Solution


    This issue is caused by a improperly formatted MySQL configuration file(s) and refers to one missing the group heading (e.g. [mysqld], [mysqld_safe], etc.).

    Source:   [Read more...]

    How To – Resolve MySQL Error Incorrect Key File for Table
    +0 Vote Up -0Vote Down

    Background Knowledge


    I using PHP v5.3.3-7 PDO running a MySQL v14.14 Distrib 5.1.49 on Debian v6.0.4 64-bit and executing a SQL load data infile statement.

    I received “PHP Warning: PDOStatement::execute(): SQLSTATE[HY000]: General error: 126 Incorrect key file for table ‘/tmp/#sql_66f_0.MYI’; try to repair it”. My database table in this instance is using the storage engine of InnoDB and therefore one can not use the “repair table”.

    From my experience I’ve found that this error can mean one of two issues however I have not found information from MySQL confirming this.

    Solution – Repair Table


    The error message may mean the database table is corrupted and requires a repair.

  • Run
  •   [Read more...]
    TaskFreak! v0.6.2 – Customizing Status
    +0 Vote Up -0Vote Down

    Background Knowledge


    The progress of a task in TaskFreak! is shown as a percentage value and is not exactly visually appealing to quickly spot the progress. With a few minor alterations we can show the percentage completed bar that fills as the task progresses and a gradient bar indicating the progress along with the percentage value.

    This solution was posted by Searcher at Re: Taskfreak Customizing Status.

    Solution


  • Edit at line #268 as shown below.
    Cod Before
    268
    
    <th width="<?php echo FRK_STATUS_LEVELS * 2; ?>%" onclick="freak_sort('statusKey')" colspan="< ?php echo FRK_STATUS_LEVELS ?>" class="sortable">< ?php echo (FRK_STATUS_LEVELS == 1)?'X':$langForm['status'];

  •   [Read more...]
    How To – Convert MSSQL Timestamp/Datetime to Unix Timestamp
    +1 Vote Up -0Vote Down

    Background Knowledge


    I will explain how to convert a DATETIME (data type) value in Microsoft SQL Server to Unix timestamp and how to convert Unix timestamp to DATETIME. A Unix timestamp is a integer value of seconds since January 1, 1970 at midnight. For further explanation of Unix timestamps refer to Wikiepedia, UnixTimestamp.com or http://unixtimesta.mp/.

    Note: This solution only work on dates prior to 2038-01-19 at 3:14:08 AM, where the delta in seconds exceeds the limit of the INT data type (integer is used as the result of DATEDIFF). See source for further details as I have not verified a solution to this problem.

    Solutions


    Convert Datetime Value to Unix Timestamp

      [Read more...]
    Example of a Basic ODBC (MSSQL Server) Query using PHP
    +0 Vote Up -4Vote Down

    An example of a basic ODBC (MSSQL Server/DSN-Less) query using PHP.

    Example of Result Set Returning One Row

    1
    2
    3
    4
    5
    6
    7
    8
    9
    
    $szDBConn="DRIVER={SQL Server};SERVER=SQLServerNameHere;DATABASE=DatabaseNameHere";
    $szDBUsername="UsernameHere";
    $szDBPswd="PasswordHere";
    $szDBQuery="SELECT FooBar, Foo_Bar, Foo_ID FROM FoobarSubscribers WHERE FooID=777";
    $rDBConnect = odbc_connect($szDBConn, $szDBUsername, $szDBPswd);  
    $rDBRes = odbc_exec($rDBConnect, $szDBQuery);
    $szLastCheck = odbc_result($rDBRes, "LastCheck");
    odbc_free_result($rDBRes);
    odbc_close($rDBConnect);

    Example of Result Set Returning Multiple Rows in an Object

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    
    $szDBConn="DRIVER={SQL
      [Read more...]
    TaskFreak! v0.6.2 – Add Unique Ticket/Task Number
    +0 Vote Up -0Vote Down

    Background Knowledge


    TaskFreak! does not show within the interface a unique ticket/task number. Depending on the use of TaskFreak! having such a value can have it’s benefits. Since this unique value does already exist in the back end, it will be fairly trivial to render this value within the task list and task details panel. I will show you how this is done using Searcher’s solution posted in the TaskFreak! Forum. There has been some modifications but not much.

    Solution


  • Edit index.php in the root of TaskFreak!. Add the table header column just below line # 248 as follows.
    2
    
    <th width="3%" onclick="freak_sort('itemId')" class="sortable" >< ?php echo
  •   [Read more...]
    PHP – mssql_query() Error
    +0 Vote Up -4Vote Down

    Background Knowledge


    I’m running PHP v5.2.9-2 with SQL Server 2005 Express on Windows Server 2003 R2 SP2.

    Error Message


    Warning: mssql_query() [function.mssql-query]: message: Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library (such as ISQL) or ODBC version 3.7 or earlier. (severity 16)

    Solution


    Specify the database columns within your database query (select filed1, field2 from foo). Avoid doing queries with the wildcard (*), select * from foo.

    Source: PHP Manual on MSSQL_Query()

    Example of a Basic MSSQL Query using PHP
    +0 Vote Up -2Vote Down

    An example of a basic MSSQL (Microsoft SQL Server/SQL Server Express) query using PHP.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    
    $szQry = "SELECT column1, column2 FROM foo";
    $szDBConn = mssql_connect("host","username","password");
    mssql_select_db("database_name", $szDBConn);
    $saResults = mssql_query($szQry, $szDBConn);
    while($obResults = mssql_fetch_row($saResults))
    {
       echo $obResults[0]." ".$obResults[1];
    }
    mssql_close($szDBConn);

    Comments/description of Example

    Line #1 SQL statement that will be sent to the MySQL database server. Line #2 MSSQL database login credentilas; host (127.0.0.1), username and password. The “host” is the server name or IP address of your database server. If your host has multiple  [Read more...]
    PHP – Upgrading v5.2.5 to v5.2.8
    +0 Vote Up -0Vote Down

    Background Knowledge


    The following is the process I took to upgrade a web server with PHP v5.2.5 to PHP v5.2.8 running on OpenBSD. PEAR is already installed on this system and up to date. I wasn’t sure if I should exclude PEAR at install or not so therefore did not tell the configurator to exclude PEAR at install.

    Installation Process


  • Download the latest stable PHP release from command prompt # wget http://ca.php.net/get/php-5.2.8.tar.gz/from/a/mirror
  • # tar -zxvf php-5.2.8.tar.gz
  • ./configure –with-mysql=/usr/local –with-mssql=/usr/local –with-apxs –with-zlib-dir=/usr/lib –with-config-file-path=/var/www/conf
  •   [Read more...]
    MySQL - Can You Concatenate Strings From a Column Into a Single Row?
    +0 Vote Up -0Vote Down

    How would one concatenate strings from a column (multiple rows) into a single row using MySQL? I see its possible with MS SQL Server 2005 and above. Any incite into how to achieve this in MySQL would be much appreciated.

    MS SQL Server 2005 - Example


    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    
    SELECT Web_Account_ID,
    GroupNameConcat = REPLACE(
    	(
    	SELECT
    		Web_Account_Group_Name_ID AS [DATA()]
    	FROM
    		tblWebAccountGroup WAG
    	WHERE
    		WAG.Web_Account_ID = WA.Web_Account_ID
    	ORDER BY
    		Web_Account_Group_Name_ID
                FOR XML PATH ('')
            ), ' ', ',')
    FROM tblWebAccounts WA
    ORDER BY Web_Account_ID

    Query Results Example


      [Read more...]
    TaskFreak! v0.6.2 - Tweaking Priority Menu
    +0 Vote Up -0Vote Down

    Background Knowledge


    For some reason or another the priority menu in the edit task panel is not wide enough and therefore it cuts off the priority names. This can be easily fixed by modifying some inline CSS. Yes I agree this should be done within the skin’s CSS file, however there is nothing present in the CSS file to alter that I could find.

    Solution


    Edit /taskfreak/include/html/xajax_panel_edit.php at line #18 by changing the width value of “40px” to “125px” or to what you desired width.

    Code Before
    16
    17
    18
    19
    
    <td>< ?php
    $objTemp = new ItemPriority();
    $objTemp->qSelect('priority',($objTask->priority)?$objTask->priority:3,'','','style="width:40px"');
    ?></td>
    Code After
    1
    2
    3
    4
      [Read more...]
    TaskFreak! v0.6.2 - Add My Projects List
    +0 Vote Up -0Vote Down

    Background Knowledge


    TaskFreak! presently does not have a means via the web interface to present a complete list of tasks for which the current user is the project leader. I will show you how to add “My Projects” list based on bpiper’s solution with a slight difference. My solution is almost identical to bpiper’s but with a different approach to continue support of the supported interface languages. To do this each supported language file will require to be edited.

    Thanks to bpiper for posting your solution.

    Solution


  • Edit /taskfreak/include/language/en/freak.php starting at line #15. Add in a new array key/value at any point you desire like so “‘my_projects’ => ‘My Projects’,” (without
  •   [Read more...]
    Pear::Date Returned Timezone is Wrong
    +0 Vote Up -0Vote Down

    Background Knowledge


    I’m trying to determine the difference in minutes between two timestamps. I’m using Pear::Date to do this. The issue comes into play when I noticed that the wrong timezone was being used by Pear::Date, UTC. If I do not use Pear::Date the timezone is set correctly.

    I have tried using date_default_timezone_set() and it does set the timezone back, however I feel this shouldn’t be necessary as the default timezone should be used. I have been using date_default_timezone_get() to determine what timezone is being used.

    It’s my understanding that Pear::Date uses UTC when it is unable to determine the default timezone. As far as I know I

      [Read more...]
    Using short if statement in programming
    +0 Vote Up -0Vote Down

    In many programing languages it is possible to shorten if statements using what’s called the ternary operator. It is sometimes referred as the “one line if statement” or the “short if statement”. This can help at times to produce cleaner code, however use this operator wisely as it is not always best to be used for more complicated statements.

    PHP Example of an if statement


    1
    2
    3
    4
    5
    6
    7
    8
    
    if($nFoo > 0)
    {
       echo "I'm at the work.";
    }
    else
    {
       echo "I'm at home.";
    }

    PHP Example using the ternary operator


    1
    
    echo $nFoo > 0 ? "I'm at the work." : "I'm at home.";

    The expression (expr1) ? (expr2) : (expr3) evaluates to expr2 if

      [Read more...]
    How To - Remove Leading Zeros
    +0 Vote Up -0Vote Down

    Solutions

    Using PHP function intval().

    Code example: echo intval(”05″);
    Returned value: 5

    Using PHP function ltrim().

    Code example: echo ltrim(”005″,”0″);
    Returned value: 5

    ShareThis

    How To - Convert MySQL Timestamp/Datetime to Unix Timestamp
    +0 Vote Up -0Vote Down

    Background Knowledge


    Since MySQL v4.1 timestamp and datetime data types are formatted “YYYY-MM-DD HH:MM:SS”. Prior to MySQL v4.1 the timestamp was formatted as YYYYMMDDHHMMSS” and datetime formatted as “YYYY-MM-DD HH:MM:SS”. Refer to MySQL Reference Manual for further details.

    The Unix timestamp differs from MySQL. Unix’s timestamp is a integer value of seconds since January 1, 1970. For further explanation of Unix timestamps refer to Wikiepedia or UnixTimestamp.com.

    Solutions


    In MySQL you can use

      [Read more...]
    French Characters Not Rendering Correctly
    +0 Vote Up -0Vote Down

    Background Knowledge


    The MySQL database (http://www.mysql.com/) v4.0.23 is using the default character set of “Latin1″. When the database was created I had no knowledge of character sets other wise it would have been “UTF-8″.
    The web pages are using a character set of “UTF-8″.

    Problem


    Data being queried from a MySQL database (http://www.mysql.com/) that contains French accent characters will not render correctly in the browser even after applying PHP htmlentities().

    Example code: $string = htmlentities($string , ENT_QUOTES, “UTF-8″);

    Solution


    The queried data from the database was inputted using the


      [Read more...]
    HTML_QuickForm Generates Invalid Code for XHTML Strict
    +0 Vote Up -0Vote Down

    One of the biggest complaints I’ve had with HTML_QuickForm is not producing valid XHTML Strict code. Well there is a simple solution to removing the name attribute of the HTML form element tag using remoteAttribute().

    Example
    $form->removeAttribute(’name’);

    ShareThis

    PHP Calendar Functions Error
    +0 Vote Up -0Vote Down

    I was trying to use the PHP calendar API and immediately received this error message, “Fatal error: Call to undefined function cal_days_in_month()”. This error message means PHP was not compiled with the calendar extension.

    Solution


    The only solution to this error message and other similar error messages relating to the PHP calendar API requires PHP to be compiled with the calendar extension by adding “–enable-calendar” to the “configure command” as stated in the PHP documentation on the Calendar functions page.

    How to Tell if the Calendar Extension is Installed


    You can verify weather or not the PHP Calendar extension was compiled at install by using the phpinfo() function. When viewing the output of phpinfo() look under “Configure

      [Read more...]
    PHP Free Chat - Joining Chat Email Notification
    +0 Vote Up -0Vote Down

    I recently upgraded an install of PHP Free Chat to that latest version of 1.0 Final. However it was still lacking a feature to notify individual(s) that someone has joined the chat if they were not already in the chat application to begin with. I came across a posting explaining how to achieve this in PHP Free Chat at PHP Free Chat Forum. After a little reading and discussion I was able to implement the feature.

    Here’s my modified version for the solution based on the forum posting. The pfcmail() function can be made to be way more versatile for any use, however for my use it was made to be simple and produce properly formated email messages.

    Solution


  • Create a new PHP file called pfcmail.php with the following
  •   [Read more...]
    PEAR::HTML_BBCodeParser Upgrade 1.1 to 1.2.2
    +0 Vote Up -0Vote Down

    Problem #1 - “[notice] child pid 13449 exit signal Segmentation fault (11)”

    On a OpenBSD v3.7 i386 system running Apache v1.3.29 (not chrooted) with PHP v5.1.4 I upgraded HTML_BBCodeParser from version 1.1 to version 1.2.2 by running at command line “pear upgrade-all”. After the upgrades were complete the web site would not load a web page anymore. I looked at my HTTPD server logs and noticed I was receiving the following error message in error_log file, “[notice] child pid 13449 exit signal Segmentation fault (11)” whenever there was a HTTP request on port 80 or 443 to the web site. I couldn’t understand what the problem could

      [Read more...]
    PEAR::HTML_BBCodeParser Parser Issue
    +0 Vote Up -0Vote Down

    I’ve come into a situation where I require to have BBCode parsed, this includes the standard tags supported by PEAR package HTML_BBCodeParser and custom BBCode tags I’ve added myself.

    My problem is this, I’ve discovered that when an value has a space within the value the value is truncated at the first occurrence of the space. This applies to a URL, image file names and any additional attribute values (alt, style, etc.). This issue is present in the stable release and latest release in CVS for HTML_BBCodeParser. Here is some examples.

    Before BBCode Parser
       [url=http://www.somedomain.com/Foo World?str=1]Foo World
      [Read more...]
    Showing entries 1 to 23

    Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

    Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.