mysql

How to alter a table to add a self join relation?

I have a table called "Users" it contain some fields such as Id, ParentId, Name. What I want is to alter this table to add new self join relation so that ParentId link to Id, but ParentId is nullable. I want to write this alter sql statment in mysql without dropping the table. ...

MySQL approach: Large self-joins to set values?

I'm working with a 12-million record MyISAM table with surname, address, gender and birthdate fields: ID SURNAME GENDER BDATE COUNTY ADDRESS CITY 1 JONES M 1954-11-04 015 51 OAK ST SPRINGFIELD 2 HILL M 1981-02-16 009 809 PALM DR JONESVILLE 3 HILL F 1979-06-...

MySQL query selecting values from two tables

I have the following two tables: Cities: id | name | county_id Counties: id | name I am passing this query a county 'name' and I am trying to select id and name of all rows from cities with the same county_id. I am having difficulty building a query across these two database tables. Here is what I have: "SELECT cities.id,cities.name ...

Gem that allows for data access using sharded mysql databases while maintaining the usage of Activerecord

This is a relatively complex problem that I am thinking of, so please suggest edits or comment on parts where you are not clear about. I will update and iterate based on your comments I am thinking of a developing a rails gem that simplifies the usage of sharded tables, even when most of your data is stored in relational databases. I be...

Can I ignore or suppress warnings in JDBC for MySQL?

I have an INSERT INTO ... ON DUPLICATE KEY UPDATE ... statement that executes fine (but with warnings) in the mysql> prompt: mysql> INSERT INTO ... ON DUPLICATE KEY UPDATE ... ; Query OK, 2 rows affected, 2 warnings (0.00 sec) Warning (Code 1364): Field 'x' doesn't have a default value However, when I try to execute the same statement...

cant undrstand the error :Access denied for user 'Pratik'@'localhost' (using password: NO) got in php

firstly i just clearify the problem. i am testing the page of login of my website. so i have two login files for testing that both contain same code but both are save in different place named "signin.php" & "login.php" resp. the problem is when i try to login from "signin.php" which located in root folder(www) gives an error:Access deni...

CMS and high traffic sites : PHP and MySQL

Hi; Is there any difference between CMS and hight traffic websites (like news portals) in logic and database design and optimization (PHP and MySQL)? I have searched for php site scalability in stackoverflow and memcached is in a majority. Is there techniques for MySQL optimization? (Im looking for a book for this issue. I have searched...

Modeling a many-to-many with multiple object types in Kohana w/ ORM

I'm working on building an app with Kohana 3.0.7, using the ORM module. I want to build an object, such as tag, where many different kinds of objects can be tagged, and these options can have multiple tags. So let's say for example I have 3 models: Tag, Post and Page. how would I structure the tables and the models to make this work bes...

At a Cross-Roads: Choice Between MySQL vs MSSQL -> Importance: Reporting

I am currently working on an application for the health care industry for my firm. This application is geared towards the smaller end of the spectrum (as in 50 or less users). The application will offer both a windows forms (wpf) app and a Silver Light app. This application will be used in the insurance side of the health care equation (...

mysql: proper way to handle relation between users and teams?

Newish to mysql DBs here. I have a table of USERS and a table of TEAMS. A user can be on more then one team. What's the best way to store the relationship between a user and what teams he's on? Lets say there are hundreds of teams, each team consists of about 20 users, and on average a user could be on about 10 teams, also note that us...

Why would I want to pay my webhost for a MS SQL database over a MySQL database?

I'm in the market for an ASP.NET host to start learning on and to host personal projects. I've noticed most of them provided both MySQL and MS SQL databases. However, usually they will only provided a couple MS SQL databases, but a large number (like 10) MySQL Databases. Can I use a MySQL database as a back end to a ASP.NET website? If ...

Fields after "ORDER BY" or "WHERE" and index in MySQL

Do Fields after "ORDER BY" or "WHERE" might have index (PRIMARY, UNIQUE, INDEX) in mysql? Consider a table with the following columns: ID | AddedDate | CatID | Title | Description | Status | Editor In these queries, are ID, AddedDate and CatID might have index? SELECT * FROM table WHERE ID = $id SELECT * FROM table ...

error calling nested stored procedures mysql

Hi I m calling a stored procedure inside another stored procedure in MySQL The error i m getting on calling simply using Mysql administrator call sp_update_back_image(2, 3); is: - OUT or INOUT argument 2 for routine void.sp_sel_options_id is not a variable or NEW pseudo-variable in BEFORE trigger The stored procedures... CREA...

How to check if email has been sent already

Hi, I have a (PHP) script which runs on a new row in MySQL. However, it will continually send the emails, even if they've already been sent...causing mass emails into the users Inbox. The script is quite big, so I cant paste it here - http://pastebin.com/6v75F4Gv Is there anyway I can somehow check if this script has already sent '...

MySQL - Why does count() change the behavior of outer join?

MySQL - Why does count() change the behavior of join I have a simple case. Events and attendees. In my test below there are no attendees yet I still want a list of events so I use an outer join and that works. However if I want a list of events and the count of attendees it only returns the first row. Why CREATE TABLE IF NOT EXISTS `ev...

MySQL: how would I go about displaying simple "categories" for this?

I'm wishing for a simple index more or less with my PHP note taking software, each note has a "language" field for the programming language its written in. When I iterate through each entry in a while loop, I'm not sure how i'd list categories of them such as: PHP: Note 1 Note 5 SQL: Note 2 Note 3 Do I need to place all entri...

How we can assign a value in mysql query

Hi How we can assign a value in the querywhat i need is assume that i write a query for output result using match against i am getting a bunch of result like http://www.flipkart.com/account.php http://www.thomas.com/account.phphttp://www.flipkart.com/account22.phphttp://www.flipkart.com/account45.phphttp://www.thomas.com/account22.phpht...

How to get related posts using PHP and MySQL

What is the best way to get related posts using PHP and MySQL? The second question is how would I get the top 5 related posts from by comparing tags and categories from each post. My MySql tables are listed below. CREATE TABLE categories ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, parent_id INT UNSIGNED NOT NULL DEFAULT 0, category VA...

Creating a related or similar posts using PHP & MySQL.

I was looking around SO and was wondering how would I go about in creating a related or similar posts to display on my web site using PHP & MySQL? What is a basic example of how to do this? ...

What is the best way to collect data for iphone sqlite db

I want to build an iphone app with data pre-filled sqlite db. But how to fill data in the beginning comes to be a small problem. My partner and I may both need to access the db and edit data. I thought to build a web interface to edit the data, but my host service only provide PHP+MYSQL solution for me, then if I put the data in MYSQL...