MySQL
Opening phpmyadmin in an incognito window
If phpmyadmin is open in a new incognito window, the user has to login again. Then he can kill the running processes in Status tab under Processes (see http://prntscr.com/df4ydl). Unless the user has the proper rights, he can only kill his own processes. After killing the process, the user can use the incognito window or the window he was using before to run another query.
Taggings:
Squash TM with MySQL
Prerequisite: Administrator rights
The exception indicated that there were some issues with the size of text that was inserted into one of the tables. Since it was a SQL error there was the possibility of fixing the error by changing the data type of the affected column:
- Open the script with a text editor
- Find the part of the script where the tables are created
- Change the data type of the column in the script from VARCHAR(32000) to Text
CREATE TABLE `CORE_CONFIG` (`STR_KEY` VARCHAR(255) NOT NULL, `VALUE` VARCHAR(32000) NULL, CONSTRAINT `PK_CORE_CONFIG` PRIMARY KEY (`STR_KEY`));
To
CREATE TABLE `CORE_CONFIG` (`STR_KEY` VARCHAR(255) NOT NULL, `VALUE` TEXT NULL, CONSTRAINT `PK_CORE_CONFIG` PRIMARY KEY (`STR_KEY`));
PDF from Database using FPDF
This solutions came to my mind when i was asked to allow registrar to create student certificates online when they were using the school's local website
- similar to TISS certificate generator
- i used the free FPDF library which is a PHP class which allows to generate PDF files with pure PHP
- the aim is to allow the registrar of school to be able to take the student’s id and course id and generate the student's certificate for a course
- the main aim is to show how the class is used in a simple and easy way for use in a small scale
- There are two files. The first file, the user inputs the student id and course id and hits create certificate. The file points to the fpdf file that goes and creates a pdf file and displays the certificate
Adding a ODBC connection to MySQL and use it within Microsoft Excel
Microsoft Excel offers to connect to all common database management systems (DMBS) trough the ODBC interface. Excel has a built connector for Microsoft Access and Microsoft SQL. Since we are using MySQL we need acces the DBMS go trough ODBC.
An ODBC connection brings two components into play:
-
ODBC driver manager
The ODBC driver manager handels all the installed ODBC connectors. Microsoft Excel uses the ODBC driver manager which interfaces the ODBC connector to Microsoft Excel. Windows offers an integrated driver manager. To open it click the Start button -> Control panel -> Administrative tools -> Datasources (ODBC).
ATTENTION: Since Mac OS X (10.6) there is no built in ODBC driver manager availble. Therefore it has to be manually installed under Mac OS X greater or equal than version 10.6. A recommendation is to use iODBC, an open ODBC driver manager. It is available for Linux as well. Download it at: http://www.iodbc.org/dataspace/iodbc/wiki/iODBC/Downloads and install it -
ODBC database connector/driver
This connector is needed for each particular database management system (DMBS) as its implementd DMBS specific that means there is no common connector for all DBMS available. In our case as we want to use MySQL we need the MySQL ODBC driver either for Windows or Mac OS X. Download the connector at: http://www.mysql.com/downloads/connector/odbc/
ATTENTION: Check for which if your Excel is a 32 bit version or a 64 bit version. Even you are using a 64 bit version of Windows, if you have a 32 bit version of Excel running on it you need the 32 bit version!
Once the driver is installed, it can be configured trough the driver manager. Open either iODBC (in case of Mac OS X) or the built in driver manager of Windows (Start button -> Control panel -> Administrative tools -> Datasources (ODBC)) and you are able to add a new user data source (a so called DSN). For this DSN you must provide all relevant information (e.g server address and port, username, password, database name). A easy to understand wizard will guide you trough this process. This works quite similar under Mac or Windows.
Once the DSN is configured properly it is possible to use this database within Excel.
Open a new spreadsheet and klick on Data -> Other sources -> From Microsoft Query (see an attached screenshot of a German Excel version). Then you can select the previously configured DSN of your datasource. Select them and Microsoft SQL appears (see attached screenshot). You can enter your query here and execute it. Once the query is valid, Excel offers you different choices what to do with the retrieved data (please see an attached screenshot). You can simply add the result set to a table or transform it into a pivot table.
Taggings:
Fetch data from an MySQL database trough excel
Mostly a large collection of data is stored in a relational database. Relational databases have the advantage that they are offering a high level of perfomance especially for a large number of data records and especially when having aggregated queries. SQL is the most common computer language to communicate and control a relational database management system (RDMS). It offers various possibilites to fetch data (e.g. a JOIN tables within one query in order to receive aggregated data from different tables in one result set). Furthermore it allows to make certain limitations (e.g. WHERE-statement and HAVING statement) as well as to group data (GROUP BY) and sorting.
Microsoft Excel is a well known spreadsheet software which offers a lot of powerful functions for data preparation and evaluation. Excel allows you to visualize data (e.g. Pivot table or various kind of charts) and it offers a lot of formulars for calculation, aggregation and reporting.
As mentioned at the beginning, data is mostly stored in databases for performance reasons but also due to the fact that a database represents a centralized storage possibility for data which can be accessed by everyone (according to the access policy of course). Webshops, ERP Systems, CMS systems, just to name but a few, are all using relational databases in most cases. MySQL is a very popular and often used RDMS but it offers not that powerful possibilites of data evaluation and reporting than Excel. So why do not user the actual data from MySQL (as the most reliable datasource), fetch them by using SQL (because of its powerful possibilities of JOINING) and process the final result set in Excel
calculating the distance with sql
i just used google maps to get the coordinates of the two addresses and used simple mathematical functions provided by sql to calculate the distance. there are even special geometrical data types in mysql which provide more possibilities working with geographical data.
Taggings:
Using xampp with Windows 7 64 Bit
Xampp that I normally use for creating PHP-sites didn't worked at this computer with a 64 bit Windows 7. The Problem is that I needed it to make some changes in the source of a php-site and this with that new computer. First I had to install xampp which I did, but it didn't worked.
Online Fan-Voting
<p>An online fan-voting process is going to be implemented, where only the email-address of the voter and the object-voted-for (person, band, subject,...) is given. It should not be possible for voters to vote more than one time. The system is implemented in PHP on a shared hosting service. Access to MySQL database and email sending component is possible.</p><p>To find a basic concept (workflow), how multiple votings by a single user can be prevented in this restricted environment, is the goal of this challenge.</p>
Easy access to MySQL data on shared hosting service
<p>MySQL databases are often the main data-source of web applications when it comes to shared hosting services with very limited possibilites (no system service support, no scheduling, no in-memory database, ...). Often, management or visualization of such databases is not easily done, since no admin utility is installed, no remote connections to the database are allowed, and access by the sql binary (via SSH) is not user-friendly. A better way to manage databases visually in such cases is the challenge.</p>
Pages
