我们可以通过使用mysqlpp:: Query来进行SQL语句的增删改查。
首先来看一下mysqlpp::Query的一些最简单的调用，
conn.connect(mysqlpp::examples::db_name, "127.0.0.1",
"root", "root");
mysqlpp::Query query = conn.query("select item from stock");
mysqlpp::StoreQueryResult res = query.store();
mysqlpp::Query query = conn.query("select * from stock");
mysqlpp::UseQueryResult res = query.use();
while (mysqlpp::Row row = res.fetch_row()) {
// row[0]; 第一列
在mysqlpp::Connection.query( )方法中，他其实是这样调用的。
MYSQL++ 的mysqlpp::Query类型
请注意，这里的Query居然继承了std: :ostream！这个是为什么？
我认为，这样做的一个好处是，可以运用如下的方式
mysqlpp::Query q(0);
q && mysqlpp::quote && &test&;
当然，mysqlpp::quote是一个enum【在mani.h中定义】，这里有很多技巧，放在专门的&escape与quote&相关内容中介绍。
首先需要说明的是，通常情况下，APP并不会直接创建这个mysqlpp::Query对象，而是call mysqlpp::Connection::query() to get one tied to that connection.
该类型提供了一些能够执行select,insert等SQL语句的方法。有下面几种方法来执行一句SQL语句
pass a SQL statement in either the form of a C or C++ string to one of the or
build up the query string over several C++ statements using Query's stream interface.
"template queries"，This is something like C's printf() function. You call the
method to tell the
object that the query string contains placeholders.
Use Specialized SQL Structures (SSQLS) to manipulate the db only by data structure.
Query有一些最基本的用法，包括
直接利用已经拼凑好了的SQL语句调用mysqlpp::Query::exec*()，mysqlpp::Query::store()或者mysqlpp::Query::use()
使用ostream接口，像构造cout一样构造SQL语句，然后再调用mysqlpp::Query::exec*()，mysqlpp::Query::store()或者mysqlpp::Query::use()
类似于printf一样使用template queries
类似于Hibernate那样使用Specialized SQL Structures feature(SSQLS)
这里只介绍最简单的两个情况，template queries和SSQLS的原理放到其他章节。
仔细来看一下Query提供的各个重要方法的重构方式（类似的方法族还有store）
首先关注参数
UseQueryResult mysqlpp::Query::use
(const char *
str, size_t
该方法主要是我们已经有了一个已经&成熟&的SQL语句，类似于，我们在UI工具上已经打好了，然后贴过去的那种，这种语句必须要已经经过了escape等转义工作，MYSQL++内部并不做进一步的转义操作。
UseQueryResult mysqlpp::Query::use ( const SQLTypeAdapter &
SQLTypeAdaper就是这个可以将普通的，未经过转义的SQL语句，或者如果&基本语句&（就是上面mysqlpp::Connection.query()传入的char*)是一个template query，那么可以将某个int，double等转义为可以供底层API所使用的string形式的工具类。（例如，如果update set a = 'sth' , 在这个sth周围就需要加上引号，而我们所传入的可能就是这个&sth&）
所以这个方法的工作就是将原始的SQL字符串 str 转换为可以供底层API所使用的字符串
UseQueryResult mysqlpp::Query::use ( SQLQueryParms &
SQLQueryParms是一个为了template query而准备的PARAM相关的类型，例如，如果有类似于这样的sql template 语句（可能和实际的MYSQL++用法不一致，只是表意）
select * from where id = %d, name = &%s&.
则我们可以这样设置SQLQueryParms
sqp && 1 && "root";
UseQueryResult mysqlpp::Query::use ( )
这个方法就是使用&基本语句&进行逐条执行。
再来关注返回值
为了讲述方便，我们先来看一下在result.h（Declares classes for holding information about SQL query）中所定义的各种类型的关系。这几个类型不在这里做仔细介绍，可以查看相关章节。
bool exec(const std::string& str);
这里返回的是bool，表示的是&语句是否执行成功&，我认为适合于那种update, delete，insert，且都不关心有多少rows被touch的情况。
SimpleResult execute();
这里的SimpleResult正如其名，其中只有如下信息
the last value used for an AUTO_INCREMENT field
（ulonglong insert_id() const）
the number of rows affected by the query
（ulonglong rows( ) const）
any additional information about the query returned by the server（const char* info( ) const）
UseQueryResult use();
由于use的语义类似于使用游标，也就是支持一行一行地拉出内容，所以UseQueryResult 也就自然而然地支持一些关于fetch row的功能。
StoreQueryResult store();
StoreQueryResult它本身就是从vector&Row&继承而来，所以它就是vector。所以用户程序可以直接使用下标的形式来获取所有的ROW。这也就是说在这个store之后，所有的ROW的内容都在了这个vecor&Row&里面了。
for_each，store_if，storein，storein_set，storein_sequence
这些方法都是模仿STL中的&algorithm&里面的迭代器的处理函数，具体可以看看文档。
insert，update，insertfrom，replace，replacefrom
这些方法都是给SSQLS所使用的。
1) insert policy
首先我们看到了在mysqlpp::Query类型的一开始，就在private定义下面定义了一个#include
通过查看这个头文件，他定义了一系列的辅助类型，他们控制着在哪些条件下ROW是可以被插入的。文档中说，这些类型都是为SSQLS服务的（核心作用是在Query::insertfrom() ）
他们的核心方法是一个叫做can_add的方法
RowCountInsertPolicy&&如果当前已经插入的rows查过了某个threshold，那么can_add就返回false
SizeThresholdInsertPolicy&&如果当前想要插入的row的大小大于预设的threshold，那么can_add就返回false
MaxPacketInsertPolicy&&如果当前已经插入的rows的总体的大小查过了某个threshold，那么can_add就返回false
2） 最简单的实现流程（普通query，而非SSQLS或者template query）
我们假设有如下的调用Query代码
conn.connect(mysqlpp::examples::db_name, "127.0.0.1",
"root", "root");
mysqlpp::Query query = conn.query("select item from stock");
mysqlpp::StoreQueryResult res = query.store();
mysqlpp::Query query = conn.query("select item from stock");
当代码进入到Connection::query的时候，该函数实际上在栈上新添了一个mysqlpp::Query对象，此时调用的构造函数是
需要注意的是两个变量
sbuffer，他是一个std::stringbuf，用来存储assembled query。另外，init和imbue方法都是std::ostream的方法。
template_defaults，这是一个SQLQueryParms，这个变量实际上视为template query做准备的，他用作&default template parameters, used for filling in parameterized queries&
注意看到上面的红色框中的内容
初始化列表中的内容都会在进入构造器之前完成，所以如果有类型在初始化列表中被构造（如下面的a(1)，和上面例子中的template_defailt(this)），编译器都会在包含类型构造之前先行构造。（我原来以为，被一个包含类型只会被调用无参默认构造函数，其实可以在成员初始化列表中调用其他构造函数）
如果有如下代码
A(int i) { cout && "in cons A " && i && endl；}
B() : a(1) { cout && "in cons B" && endl；}
输出结果将会是
in cons A 1
根据上面的理论，template_defaults(this)，实际上是调用了SQLQueryParms(Query * q)这个构造函数来构造SQLQueryParms.
其他的都是将sql语句加入到sbuffer中。但是需要注意的是上面提到的imbue用法，我们需要使用的是最通用的locale。
query.store( );
AutoFlag其实就是一个RAII，在构造函数中将 template_defaults.processing_ 置为 true，在析构函数中将其置为false
Query: ：store有以下四个不同的overload，具体的不同点请参看上文，以下四者殊途同归，最终调用的都是最后一个版本
StoreQueryResult store();
StoreQueryResult store(SQLQueryParms& p);
StoreQueryResult store(const SQLTypeAdapter& str);
StoreQueryResult store(const char* str, size_t len);
再来看一下Query: ：str( SQLQueryParms)的源代码
先解释一下什么是parse_elems_，他的定义是 std::vector&SQLParseElement& parse_elems_; 表示 List of template query parameters
所以很显然，在我们的这一章节所关注的用法中，Query:: str( SQLQueryParms& )直接返回的就是sbuffer_.str( ），也就是构造Query时候所传入的SQL语句。
当回到Query:: store( )的时候，该方法直接调用的就是Query:: store(const SQLTypeAdapter& )，其中str的返回值（string）到SQLTypeAdapter是隐式转换并进行构造的（因为SQLTypeAdapter有一个以string为param的构造器）
该方法又会调用最为核心的store版本。StoreQueryResult store(const char* str, size_t len);（else里面的那句）
该版本其实也不难理解，
523行到530行这一段是对template query的处理，这里不涉及，先略过。
从531行开始然后就是调用DbDriver的exec( )方法进行SQL语句执行，并通过DbDriver:: store_result( ) 方法获取表示结果集的 MYSQL_RES。
536行开始，针对res的结果进行处理，这里强调区分了SQL语句无返回值和SQL语句执行失败（res返回的是null，但是sql_error==0）两种情况。
对于非template query，需要做的是直接reset(调用Query:: reset( ))，然后构造一个StoreQueryResult返回
最后来说一下这个Query:: reset( )函数
该方法直接将ostream的buffer放空，并调整指针位置，重置所有的变量，防止下一次的执行情况和这一次的冲突。
最后需要说明的一点是，对于MYSQL C API而言，需要返回结果行的语句（如select)和不需要返回结果行的语句（如insert, delete)都是通过mysql_query( )函数进行的，所以其实在mysqlpp:: Query类型中，store( )和execute( )系列方法在最终执行上是一致的，即都是通过调用DbDriver:: execute(const char* qstr, size_t length)进行的。所以store( )和execute( )的唯一区别在于store( )其实最终返回的是带有结果的 StoreQueryResult类型，而execute( )返回的是 SimpleResult
原创作品，转载请注明出处。
阅读(...) 评论()当前位置：&>&&>&&>&
mysql count函数查询时if语句忽略空值
发布时间：编辑：
本文介绍了mysql count函数中使用if语句进行条件查询的方法，使用count求总数，但会忽略null空值，需要的朋友参考下。
例子， count函数用法，if语句实现按条件返回统计结果。
复制代码 代码示例:
mysql& CREATE TABLE Employee(
&&& -&&&&& id&&&&&&&&&&& int,
&&& -&&&&& first_name&&& VARCHAR(15),
&&& -&&&&& last_name&&&& VARCHAR(15),
&&& -&&&&& start_date&&& DATE,
&&& -&&&&& end_date&&&&& DATE,
&&& -&&&&& salary&&&&&&& FLOAT(8,2),
&&& -&&&&& city&&&&&&&&& VARCHAR(10),
&&& -&&&&& description&& VARCHAR(15)
Query OK, 0 rows affected (0.02 sec)
--添加数据
mysql& insert into Employee(id,first_name, last_name, start_date, end_Date,&& salary,& City,&&&&&& Description)
&&& -&&&&&&&&&&&&&& values (1,'Jason',&&& 'Martin',& '',& '', 1234.56, 'Toronto',& 'Programmer');
Query OK, 1 row affected (0.00 sec)
mysql& insert into Employee(id,first_name, last_name, start_date, end_Date,&& salary,& City,&&&&&& Description)
&&& -&&&&&&&&&&&&&&& values(2,'Alison',&& 'Mathews',& '', '', 6661.78, 'Vancouver','Tester');
Query OK, 1 row affected (0.00 sec)
mysql& insert into Employee(id,first_name, last_name, start_date, end_Date,&& salary,& City,&&&&&& Description)
&&& -&&&&&&&&&&&&&&& values(3,'James',&&& 'Smith',&&& '', '', 6544.78, 'Vancouver','Tester');
Query OK, 1 row affected (0.00 sec)
mysql& insert into Employee(id,first_name, last_name, start_date, end_Date,&& salary,& City,&&&&&& Description)
&&& -&&&&&&&&&&&&&&& values(4,'Celia',&&& 'Rice',&&&& '', '', 2344.78, 'Vancouver','Manager');
Query OK, 1 row affected (0.00 sec)
mysql& insert into Employee(id,first_name, last_name, start_date, end_Date,&& salary,& City,&&&&&& Description)
&&& -&&&&&&&&&&&&&&& values(5,'Robert',&& 'Black',&&& '', '', 2334.78, 'Vancouver','Tester');
Query OK, 1 row affected (0.01 sec)
mysql& insert into Employee(id,first_name, last_name, start_date, end_Date,&& salary,& City,&&&&&& Description)
&&& -&&&&&&&&&&&&&&& values(6,'Linda',&&& 'Green',&&& '', '', 4322.78,'New York',& 'Tester');
Query OK, 1 row affected (0.00 sec)
mysql& insert into Employee(id,first_name, last_name, start_date, end_Date,&& salary,& City,&&&&&& Description)
&&& -&&&&&&&&&&&&&&& values(7,'David',&&& 'Larry',&&& '', '', 7897.78,'New York',& 'Manager');
Query OK, 1 row affected (0.00 sec)
mysql& insert into Employee(id,first_name, last_name, start_date, end_Date,&& salary,& City,&&&&&& Description)
&&& -&&&&&&&&&&&&&&& values(8,'James',&&& 'Cat',&&&& '',& '', 1232.78,'Vancouver', 'Tester');
Query OK, 1 row affected (0.00 sec)
mysql& select * from E
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| id&& | first_name | last_name | start_date | end_date&& | salary& | city&&&&& | description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
|&&& 1 | Jason&&&&& | Martin&&& |
| 1234.56 | Toronto&& | Programmer& |
|&&& 2 | Alison&&&& | Mathews&& |
| 6661.78 | Vancouver | Tester&&&&& |
|&&& 3 | James&&&&& | Smith&&&& |
| 6544.78 | Vancouver | Tester&&&&& |
|&&& 4 | Celia&&&&& | Rice&&&&& |
| 2344.78 | Vancouver | Manager&&&& |
|&&& 5 | Robert&&&& | Black&&&& |
| 2334.78 | Vancouver | Tester&&&&& |
|&&& 6 | Linda&&&&& | Green&&&& |
| 4322.78 | New York& | Tester&&&&& |
|&&& 7 | David&&&&& | Larry&&&& |
| 7897.78 | New York& | Manager&&&& |
|&&& 8 | James&&&&& | Cat&&&&&& |
| 1232.78 | Vancouver | Tester&&&&& |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
8 rows in set (0.00 sec)
--select count if语句查询
mysql& SELECT COUNT(IF(salary&5000,1,NULL))
&&& -& FROM
+-------------------------------+
| COUNT(IF(salary&5000,1,NULL)) |
+-------------------------------+
|&&&&&&&&&&&&&&&&&&&&&&&&&&&& 3 |
+-------------------------------+
1 row in set (0.00 sec)
mysql& drop table E
Query OK, 0 rows affected (0.00 sec)
与 mysql count函数查询时if语句忽略空值 有关的文章
本文标题：
本页链接：
12345678910
12345678910Keyboard Shortcuts?
Next menu item
Previous menu item
Previous man page
Next man page
Scroll to bottom
Scroll to top
Goto homepage
Goto search(current page)
Focus search box
Change language:
Brazilian Portuguese
Chinese (Simplified)
mysql_query
mysql_query & Send a MySQL query
This extension was deprecated in PHP 5.5.0, and it was removed in PHP 7.0.0.
Instead, the
extension should be used.
for more information.
Alternatives to this function include:
Description
mysql_query
( string $query
[, resource $link_identifier = NULL
Parameters
An SQL query
The query string should not end with a semicolon.
Data inside the query should be .
link_identifier
The MySQL connection. If the
link identifier is not specified, the last link opened by
is assumed. If no such link is found, it
will try to create one as if
had been called
with no arguments. If no connection is found or established, an
E_WARNING level error is generated.
Return Values
For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning resultset,
mysql_query()
on success, or FALSE on
For other type of SQL statements, INSERT, UPDATE, DELETE, DROP, etc,
mysql_query() returns TRUE on success
or FALSE on error.
The returned result resource should be passed to
, and other
functions for dealing with result tables, to access the returned data.
to find out how many rows
were returned for a SELECT statement or
to find out how many
rows were affected by a DELETE, INSERT, REPLACE, or UPDATE
statement.
mysql_query() will also fail and return FALSE
if the user does not have permission to access the table(s) referenced by
the query.
Example #1 Invalid Query
The following query is syntactically invalid, so
mysql_query() fails and returns FALSE.
&?php$result&=&mysql_query('SELECT&*&WHERE&1=1');if&(!$result)&{&&&&die('Invalid&query:&'&.&mysql_error());}?&
Example #2 Valid Query
The following query is valid, so mysql_query()
returns a .
&?php//&This&could&be&supplied&by&a&user,&for&example$firstname&=&'fred';$lastname&&=&'fox';//&Formulate&Query//&This&is&the&best&way&to&perform&an&SQL&query//&For&more&examples,&see&mysql_real_escape_string()$query&=&sprintf("SELECT&firstname,&lastname,&address,&age&FROM&friends&&&&&WHERE&firstname='%s'&AND&lastname='%s'",&&&&mysql_real_escape_string($firstname),&&&&mysql_real_escape_string($lastname));//&Perform&Query$result&=&mysql_query($query);//&Check&result//&This&shows&the&actual&query&sent&to&MySQL,&and&the&error.&Useful&for&debugging.if&(!$result)&{&&&&$message&&=&'Invalid&query:&'&.&mysql_error()&.&"\n";&&&&$message&.=&'Whole&query:&'&.&$query;&&&&die($message);}//&Use&result//&Attempting&to&print&$result&won't&allow&access&to&information&in&the&resource//&One&of&the&mysql&result&functions&must&be&used//&See&also&mysql_result(),&mysql_fetch_array(),&mysql_fetch_row(),&etc.while&($row&=&mysql_fetch_assoc($result))&{&&&&echo&$row['firstname'];&&&&echo&$row['lastname'];&&&&echo&$row['address'];&&&&echo&$row['age'];}//&Free&the&resources&associated&with&the&result&set//&This&is&done&automatically&at&the&end&of&the&scriptmysql_free_result($result);?&
- Open a connection to a MySQL Server
- Returns the text of the error message from previous MySQL operation
- Escapes special characters in a string for use in an SQL statement
- Get result data
- Fetch a result row as an associative array
- Send an SQL query to MySQL without fetching and buffering the result rows.
Simulating an atomic operation for application locks using mysql.$link = mysql_connect('localhost', 'user', 'pass');if (!$link) {& & die('Not connected : ' . mysql_error());}// make foo the current db$db_selected = mysql_select_db('foo', $link);if (!$db_selected) {& & die ('Can\'t use foo : ' . mysql_error());}$q = "update `table` set `LOCK`='F' where `ID`='1'";$lock = mysql_affected_rows();If we assume& && NOT LOCKED = "" (empty string)& && LOCKED = 'F'then if the column LOCK had a value other than F (normally should be an empty string) the update statement sets it to F and set the affected rows to 1. Which mean than we got the lock.If affected rows return 0 then the value of that column was already F and somebody else has the lock.The secret lies in the following statement taken from the mysql manual:"If you set a column to the value it currently has, MySQL notices this and does not update it."Of course all this is possible if the all application processes agree on the locking algorithm.
Use this to neatly insert data into a mysql table:&?phpfunction mysql_insert($table, $inserts) {& & $values = array_map('mysql_real_escape_string', array_values($inserts));& & $keys = array_keys($inserts);& & & & & & return mysql_query('INSERT INTO `'.$table.'` (`'.implode('`,`', $keys).'`) VALUES (\''.implode('\',\'', $values).'\')');}?&For example:&?phpmysql_insert('cars', array(& & 'make' =& 'Aston Martin',& & 'model' =& 'DB9',& & 'year' =& '2009',));?&
mysql_query doesnt support multiple queries, a way round this is to use innodb and transactionsthis db class/function will accept an array of arrays of querys, it will auto check every line for affected rows in db, if one is 0 it will rollback and return false, else it will commit and return true, the call to the function is simple and is easy to read etc----------class MySQLDB{&& private $& & & & & // The MySQL database connection&& /* Class constructor */&& function MySQLDB(){& & & /* Make connection to database */& & & $this-&connection = mysql_connect(DB_SERVER, DB_USER, DB_PASS) or die(mysql_error());& & & mysql_select_db(DB_NAME, $this-&connection) or die(mysql_error());&& }&& /* Transactions functions */&& function begin(){& & & $null = mysql_query("START TRANSACTION", $this-&connection);& & & return mysql_query("BEGIN", $this-&connection);&& }&& function commit(){& & & return mysql_query("COMMIT", $this-&connection);&& }&& && function rollback(){& & & return mysql_query("ROLLBACK", $this-&connection);&& }&& function transaction($q_array){& & & && $retval = 1;& & & $this-&begin();& & & && foreach($q_array as $qa){& & & & & & $result = mysql_query($qa['query'], $this-&connection);& & & & & & if(mysql_affected_rows() == 0){ $retval = 0; }& & & && }& & & if($retval == 0){& & & && $this-&rollback();& & & &&& & & }else{& & & && $this-&commit();& & & &&& & & }&& }};/* Create database connection object */$database = new MySQLDB;// then from anywhere else simply put the transaction queries in an array or arrays like this:&& function function(){& & & global $& & & $q = array ( & & & && array("query" =& "UPDATE table WHERE something = 'something'"),& & & && array("query" =& "UPDATE table WHERE something_else = 'something_else'"),& & & && array("query" =& "DELETE FROM table WHERE something_else2 = 'something_else2'"),& & & );& & & $database-&transaction($q);&& }
here's a script for parsing a *.sql file (tested only on dumps created with phpMyAdmin) which is short and simple (why do people say "here's a short and simple script" and it has a 100 lines?). the script skips
to be present within the querys&?php& function parse_mysql_dump($url){& & $file_content = file($url);& & $query = "";& & foreach($file_content as $sql_line){& & & if(trim($sql_line) != "" && strpos($sql_line, "--") === false){& & & & $query .= $sql_line;& & & & if(preg_match("/;[\040]*\$/", $sql_line)){& & & & & $result = mysql_query($query)or die(mysql_error());& & & & & $query = "";& & & & }& & & }& & }& }?&
It should be noted that mysql_query can generate an E_WARNING (not documented).& The warning that I hit was when the db user did not have permission to execute a UDF. Expected behavior would be like an Invalid SQL statement, where there is no E_WARNING generated by mysql_query.Warning: mysql_query() [function.mysql-query]: Unable to save result set in filename.phpThe mysql_errno is 1370 and the mysql_error is:execute command denied to user 'username'@'%' for routine 'database_name.MyUDF'
Keep in mind when dealing with PHP & MySQL that sending a null-terminated string to a MySQL query can be misleading if you use echo($sql) in PHP because the null terminator may not be visible.For example (this assumes connection is already made),$string1 = "mystring\0";$string2 = "mystring";$query1 = "SELECT * FROM table WHERE mystring='".$string1."'"$query2 = "SELECT * FROM table WHERE mystring='".$string2."'" $result1 = mysql_query($query1);$result2 = mysql_query($query2);//$result1 IS NOT EQUAL TO $result2 but will not provide an error//but printing these queries to the screen will provide the same resultecho($result1);echo($result2);Not knowing this could lead to some mind-numbing troubleshooting when dealing with any strings with a null terminator.& So now you know! :)
When you run a select statement and receive a response, the data types of your response will be a string regardless of the data type of the column.&?php$query = 'SELECT user_id FROM users WHERE user_id = 1';$result = mysql_query($query);$array = mysql_fetch_assoc($result);echo gettype($array['user_id']);?&
If, like me, you come from perl, you may not like having to use sprintf to 'simulate' placeholders that the DBI package from perl provides. I have created the following wrapper function for mysql_query() that allows you to use '?' characters to substitute values in your DB queries. Note that this is not how DBI in perl handles placeholders, but it's pretty similar.&?php& & function mysql_prepare ($query, $phs = array()) {& & & & foreach ($phs as $ph) {& & & & & & $ph = "'" . mysql_real_escape_string($ph) . "'";& & & & & & $query = substr_replace(& & & & & & & & $query, $ph, strpos($query, '?'), 1& & & & & & );& & & & }& & & & return mysql_query($query);& & }& & list($user, $passwd) = array('myuser', 'mypass');& & $sth = mysql_prepare(& & & & 'select userid from users where userid=? and passwd=?',& & & & array($user, sha1($passwd))& & );& & $row = mysql_fetch_row($sth);& & if ($row !== false) {& & & & echo "logging in as '{$row[0]}'!\n";& & }& & else {& & & & echo "Invalid username and password combination.\n";& & }?&
When trying to INSERT or UPDATE and trying to put a large amount of text or data (blob) into a mysql table you might run into problems.
In mysql.err you might see:
Packet too large (73904)
To fix you just have to start up mysql with the option -O max_allowed_packet=maxsize
You would just replace maxsize with the max size you want to insert, the default is 65536
this could be a nice way to print values from 2 tables with a foreign key. i have not yet tested correctly but it should work fine.$buscar = mysql_query("SELECT k.*, e.Clasificacion FROM cat_plan_k k, cat_equipo e WHERE Tipo='$tipo' AND k.ID_Eq=a.ID_Eq"); & & while ($row=mysql_fetch_array($buscar))& & & & {& & & & & & $nombre = "e.Clasificacion"; & & & & & & $row[$nombre] = $C echo $row[$nombre].'convertido en '.$C& & & & }& & & & & & & mysql_free_result($buscar);
Until this function prohibits them, watch out for SQL comments (--) in your input.
Dunno if is it a bug but when you are working with replications servers and work with multiple databases queries if you don't select the database it will only insert,update,delete into the master and bypass the slave, I think it its because it doesn't insert the sql on the binary log so the work around its to just call mysql_select_db MYSQL : 5.0.51a-logPHP: 5.2.6Example:&?php$link=mysql_connect('host','user','pass');$sql ="INSERT INTO mysql.host (host) VALUES ('localhost');"var_dump(mysql_query($sql,$link));$link2=mysql_connect('host','user','pass');$select_db = mysql_select_db('mysql', $link2);var_dump(mysql_query($sql,$link2));&& ?&
Here's a parameterised query function for MySQL similar to pg_query_params, I've been using something similar for a while now and while there is a slight drop in speed, it's far better than making a mistake escaping the parameters of your query and allowing an SQL injection attack on your server.&?php&& if( !function_exists( 'mysql_query_params' ) ) {& & & & & & & & function mysql_query_params__callback( $at ) {& & & & & & & & & & & & global $mysql_query_params__parameters;& & & & & & & & & & & & return $mysql_query_params__parameters[ $at[1]-1 ];& & & & & & & & }& & & & & & & & function mysql_query_params( $query, $parameters=array(), $database=false ) {& & & & & & & & & & & & global $mysql_query_params__parameters;& & & & & & & & & & & & foreach( $parameters as $k=&$v )& & & & & & & & & & & & & & & & $parameters[$k] = ( is_int( $v ) ? $v : ( NULL===$v ? 'NULL' : "'".mysql_real_escape_string( $v )."'" ) );& & & & & & & & & & & & $mysql_query_params__parameters = $parameters;& & & & & & & & & & & & if( false===$database )& & & & & & & & & & & & & & & & return mysql_query( preg_replace_callback( '/\$([0-9]+)/', 'mysql_query_params__callback', $query ) );& & & & & & & & & & & & else& & return mysql_query( preg_replace_callback( '/\$([0-9]+)/', 'mysql_query_params__callback', $query ), $database );& & & & & & & & }& & & & }?&
Regarding the idea for returning all possible values of an enum field, the mySQL manual says that "SHOW COLUMNS FROM table LIKE column" should be used to do this.The function below (presumes db connection) will return an array of the possible values of an enum.function GetEnumValues($Table,$Column)& & {& & $dbSQL = "SHOW COLUMNS FROM ".$Table." LIKE '".$Column."'";& & $dbQuery = mysql_query($dbSQL);& & $dbRow = mysql_fetch_assoc($dbQuery);& & $EnumValues = $dbRow["Type"];& & $EnumValues = substr($EnumValues, 6, strlen($EnumValues)-8); & & $EnumValues = str_replace("','",",",$EnumValues);& & return explode(",",$EnumValues);& & }Cavaets:1) If the LIKE matches more than one column you get the enum from the first, so be careful with the $Column argument2) You can't have ',' as part of one of the enums (I guess mySQL would escape this, but I haven't tried)3) If the field isn't an enum you'll get garbage back!This is just a quick example to show how to do it, some tidying up needs to be done (ie checking if the field is actually an enum) before it is perfect.
Note that the 'source' command used in the mysql client program is *not* a feature of the server but of the client.This means that you cannot do&& mysql_query('source myfile.sql');You will get a syntax error. Use LOAD DATA INFILE as an alternative.
For all you programmers out there getting the 'Command out of synch' errors when executing a stored procedure call:There are known bugs related to this issue, and the best workaround for avoiding this error seems to be switching to mysqli.Still, I needed mysql to also handle these calls correctly.The error is normally related to wrong function call sequences, though the bug report at&
shows otherwise.For me, after commenting out hundreds of lines and several introspection calls to parse the procedure information (using information_schema and 'SHOW' extensions), I still got the same error.The first result is returned, because I initiated my connection using the MYSQL_MULTI_RESULTS value of 131072 (forget this and you will never get any output, but an error message stating mysql cannot return results in this context)After testing with this code (sproc2 simply calls 'SELECT * FROM sometable'), I found the error must be in the mysql library/extension. Somehow, mysql does not handle multiple resultsets correctly, or is at least missing some functionality related to handling multiple results.&?php& & $rs = mysql_query('CALL sproc2(500)');& & while (($row=mysql_fetch_assoc($rs))!==false) {& & & & print_r($row);& & }& & mysql_free_result($rs);& & $rs = mysql_query('CALL sproc2(500)');& & print mysql_error(); while (($row=mysql_fetch_assoc($rs))!==false) {& & & & print_r($row);& & }& & mysql_free_result($rs);?&After spending hours debugging my code (the full library is already over the MB), the only solution seemed to be to CLOSE the connection after the first call, and reopening it before the second. So if you ever make a uniform database accessing interface and implement stored procedures/prepared statements (or classes for it), this could be a solution if you really wish to enable stored procedures.Still, be aware that this is really a serious flaw in your design (and IMHO, the mysql extension)Also see the documentation for mysqli on mysqli_query, which seems to be working fine.
I much prefer to use the same syntax for single INSERT, REPLACE and UPDATE queries as it is easier to read and keeps my code shorter (no seperate building of insert and update values)INSERT INTO table SET x='1', y=3UPDATE table SET x='2' WHERE y=3So if your using a function to build your query, you will only ever need to code the "field=value, field2=value2" part for any query.
I believe there is a typo in celtic at raven-blue dot com version with:if (($sql != "") && (substr($tsl, 0, 2) != "--") && (substr($tsl, 0, 1) != "#")) {I think you really ment: if (($tsl != "") && (substr($tsl, 0, 2) != "--") && (substr($tsl, 0, 1) != "#")) {I changed the $sql to $tsl
The following query is not valid as expected:&?php$username = 'dicteworld';$username{4} = '';$sql = "SELECT * FROM `user` WHERE `User` = '$username'";print($sql); $res = mysql_query($query);$row = mysql_fetch_array($res);print_r($row);?&Pay more attention that null string '' is equivalent to '\0',therefore SQL statement above is equivalent to SELECT * FROM `user` WHERE `User` = 'dict\0world',though printing string is right.
For those of you whom spent hours bashing your brains against the keyboard wondering why your non-English characters are output as question marks... Try the following:
$db = mysql_connect('YOUR_DB_ADDRESS','YOUR_DB_USER','YOUR_DB_PASS') or die("Database error");
mysql_select_db('YOUR_DB', $db);
$result = mysql_query("set names 'utf8'");
$query = "select * from YOUR_DB_TABLE";
$result = mysql_query($query);
Simply run the query "set names 'utf8' " against the MySQL DB and your output should appear correct.
When processing a RENAME TABLE query, PHP apparently always returns false, no matter if the query was successfully processed or not.
One way to reduce the dangers of queries like the dlete command above that dletes the whole DB is to use limits wherever possible. EG. If you have a routine that is only deisnged to delete 1 record, add 'LIMIT 1' to the end of the command. This way you'll only lose one record if someone does something stupid.You should also check all input, especially if it is sent using GET. ie. make sure that $_GET['id'] is not NULL or == "", is a number that is positive, not 0 (generally, I know this doesn't apply to some table types, but it applies to the default) and is within the valid range for that field.Just don't trust ANY data that is sent to your script.HTHAllen
Windows programmers, keep in mind that although table names in Windows queries are not case sensitive, many *NIX versions of Mysql require the correct table name case (perhaps others as well). So you're better off using the right case from the beginning, in case you ever decide to go with a *NIX server.
I think it's important to note (for newbies, like me especially) that an empty result is not the same as an error:&?php$rs = mysql_query("SELECT `foo` FROM `bar`")if($rs) {& echo mysql_num_rows($rs); }
If you need to execute sevaral SQL commands in a row (usually called batcg SQL) using PHP you canot use mysql_query() since it can execute single command only.Here is simple but effective function that can run batch SQL commands. Take cere, if string contains semicolon (;) anywhere except as command delimiter (within string expression for example) function will not work.function mysql_exec_batch ($p_query, $p_transaction_safe = true) {& if ($p_transaction_safe) {& & & $p_query = 'START TRANSACTION;' . $p_query . '; COMMIT;';& & };& $query_split = preg_split ("/[;]+/", $p_query);& foreach ($query_split as $command_line) {& & $command_line = trim($command_line);& & if ($command_line != '') {& & & $query_result = mysql_query($command_line);& & & if ($query_result == 0) {& & & && & & };& & };& };& return $query_}