| how to enable slow_query_log in mysql for log the slow executed queries in mysql |
Depending on your version, the relevant options could be
slow_query_log_file and slow-query-log (the log-slow-queries option has
been deprecated since v5.1.29 and was removed in v5.6.1)
Whatever your version, you also need to adjust the values of
long_query_time and min_examined_row_limit
Read also: 5.2.5. The Slow Query Log
|
| Causes of MySQL error 2014 Cannot execute queries while other unbuffered queries are active |
I am hoping for a better answer than the following. While some of these
solutions might "fix" the problem, they don't answer the original question
regarding what causes this error.
Set PDO::ATTR_EMULATE_PREPARES=>true (I don't wish to do this)
Set PDO::MYSQL_ATTR_USE_BUFFERED_QUERY (didn't work for me)
Use PDOStatement::fetchAll() (not always desirable)
Use $stmt->closeCursor() after each $stmt->fetch() (this mostly
worked, however, I still had several cases where it didn't)
Change PHP MySQL library from php-mysql to php-mysqlnd (probably what I
will do if no better answer)
|
| Is it possible to combine MySQL queries to multiple tables into a single query based on the results from one of the queries? |
You want to look at MySQL Joins.
I think this may do something like what you're after, but it will almost
definitely need debugging!
SELECT DISTINCT s.ownerid, s.message
FROM statusupdates s
LEFT JOIN friends f ON ($userid = f.requestfrom)
LEFT JOIN friends f ON ($userid = f.requestto)
ORDER BY s.createddate;
|
| Linking different schemas from different mysql servers into only one mysql server so he can manage the queries |
MySQL Proxy:
The MySQL Proxy is an application that communicates over the network
using the MySQL network protocol and provides communication between one or
more MySQL servers and one or more MySQL clients.
However, note:
Warning
MySQL Proxy is currently an Alpha release and should not be used within
production environments.
The FEDERATED Storage Engine:
The FEDERATED storage engine lets you access data from a remote MySQL
database without using replication or cluster technology. Querying a local
FEDERATED table automatically pulls the data from the remote (federated)
tables. No data is stored on the local tables.
Replication:
Replication enables data from one MySQL database server (the master) to
be replicated to one or more MySQL database servers (the slaves).
|
| Yii Get MySql query executed |
You can use the methods
class Objects extends CActiveRecord
{
protected function beforeSave()
{
// Your code goes here
}
protected function beforeDelete()
{
// Your code goes here
}
}
For Logging of query you refer this thread Logging
u can also see the log on the page by just uncommenting the follwing code
in config.main file
// uncomment the following to show log messages on web pages
array(
'class'=>'CWebLogRoute',
),
|
| how can i add condition in mysql query executed in another table? |
First, check out sql-injections before continuing to develop in your
ebook-application. Looks like your keyword is not checked to be a safe
parameter. And just to be sure, do you know about csrf and xss? If not,
check about that too. This is very important.
Secondaly, you should consider working on your database design to avoid
having duplicated values. Check out "database normalization" for more
information. Seems like you could do another table to extract your "contact
information" like name, state, id etc. This would make it possible for your
author-table and publisher-table to use a "contact_id" referencing the
contact-information-table.
Last but not least, to answer your question, you can generelly solve such
problems with an "anti join". Use a left join on the authors table in the
|
| why does SELECT statement doesn't get executed by EVENT scheduler in mysql? |
You can find the answer here:
http://dev.mysql.com/doc/refman/5.1/en/create-event.html
Answer:
Statements such as SELECT or SHOW that merely return a result set have no
effect when used in an event; the output from these is not sent to the
MySQL Monitor, nor is it stored anywhere. However, you can use statements
such as SELECT ... INTO and INSERT INTO ... SELECT that store a result.
(See the next example in this section for an instance of the latter.)
|
| MySQL efficiency - do repeated statements in one query get executed multiple times? |
Try to compare the execution times between:
SELECT
AVG(var1) AS var1_average,
AVG(var2) AS var2_average,
(
AVG(var1) +
AVG(var2)
)/2.0 AS total_average
FROM readings
And:
SELECT
(var1_average+var2_average)/2.0 AS total_average
var1_average,
var2_average
FROM (
SELECT
AVG(var1) AS var1_average,
AVG(var2) AS var2_average
FROM readings
) as tmp
Make sure to run them multiple times and also add SQL_NO_CACHE to get
meaningful results.
NOTE:
The second query should have a small overhead since mysql will create a
temp table but this will be irrelevant if there are a lot of records in
readings table.
|
| Query runs fine when executed on DB directly , but when executed using php it fails |
The problem is you can't use INTO OUTFILE statement in PHP. MySQL docs
says:
The SELECT ... INTO OUTFILE statement is intended primarily to let you
very quickly dump a table to a text file on the server machine. If you
want to create the resulting file on some other host than the server
host, you normally cannot use SELECT ... INTO OUTFILE since there is
no way to write a path to the file relative to the server host's file
system.
I think, their suggestion below can help you.
However, if the MySQL client software is installed on the remote
machine, you can instead use a client command such as mysql -e "SELECT
..." > file_name to generate the file on the client host.
If not, maybe there will be other way, but I'm sure you need to dig into
that way.
|
| MySQL Procedure: substring_index throwing exception from special characters (executed in bash) |
Your question is really too board, but here is an example of what I mean
a script file:
#!/bin/bash
case $# in
1 ) inFile=$1 ;;
* ) echo "usage: myLoader infile"; exit 1 ;;
esac
awk 'BEGIN {
FS=" "'; OFS="|"
}
{
sub(/badChars/, "", $0); sub(/otherBads/, "", $0) ; # .... as many as
needed
# but be careful, easy to delete stuff that with too broad a brush.
print $1, $2, $5, $4, $9
}' $inFile > $inFile.psv
bcp -in -f ${formatFile:-formatFile} $inFile.psv
Note how awk makes it very easy, by repeating sub(...) commands to remove
any "bad chars" you may have in your source data AND to reorganize the
order of the columns in your data. Each $n is the value in numbered column
on a line, so $1, $2, $5 skips fields $3 and $4, for example.
Th
|
| How to optimize queries in mysql |
When I am generating my queries with optional fields, I create an array of
each field, then join them with implode
$query_array = array();
$queries_stringed = '';
if(strlen($location) > 0){
$query_array[] = "location ='{$location}'";
};
if(strlen($minprice) > 0 && strlen($maxprice) > 0){
$query_array[] = "(price BETWEEN '$minprice' AND '$maxprice')";
};
if(!empty($query_array)){
$queries_stringed = "WHERE ".implode(" AND ", $query_array);
};
$result = mysql_query("SELECT * FROM room_tb $queries_stringed");
|
| How to refactor these MySQL queries with PHP? |
If you want one page which shows all sites, you can do the following: add
"?site=CHI001" to the end of your link.
In your script, this will show up as $_GET['site'] (you can use any name
you like).
Convert the variable with mysql_real_escape_string - `$site =
mysql_real_escape_string($_GET['site']);'
DO NOT! attempt to put the variable in directly - this WILL let an attacker
run any database command they like! See php.net article on SQL injection or
take my word for it - it is BAD!
Now you can add this value into your sql - replace WHERE site = 'CHI001'
with WHERE site = '$site'
Now you can test the page, for different sites.
|
| PHP, MYSQL Nested Queries |
In this case, you have the same columns coming out from the query. Some
are from Project and some are from Todo. The challenge is to order them in
the right way:
select which, name
from ((select 'Project' as which, name, 1 as ordering, projectid, NULL as
id
from Project p
) union all
(select 'List' as which, entry, 2 as ordering, projectid, id
from Todo t
)
) t
order by projectid,
ordering,
id;
|
| MySQL Joins and Queries |
SELECT name,clientid FROM clients a
JOIN maintenancewindow b ON a.clientid=b.clientid
JOIN locations c ON b.maintenanceid=c.maintenanceid
I`m assuming that is the result you want
The end goal is to get a list of my clients name, location, and
maintenance windows in readable format, ie the names of clients,
locations, maintenance windows versus the 'id's'.
This part is confusing.You mean a specific id?Just add WHERE id=someid.
|
| PDO much slower than MySQL queries |
The most PDO way to achieve the same (also taking advantage of UNION to
aggregate the results within the database layer):
public function numJobsMil()
{
$STH = $this->_pdo->dbh->prepare('
SELECT job_id FROM job_materials_lot WHERE lot = ?
UNION ALL
SELECT job_id FROM job_materials_prop WHERE lot = ?
');
$STH->execute([$this->_lot, $this->_lot]);
return $STH->fetchAll(PDO::FETCH_COLUMN, 0);
}
Whenever I see UNION used in this way, I cannot help but wonder whether the
two tables ought to be combined, with a column to flag from which table the
underlying records originated.
|
| mysql different select queries in one |
Shouldn't you group by post_ID ? (that would return only one line)
SELECT
comments.*
, COUNT(approved.comment_approved) AS "has_commented"
FROM wp_comments AS comments
JOIN wp_comments AS approved
ON (comments.comment_author = approved.comment_author)
WHERE comments.comment_post_ID = 14616
GROUP BY comments.comment_post_ID
;
Or do you want one line per "approved" comment ?
|
| Mysql queries don't seem to be working |
You're not actually issuing a query in your code:
<?php
include '../inc/connect.php';
if(isset($_POST['hide'])){
$chk = (array) $_POST['hide'];
$p = implode(',',array_keys($chk));
echo $p;
$t = "SELECT * FROM headerrotatorimage WHERE id IN ($p)";
echo $t;
// Execute query and process result set for $t
$s = "UPDATE headerrotatorimage SET status = 2 WHERE id IN ($p)";
echo $s;
// Execute query and process result set for $s
}
?>
|
| How can i join this 2 MySQL queries together |
In MS-SQL it's possible to select this as:
SELECT p.pid,
p.link,
p.created_at,
(SELECT AVG(rating) FROM ratings r where r.pid = p.pid) as rating
FROM products p
ORDER BY timestamp DESC
|
| Combining MySQL queries |
You can use subquery.
You can combine both queries into something like:
$search_query= "SELECT * FROM users WHERE fname LIKE '%".$fname."%' AND
lname LIKE
'%".$lname."%'" AND id in (SELECT id FROM relations where user1=
'".$user_id."' AND status= 1)"
|
| mysql 3 queries on 2 tables in one |
This is a query that requires a join and conditional aggregation:
select j.jobid, j.description, j.someinfo,
sum(case when il."type" = 'add' then amount else 0 end) as
AmountAdd,
sum(case when il."type" = 'rem' then amount else 0 end) as
AmountRem,
(sum(case when il."type" = 'add' then amount else 0 end) -
sum(case when il."type" = 'rem' then amount else 0 end)
) as totaladdrem
from tbl_jobs j left outer join
tbl_invlog il
on j.jobid = il.jobid
group by j.jobid, j.description, j.someinfo;
Note some things. First, the tables have table aliases, defined in the
from clause. This allows you to say which table the columns come from.
Second, the table aliases are always used for all columns in the query.
MySQL would allow you to just do group
|
| Avoid multiple MySql queries using php |
If I understand you correctly, you're fetching the entire set of products
in your category in the second query, but fetching just one page's worth in
the first query (e.g., items 10 through 19). I would just fetch all the
items with the second query, then load the rows into a PHP array and use
array_slice() to grab the segment of the array you need for the current
page.
EDIT: As others have said, the actual MySQL error may be the lack of the
space between SELECT and *, but you can also do what you're trying to do
without hitting the database twice.
|
| MySQL alternative to nested queries? |
select stuff, count(*) as votes
from comments c, comment_ratings cr
where c.id = cr.id
group by stuff
order by votes desc;
and as gordon mentioned, to not forget the comments with no rating.. go for
left join:
select stuff, count(cr.id) as votes
from comments c left join
comment_ratings cr on c.id = cr.id
group by stuff
order by votes desc;
http://sqlfiddle.com/#!2/79e54/2
|
| Compare queries on PostgreSQL and MySQL |
The repeated query hit the MySQL query cache, a feature which, I believe,
does not exist in PostgreSQL.
Repeat the test after disabling the query cache, or add the SQL_NO_CACHE
directive to your query to MySQL (SELECT SQL_NO_CACHE * FROM test...).
|
| how to run MySQL sub queries to count the records. |
From what I understand by your question, you need something like below
SELECT a.id, count(distinct b.id) Total_GroupB, count(distinct c.id)
Total_GroupC from GroupA a
LEFT JOIN GroupB b ON a.id=b.groupa_id
LEFT JOIN Groupc c ON b.id=c.groupb_id
GROUP BY a.id
|
| Mysql: Set time_zone for select queries, UTC for everything else |
User CONVERT_TZ() in mysql function
SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');
Ref :
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_convert-tz
|
| Is it possible to get all the mysql queries called on the page? |
There's actually a vQmod file you can install that will create a log file
for you with all of the database queries and their time taken to execute.
You can find the thread and XML file here
|
| What is the reason the counts are different in the mysql queries? |
Something like this is happening:
Persons table
ID Name
-- ------------
1 John Doe
2 Xaisoft
Colors table
PersonID ColorName
-------- ---------
1 Red
1 Yellow
2 Yellow
Now query Persons.* for John Doe while joining to Colors:
SELECT p.*
FROM Persons p
INNER JOIN Colors C ON c.PersonID = p.ID
WHERE p.Name = 'John Doe';
Your result will be one p.* row for each color under John Doe:
ID Name
-- ------------
1 John Doe
1 John Doe
If you apply a COUNT DISTINCT against this you'll end up with 1.
If you count Red and Yellow separately, you'll get one for each query. Add
them up and you'll get 2.
Look at it this way: if you throw out the COUNT and just SELECT DISTINCT
Persons.* you get these results:
Red or Yellow for John Doe:
SELECT DI
|
| Multiple MySQL queries from same table |
This should work for you using count with distinct:
select pin
from attributes
where attribute in (4,5)
group by pin
having count(distinct attribute) = 2
This will return any pins that have both attributes 4 and 5.
SQL Fiddle Demo
|
| Two MySQL queries giving different results |
Chances are the JOIN is including duplicated rows in your COUNT. If I'm
understanding your question correctly, assuming FavoritesHeaderID is unique
and that's the field you're trying to COUNT, you can add DISTINCT to each
query and they should return the same count:
select
count(distinct `FavoritesHeaderID`) `count`
from `favoritesheader`
join `vwactiveevent` on
`vwactiveevent`.`MainEventID`=`favoritesheader`.`MainEventID`
join `invoiceheader` on
`vwactiveevent`.`MainEventID`=`invoiceheader`.`MainEventID`
where `Admin`=0
and `Phone`=`favoritesheader`.`CellPhone`
and `OrderStatusID`=2
|
| Making a Counter in MySQL Queries |
How to do inremental values :
SELECT s.*,
@i:=@i+1 AS increnatal
FROM table_source AS s,
(SELECT @i:=0) AS inc
Exact insert i can provide after showing by You input.
INSERT INTO RESULT (COLUMNS)
SELECT '1-10218', 'FIRST', CONCAT('1-', @i:=@i+1), 'SECOND'
FROM table_source AS s,
(SELECT @i:=10219) AS inc
|
| Two queries mysql in one object json |
You were very close, but you want the People array to be a direct value of
the outer array and you've wrapped it in an extra array.
Also, please note that the MySQL library you are using is deprecated. That
means it will be removed from PHP in a future release. You should replace
calls from the MySQL_* family of functions with either mysqli or pdo
$result = mysql_query("SELECT * FROM data where id='123456'");
$fetch = mysql_query("SELECT name,age,city FROM people where id='123456'");
$json = array();
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)){
$json[] = $row;
}
$json['people'] = array();
while ($row = mysql_fetch_assoc($fetch)){
$row_temp["name"]=$row["name"];
$row_temp["age"] = $row["age"];
$row_temp["city"] = $row["city"];
array_push($json['pe
|
| Merge Two MySQL Queries in one Array |
Use the UNION clause:
$sql = "(SELECT * FROM `login` WHERE `user` = '$word')";
$sql.= " UNION";
$sql.= " (SELECT * FROM `login` WHERE `user` != '$word' ORDER BY RAND()
LIMIT 3)";
$sql.= " ORDER BY RAND()";
To get the results you can use for example MySQLi (poseted before OP added
his code with mysql_* functions):
$MySQL=new mysqli("localhost", "username", "password", "database");
$query = $MySQL -> query($sql);
while ($entry = $query -> fetch_row())
{
// $entry is an array with the results, use for example:
echo $entry[0]; // will return the first column of your table
echo $entry[1]; // will return the second column of your table
// try also:
var_dump($entry); // outputs everything for testing purposes
}
Please, don't use the mysql_* functions, they are dep
|
| Termination of PHP script between two mysql queries |
Use a transaction:
START TRANSACTION;
INSERT INTO foo ...
INSERT INTO bar ...
COMMIT
If either INSERT fails, you ROLLBACK the transaction and you won't be left
with "useless" records.
|
| Combining multiple queries in MySQL |
Those queries vary in complexity and what they are doing.
The first 3 could be combined into a single query returning (up to) 3
rows:-
SELECT action, COUNT(*) AS `count`
FROM `notifications`
WHERE `action` IN ( 'started_following', 'stopped_following',
'sent_message')
AND `subject` = '{$this->id}'
$sinceString
AND `notifications`.`user_id` != '{$this->id}'
GROUP BY action
To force that to return 3 rows you could possibly do something like this:-
SELECT WantedActions.WantedAction, COUNT(*) AS `count`
FROM (SELECT 'started_following' AS WantedAction UNION SELECT
'stopped_following' UNION SELECT 'sent_message') AS WantedActions
LEFT OUTER JOIN `notifications`
ON WantedActions.WantedAction = notifications.action
WHERE `subject` = '{$this->id}'
$sinceSt
|
| Flexible MySQL Search Queries |
I would split at the spaces then use LIKE on all the possible columns. Then
if there is a record that has the most hits, I would choose that one. It
may be possible to use the ORDER BY to do the counts for you so you don't
have to do it in PHP and the best record can be returned.
|
| MySQL Joining Results of Queries |
In join queries, you must put all your columns in the first select
statement. The second select statement in your query is invalid.
SELECT
t1.id as id, t2.id, t3.id, b.id
FROM
(table1 t1
inner join table2 t2 on t2.id=t1.id
inner join table3 t3 on t3.id=t2.id)
right join table4 b on t1.id = b.id
WHERE b.col1 IS NOT NULL AND b.col2 IN (1, 2))
|
| separate queries in mysql, db2 and oracle |
In Oracle SQL*PLUS you use semicolon ; as SQL terminator and . as PL/SQL
block
terminator. Unlike sql terminator(;), which terminates and executes SQL
statement,
PL/SQL block
terminator(.) only informs SQL*PLUS that the block has ended and left in
the buffer for further editing
if such a need arises or execution using slash /.
You can change SQl terminator by issuing SET SQLTERMINATOR ':' (for
example).
You can change PL/SQL block terminator by issuing SET BLOCKTERMINATOR
'!'(for example).
To terminate and execute a PL/SQL block you use forward slash /.
|
| Three similar MySql queries, one not working |
get_var returns a single value. for your queries on the bottom, try adding
limit 1 to them, so
$street = $wpdb->get_var($wpdb->prepare("SELECT
adcontact_countyvillage FROM $tbl_ads WHERE ad_id='$adid' LIMIT 1;"));
$plz = $wpdb->get_var($wpdb->prepare("SELECT adcontact_state FROM
$tbl_ads WHERE ad_id='$adid' LIMIT 1;"));
$city = $wpdb->get_var($wpdb->prepare("SELECT adcontact_city FROM
$tbl_ads WHERE ad_id='$adid' LIMIT 1;"));
|
| Creating complex IF, OR, NOT & AND SQL queries (MySQL) |
I think you have to take a step back and try to visualize your question on
paper first. Examples 1 and 2 are pretty easy, but let's look at example 3.
For conditions where all your criteria are 'AND' or 'OR' - things are very
simple. Just do a long WHERE, just liek before.
However, when you start mixing them you have to answer yourself a serious
question:
How do you split the conditions?
Lets say someone picked up those criteria:
and A
or B
and C
This gives you so many permutations of your query! eg:
(A or B) and C
A or (B and C)
(A and C) or B
If you add one more 'OR' to it, you will end it with tens of combinations
more! Leaving you in a place where you have to guess what to do. Don't even
want to think what would happen if there is a NOT involved...
This is not a direct an
|
| Chaining MySQL commands Vs. Raw queries |
I can't speak for CodeIgniter (what I've seen of it seems rather
slung-together, frankly), but there are a few reasons such systems may be
used:
as part of an abstraction layer which supports different DBMS back-ends,
where for instance ->offset(10)->limit(10) would automatically
generate the correct variant of OFFSET, LIMIT, and similar clauses for
MySQL vs PostgreSQL etc
as part of an "ORM" system, where the result of the query is automatically
mapped into Model objects of an appropriate class based on the tables and
columns being queried
to abstract away from the exact names of tables and columns for
backwards-compatibility, or installation requirements (e.g. the table
"news" might actually be called "app1_news" in a particular install to
avoid colliding with another application
|
