Snipplrhttp://snipplr.com/tags/mysql
Recent snippets posted on Snipplr.comen-usSat, 10 Dec 2016 00:49:23 GMT(MySQL) Show the output of a query in a more readable vertical format - rm1984http://snipplr.com/view/147232/show-the-output-of-a-query-in-a-more-readable-vertical-format/
Show the output of a query in a more readable vertical format ]]>Thu, 30 Jun 2016 18:33:28 GMThttp://snipplr.com/view/147232/show-the-output-of-a-query-in-a-more-readable-vertical-format/(SQL) mysql pivot data - ktrouthttp://snipplr.com/view/145691/mysql-pivot-data/
pivot data. input source query in the form :
+--------+-----------+-------------+-------------+
| name | pivot_fid | pivot_fname | pivot_value |
+--------+-----------+-------------+-------------+
| apple | 1 | is red | 1 |
| apple | 2 | is blue | 0 |
| orange | 1 | is red | 1 |
| orange | 2 | is blue | 0 |
+--------+-----------+-------------+-------------+
and fields to pivot around ( eg. name ), outputs :
+--------+--------+---------+
| name | is red | is blue |
+--------+--------+---------+
| apple | 1 | 0 |
| orange | 1 | 0 |
+--------+--------+---------+ ]]>Sun, 10 Jan 2016 16:52:37 GMThttp://snipplr.com/view/145691/mysql-pivot-data/(SQL) mysql procedure throw_error - ktrouthttp://snipplr.com/view/145607/mysql-procedure-throwerror/
procedure to throw application defined error ]]>Sun, 03 Jan 2016 20:43:30 GMThttp://snipplr.com/view/145607/mysql-procedure-throwerror/(SQL) mysql rank with ties - ktrouthttp://snipplr.com/view/145604/mysql-rank-with-ties/
calculate rank ( with ties ) over points table ]]>Sun, 03 Jan 2016 14:10:07 GMThttp://snipplr.com/view/145604/mysql-rank-with-ties/(SQL) mysql clone table with structure and indexes - ktrouthttp://snipplr.com/view/145603/mysql-clone-table-with-structure-and-indexes/
clone table with structure and indexes ]]>Sun, 03 Jan 2016 13:52:46 GMThttp://snipplr.com/view/145603/mysql-clone-table-with-structure-and-indexes/(SQL) mysql unroll variable length string list - ktrouthttp://snipplr.com/view/145602/mysql-unroll-variable-length-string-list/
unroll variable length string list to normalised form. ]]>Sun, 03 Jan 2016 13:43:52 GMThttp://snipplr.com/view/145602/mysql-unroll-variable-length-string-list/(SQL) mysql tablename search information schema - ktrouthttp://snipplr.com/view/145601/mysql-tablename-search-information-schema/
search for tables in mysql catalog ( information_schema ) ]]>Sun, 03 Jan 2016 13:32:34 GMThttp://snipplr.com/view/145601/mysql-tablename-search-information-schema/(SQL) mysql batch sql statements - ktrouthttp://snipplr.com/view/145600/mysql-batch-sql-statements/
stored procedure for executing batch sql statements ]]>Sun, 03 Jan 2016 13:26:43 GMThttp://snipplr.com/view/145600/mysql-batch-sql-statements/(SQL) mysql calendar table - ktrouthttp://snipplr.com/view/145599/mysql-calendar-table/
creates a minimal calendar table ]]>Sun, 03 Jan 2016 13:15:46 GMThttp://snipplr.com/view/145599/mysql-calendar-table/(SQL) mysql sequence generator ( decimal definition ) - ktrouthttp://snipplr.com/view/145598/mysql-sequence-generator--decimal-definition-/
one cross join for each ai in definition of decimal number as sum(ai*10^i) ]]>Sun, 03 Jan 2016 13:09:01 GMThttp://snipplr.com/view/145598/mysql-sequence-generator--decimal-definition-/(PHP) php code example - johnschneiderhttp://snipplr.com/view/96472/php-code-example/
example of php code I worked on ]]>Sun, 12 Apr 2015 01:40:13 GMThttp://snipplr.com/view/96472/php-code-example/(PHP) PHP - Doctrine - Left join ON condition AND other condition syntax in Doctrine - satie83http://snipplr.com/view/94098/php--doctrine--left-join-on-condition-and-other-condition-syntax-in-doctrine/
In SQL I can get this by adding AND to the join
LEFT JOIN installations i ON a.id = i.app AND i.page = :page
This way I get the installation info for an app that has an installation on the page, but I get null values on the columns for app's that have installations on other pages or not at all.
Is there a way to do this in Doctrine or am I better off just getting all the installations for each application and then checking against the found page with php?
##########################################################################
See function leftJoin in doc: http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/query-builder.html#high-level-api-methods ]]>Fri, 20 Mar 2015 00:36:12 GMThttp://snipplr.com/view/94098/php--doctrine--left-join-on-condition-and-other-condition-syntax-in-doctrine/(VB.NET) VB.NET SQL Example: How to send SMS in VB.NET using SQL database - LoganTurnhttp://snipplr.com/view/93629/vbnet-sql-example-how-to-send-sms-in-vbnet-using-sql-database/
I thought I share this code snippet because I find this way of SMS sending an easy solution. I believe that even beginners can implement this VB.NET SQL SMS application, but I think some extent of perfection in the SMS technology is required for better understanding. Okay, let’s jump right in the middle of the project that allows you to send and receive SMS through database tables by using your own VB.NET application.
Project information:
• Language: VB.NET
• Level: Intermediate
• IDE: Microsoft Visual Studio
• Database server: MS SQL, MS SQL Express, MySQL, Access, Oracle, etc. I used MySQL (http://www.mysql.com/).
• Special prerequisites: .NET Framework (http://www.microsoft.com/hu-hu/download/details.aspx?id=17851), SMS Gateway (in order to let your PC to send/receive SMS transmissions to/from a telecommunications network). I used Ozeki NG (www.ozekisms.com).
Project description:
First of all, you need to create the necessary database tables: one for the outgoing messages, and another one for the incoming messages. The snippet shows my MySQL commands, but it can be changed of course depending on the database server you use. In order to be able to use your database you need to connect it to your SMS gateway, then you can insert an SMS into the table of the outgoing messages by using an SQL INSERT command. To execute this command, you need to connect to the database by using the myConnection.Open() method. Thereafter insert the message into the database ( mySqlCommand.ExecuteNonQuery() ), then close the database connection ( myConnection.Close() ). That's all there is to it! It wasn't that black magic, was it? ]]>Mon, 16 Mar 2015 17:18:43 GMThttp://snipplr.com/view/93629/vbnet-sql-example-how-to-send-sms-in-vbnet-using-sql-database/(PHP) Simple PDO Connection and select - snstrohttp://snipplr.com/view/92256/simple-pdo-connection-and-select/
This is the basic code that you need to know to connect and list data with PDO.
Don't use de old method (mysql_connect and mysqli_connect) to connect and interact with databases. ]]>Fri, 06 Mar 2015 02:32:03 GMThttp://snipplr.com/view/92256/simple-pdo-connection-and-select/(PHP) IP Address (IPv6) Lookup in Bulk Using PHP and MySQL Database - Hexahowhttp://snipplr.com/view/87308/ip-address-ipv6-lookup-in-bulk-using-php-and-mysql-database/
The code below can be used to lookup IP address in bulk in return of geolocation information using PHP programming languages and MySQL database. In this tutorial, we use the IP2Location LITE database to lookup country of origin from the visitor's IP address. Free databases are available for download at IP2Location LITE database. ]]>Fri, 16 Jan 2015 13:45:15 GMThttp://snipplr.com/view/87308/ip-address-ipv6-lookup-in-bulk-using-php-and-mysql-database/(Python) Scraped item to mysql in scrapy - zwidnypublichttp://snipplr.com/view/82246/scraped-item-to-mysql-in-scrapy/
To store you item into mysql database.
1. I say you have been have mysql database and the table what field is the same in item class that you wang to be stored
2. I just ensure you item insert into mysql, do not ensure the performance
3. Todo
Abstract pipeline class ]]>Tue, 25 Nov 2014 13:30:21 GMThttp://snipplr.com/view/82246/scraped-item-to-mysql-in-scrapy/(MySQL) A example option file for mysql in windows - jacktanhttp://snipplr.com/view/80412/a-example-option-file-for-mysql-in-windows/
`mysql-dev.ini` can be used in a developing environment and put it in the Mysql Home dir ]]>Thu, 30 Oct 2014 01:52:11 GMThttp://snipplr.com/view/80412/a-example-option-file-for-mysql-in-windows/(DOS Batch) A windows batch to run Mysql client - jacktanhttp://snipplr.com/view/80411/a-windows-batch-to-run-mysql-client/
**Vars** to to specify
+ ${EnvSet_Bash}
**Demo**
vars specified like
+ ${EnvSet_Bash}=[set-mysqlRT-Env](http://www.snipplr.com/view/80407/an-example-bash-file-for-set-temporary-path/ "set-mysqlRT-Env.bat")
pluss other bash commond
**Demo**
@echo off
call set-mysqlRT-Env
mysql --defaults-file=%mysqlConf% --print-defaults
pause
mysql --defaults-file=%mysqlConf%
pause ]]>Thu, 30 Oct 2014 01:43:22 GMThttp://snipplr.com/view/80411/a-windows-batch-to-run-mysql-client/(DOS Batch) A windows batch to shutdown Mysql server - jacktanhttp://snipplr.com/view/80409/a-windows-batch-to-shutdown-mysql-server/
Run `mysqladmin` to shutdown msyql server with a specified option file.
**Vars** to to specify
+ ${EnvSet_Bash}
**Demo**
vars specified like
+ ${EnvSet_Bash}=[set-mysqlRT-Env](http://www.snipplr.com/view/80407/an-example-bash-file-for-set-temporary-path/ "set-mysqlRT-Env.bat")
pluss other bash commond
@echo off
call set-mysqlRT-Env
mysqladmin --defaults-file=%mysqlConf% --print-defaults
pause
mysqladmin --defaults-file=%mysqlConf% shutdown
pause
Click the bat file to run it . ]]>Thu, 30 Oct 2014 01:21:38 GMThttp://snipplr.com/view/80409/a-windows-batch-to-shutdown-mysql-server/(DOS Batch) A windows batch to run Mysql server - jacktanhttp://snipplr.com/view/80408/a-windows-batch-to-run-mysql-server/
Run a Mysql server using command line with a specified option file.
**Vars** to to specify
+ ${EnvSet_Bash}
**Demo**
vars specified like
+ ${EnvSet_Bash}=[set-mysqlRT-Env](http://www.snipplr.com/view/80407/an-example-bash-file-for-set-temporary-path/ "set-mysqlRT-Env.bat")
pluss other bash commond
@echo off
call set-mysqlRT-Env
mysqld --defaults-file=%mysqlConf% --print-defaults
pause
mysqld --defaults-file=%mysqlConf% --console
pause
Click the bat file to run it . ]]>Thu, 30 Oct 2014 01:10:54 GMThttp://snipplr.com/view/80408/a-windows-batch-to-run-mysql-server/