w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
How to get a tree-like output of mysql select queries on command line?
You are referring to the G option, the vertical mode or according to mysql help the ego mode. mysql> SELECT * FROM posts LIMIT 1G To make it standard behaviour add the following to your .my.cnf: [client] vertical

Categories : Mysql

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

mysql multiple joins vs multiple queries
As a rule you will do better with fewest queries. The database has to do less work (19 fewer parses) the cache will probably be primed and the plan will be optimised for the problem at hand, not 5% of the problem. Add in network latency for the round trips and it's getting quite concincing. If, on the other hand, you find 20 separate queries cheaper then you may find the database is struggling with out of date stats or something and needs a bit of a tune up.

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

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

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

No output for multiple queries in mysql
Those zeros come from the sleep lines. Get rid of them, you don't need them. Also, for performance sake, if you are using a schema that supports transactions, you should add: START TRANSACTION INSERT... INSERT... INSERT... ... COMMIT

Categories : PHP

MySQL not using multiple column index for some queries
If you expect some of the queries to be game changer for the performance it is good idea to use you own indexes, like: EXPLAIN SELECT * FROM `test` USE INDEX (number) WHERE (`text` = 'bbbb' AND `number` = 1); or EXPLAIN SELECT * FROM `test` USE INDEX (text_number) WHERE (`text` = 'bbbb' AND `number` = 1); Generally, you can relay on the built-in query optimizer for most of the queries, but for the crucial or problematic ones it is good idea to look closer.

Categories : Mysql

multiple queries in one mysql connection fail to be run with php
Based on your comments, it appears that your query is inserting a duplicate value for the page_id value, which appears to be set as a field that cannot have duplicate values. According to your query, you're using $pageNumber for that field, but I don't see it changing in your loop. You either need to get rid of the constraint preventing you from using the same value or make sure that $pageNumber has a value that isn't being used already.

Categories : PHP

MySql: Calculate a value based on multiple sub-queries
This query is awkward because you have stored attributes on separate rows. This design is called Entity-Attribute-Value, and it's usually a bad idea for a relational database. The following query might be a little more efficient and easier to write: SELECT (eav.ReqRcvd - eav.AccSent) * 100 / eav.AccSent AS MyCalc FROM ( SELECT timestamp, MAX(IF(ref_type='S11S4CreateSessionReqRcvd', ref_data)) AS ReqRcvd, MAX(IF(ref_type='S11S4CreateSessionRespAccSent', ref_data)) AS AccSent FROM test.sgw_S5S11 WHERE timestamp = '2013-08-21 00:00:06' GROUP BY timestamp ) AS eav; PS: Use single quotes ' for string and date literals, not double quotes ". In standard SQL, double quotes are for delimiting table and column identifiers. MySQL treats the two types of quotes the

Categories : Mysql

mysql query to avoid multiple queries
Use Case statement Select col1, col2, col3, case when 0101 then x else '' end as 0101, case when 0202 then x else '' end as 0202, case when 0303 then x else '' end as 0303 from table. Vinit

Categories : Mysql

Codeigniter / PHP, 2 Mysql Tables, Multiple Queries
Its very hard to know when you haven't post your relationships in a table but taking some assumptions $query = "SELECT items.id as item_id, items.name as item_name, notes.id as note_id, notes.description as note_description, note_image.image as note_image from notes LEFT JOIN notes ON items.id = notes.item_id LEFT JOIN note_image ON notes.id = note_image.note_img_noteid"; //this wil fetch all you items with description, notes and images, because item can have multiple notes, your result wil have multiple entires of the item. so you have to index correctly to use in views $result = $this->db->query($query) $my_items = array(); foreach ($result->result() as $row){ if(!isset($my_items[$row->item_id])){ //you item it becaoms a key //here you

Categories : PHP

What does the ( ' > ) symbol mean in the command line in MySQL?
It means that it is treating any input which follows as part of a string literal, until it encounters a(n unescaped) string termination quote ' character. This will have happened because you previously began the string literal with such a string termination quote character. For example: mysql> SELECT foo -> FROM tbl -> WHERE bar LIKE 'somestring '> this is still part of somestring' -> ;

Categories : Mysql

Buffer MySQL queries or make multiple connections?
Making connections is expensive so it's probably best not to keep making them. However, holding one connection open all the time has its own problems ( what happens if it closes for some reason? ) Why not have a look at database connection pooling - google will show up several competing connection pool implementations for you. You'll get the best of both worlds. To your application, the connection will appear to be permanently open, but if the connection fails for some reason, the connection pool will handle re-opening it for you.

Categories : Java

MySQL Multiple UPDATE: many queries equivalent without foreach()
Check out the syntax for the UPDATE statement generally you can write UPDATE <table> SET <col1> = <val1>, <col2> = <val2> ... WHERE ... Similar post here

Categories : PHP

Combine multiple mysql queries to get result as a single row
For next Record(ID): SELECT * FROM `table_name` WHERE ID > $id ORDER BY ID LIMIT 1; For prev Record(ID): SELECT * FROM `table_name` WHERE ID < $id ORDER BY ID LIMIT 1; Here $id is a variable in php which changes according to your desire

Categories : PHP

Multiple mysql queries in Joomla eventlist function
Execute the first query before setting the second one. $this->_db->setQuery($query); $this->_db->query(); $this->_db->setQuery($query2); $this->_db->query();

Categories : PHP

Does anyone use command line exclusively for managing mysql DB?
You can use G at the end of your query to display table columns as rows and make it easier to see the result set mysql> select * from users; +---------+---------+-----------------------+------------------+------------------------------------------+---------------------+ | id_user | name | email | salt | hash | created_at | +---------+---------+-----------------------+------------------+------------------------------------------+---------------------+ | ... | ... | ... | ... | ... | ... | ... X row in set (0.00 sec) mysql> select * from usersG *************************** 1. row *************************** id_user:

Categories : Mysql

How do I set MySQL temporarily to read-only through the command line?
Well, if the user right now has all privileges first, you need to revoke it $>mysql -u DB_USER -pDB_PASS --execute="REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'YOUR_USER';" After that you give him, the select permission $>mysql -u DB_USER -pDB_PASS --execute="GRANT SELECT ON 'YOUR_DATABASE'@.* TO 'YOUR_USER'@'%';FLUSH PRIVILEGES;" Do your stuff and after that grant privileges again to the user $>mysql -u DB_USER -pDB_PASS --execute="GRANT ALL ON 'YOUR_DATABASE'@.* TO 'YOUR_USER'@'%';FLUSH PRIVILEGES;" And that's all folks NOTE: Review for quotation, perhaps i forgot something

Categories : Mysql

How to import an SQL file using the command line in MySQL?
Try: mysql -u username -p database_name < file.sql Check MySQL Options. Note: It is better to use the full path of the SQL file file.sql.

Categories : Mysql

Use mysql command line interface with memcached
Memcached has its own protocol. The MySQL client cannot connect directly to a memcached server. You may be thinking of the MySQL 5.6 feature that allows MySQL server to respond to connections using a memcached-compatible protocol, and read and write directly to InnoDB tables. See http://dev.mysql.com/doc/refman/5.6/en/innodb-memcached.html But this does not allow MySQL clients to connect to memcached -- it's the opposite, allowing memcached clients to connect to mysqld. Re your comment: The InnoDB memcached interface is not really a caching solution per se, it's a solution for using a familiar key/value API for persistent data in InnoDB tables. InnoDB does do transparent caching of data pages in its buffer pool, but this is no different from conventional data reads with SQL. Inno

Categories : Mysql

mysql command line to vertical output
I'm not sure this is a good solution, but if explicitly using egrep is annoying, you could define a shell function to launch mysql with the desired pager. Assuming you're using a bash (or compatible): # define a shell function to launch mysql with the required _pager_ sh$ mysql() { `which mysql` $* --pager="egrep -v '^*.**$'" ; } [ ... ] # launch mysql "as usual" (in reality, this is the shell function that is invoked) sh$ mysql -u user -p -h mysql-host mydb Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 67 Server version: 5.1.49-3 (Debian) -- Use mysql normally -- but the "egrep pager" will remove the "star lines" mysql> select * from TG col1: w col2: x col3: 0.4 1 row in set (0.00 sec) As I said before, this is not a perfect

Categories : Mysql

mysql command line connection to azure
Key points: -p thepass using password: NO As per the docs: --password[=password], -p[password] The password to use when connecting to the server. If you use the short option form (-p), you cannot have a space between the option and the password. If you omit the password value following the --password or -p option on the command line, mysql prompts for one. So you're asked for a password, but you apparently didn't type anything at the Enter password: prompt. Thus the error.

Categories : Mysql

Advanced mysql query with multiple joins, sub queries and radius
You could simply run that first query as a sub-select, e.g. WHERE id IN ( SELECT id FROM prices ORDER BY price, min_price ) AS p WHERE weight >= ".self::connection()->escape($weight)." AND cu_meters >= ".self::connection()->escape($cu_meters)." GROUP BY position_id ORDER BY price, min_price ) The only time you'd want to run them as seperate queries is if you'd want to keep those IDs from the sub-query for other purposes. But if you don't need them again elsewhere in the script, then the subselect will be somewhat more efficient, but chopping out at least one SQL parsing sequence.

Categories : PHP

Can I execute multiple queries separated by semicolon with MySQL Connector/J?
No you can't. What are you expecting to get by calling statement.execute(...)? It returns one ResultSet (... which means one table). You can just call "select fullName from user where user_id in (1, 2)" to geht back both results. Having semicolons in JDBC statements is very error prone in general. Some JDBC drivers do not support this (e.g. IBM's JDBC driver for DB2 10.x throws an exception if you close your SQL statement with ";").

Categories : Java

How to optimize a UNION mysql query with multiple select queries?
Keeping using ORDER BY RAND() (which is not efficient) then a very basic improvement would be to add a priority for each clause, move the order clause to the end so it is only needed once. (SELECT pd.product_id, pd.name, p.price , 1 AS recpriority FROM mg_product_description pd JOIN `mg_product` p ON p.product_id = pd.product_id WHERE pd.size_id = '33' AND pd.color_id = '2' AND pd.category_id = '3' AND pd.company_id = '1' AND pd.product_id != '53' AND p.status = '1' ) UNION (SELECT pd.product_id, pd.name, p.price , 2 AS recpriority FROM mg_product_description pd JOIN `mg_product` p ON p.product_id = pd.product_id WHERE p

Categories : PHP

How to make MySQL command line works with MAMP?
The MAMP mysql socket is located elsewhere. If you installed MAMP and own mysql parallel, you have two instances. One with MAMP context, which has its binary in /Applications/MAMP/Library/bin/ Other one depending on your settings during install. Make sure to start mysql (the one you would like to work with). You might want to use command line as follows: /Applications/MAMP/Library/bin/mysql [command goes here] To access the MAMP mysql server.

Categories : Mysql

What is the method for clearing the screen on mysql command line?
Solution 1 system clear Solution 2 ! clear Solution 3 /c Not possible on Windows: See this link from SuperUser: http://superuser.com/questions/196806/is-there-a-command-to-clear-the-screen-of-mysql-under-windows

Categories : Mysql

Do MySQL transactional queries improve performance for multiple SELECT statements?
Transactions can give big boost for INSERT/UPDATE/DELETE queries (provided that you use transactional engine like InnoDB), but on SELECT only queries, using transactions are NOT likely to improve performance (but may be still needed by your application logic to ensure consistency). Main reason for speed difference: if you do not use transactions, server must flush every single update to disk as soon as possible, to make sure that data will survive should server crash or lose power. With transactions, server only needs to guarantee that all updates batched in one transactions are committed to disk, or the whole thing is rolled back. In other words, server is free to group many writes into few disk operations, and only needs to commit transaction when it is absolutely sure that data is safe

Categories : PHP

MySQL Insert Into Table From Multiple Tables Using Separate SELECT Queries
It's one of the basics of SQL - use join. In your case it's better to use outer join so you won't miss people from people_temp1 who don't have corresponding records in people_temp2: insert into people(name, age, profession, location) select p1.name, p1.age p2.profession, p2.location from people_temp1 as p1 left outer join people_temp2 as p2 on p2.id = p1.person_id

Categories : Mysql

Flyway command line filesystem migrations from actual command line input
flyway.cmd -configFile=/path/to/other/configFile.conf should do what you want. I just checked and it seems I forgot to document this on the website (It is in the usage description of the tool itself). Could you file an issue against the website, requesting this to be added?

Categories : Windows

multiple input parameters from command line in C
To be able to call a function that you have as a string, you have know which name is paired to which function. If all functions take the same arguments, you can have an array of structures with name and function pointer, and then match the name with the correct entry in the table. Otherwise, if the arguments are different you have to have a chain of strcmp calls to call the correct function.

Categories : C

How can I execute mysql query from command line on Server 2008 R2?
Usually the mysql command-line tool will get you there, where you may need to provide any credentials to get access. This comes with most installations of MySQL, but you'll need to check that it's located in your PATH or it might not be found. It may be easier to use the MySQL Workbench tool to connect and run queries instead.

Categories : Mysql

How to find the mysql data directory from command line in windows
You can issue the following query from the command line: mysql -uUSER -p -e 'SHOW VARIABLES WHERE Variable_Name LIKE "%dir"' Output (on Linux): +---------------------------+----------------------------+ | Variable_name | Value | +---------------------------+----------------------------+ | basedir | /usr | | character_sets_dir | /usr/share/mysql/charsets/ | | datadir | /var/lib/mysql/ | | innodb_data_home_dir | | | innodb_log_group_home_dir | ./ | | lc_messages_dir | /usr/share/mysql/ | | plugin_dir | /usr/lib/mysql/plugin/ | | slave_load_tmpdir | /tmp | | t

Categories : Mysql

Formatting input from excel to mysql using command line client
seems u have used '|' in your csv file as delimiters instead of comma, try the code as `LOAD DATA LOCAL INFILE 'path to file.csv' INTO TABLE table_name FIELDS TERMINATED BY '|'LINES TERMINATED BY ' ';`

Categories : Mysql

ACCESS DENIED to mysql DB with PHP but can login with phpmyadmin and on the command line
The user you're going to use with your scripting should have both the "@localhost" and "'@%'" - Have a peek at the man. http://dev.mysql.com/doc/refman/5.1/en/adding-users.html This gave me a headache once.

Categories : PHP

Executing mysql from command line just listing overview of arguments/variables
As documented under mysql Options (emphasis added): --password[=password], -p[password] The password to use when connecting to the server. If you use the short option form (-p), you cannot have a space between the option and the password. If you omit the password value following the --password or -p option on the command line, mysql prompts for one. Specifying a password on the command line should be considered insecure. See Section 6.1.2.1, “End-User Guidelines for Password Security”. You can use an option file to avoid giving the password on the command line.

Categories : Mysql

Is it ok to run multiple PHP cURL scripts via windows command line?
Yes, you can definitely do that. From your code perspective, it is the same as having 5 different users load the site at once. Same from the server perspective. In fact, this is a pretty simplistic way to test simulated load on a page and see how it handles it. Definitely not the best way by far, but quick and dirty. Just be careful when generating these 5 scripts that you don't end up in a runaway situation where those 5 have not completely finished and exited before you run more. You could quickly overload your own server/site if you end up with dozens or even hundreds of scripts running. Also be aware that if you're doing heavy processing in those scripts, you may hit PHP/MySQL/Apache timeouts, so keep an eye on that. Make sure these calls are also thoroughly protected from the publi

Categories : PHP

How can I use the command line to combine multiple text files?
There are different ways: I tested with f1, f2, f3. If the name follows the pattern fXX, it can be done like this: $ paste f* abc123 2468, def zyx987 $ paste -d' ' f* #set space as delimiter abc123  2468, def zyx987 $ cat f* abc123 2468, def zyx987 If you want the output to be a file, just add > result $ cat f* > result $ cat result abc123 2468, def zyx987

Categories : Unix

Bulk import multiple CSV using command line SSIS DTEXEC.exe
Assuming all files have the same column layout, you could use a Foreach Loop Container with the enumerator type set to Foreach File. Example looping through Excel files

Categories : Csv



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