query on sql 5.1
Hi, In a sql based query, I need to check that the persons I select from data should have a cell phone with area code 256. How do we check this? I have cell phone numbers as BIGINT. ...
mysql
error in running query
where h.er = select ( p.er from tip p where p.os = 'Android' OR 'iOS' ) AND h.ssn = (select h2.ssn from persons h2 where h2.ssn like '213%'); I am performing this function in mysql. IT is not letting me to do it. An error is coming like check the manual the mysql version cooresponds to. I am using 5.1.5.1 MYsql ...
MySQL inserting text before and after value
I have made an SQL query that picks out web statistics from a database. This information is being picked out on a weekly basis, outputted as a HTML table and then sent by email. One of the rows contains the IP addresses of the visitors. I would like to make the IP address a link leadning to "http://whois.domaintools.com/ip.goes.here". ...
How to drop a primary key? I get incorrect table definition.
I am trying to run this query: ALTER TABLE table DROP PRIMARY KEY, ADD PRIMARY KEY( `CUSTNO` , `DEPTNO` , `PRODNO` , `DT` ); I get Incorrect table definition; there can be only one auto column and it must be defined as a key ...
Are there unit testing for SQL queries, to measure if they run the same time after schema have changed?
I have a really heavy database, with multiple large tables, and lot of indexes. We keep constantly adding new indexes, and sometimes the old queries which ran ok at that moment, they come up to use a new index, the wrong for that query. I am wondering if there is some unit testing out there for MySQL queries, that can track if the resp...
UPDATE column when a user renames data from another table
Hey guys, how the heck do I go about doing this. I have an address book I'm making and I'm trying to figure out how to tackle my groups. I let the users rename their groups at will. But then this complicates my life ;0 I have 2 tables. groups and contacts the groups has the group names per user. the contacts has a group column that say...
How to count specific values in a table.
I've a column that have 15 distinct values. I'd like to count how many there are of a few of them, I've come up with e.g. select a,COUNT(IFNULL(b != 1,NULL)),COUNT(IFNULL(b != 2,NULL)) from mytable group by a select a,SUM(CASE WHEN a = 1 THEN 1 ELSE 0)),SUM(CASE WHEN a = 2 THEN 1 ELSE 0)) from mytable group by a What's the bes...
Mysql coding question.
Is there an if or if else type statement for mysql. ...
SQL: filter by date
Hi, I have a table SIGNUPS, where I register all signups to a specific event. Now, I would like to get all people who signed up to an event, with an extra column STATUS telling if the user is actually accepted (STATUS = "OK") or if it is in a waiting list (STATUS="WL"). I tried something like this SELECT *, IDUSER IN (SELECT IDUSER FR...
Merging multiple tables into one result
Hi, I have two tables for a facebook and twitter stream. Those tables have to be separated, so there's no point merging them into one table. But here's the thing. I want to have all the results from those two tables merged into one result and I cant seem to figure out how to get that working. Oh, and it has to be fast. I read something...
use WHERE CLAUSE for search data from A date until B date
lets say i have two drop down list and one button on my search page: From <select id="1stdate"> Until <select id="2nddate"> <input type="button" id="search"> i want to search data from 1stdate until 2nddate, how to use WHERE CLAUSE for this case? for ex. i want to search data "from 09-2010 until 11-2010". this my query: SELECT CONCAT...
Can you use JOIN in a recursive function PHP/MySQL?
I have the following table. CREATE TABLE IF NOT EXISTS `omc_schedule` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `trainer_id` int(11) NOT NULL, `course` varchar(255) NOT NULL, `capacity` int(11) NOT NULL, `active` tinyint(1) NOT NULL DEFAULT '1', `parentid` int(10) NOT NULL, `order` int(11) NOT...
Check if value exists in a json_encode stored mysql table column??
Hello, I am trying to create a query which checks if a value exists in a column. The column constists of json_encoded values (like, ["3","10"]) function totPerWorkshop($id){ $q = "SELECT * FROM tblWorkshop WHERE workshops = $id"} Does something like SELECT * FROM tblWorkshop WHERE in_array($id, workshops); exists? Any suggestions ...
Should I use InnoDB or MyISAM for a "write-only" table?
I have a table where I'm esentially only writing. Think of it as a "transactions" list, where I keep all the details, and I also have a "total" stored in another table. That total is redundant, for performance, but it can be re-calculated if needed from this transactions table. What is better from a performance point of view, keeping ...
mysql NULL value in where in CLAUSE
how to deal with NULL value in mysql where in CLAUSE i try like SELECT * FROM mytable WHERE field IN(1,2,3,NULL) it not working only work like : SELECT * FROM mytable WHERE field IN(1,2,3) OR field IS NULL how can i get it work in WHERE IN ? it is possible ? ...
MySQL query problem.
I'm trying to just display articles and their comments where users deletion field is not 1 and active is NULL, but for some reason my query displays comments from articles where the deletion field is 1. Can someone help me fix this? Sorry for all the info but I have been stuck on this for about 3 days and really wont to solve this pro...
about mysqlimport show warnings
i try to use mysql command to achieve show warnings after execution but my command seems didn't work,what's wrong with the below code,i have tested for many times mysql -uroot -pbin \W --execute="LOAD DATA LOCAL INFILE '/home/mlzboy/code/mq/model_product.csv' INTO TABLE search.model_product FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAP...
MySQL: count data from A month until B month
regarding to this question, i have a problem if use that query for count some data. How to make it can count data for ex. from 2010-01 until 2010-05? after use that query the show as null. this is my query: SELECT id, Line, COUNT( Serial_number ) AS Qty, SUM(S), SUM(A), SUM(B), SUM(C), (SUM( S ) + SUM( A ) + SUM( B ) * 0....
How can I generate the SQL query using SQL::Abstract?
How do I generate the WHERE clause for this query using SQL::Abstract: SELECT COUNT(*) FROM table WHERE id = 111 AND NOT FIND_IN_SET(type, '1,2,3,4') AND status = 'pending'; What's the proper way to include conditions like WHERE FIND_IN_SET(type, '1,2,3,4')? ...
"Show all except" in MySQL Boolean Full-Text Searches
With MySQL Boolean Full-Text Searches... http://dev.mysql.com/doc/refman/5.1/en/fulltext-boolean.html A leading minus sign indicates that this word must not be present in any of the rows that are returned. Note: The - operator acts only to exclude rows that are otherwise matched by other search terms. Thus, a bool...