mysql

Finding largest time interval between list of dates

I have a table of items, each with a timestamp connected to it. I want to find the largest interval between any pair of consecutive items. Is this possible with a query only? UPDATE: Let me see if I can make this a little less confusing. What I want to do is compare each items timestamp to the item before and after it, to find out how...

update row if already exists in mysql

I have a table in mysql like this Name Result T1_09_03_2010 fail T2_09_03_2010 pass T3_09_03_2010 pass T4_09_03_2010 fail T5_09_03_2010 fail T6_09_03_2010 pass T7_09_03_2010 fail Well, T1_09_03_2010 is testcase name. If for some reason the developer wants to test the same testcase...

MySQL Workbench - How to synchronize the EER Diagram

I am creating a visual representation of my existing database with MySQL Workbench and I am able to synchronize the models with the "Database -> Synchronize Model..." menu. However, every time I synchronize (update) my model, I have to recreate the EER Diagram and rearrange all the tables. Is there a way to update or synchronize the EER ...

Save Rails ActiveRecord objects into a temporary table (MySQL)

A user can import data into our website from a file. The data normally contains several hundred Items (Item < ActiveRecord::Base). Although the validations help, they cannot solve the problem of sanity-checking the content. For that we would like to have a test mode. Could we use a temporary Items table for this with Rails/MySQL, and,...

php/mysql zip code proximity search

Hi, I'm just looking for suggestions on the best way to do this... I need to create a search function that searches for "users" within a 50 mile radius of a zip code. I have a zip code table that contains all the U.S. zip codes with their latitude/longitude but I'm just trying to figure out the best way to structure and query my data....

How to Reset an MySQL AutoIncrement using a MAX value from another table?

I know this won't work, tried it in various forms and failed all times. What is the simplest way to achieve the following result? ALTER TABLE XYZ AUTO_INCREMENT = (select max(ID) from ABC); This is great for automation projects. Thank you! SELECT @max := (max(ID)+1) from ABC; -> This works! select ID from ABC where ID = (@max-...

PHP value of nothing ?

If have a piece of code that gets some data from a sql database $comms = $row['comments'] ; if ($comms != "") { $tooltip = "<b>Notes :</b> $comms </br> "; } What i want to do is display the result ONLY if there is something in the data. I am using the if statement to determine if $comms has any data in it but everything i try ...

DISTINCT a column in a database

Hi, I'm trying to perform a DISTINCT clause on a query like this SELECT DISTINCT username, wiki.text, wiki.date FROM wiki_house INNER JOIN wiki ON wiki_house.wiki_id = wiki.id INNER JOIN users ON wiki.user_id = users.id AND wiki_house.house_id = 1 AND wiki.language = 'it' ORDER BY wiki.date DESC LIMIT 10 this returns: username wi...

MySql displaying results in same order no matter "array-order"

I am using "solr" search engine to query an index for classifieds that match a given criteria. The results are the ID:numbers of the classifieds, which I then use to find all matches in a MySql database with those ID:s. The ID:s returned are put into an array. As you can see below the array is imploded. Then I use the "IN" to find all m...

How easy (or otherwise) is it to to tune a database AFTER 'going LIVE' ?

It is looking increasingly like I'll have to go live before I have had the time to tweak all the queries/tables etc, before I go live with a website (already 6 months behind schedule, so all though this is not the ideal scenario - thats how things are). Its now a case of having to bite the bullet. Its just a case of trying to work out h...

MySQL DataCOnnection and ADO.NET

I want to use mysqlconnection, command and datareader or sqlconnection, command and datareader based on a condition. I want to use same variables for both type of connection Ex: Currently i have dim _c as New mySqlConnection() dim _cmd as New mysqlCommand() dim _reader as New mysqlreader() _reader = _cmd.executereader() 'loop through ...

Logics of searching for "available hotel rooms" help needed

Say you have a website for users to search for "hotel rooms for rent". I don't know how the logics of current systems work, which is why I am asking you guys. I currently have two fields in the form: Date available from: //example 2010-04-01 // 1st april 2010 Date available to: //example 2010-05-01 // 1st may 2010 Then Submit!...

Is it necessary to escape parameters for stored procedures?

Or the call to real_escape_string() in such cases is not needed? /Email value comes from user input./ function findUser($email) { $mysqli = connectDB(); $email = $mysqli->real_escape_string($email); $query = "CALL FindUser('{$email}')"; // ... } ...

Query points within a given radius in MySQL

I have created the following MySQL table to store latitude/longitude coordinates along with a name for each point: CREATE TABLE `points` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(128) NOT NULL, `location` point NOT NULL, PRIMARY KEY (`id`), SPATIAL KEY `location` (`location`) ) ENGINE=MyISAM DEFAULT CHARS...

Are there generic options for version control within a database?

I have a small amount of experience using SVN on my development projects, and I have just as little experience with relational databases. I know the basic concepts like tables, and SQL statements, but I'm far from being an expert. What I'd like to know is if there are any generic version control type systems like SVN, but that work with...

Storing annually repeatable values in a MySQL database

I've been hammering my head against my desk for the past few days on this, and so I turn to you, Stack Overflow. The software I'm working on has time-sensitive data. The usual solution for this is effective and expiration dates. EFF_DT XPIR_DT VALUE 2000-05-01 2000-10-31 100 2000-11-01 (null) 90 This would be easy. ...

Creating a query to retrieve certain rows in Mysql.

I have an orders table with a schema like this. CREATE TABLE orders ( order_id int NOT NULL AUTO_INCREMENT, customer_id int unsigned NOT NULL DEFAULT 0, order_date int NOT NULL ) I'm trying to retrieve a list of each customers most recent order. The table can have multiple orders by a customer, and each or...

want to change display format of date and time field in mysql php

my select statement in php is "select * from table"; i use following php statement to display date & time of mysql field <?php echo $row['mdate']; ?> the result come like this 2010-03-09 16:59:18 i want to view the result i following format 09-03-2010 16:59:18 and i want to view the result i f...

Restore a single table from a mysqldump database backup to a different table in the same database?

I've got a 3.5gb database dump. Is there a way to restore just a single table from that file to a differently named table in the same database without editing the file, using mysqladmin, or some other commonly available command line application that runs on FreeBSD 6? ...

Get Last Monday - Sunday's dates: Is there a better way?

Hi, I'm preparing a query for mySQL to grab record from the previous week, but I have to treat weeks as Monday - Sunday. I had originally done this: WHERE YEARWEEK(contactDate) = YEARWEEK(DATE_SUB(CURDATE(),INTERVAL 7 DAY)) to discover that mySQL treats weeks as Sunday - Monday. So instead I'm parsing getting the begin & end dates in...