Clear mysql screen
Hello everyone, How do i clear the mysql screen, which i opened through the MySQL Command Line Client? ...
mysql
How to create a "facade" table?
A legacy database contains a join table which links tables table1 and table2, and contains just two foreign keys: TABLE_ORIG: table1_id table2_id In order to utilize this table using JPA I would need to create a surrogate primary key to the link table. However, the existing table must not be modified at all. I would like to create an...
microsoft sql equivalent of mysql REGEXP
I am trying to reconstruct a database query I created for mysql in Microsoft sql. I am looking for a term in Microsoft sql, which acts like REGEXP here is an example of how am using the term select * from musicdetails WHERE artistname REGEXP '^".mysql_escape_string($_GET['search'])."$' any help would be much appreciated ...
MySQL query from subquery not working
I am trying to return a number based on the count of results from a table and to avoid having to count the results twice in the IF statement I am using a subquery. However I get a syntax error when trying to run the query, the subquery I have tested by itself runs fine. Any ideas what is wrong with the query? The syntax looks correct to...
Changinge case of tablenames inside stored procedure of mysql...
hi guys... I am here to change the uppercase table name present in the stored procedures. I have got nearly 100 stored procedures. is there any shortcut way so that i can change all the uppercase table names to lowercase within short time... ...
stored procedures, error #1312, CLIENT_MULTI_RESULTS flag
i am writing stored procedures in MySQL that return values; CREATE PROCEDURE getCustomerById (id int) BEGIN SELECT * FROM customer WHERE customer.id = id; END; and i get the error that the results cannot be shown in the given context. after some googling, i think that i need to set the flag "CLIENT_MULTI_RESULTS" - i am conne...
How to use TDD in a not very "Testy" environment
I work in a company where OOP is... well, not fobidden, but at least frowned upon as "too complex". My coworkers write lots of 100+ lines functions and they are usually all in a "funcs.inc.php" or "something.inc.php", if they use any functions at all, often they don't since copy-paste is faster. I would love to start using TDD at least ...
How to make #include <mysql.h> work?
I'm building an open source project from source, and it need to include <mysql.h>: #if USE_MYSQL #include <mysql.h> #endif and the compilor reports: mysql.h no such file or directory MySQL is yet another greater open source project,what do I need to do to make it work? ...
SQL IN statement and ordering the results
I have a comma delimited list of ids that I want to use to retrieve records from the database. I can use the IN statement to get the results but I want the order of the results to be the same order as the original list. EG $list = "3,1,4,2,5"; $query = "SELECT * FROM table WHERE id IN (" . $list . ")"; $result = @mysql_query($query); ...
SQL: Daily Average of Logins Per Hour
This query is producing counts of logins per hour: SELECT DATEADD(hour, DATEDIFF(hour, 0, EVENT_DATETIME), 0), COUNT(*) FROM EVENTS_ALL_RPT_V1 WHERE EVENT_NAME = 'Login' AND EVENT_DATETIME >= CONVERT(DATETIME, '2010-03-17 00:00:00', 120) AND EVENT_DATETIME <= CONVERT(DATETIME, '2010-03-24 00:00:00', 120) GROUP BY DATE...
mysql GROUP_CONCAT
I want to list all users with their corropsonding user class. Here are simplified versions of my tables CREATE TABLE users ( user_id INT NOT NULL AUTO_INCREMENT, user_class VARCHAR(100), PRIMARY KEY (user_id) ); INSERT INTO users VALUES (1, '1'), (2, '2'), (3, '1,2'); CREATE TABLE classes ( class_id INT NOT N...
Select count / duplicates
Hello! I have a table with all U.S. zip codes. each row contains the city and state name for the zip code. I'm trying to get a list of cities that show up in multiple states. This wouldn't be a problem if there weren't X amount of zip codes in the same city... So basically, I just want to the city in a state to count as 1 instead of ...
Mysql - second auto_increment col with diff behaviour
Hi guys! I have a proposals table like this - ID (auto_increment) - proposal_id - client_id There a way in sql that the proposal_id increments just for each client_id example: ID proposal_id client_id 1 1 1 2 1 2 3 2 1 4 3 1 5 2 2 6 3 2 i know i can get the last poposal_id and +1 and i add the new entry... but i dont want to...
How do I identify which MySQL slave has responded?
I have a MySql Master server replicating to three Slaves. A legacy website is performing load-balanced Reads from the Slaves. Is there a method of identifying from the website which of the Slaves is serving a Read request? I'd prefer a function that I can use to return a server name or ip address as part of the SELECT, but any reaso...
HTML anchor too pull all Sticky Posts ?
Is there a way to format an tag to send a query, pull all Sticky Posts, and loop through them? Since 'sticky' is not a category, I cant format the link in the normal : http://mysite.com/category/cars I'm thinking is probably going to have to be some kind of url query string sent in as GET. ...
What's the difference between !col and col=false in MySQL?
The two statements have totally different performance: mysql> explain select * from jobs where createIndexed=false; +----+-------------+-------+------+----------------------+----------------------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows |...
Mysql IN clause, php array
I know that this has been asked and answered before, but for the life of me I cannot find where I am going wrong. My code is below. $backa = array("1", "7", "8", "9", "12"); $backaa = implode(",", $backa); /* code to create connection object $wkHook */ $getOpt=$wkHook->prepare("select movementId, movementName from Movement where moveme...
'AND' and 'OR' in SQL queries
Hi, I was hoping someone could give me a hand on how I write this correctly. $details_quey = mysql_query("SELECT * FROM `accounts` WHERE ONLINE = 'true' OR FORCE_AWAY = 'true' OR AWAY = 'true' AND FORCE_OFFLINE = 'false' AND TOKEN = '$con_token' ", $CON ) Any help much appreciated. ...
MySql Not write CREATE TABLE to Bin Log, others do get written
Hello, We set up replication on a virtual box, imported a mysql dump and for UPDATEs, INSERTs and DELETEs is working fine. Tested today to drop a table and it didnt go through, checked the binary log in the Master and cant find any record for the DROP. It is taking the replication out of sync since the DROPs need to happen for reinstal...
Retrieve and entry for all "type"s but only the most recent entry for each "type" from MySQL table
Basically if I had a mysql table with columns: id, type, content, version. There will be multiple entries with the same type. Version is a number which increments with each new entry of a certain type. I want to be able to get the most recent entry of each type for all types with one query. Is this possible or do I need to do separa...