mysql

MySQL - Control which row is returned by a group by.

I have a database table like this: id version_id field1 field2 1 1 texta text1 1 2 textb text2 2 1 textc text3 2 2 textd text4 2 3 texte text5 If you didn't work it out, it contains a number of versions of a row, and then ...

How should I select the right technology and platform for a project?

Here are some (certainly there could be more) of the guiding principles for a new project I'm starting. Addressable URLs Very fast Mobile - cater to iPhones and other smart devices Use existing social networks Live Facebook LinkedIn Twitter Craigslist Etc. Use existing ID services Live ID Open ID Etc. Client and online management a...

Display mysql join/union results differently based on table

Hello, I need to generate a list of activity from multiple tables in order of the date they were entered into the tables, but when i spit them out (with php), i want each result to look specific to a design that i have for each table. ie...i have the tables: listings, photos, comments -the results from listings should be designed...

Select in MySQL where all rows meet a condition

In MySQL, how can you select data where every row meats a certain condition? For example lets say I have a table showing when employees arrived at work, it has three fields: CREATE TABLE ArrivalTimes (UserID INT ,Day DATE ,ArrivalTime TIME ); I want to select all UserIDs of employees who have never been late (arrived 9am or earlier)...

Can I stop CakePHP fetching all rows for a query?

Hi, I'm using CakePHP with $modelName->find(...) calls to select quite a number of rows (could be hundreds) Normally, in PHP/MySQL of course this wouldn't be a problem, as you fetch them in a while loop. But, CakePHP loads all rows in to an array which exhausts the memory limit. Is there a way to use the $modelName->find(...) construc...

How do I select mysql database in php?

I have this code: if(!mysql_connect($host,$user,$passwd)){ die("Hoops, error! ".mysql_error()); } ...no error from here. if(!mysql_select_db($db,$connect)){ $create_db = "CREATE DATABASE {$db}"; mysql_query($create_db,$connect...

mySQL database efficienty question

Hi, I have a database efficiency question. Here is some info about my table: -table of about 500-1000 records -records are added and deleted every day. - usually have about the same amount being added and deleted every day (size of active records stays the same) Now, my question is.....when I delete records,...should I (A) delete the...

Steps in implementing hashtable in PHP and Mysql

Hi, I am new to programming language and I am using PHP and mysql. I got an assignment to do a hashtables in php. What I need to do is, store items that a user collected and then display it. After do some research over the internet, I will do the following steps when implement the hashtable, please correct me if I am wrong: Set up the ...

Updating multiple rows with a value calculated from another column

I have a table with a row that looks like this: (2009123148498429, '...', '...') The first part, id, is a timestamp followed by a random number. (needed to work with other parts in the system) The data already exists in the table. I want to create a column, timestamp, and extract just the date (20091231) and update all the rows with ...

Questions about Php and Mysql Hash Table

I am a new php and mysql programmer. I am handling quite large amount of data, and in future it will grow slowly, thus I am using hash table. I have couple of questions: Does mysql have hash table built in function? If yes, how to use that? After couple of days doing research about hash table. I briefly know what hash table is but I ju...

How does a hash table work? Is it faster than "SELECT * from .."

Let's say, I have : Key | Indexes | Key-values ----+---------+------------ 001 | 100001 | Alex 002 | 100002 | Micheal 003 | 100003 | Daniel Lets say, we want to search 001, how to do the fast searching process using hash table? Isn't it the same as we use the "SELECT * from .. " in mysql? I read alot, they say, the "SELECT *" sea...

SQL: Select records belonging to excluded category that ONLY belong to excluded category

I have a SELECT statement that works, and runs fast enough on my tables (<0.01sec on 50k+ products, 3k+ categories). But in my mind it's not very elegant and would like to hear any suggestions on making it better. There are 3 tables of interest: products - key productID categories - key categoryID products_tree - link table (...

How to write UTF8 text to MySQL from ASP.NET via ODBC?

I'm using MySQL 5 on shared hosting, connecting from ASP.NET 3.5 using the MySQL 5.1 ODBC driver. I'd like to store UTF8 strings. My tables used to be all in "latin1_swedish_ci", but I converted the the database, table, and column to UTF8 using: ALTER DATABASE `my_db` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ; ALTER TABLE `my_...

remote database connection with my iphone application using cocos2d

MCPResult *theResult; MCPConnection *mySQLConnection; //initialize connection string vars NSString *dbURL = @"192.168.0.16"; NSString *userName = @""; NSString *pass = @""; int port = 3306; //open connection to database mySQLConnection = [[MCPConnection alloc] initToHost: dbURL withLogin:userName pass...

optimize SQL query

What more can I do to optimize this query? SELECT * FROM (SELECT `item`.itemID, COUNT(`votes`.itemID) AS `votes`, `item`.title, `item`.itemTypeID, `item`. submitDate, `item`.deleted, `item`.ItemCat, `item`.counter, `item`.userID, `users`.name, TIMESTAMPDIFF(minute,`submitDate`,NOW()) AS 'timeMin' , ...

Optimize MySQL search process

Here is the scenario 1. I have a table called "items", inside the table has 2 columns, e. g. item_id and item_name. I store my data in this way: item_id | item_name Ss001 | Shirt1 Sb002 | Shirt2 Tb001 | TShirt1 Tm002 | TShirt2 ... etc, i store in this way: first letter is the code for clothes, i.e S for shirt, T for tshi...

How to lookup custom ip address field stored as integer in Django-admin?

In my Django model I've created custom MyIPAddressField which is stored as integer in mysql backend. To do that I've implemented to_python, get_db_prep_value, get_iternal_type (returns PositiveIntegerField) and formfield methods (uses stock IPAddressField as form_class). The only problem is field lookup in cases like builtin search in M...

Dump mysql view as a table with data

Say I have a VIEW on my database, and I want to send a file to someone to create that views output as a real TABLE on their database. mysqldump of course only exports the 'create view...' statement (well ok it includes the create table, but no data) What I have done is simply duplicate the view as a real table and dump that. But for a...

PHP and outputting one-to-many results

I've only dealt with one-to-one relationships in php so far, but I'm stuck on a problem which involves a one-to-many relationship. I've been sitting on this for a few days with no luck, so I'm desperate for someone to step in and show me a solution before I lose my mind. In my database have a series of urls, which are received by a SELE...

Why does Apache complain that my mod_perl program "disconnect invalidates 1 active statement handle"?

disconnect invalidates 1 active statement handle (either destroy statement handles or call finish on them before disconnecting) The following code which grabs data from MySQL gets executed successfully, but will cause Apache to generate the above message in its error log: my $driver = "mysql"; my $server = "localhost:3306...