mysql

mySQL regex in the where clause

SELECT telephone_number FROM table WHERE telephone_number REGEXP '^1[() -]*999[() -]*999[() -]*9999$'; how do i make so its valid for any number format and any number like 407-888-0909 1(408)998-7654 7776654433 876-7788 right now its only valid for 1-999-999-9999 ...

COUNT is grouping rows unexpectedly

Table items keeps track of the different types of items that can be stocked. item_type item_name last_stock 1 cake 2010-08-10 2 fruit 2010-08-07 3 soda 2010-08-07 4 water 2010-08-09 Table individual_items keeps track of each specific item. id item_type 1 ...

Insert rows of states and iso's from comma separated textfile into webbased Mysql

I'm building an international webshop and in the part where a customer has to fill in the address I wonder how I insert the rows in a comma separated text file with a list of stats in my web based MySQL? Example AM,04,"Geghark'unik'" AM,05,"Kotayk'" AM,06,"Lorri" AM,07,"Shirak" AM,08,"Syunik'" AM,09,"Tavush" AM,10,"Vayots' Dzor" I fou...

C program mysql connection

Heey, Can somebody help me out? I'm working on a simple c program that has to connect to my database, then do a query and then close the connection. int main() { MYSQL *conn; conn = mysql_init(NULL); if (conn == NULL) { printf("Error %u %s\n", mysql_errno(conn), mysql_error(conn)); exit(1); } if (...

Order By a field being equal to a specific value?

Let's say I have this MySQL query: SELECT * FROM A WHERE x='abc' OR y=0; How can I prioritize the rows so that cases where x='abc' are ordered FIRST? If y=0 but x!='abc', I want those rows to come after cases where x='abc'. Can this be accomplished with a simple ORDER BY clause? Thanks! ...

The most efficient way to setup MySQL on Ubuntu Server 10 LTS?

Very soon I will setting up a web server for a client who has a very busy site. He has a forum (run on Simple Machines Forum SMF written in PHP) that cannot really be changed. I want to move this to a separate virtual private server on it's own, as it's very busy at around 40,000 hits a day with 400+ posts a day. With the version of SMF ...

Optimal MySQL design for user-specific activity feeds

I'm building a website that constructs both site-wide and user-specific activity feeds. I hope that you can see the structure below and share you insight as to whether my solution is doing the job. This is complicated by the fact that I have multiple types of users that right now are not stored in one master table. This is because the...

What is the "best practice" for choosing character encoding with PHP/MySQL applications?

I'm self-taught php programmer, so I often don't know the "correct" way to do something. I want to normalize my character encoding practices between my PHP, HTML, and MySQL data. -- live in the US, -- work on sites for people who speak English, -- most foreign languages I will encounter are western (Spanish, Italian, French) -- living...

Google app engine combined with some mysql instances?

Hi, I have a distributed database (google app engine) which stores user ratings for products. I want to provide a service where I can give a user a histogram of the ratings all their friends have made for a particular product. A user may have a large number of friends (hundreds to a couple thousand). Selecting all the rating records ...

How to use MySQL Found_Rows() in PHP?

I try to avoid doing Count() because of performance issue. (i.e. SELECT COUNT() FROM Users) If I run the followings in phpMyAdmin, it is ok: SELECT SQL_CALC_FOUND_ROWS * FROM Users; SELECT FOUND_ROWS(); It will return # of rows. i.e. # of Users. However, if I run in in PHP, I cannot do this: $query = 'SELECT SQL_CALC_FOUND...

Storing price in PHP. What is better double or string?

I've been making "Product Object" for my app and I've been setting attributes, when suddenly I got to price attribute and stumbled upon it with question what type better to choose? I understand, that for PHP it doesn't matter so much, but lets say for MySQL it does. Any suggestions? ...

SQL Help - How to "linearize" data?

I'm not sure if "linearization" is the proper term, but I need a query that will output something like this: item_name item_price first_name last_name ----------------------------------------------- 'camera' '100' 'Little' 'Timmy' 'computer' '200' 'Little' 'Timmy' Here's my DB: http://pastebin.com/iS4QKHEb...

sql records in last few hours

I have records in mysql table and I want to know how many records were in last 3, 7 and 12 hours. What would be the sql for that? My date field in in the format as 2010-08-09 09:52:27 2010-08-09 09:52:27 2010-08-09 09:52:27 2010-08-09 10:44:46 2010-08-09 10:44:46 2010-08-09 11:58:27 2010-08-09 14:48:22 2010-08-09 14:48:22 ...

Submitting values of "jQuery Editable Invoice" into a MySQL DB using PHP

Hello Everyone, I am using this fantastic example of a jQuery editable invoice as a template for creating dynamic invoices for my users. It's working quite well and I am successfully generating the items but I now need to save the values entered into the various text fields and enter them into the MySQL database. I am confident in doi...

MySQL foreign keys' referenced table name always forced to lowercase

First, create these two tables: CREATE TABLE IF NOT EXISTS TAB_COMPANY ( ID INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, NAME VARCHAR(100) NOT NULL, PRIMARY KEY(ID), UNIQUE KEY(NAME) ) ENGINE=INNODB DEFAULT CHARSET=UTF8; CREATE TABLE IF NOT EXISTS TAB_DEPARTMENT ( ID INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, NAME VARCHAR(100) ...

Massive Wordpress database migration

This is a doozy of a question... I am developing a new wordpress website to replace my client's massively sized blog (tens of thousands of pages). Their existing blog is built on a custom PERL blogging platform from 1992...so as you can imagine, it needs updating. My job is to find the BEST possible way to migrate all of their existing...

Solution for passing special characters from iPhone to PHP Script (SQL Database)

OK, after much research, and help with stackoverflow users, I've narrowed down how to fix my problem. Most special characters work, except the ampersand. So, how can I implement the following code twice? I want to do this [[tvQ.text stringByReplacingOccurrencesOfString:@"&" withString:@"and"] stringByAddingPercentEscapesUsingEncoding:N...

MySQL query display problem.

I have this query that should display all posts related to a specific tag but for some reason it will only display one result instead of all the results can some one help me fix this problem? Here is MySQL code. "SELECT users.*, users_posts.*, tags.*, posts_tags.*, FROM users INNER JOIN users_posts ON users_posts.user_id = users.user...

one database or multiple databases

Possible Duplicates: What is better: to have many similar databases or one database with similar tables or one database with one table? Database per application VS One big database for all applications SaaS Application with many users. One database for each user's information tables and one central database, one database con...

How can I SELECT from multiple tables in CodeIgniter

This is really boggling my mind on how to do this in ActiveRecord Queries in CodeIgniter. Maybe I'm over complicating it. I have three tables: (to keep it simple I'll only show the relevant fields) Articles (id, title, text, author) Comments (id, article_id, text, author) Users (user_id, user_type, first_name, last_name, email, passwo...