mysql

How do I quickly create a complex select query using Zend_Db?

Let's say have something like: SELECT energy_produced, energy_consumed, timestamp1 AS timestamp FROM ( SELECT max(energy_produced) AS energy_produced, mid(timestamp, 1, 10) AS timestamp1 FROM tbl_energy WHERE site_id = 1366 AND mid(timestamp, 1, 10) >= DATE_SUB(curdate(),INTERVAL 1 day) group by mid(timestamp1, 1, 10)) AS e1 INNER JOIN...

How to find missing data either in array or in mySQL table?

Hello, I have an array filled with values (twitter ids) and I would like to find the missing data between the lowest id and the highest id? Any care to share a simple function or idea on how to do this? Also, I was wondering if I can do the same with mySQL? I have the key indexed. The table contains 250k rows right now, so a temporary t...

Getting the number of rows with a GROUP BY query

I have a query to the effect of SELECT t3.id, a,bunch,of,other,stuff FROM t1, t2, t3 WHERE (associate t1,t2, and t3 with each other) GROUP BY t3.id LIMIT 10,20 I want to know to many total rows this query would return without the LIMIT (so I can show pagination information). Normally, I would use this query: SELECT COUNT(t3.id) F...

PHP+MySQL: searching using wildcards

So, I have the following rows in the DB: 1 | /users/ 2 | /users/admin/ 3 | /users/admin/* 4 | /users/admin/mike/ 5 | /users/admin/steve/docs/ The input URL is /users/admin/steve/, and the goal is to find the URL match from the DB. I want to return #3 as the correct row, since the wildcard "*" specifies that anything can go in plac...

MySql- on duplicate key insert with string added.

Hi How to insert from one table to another and on duplicate key append string to new row? If i got table with values: a b c and want insert tablevith value: a i want append string like 'x' so i will get table with values: a b c xa ...

Large primary key: 1+ billion rows mySQL + InnoDB?

I was wondering if InnoDB would be the best way to format the table? The table contains one field, primary key, and the table will get 816k rows a day (est.). This will get very large very quick! I'm working on a file storage way (would this be faster)? The table is going to store ID numbers of Twitter Ids that have already been processe...

How to keep a php script from timing out because of a long mysql query

I have an update query being run by a cron task that's timing out. The query takes, on average, five minutes to execute when executed in navicat. The code looks roughly like this. It's quite simple: // $db is a mysqli link set_time_limit (0); // should keep the script from timing out $query = "SLOW QUERY"; $result = $db->query($query...

MySQL-problem when baking with CakePHP.

I am currently reading "Beginning CakePHP:From Novice to Professional" by David Golding. At one point I have to use the CLI-command "cake bake", I get the welcome-screen but when I try to bake e.g. a Controller I get the following error messages: Warning: mysql_connect(): Can't connect to local MySQL server through socket '/var/mysql/my...

MySQL/SQL: Group by date only on a Datetime column.

Having a table with a colum like: mydate DATETIME ... I have a query such as: SELECT SUM(foo), mydate FROM a_table GROUP BY a_table.mydate; This will group by the full datetime, including hours and minutes. I wish to make the group by, only by the date YYYY/MM/DD not by the YYYY/MM/DD/HH/mm. Anyone know how to do this? I can still d...

Java Driver.getConnection() yields "Connection Refused" from mysql on live system, not dev.

Pretty straightforward stuff, here -- I'm just not good enough with mysql to understand what it wants from me. I've got a short java test-case that opens a connection on mysql on my dev system but, when I try to put it to my server, it fails. Any help in tracking this down would be most appreciated. Thanks! Test Code import java.ut...

Limit Per Criteria

Hi, I have an articles table and a categories table. I want to fetch 7 articles for each category. Currently I have this but it's terrible slow on large tables so it's not really a solution: SELECT id, title, categories_id, body, DATE_FORMAT(pubdate, "%d/%m/%y %H:%i") as pubdate FROM articles AS t WH...

What is the best collation to use for MySQL (with PHP)

I'm wondering if there is a "best" choice for collation in MySQL for a general website where you aren't 100% of what will be entered? I understand that all the encodings should be the same, such as MySQL, Apache, the HTML and anything inside PHP. In the past I have set PHP to output in "UTF-8", but which collation does this match in MyS...

SQL - find records from one table which don't exist in another

Hi, I've got the following two SQL tables (in MySQL): Phone_book +----+------+--------------+ | id | name | phone_number | +----+------+--------------+ | 1 | John | 111111111111 | +----+------+--------------+ | 2 | Jane | 222222222222 | +----+------+--------------+ Call +----+------+--------------+ | id | date | phone_number | +----...

mysql range index

Hi everybody, I have very simple select like this: SELECT * FROM table WHERE column1 IN (5, 20, 30); on column1 is seted index, after explaining query is index used, all looks to be ok. but if there are more than three values in range, like this: SELECT * FROM table WHERE column1 IN (5, 20, 30, 40); index is not used...

Why does com.mysql.jdbc.Driver take forever to open in MATLAB?

I'm having an issue with com.mysql.jdbc.Driver in MATLAB and I'm hoping someone else has run into it and can help me out. Basically, my problem is that on one machine, every time I call database('mysql.jdbc.Driver', ...), that call takes approximately 30 seconds. I would just chalk this up as normal except that when I run the same scri...

Hidden Features of MySQL

I've been working with Microsoft SQL Server with many years now but have only just recently started to use MySQL with my web applications, and I'm hungry for knowledge. To continue with the long line of "hidden feature" questions, I would like to know any hidden or handy features of MySQL which will hopefully improve my knowledge of thi...

mysql procedure? Function?

I need to have a routine/function/stored proc/whatever to archive particular accounts from our 'active' table, to an inactive table. I can write this out as a group of queries, all executed in order by PHP one at a time, but I'm looking to offload most of the work to mysql because it's not necessary for PHP to be involved here. Basical...

Error in retrieving and binding to a datagridview from a mysql stored procedure with concat()

Everytime i retrieve data from a mysql stored procedure with a concat function an error will occur after binding the datagridview to the datatable that holds the returned rows. ...

How do I map a hibernate Timestamp to a MySQL BIGINT?

I am using Hibernate 3.x, MySQL 4.1.20 with Java 1.6. I am mapping a Hibernate Timestamp to a MySQL TIMESTAMP. So far so good. The problem is that MySQL stores the TIMESTAMP in seconds and discards the milliseconds and I now need millisecond precision. I figure I can use a BIGINT instead of TIMESTAMP in my table and convert the types i...

Excel Add-On to display data from (My)SQL Database

Hello, is there an easy way of importing data from a mysql/odbc datasource into an excel spreadsheet? The user should be able to select some values from drop downs (e..g date range, branch name etc.) and the values from the dropdown should be used to populate (prepared) SQL statements. The results should be displayed in the Excel file...