MySQL - Rename Column
How to rename mysql column from help to content in my table tbl_help mysql_query("ALTER TABLE tbl_help " . "CHANGE COLUMN help content;"); ...
mysql
SQL scheme conversion script for MySQL, PostgreSQL, SQLite …
I’m looking for a script that allows me to define my database relations in a simple model file and have the possibility to convert this scheme definition to the explicit formats for MySQL, PostgreSQL and SQLite. The format could be similar to the definitions you would do in activerecord, so if all fails, I will somehow go with AR if I ca...
Two foreign keys of the same table. How do I write this SELECT statement?
Users Table user_id username thumb_id fullimage_id 1 jprescott 14 15 2 lpausch 18 19 Images Table image_id path 14 jprescott/small.jpg 15 jprescott/big.jpg 16 msamuels/small.jpg 17 msamuels/big.jpg 18 lpausch/small.jpg 19 lpaus...
text or varchar?
Hi, I have 2 columns containing text one will be max 150 chars long and the other max 700 chars long, My question is, should I use for both varchar types or should I use text for the 700 chars long column ? why ? Thanks, ...
Most efficient way to query the newest 20 posts per category on the sme page
I am using PHP and mySQL. I have a table of photographs. in the photographs table I have: a link to the photo, a category_id, date. What would be the best way to list all the categories on the page with the newest 20 photos under each? Right now I am selecting all the photos and then sorting them out after in PHP. If there gets to be 5...
Display of Columns From Database
I have 3 text files each of which contains a list of 20 items. A new column will be added per new day. I'd like to display these items in a spreadsheet or spreadsheet type layout with each column adjacent to the next. Spreadsheet output like: 10-28-09 10-29-09 10-30-09 data1 data2 data3 data1 data2 data3 data1 data2 ...
how to find the greatest number from an array
Hi This is the books table on db; book_ID writer_ID -------- ----------- 1 10 2 10 3 10 4 10 5 10 This is the rates table on the db, book_ID rate ------- -------- 1 4 2 3 2 5 ...
Timestamp comparison in MySQL and PHP ( UTC, +0000, Timezones )
I'm trying to determine whether a string that represents the date and time, given in a JSON Twitter feed is within a range of timestamp columns in MySQL. Here's the example string: 'Sat, 31 Oct 2009 23:48:37 +0000', The +0000 according to the API ( created_at ) indicates it is indeed UTC. Now, I'm using strtotime and date just to con...
php loops mysql Help
I am trying to make it stop showing the rest if $walk_jail is not empty 1) $walk_jail is empty 2) $walk_jail is empty 3) $walk_jail is empty 4) $walk_jail is Not empty Stop showing the rest. $walk = 15; for ( $counter = 1; $counter <= $walk; $counter += 1) { $result= mysql_query("SELECT * FROM walk_txt") or die (mysql_error()); $...
Spring, iBatis, MySQL - how to manage transactions?
Hello I'm building a web application with spring ibatis and mysql. I'm going to use mysql stored procedures and I will call them with ibatis. My question is about how to manage the transactions. Should I manage the transactions inside the stored procedures or with spring/ibatis or with both? ...
How much faster is timestamp than datetime column in MySQL?
This consists of two questions: Is MySQL's timestamp field really faster than datetime field in "order by" query? If the answer to above question is yes, how much faster it could be? Supposed in a table of 100 million rows and frequently sort a bunch of 100-200k rows based on timestamp field inside MySQL, will the sort time improvement...
MySQL tuning on a regular basis without restart
I have a 5GB database, all tables are MyISAM. It runs into heavy load time from 01:30AM to 8:30AM (100+ selects, 150+ updates, 200+ cache hits per second) to do data analysis, during other time, load is moderate (10 selects, 5 inserts per second). Problem is after a few days, data analysis during heavy load time appears to be slow down ...
Generating unique tracking numbers
I am building a small rails app and I need a way to generate tracking numbers to give to customers to check up on their status. I want something similar to what the major shipping companies use, but not as long. Im thinking 12-16 characters long is fine, but I dont want something like 0000000001 where people can just type in incremente...
mysql tables mysteriously emptied
Hi, I noticed that a couple of my mysql tables today were emptied. I can't think of any reason why this would occur. I store the DB in an AWS volume and we are well under the amount of space allocated to us. I was wondering if anyone had this issue before? Any suggestions of things that I can test? Sorry if I haven't provided enough...
mysql binary log only log deletes
Hi, Is there any way in MySQL to only log deletes? I tried using the mysql binary log option but unfortunately, there are too many inserts to my database and the file swells instantly. If I let it grow for more than a day or so it will take up all the room on the server. I just want to log deletes for disaster recovery purposes. Any ...
php mysql query syntax question regarding ""'s
Forgive me, I'm a beginner. The following code returns a parse error: $query = "INSERT INTO scenario_needgames VALUES ("$id", "Battle of the Bulge")"; the query builder in phpMyAdmin gave me this slightly modified string that works: $query = "INSERT INTO scenario_needgames VALUES (\"$id\" , \"Battle of the Bulge\");"; but I'm confu...
Join user table w/posts and create an instance for each
Let's say I'm building feed of comments and need the comment data and user data of the author. Comments are represented by the Comment class. Users are represented by the User class. When pulling the comments, the $author member of Comment should be set to an instance of User (to keep all the info about the author). class Comment { v...
MySQL LOAD DATA INFILE - Loading a file with no primary key
I was trying to load a data file into mysql table using "LOAD DATA LOCAL INFILE 'filename' INTO TABLE 'tablename'". The problem is the source data file contains data of every fields but the primary key is missing ('id' column). I have to add a unique id to each line of source data file otherwise the import will not go through. Is ther...
mysql - keyword search table - a good idea?
Is it a good idea to have a 'search table'? for example a search that can search 'users', 'groups' and 'pages' (facebook style) would have fields like keywords, userid, groupid, pageid that way the system can do a LIKE query on the keywords from one table. or would it be better like keyword1, keyword2, keyword3, keyword4, keyword5, u...
Can I have 2 unique columns in the same table?
I have 2 tables: roomtypes[id(PK),name,maxAdults...] features(example: Internet in room, satelite tv) Can both id and name field be unique in the same table in mysql MYISAM? If the above is posible, I am thinking of changing the table to: features[id(PK),name,roomtypeID] ==> features[id(PK),name,roomtypeNAME] ...because it is he...