w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
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

Categories : Mysql

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)

Categories : PHP

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;

Categories : PHP

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).

Categories : Mysql

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', ),

Categories : PHP

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

Categories : PHP

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.)

Categories : Mysql

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.

Categories : Mysql

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.

Categories : PHP

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

Categories : Mysql

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");

Categories : PHP

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.

Categories : PHP

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;

Categories : PHP

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.

Categories : Mysql

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.

Categories : PHP

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 ?

Categories : Mysql

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 } ?>

Categories : PHP

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

Categories : PHP

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)"

Categories : PHP

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

Categories : Mysql

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.

Categories : PHP

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

Categories : Mysql

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...).

Categories : Mysql

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

Categories : Mysql

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

Categories : Mysql

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

Categories : Mysql

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

Categories : Mysql

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

Categories : Mysql

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

Categories : Mysql

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

Categories : Mysql

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

Categories : PHP

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

Categories : PHP

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.

Categories : PHP

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

Categories : Mysql

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.

Categories : Mysql

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))

Categories : Mysql

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 /.

Categories : Mysql

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;"));

Categories : Mysql

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

Categories : PHP

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

Categories : PHP



© Copyright 2017 w3hello.com Publishing Limited. All rights reserved.