How do you select from mysql where last character in a string = x?
I'd like to select rows from the database where the last character in the mov_id column equals to 1 and 2. How would the query look like? ...
mysql
DB design : save different details of payment (credit or check)
my question is: i have a member that can pay in 3 different way credit card check transfer from bank account how design the table that contain the history of payment. the fields of every way is different . if i save all in one table , i have lot of blank field ? what the right way to deal it ? ...
€ char is shown as ? in UTF8 Output
I have reworked a website and now it is xhtml valid etc and using UTF8. Everything is fine, but if anywhere in the Database is a Euro-char it is just displayed as a questionmark. What would be the right way to fix this? As output is done by Typo3 i cant change much about that. ...
Version Tracking with mysql
I have an database with books. One book has one Author, Publisher. Some Prices, ID's and Descriptions. I want to keep track of changes made to one product. One way is to save the product with time AND id as primary key. Are there other ways? Are there database systems (i've only used mysql) who can keep track of changes automaticly? ...
MYSQL: How can I find 'last monday's date'
is there a simpler way than writing: select date_sub(curdate(), interval WEEKDAY(curdate()) day) as LastMonday from dual ...
What is the MSSQL equivalent of INET_ATON
As the question says what is the MSSQL equivalent of INET_ATON from mySql. The reason i need this is because i imported a IP data base from http://ipinfodb.com/ip%5Fdatabase.php into MSSQL 2005 and would like to query the table now. But based on their examples i am not sure how to do that. INET_ATON(expr) Given the dotted-quad ...
Listing both null and not null in mysql query
Let's say I have the table NAME | ID | REF foo1 | 1 | NULL foo2 | 2 | 1234 foo2 | 3 | 567 foo1 | 4 | NULL foo3 | 5 | 89 I'd like to count all instances of NULL and NOT NULL in one query so that I can say NAME | null | not null foo1 | 0 | 2 foo2 | 2 | 0 foo3 | 0 | 1 I could run these two queries selec...
MySQL innodb not releasing disk space after deleting data rows from table
I have one mysql datatable of type INNODB, it contains about 2M data rows. When I deleted data rows from the table, it did not release alloted disk space. Is there any way to reclaim disk space from mySQL. I am in a bad sitution, this application is running at about 50 different locations and now problem of low disk space is appearing ...
Does Mysql 5 have procedures for managing hierarchical data?
I use nested set model for saving hierarchical data into database table and I'm interested in if MySQL has built-in support for adding/deleting nodes or I have to write stored procedures from scratch. Thanks. ...
php/mysql - date_format and the time portion
Apologies if this has already been answered many times, but I was unable to find the answer and I was flummoxed. I have a mysql query which seemingly outputs the result I want when I run it in the database - however when I run the query through PHP, it does not output correctly. In the database, dates are stored like this: 2009-08-13T...
How to create a table in mysql for containing 3 columns?
I want to create a table containing three string columns: The JSP page Part of JSP page (like footer, header, text) Actual value text How do I do that: from the command prompt from a Java application (Create if it doesn't exist -- how common is that?) Thanks ...
What is the syntax to force the use of an index for a join in MySQL
The use of the "FORCE/USE/IGNORE INDEX" when doing a straightforward select is well-documented, but it's not clear from the documentation how to do it for a JOIN. How do you force a specific index to be used for a joined table? ...
Problem encoding UTF8 data from Rails app to Mysql
I'm having trouble saving UTF8 data in a form and having it correctly saved in mysql. In particular, via my ruby application I'm post a form that includes the following: Gerhard Tröster Which in my terminal I see is being updated in the database as: UPDATE `xxxx` SET `updated_at` = '2009-08-13 14:22:33', `description` = '<p><s...
How do you determine what should be a primary key?
It's fairly general question, but I'd like to know what do you use in determination of primary key of the table. Examples supplied with you reasoning are highly desired. I noticed that many programmers add ID column and use it as a primary key. I think, it is flawed from design point of view, as ID in that case HAS NOTHING TO DO WITH T...
selecting records limited by only those with both criteria met
OK, the title is confusing but i will explain. Say i have a employee skills db for which i would like managers to be able to search through. A manager may want to search for employees with nunchuck skills... ok great, thats easy: SELECT * FROM skilllist WHERE skillname= "nunchuck" Bam! I got a list of employees with nunchuck skills. B...
Issue changing innodb_log_file_size
I haven't done much tweaking in the past so this might be relatively easy however I am running into issues. This is what I do: Stop MySQL Edit my.cnf (changing innodb_log_file_size) Remove ib_logfile0/1 Start MySQL Starts fine however all InnoDB tables have the .frm file is invalid error, the status shows InnoDB engine is disabled so...
join as an alternative to multiple OR's
Hi. I have two tables with the following structure: table name: friends uid (PK)(FK) friend_id (PK)(FK) table name: posts post_id (AI)(PK) post_text uid (FK) //poster id I'd like to select posts that were only made from friends of user x. My inital plan was to use the following code: SELECT posts.post_text INNER JOIN friends ON po...
group by with limit and hopefully no empty sets
I've got a (for my abilities) rather complex query that I am trying to improve upon. I use the same query multiple times, changing only some parameters, so I'm hoping to not need a full-re-write. I am searching for a bunch of events by date and location. I put these into an html table where Date 1 Date2 Date3 event1...
Zend_Db query Mysqli prepare error: Operand should contain 1 column(s)
I have a problem with a query that works in phpmyadmin but not with Zend_db. You can read about the query in http://stackoverflow.com/questions/1268376/sql-statement-with-several-joins I have these tables Post ID entry Category ID name CategoryBinding ID postID categoryID And this is my php code: clas...
how to optimize mysql query if i have too many OR operators in my query?
using PHP and MySQL i have grabbed an array of facebook user ids from facebook. Now i want to find the corresponding username in my application for this array. Clearly in my application the user table contains unique username and unique fb_uid values. my rudimentary understanding oof programming led me to 2 ways: 1) use a loop and r...