mysql

milliseconds in mysql

This page explains how to format milliseconds http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_now but how do I get the actual millisecond value beyond just .00000? I've tried these: select unix_timestamp()+0; select SYSDATE()+0; select date_format(now(), '%f'); select now()+0; but none of theme g...

PHP problem with query result.

hi, i have a problem with a query in a php file. I have a query that retrieves an e-mail address, it works about right, but when it sends the email, the address is added twice. SELECT email FROM jos_users WHERE id=$i $i is a number that auto increments. i have a for to check each user id to see if a email should be sent. Any ideas? ...

Twitter OAuth - Storing Tokens in MySql

Im using the Twitter OAuth class to connect to Twitter found here: Currently the script just uses the tokens supplied but doesnt store them in a database, i'd like the script to do this. This is what I currently have in my callback script: <?php /** * @file * Take the user when they return from Twitter. Get access tokens. * Verify...

sql query and php script to show average questions per day

In my questions table, each post is timestamped (MM-DD-YYYY). Each individual post has a QuestionID, and a response has both a QuestionID and ResponseID. I want to run a php script that will grab all questions (all posts where ResponseID = null) and display how many questions per day. I'd like it to be avg questions per day since June 1...

If db query A does not return enough results, run query B: how to optimize?

I am looking for a good design pattern, or best practice to achieve a situation of "either this query, or else that other query", with best performance and least overhead. Business logic/program of demans says "all items since Foo", unless that returns less then three items, then "all items". I am refactoring the current code, and can...

MySQL Syntax Error

I am getting this error when I try to run this MySQL command: CREATE TABLE READERWARE(TITLE VARCHAR_IGNORECASE NOT NULL, AUTHOR VARCHAR_IGNORECASE NOT NULL, ISBN CHAR, PUBLISHER VARCHAR_IGNORECASE, BOOKFORMAT CHAR, FIRST CHAR, SIGNED CHAR, PUBDATE VARCHAR_IGNORECASE, PUBPLACE VARCHAR_IGNORECASE, COPIES CHAR, RATING INTEGER, CONDITION IN...

how to send database data to a MySQL server to update the server database.

Hi everyone, I am developing Smoking counter and I need to send all the smoking record (about the time user smoking) to database on the server. The server database is MySQL database. I knew that I must have send data to PHP page/script, and this page/script will run on the data to insert record to database. I would like to know: is the...

MySQL: how do I add up values if the occur on the same day?

I have a column of datetimes, and a column of values. How do I add up all teh values that occur on the same day? so like... midnight:01 to 23:5 => add all the records that occur in that time period. then group by day. bit hard to explain. sadness. ...

transaction isolation level good explanation

Hi All, Does someone knows a good book where transaction isolation levels are explained with examples and advices in what case what isolation level to use ? Regards ...

MySQL: query to sort data based on the attribute of another column.

So, coming up with a title for this is difficult SELECT Date(p.born_at), SUM(p.value) as 'total_value_for_day' FROM puppies as p WHERE p.status = 2 GROUP BY DATE(p.born_at); So, this gives a 2 column result. What I want is this: columns: date | status = 1 | status = 2 | status = 3 DATA!!! is there a way to do that? ...

MySQL rename column from within PHP

I need to rename columns in my MySQL table using PHP. This is made difficult because the syntax is ALTER TABLE [table] CHANGE COLUMN [oldname] [newname] [definition]. The definition is a required parameter. Is there a way to grab the definition and simply feed this back into the SQL statement? Some sample code would be fantastic, tha...

MySQL between date formats

I have the query... SELECT COUNT(order_id) as orders, SUM(order_total) as sales FROM uc_orders WHERE FROM_UNIXTIME(created) BETWEEN "2010-08-14" AND "2010-08-15" AND order_status = "payment_received" The problem is that's not returning the orders from the provided date period. I'm not getting any SQL errors. Is ther...

Attaching simple metadata to a MySQL database

Is there a way to attach a piece of metadata to a MySQL database? I'm trying to write code to automatically update the database schema whenever a code upgrade requires it. This requires the storage of a single integer value -- the schema version. I could of course create a whole table for it, but that seems like overkill for just a simpl...

MYSQL: replace null with 0?

if i get a null table cell in my results, is there a way to convert it to 0? but just for the results? not actually modifying the data? ...

MySQL Error 1045 Access Denied

Good Morning, I wrote the code block below on my local Windows 7 PC and tried to run it. Unfortunately, I received: Connect Error (1045) Access denied for user 'dbuser'@'myhost(using password: YES) I have granted dbuser Insert, Select, Update, and Execute using both localhost and % for this database schema. I am able to mysql -u db...

SQL Select Question

I have the following SQL Query wich selects a result from 3 tables 'work', 'media' and 'work_media' I want all the work with 1 of its associated media, the following SQL query does that. But it only return the work that actually has media linked with it and thats's not what I want, I also want the work returned that has no media attache...

MySQL: how do you convert a datetime to unix time?

can I do this is a query? ...

Query to Database MySQL

I need Yoour help: I want search from table how many users(id_uzy) makes reservation on example this week, next which hours they reserved (godzina[pl] -> hour[en]) and for this hours find all id_urz. Example: id_uzy->1->hour(9-10)-->id_urz(2,3,4) id_uzy->1->hour(10-11)-->id_urz(2,3,4) id_uzy->2->hour(10-11)-->id_urz(4,5) id_uzy->1->h...

statistics from models created

I'm trying to figure out an efficient way to provide data to a plotting script jqplot of the amounts of clicks on a certain model I have the main model: Listing has_many clicks each click has a datestamp how can I get a count of clicks created for each day in a date range? ...

MYSQL: how do I remove the first row?

The first row is my results is all null, how do I remove that from my results? This is my query: SELECT COALESCE(UNIX_TIMESTAMP(Date(p.published_at)),0) as 'day', COALESCE(SUM(case when p.status = 2 then p.value end),0) as 'total_accepted', COALESCE(SUM(case when p.status = 1 then p.value end),0) as 'total_open', ...