MYSQL :: Which Design Would Be Correct?

Min Returns Correct Min Value, But Not Correct Row
select min(time_to_sec(bests.time)) as aggregate_value, * from bests left join events on bests.event_id = events.id where fid = 13463 group by bests.event_id

if i query that i get the correct min value as "aggregate_value" but everything else is for a random row (presumably the first row as ordered by id asc). is this the correct functionality of mysql?

Which One Ini File Is The Correct One ?
I have install mysql in my windows machine.The confusion part here is that I find there 7 different ini files. The files are my.ini,my-huge.ini,my-innodb-heavy-4G.ini,my-large.ini,my-medium.ini,my-small.ini and my-template.ini. So which one is being used as the configuration files. I assume it is the my.ini one right ? So why is there so many other ini files? My another question is that in my.ini I dont find the option for the bin-log settings but ? Because I want to the option of binary log to be enable to allow me to trace back if there is any mistake ?

Correct Syntax
I have a database of addresses where I am trying to remove rows that have a particular duplicate field.
For example, say there are 15 rows in the table sales_info that have the value 'Mr. Smith' in the contact_name column.
I've tried using the following command but received an error (1064 check you manual for the right syntax):

UPDATE sales_info
DELETE* FROM sales_info
WHERE contact_name IS NOT UNIQUE;

Can anybody help me with the right syntax to use?

Correct Results From Db
I have a column of type text, in this column I am adding in numbers that relate to another column in the table. For example the column in question could end up having the following data in it: 1, 2, 6, 9, etc

The problem I am having is if I want to return all the results that relate to lets say the number 1 and if the column in question has more numbers in it than the number 1, I don't get anything returned.
Is this an error with my ASP script or the way the data is stored in the dababase. Do I need to change the data type,

Correct Join For DELETE
Whats is the correct syntax to delete from a table using joins to associate the id?
I have sommin like:
mysql_query("DELETE FROM gallery
LEFT JOIN gallery_subcat as s ON s.subcat_id = g.subcat_id
WHERE s.maincat_id='".NumValidate($_GET['maincat_id'])."'");

Is This Search Arranging Correct?
If I want to filter results from using a LIKE search if I ORDER it by the field it's searching will it return results based on the number of times what I am searching for occurs in the field?

Correct Use Of MERGE Tables?
I have seen general MERGE table usage for logging where a table is created for a fixed period of time, e.g. a month.

I am looking to do something different. I currently have a very large table, let's call it emails. There are say a million rows in here. The problem is queries running against this can be slow especially any queries against rows that are not indexed (not all of them can be).

What I am thus thinking is to use MERGE tables. Any email that has not been opened in the last year would be moved into an acrhive table. Then what is now the emails table would be renamed so I have emails_archive and emails_current and create the MERGE table emails.

Then, any searches would be run on emails_current unless a checkbox was clicked to run on *all* emails.

Is this a correct way to use MERGE tables? Is moving rows from emails_current to emails_archive going to be a problem with indexes and unique keys? I believe I can insert rows into the MERGE table and they will by phyiscally put inside emails_current but will check the unique indexes on both the tables.

Correct Wrong Spelling
Is it possible with mySQL to get results when the spelling is not exactly correct. E.g. a visitor fills out the field with Amstrdam (note that the "e" is missing). Is there a function for mySQL that also shows results similar to Amstrdam:
-Amsterdam
-Amsteldam

Correct Search Query
Can anyone help me out with this please. I am looking at searching among comma delimited id's stored inside "IDs", trying to figure out what the correct SQL query for that would be. I need to extract the ID where IDs contains the value 7. I know I can't use something like "WHERE IDs LIKE '%7%'" because the result would be inaccurate. So how do I go about it, please? Any directions would be appreciated, thanks!

+------+-----------+
| ID | IDs |
+------+-----------+
| 1 | 3,17,20 |
+------+-----------+
| 2 | 1,7,9,12 |
+------+-----------+

Picking The Correct Date
My client's art gallery site has a page that lists the current show and the upcoming show. Each show is a approx a month long, and the dates can overlap.

Instead of making the gallery owner update the database with a checkbox indicating which show is current and which is upcoming, I'm trying to automate this page so that mysql will decide upon which show is current and upcoming and display the correct one. Unfortunately, this isn't working as planned.

PHP

CREATE TABLE `shows` (
  `show_id` int(10) unsigned NOT NULL auto_increment,
  `show_name` varchar(30) default NULL,
  `show_text` text,
  `show_postcard_img` varchar(60) NOT NULL default '',
  `show_start_date` datetime default NULL,
  `show_end_date` datetime default NULL,
  PRIMARY KEY  (`show_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

--
-- Dumping data for table `shows`
--

INSERT INTO `shows` VALUES (1, 'Grand Opening', 'txt', 'postcard1.jpg', &#55614;&#57159;-09-08 16:52:08', &#55614;&#57159;-10-06 16:52:13');
INSERT INTO `shows` VALUES (2, 'Current Show', ' txt', 'postcard2.jpg', &#55614;&#57159;-10-13 17:18:00', &#55614;&#57159;-11-13 17:18:00');
INSERT INTO `shows` VALUES (3, 'Upcoming Show', 'txt', 'postcard3.jpg', &#55614;&#57159;-11-17 11:53:26', &#55614;&#57159;-12-14 11:53:34');

$query_current_show = "
SELECT show_id,show_name, show_postcard_img
FROM shows
WHERE MONTH(show_start_date) = MONTH(NOW())
AND YEAR(show_start_date) = YEAR(NOW())
OR MONTH(show_end_date) = MONTH(NOW())
AND YEAR(show_end_date) = YEAR(NOW())";

$query_upcoming_show = "
SELECT show_id,show_name, show_postcard_img
FROM shows
WHERE MONTH(show_start_date) > MONTH(NOW())
AND YEAR(show_start_date) = YEAR(NOW())
LIMIT 1";

As of yesterday, show 2 "Current Show" should be the current show, and show 3 "Upcoming Show" should be showing as the upcoming show. Instead, show 1 is still current and the upcoming show is show 3.

Correct Datatype Question
I'm trying to construct a database containing information that will eventually be brought into a flash movie. It's basically information to be used in a digital clothing catalogue slideshow. I need to include the following information for each clothing item:

Style Number
Description
Colours
Sizes

I was just going to create 4 tables (haven't decided yet how these will be structured) but just wanted to ask a question about the description column. I was going to make this a text datatype even though information included in it would be be repeated. That is possible information could be a number of sleeve lengths, cuff types, fabric compositions, collar types etc. All of these could very well be placed in separate tables to avoide redundancy but I just wondered if, in this particular instance, it is really necessary to do so.

I was going to create a simple CMS so that the client could enter new items, ie. style number, description, colours, sizes and then maybe have the ability to delete these items. So I guess my question is should I be using a text datatype for the description or be breaking the information up further?

Need To Know How To Group By To Get Correct Count
In the following sample data, I have a field called x_duration which is the duration in seconds for a particular record in the database. The table is denormalized so that one row equates to one 'play' or 'session' for a particular track.

I need to create a view to support a reporting application where, among other metrics, I need to show how many records we have where x_duration >=15 seconds and how many records we have where x_duration >=20 seconds. Additionally, I need to sum the x_duration for all records....

Correct Import Command
i am having trouble importing a database into MySql 5.0

i have created a database and have the .sql file ready but when i type in my command i never get the next step.

database name = bdatabank

command I am trying to use from the mysql> prompt is below:

-u username -p -s bdatabank < BDB-sql-2007-11-16.sql

--after typing this and pressing enter i should be getting the 'Enter Passwor' prompt. but all it get is another blank line with a prompt of ->

am i miss typing something?

Correct Syntax For An Update
two databases, one has an out of date email field.
I need the new table to update the email field on the old table.

My query selects the rows with the wrong email address:
select * FROM `oldtable` , `newtable` WHERE oldtable.memberid = newtable.memberid and oldtable.Email != newtable.email

but how do I update it?
update`oldtable`set oldtable.Email = newtable.email where oldtable.memberid = newtable.memberid

My above query fails.

Correct Syntax Construction
how to select column in the sql table with two values eg. ("delete from p where id='$name' and code='$code'" )

Cannot Perform Correct Update.
i have 2 tables, orders(orderNumber, BuyerID, ...) and status(orderNumber, status). The first table works good when I want to check the orders. Now, I need to track the orders status. That's why I created the 2nd table. The problem is that when I update status (pending or delivered), ALL my orders get the same value. I mean, I cannot update the status for just one product. When one order status is updated to e.g "delivered", all orders status get the same value.
Any ideas? Probably the problem is that I do not put any "WHERE orderNumber=..." but I don't know how to tell MySQL that the orderNumber for both tables is the SAME

Choosing Correct MYSQL
I am setting up tables to store data sent from FCKeditor.
What type of column am I best to use for storing the html code?

Cant Work Out Correct Select Query For ..
mysql> select * from parent_cat;
+-------+----------+----------+
| catid | pname | psection |
+-------+----------+----------+
| 1 | Trainers | Men |
| 2 | Bags | Woman |
| 4 | Boots | Woman |
| 5 | Fragance | Men |
+-------+----------+----------+
4 rows in set (0.00 sec)

mysql> select * from categories;;
+-------+---------------------------+---------+------+
| catid | catname | section | pid |
+-------+---------------------------+---------+------+
| 1 | Nile | Men | 1 |
| 2 | Bags | Woman | 2 |
| 4 | Boots | Woman | 4 |
| 6 | Air Force I & II Trainers | Men | 1 |
| 7 | Adidas Trainers | Men | 1 |
| 8 | Timberland Trainers | Men | 1 |
| 9 | Lacoste Trainers | Men | 1 |
| 10 | Gucci & Hogen Trainers | Men | 1 |
| 11 | D&G Trainers | Men | 1 |
| 12 | Prada Trainers | Men | 1 |
| 13 | Chanel Trainers | Men | 1 |
+-------+---------------------------+---------+------+

and the following query:

$pid = $_GET['catid'];

$query = "SELECT categories.catid, categories.catname, categories.pid
FROM categories, parent_cat
WHERE categories.section = 'Men'
AND parent_cat.catid=categories.pid
AND categories.pid = '$pid'";

At the moment the query only returns all categories.catname when my categories.catid = 1, but returns nothing if it equals something else?

What Is The Correct Format For Specifying Which Field Your Inserting Into?
INSERT INTO `table` ('field') VALUES('$field') but it comes back with an error? Can someone elaborate?

Correct Column Type For Version No (5.0.3)
I apologize if this is answered elsewhere in the forum. I've been searching but have yet to find it. I'd like to store version revisions in my DB with pertinent info to that revision. I'd then like to be able to return the highest version number. The format I would like to use is similar to seemingly most of the software developers such as MySQL, PHP, etc. The format is x.x.x, using two decimal places, such as 5.0.3, or 6.1.2.

Transfer InnoDb In Correct Order ?
I want to transfer the structure of my InnoDb database to another
mysql server 3.23.

However, I don't seem to get this to work correctly as the tables are
not transfered in the correct order such that I get a bunch a foreign
key constraints.

Even if I use the MysqlPhp admin tool, Mysql Manager, SqlYog, it
doesn't work: they make an export of the db structure but the db
structure, that consists of create table statements, isn't in the
correct order, such that when I use the export script to make the new
database somewhere else, the tables can't be created because of
foreign key constraints.

How can solve this, without putting the create statements in the
correct order manually ? Isn't there a handy tool that first checks if
other tables should be created, and if so, it does this, before adding
the table itself?

Average Query Results Not Correct
This query works, but average results are not correct.

I don't no what the problem is!

Should i use subquery's?

Joining is not working fot this type of query's?

Correct Order By For Logical Output
I have a database with laws. Every law is a table. Every paragraph of the law is a row with among others one special column where the paragraph number (1,2,2a,2b,3,etc.) is saved.

Now I want to list the paragraphs in the correct way (2 befor 2a). I always thought that "order by column +0" is the correct syntax but it seems that I was wrong.

Transfer InnoDb In Correct Order ??
I want to transfer the structure of my InnoDb database to another mysql
server 3.23.

However, I don't seem to get this to work correctly as the tables are not
transfered in the correct order such that I get a bunch a foreign key
constraints.

Even if I use the MysqlPhp admin tool, Mysql Manager, SqlYog, it doesn't
work: they make an export of the db structure but the db structure, that
consists of create table statements, isn't in the correct order, such that
when I use the export script to make the new database somewhere else, the
tables can't be created because of foreign key constraints.

How can solve this, without putting the create statements in the correct
order manually ? Isn't there a handy tool that first checks if other tables
should be created, and if so, it does this, before adding the table itself
?

ERROR 1045 Using -u -p With Correct Password
I'm facing a strange problem on a freshly installed Debian.

I defined passwords for root and several others users I created.

When I try to connect using the command :

mysql -u root -prootpassword

I receive ERROR 1045 Access Denied for user root@localhost using password=YES

but when connecting using

mysql -u root -p and typing the password when asked for, it connects without any problem ...

This is the same with mysqldump, so I can't automate backups ..

The problem appears with each and every user, whatever their rights

PHPMyAdmin works perfectly, if needed.

Any idea ?

Text Editor To Correct Queries
In Oracle there is a way to correct any mistakes in a query, by simply entering the edit command. And from there, a text editor is opened, to allow the corrections to be made to the query. This eleminates having to retype the whole query. Is this possible in MySQL?

MYSQLDUMP Not Writing Correct Sql Statement?
I am dump the database nightly for backup purposes using mysqldump. It generates the file which contains the statements to build several tables and the insert statements to populate.

However, there are two Create table statements that have invalid syntax. How can this be if MySQL is the one writing the syntax? Below is one of the Create table statements.....

Float Not Accept Correct Update
have a table donations with the following field:
amount float(10,2)

If I have a record with amount = 98765432.00

and then I try to do the following update:
UPDATE donations SET amount = "98765430.00" WHERE (donations_id = "31948")

The update seems to work and there are no errors but when I go to look at the record the amount is still
98765432.00

Any ideas? I can't figure out why it's doing this - and sometimes the number is updated but to something other then what was in the update.

Selecting The Correct Row In A Table Which Records Changes
I have an "employees" table which records which department each employee has worked in, and the date that each employee started working in their departments. Since it is possible for an employee to change departments, a single employee can have several records in this table. For example, consider the following history for Employee 42:
1. On Jan. 15, 2007, Employee 42 is hired to work in Department 21
2. On Feb. 1, 2007, Employee 42 changes departments to Department 5
3. On Feb. 14, 2007, Employee 42 changes departmets to Department 22
4. On Mar. 3, 2007, Employee 42 changes departments to Department 18

Then, Employee 42's records in the "employees" table would look like this:
CODEselect * from employees where employee_id = 42;

Correct Term For Is Null String
These won't persuade mysql.

UPDATE table SET label = 'margarine' WHERE label = ' ';

UPDATE table SET label = 'margarine' WHERE label IS NULL;

UPDATE table SET label = 'margarine' WHERE LENGTH(label) <=1;

Selecting Correct Versions Of MySQL
I have searched this forum and google to try and get a comprehensive answer to this question but i haven't found anything yet.
I am looking to select the correct versions of MySQL, Apache and Php so that they all work together. I notice that there are alot of issues with various different versions sets being chosen and there seem to be instances where it can be very problematic if the wrong one is chosen.
I am looking to use InnoDB and would like to use the latest versions of everything as possible but it doesn't really outline anywhere what works with what best. There are lots of cases that outline what can be problematic but i'm trying to avoid this if possible and find what isn't problematic.

UPDATE To Correct /n In Text Fields
Not sure if this can be done in MySQL or if I need do dump the database and correct it in a text editor.
I have a text field in my database which got pulled off of another db somewhere in the transfer line breaks became /n and they are in the text as /n can I run an UPDATE which will actually check the text field for /n and convert it to a <return>.
The db is not massive so I can always dump it and run it through bbEdit if need be

Query Of Gmdate Not Working... Is My Syntax Correct?
$query="SELECT distinct(id),subject,view, postdate FROM threads where gmdate(Y-m-d H:i:s, postdate + 3600) >= date_sub('".$current_date."', interval 7 day) order by view desc limit 0 , 5 ";

I tried to get my postdate to compare with the current date in order to display the most view topic in last 7 days in my page...

however, the above coding is not working...

Choosing Correct MYSQL Column Type
I am setting up tables to store data sent from FCKeditor. What type of column am I best to use for storing the html code?

Need Correct Syntax To Change Column Type
I want to change a column ('svid') type of varchar(3) to something else. I can't figure out the syntax. The following only generates vague errors:
ALTER TABLE spotlight_veh
ALTER svid PRIMARY KEY AUTO_INCREMENT NOT NULL;
What's the correct syntax? I'm using MySQL, of course.

When A New Item Is Added I Need To Make Sure That The ItemID Is Correct In Each Table
If i have 2 tables

table link
PageID, ItemID

table Item
ItemID, Data

each Item is linked to a page and a page can have 1 or more Items

When a new item is added I need to make sure that the ItemID is correct in each table, how do I insure this. Should I make Item ID auto increment in each table starting at 1 and make sure that Items are removed at the same time upon deletion?

Correct Table Structure To Cross Sell Products
I have implemented an e-commerce system which is currently being used in about 10 different websites. I would like to develop to second phase to it by implementing cross sell/up sell so that I can display related products to the customer when viewing one item.

The way I’m planning on forming the relationship between two liked products is by creating a table that with the following fields:

Create table cross_sell…{
Id int..,
Product_id int.., - this will hold the ID of the source product I want to compare
Related_product_id int – this will hold the ID of the product that is similar to Product_id
}

I’m not so sure if this is the industry standard for creating cross-sell/up-sell relation, so I would greatly appreciate it if you provide me with some input.

Simple Sql Select Does Not Return The Correct Number Of Rows
I have 2668 rows in my table.

select count(distinct ID) from mytable;

gives the following results: 2668

However, when I use:

select ID from uniprot;

DB Design
is there a tool to view a schematic of a MySQL DB I don't mean the .sql file I
mean a pretty chart type schematic.

DB Design
inserting a base64 string into a text field in my db. Again I may have design issues with this,but why in the world would it take 17 seconds to insert 1.7kb worth of text?
I can read the record in milliseconds, why would it be different the other way around?
What should I do to speed this up or design differently?

DB Design
I don't know why i am haveing a tough time on this one. i could just do it, but i want to do it the best way.

Its for cars; make, model, engine,

I'm just not convinced this is the way I should do it, because I also want to inlcude the years for the cars, and that vairies per model / engine combination

I was going to have a cars table that would have the following, but need help with getting the years in there also. Should Each row in the CARS table have a YEAR field, so a 1993 VW Corrado SLC and a 1994 VW Corrado SLC would have their own rows, even though they are basicly identica cars? or should I do a single row with a from and to year field?

i am leaning towards the one row for every configuration, since the data is small (mostly foriegn keys) and it would be precise and easy to look up between years, by model, etc.

Db Design
i am after a bit of advice here. i am creating a database that will apply to 3 venues london, paris and newyork with the potential for this to grow.
i will be taking bookings for these three venues. Would it be more efficient to have sepearat tables for the bookings and rates and rooms for each venue eg

london.bookings table
paris.booking table
etc

or just have one big booking table and a location id.
It is all theoretical but im working on the assumption that there will be only hundreds of site visitors each day.

Table Design
I am designing a table for "friend relationships".

I have three possible solutions:
a)
user_id (int)
friend_id (int)
created_at (timestamp)

This will require 2 rows per friendship.
b)
user_id (int)
friend_id (int)
user_id2 (int)
friend_id2 (int)
created_at (timestamp)
This requires only 1 row, but 2 indices.
c)
user_id (int)
friends (text)

This contains a comma delimited lists of all friend_ids.

I am leaning towards option 3, ofcourse, adding & deleting friends will be a bit slower, but the bulk of the load is on reading of friend data anyways.

However I have a question if this would be slower:

QUERY 1
select items.* from items inner join friends ON friends.friend_id = items.user_id WHERE friends.user_id = [my_user_id]

QUERY 2
select items.* from items where user_id IN (select friends from network where user_id = [my_user_id]

QUERY 3
$friends (variable) = select friends from network where user_id = [my_user_id]

select items.* from items where user_id IN ($friends)

Query 3 requires 2 queries.

1. Which of these would be faster?
2. And would you still choose the solution you chose in 1. if I had to use the friend userlist on multiple occasions on the same page?

(select from groups, select from bulletins, select from items, select from users)

How To Design Database
I want to build a database for menu driven.
like
file
--new
--- html
--- php
--open
--- html
--- php
--save
edit
--cut
--copy
--paste
and like that.

So,please help me how to start with designing the database as it can have n no. of levels.

DB Design Question...
I am almost done building a LAN-based Point-of-Sale system. I am somewhat new to mySQL, although I have worked with it for a few years now. I've only used mySQL for simple things like storing images and contact info for people that use my web-based contact forms. Now I want to do something a little more in-depth, and I need some suggestions.

I want to be able to not only insert each invoice into the DB, but I also want to do so in a way that would allow me to provide various reports in a logical wy. As it stands now, I have 1 table that I call "invoices", where I store all the info that is found on a completed invoice: customer's information, date, sale type, and sale details, which is a semi-colon delimited list of all the items, quantities, and prices for everything they bought.

Now, after building that table and playing with it a little, it seems rather cumbersome to work with. I will have to do a lot of parsing in order to make reports like 10 Most Popular Items, Total Profit for the last week, Total Item Cost of all tickets for month of Nov, for example, since all of those details are in a list in one column.

How would you guys build your table/tables to make such data storage and it's subsequent access more logical with less overhead? A seperate customers table linked with an items table? Or would you do something different?

How Best To Design DB On MySQL 5.0 So That ..
I couldn't find this by a forum search.

On my local PC, I have MYSQL 5.x and was wondering how best to design certain fields in my table that MySQl 4.1 ( my hosting service ) won't complain about.

For instance, I noticed that when I pushed a PHP app from my local PC, up to my Host provider account, things failed because MySQl 4.1 didn't like 1024 character varchar's.

Is there a guide somewhere that would make it easy to design an downward-compatible DB ?

OO Design With MySQL
I know how to pull data from MySQL into an ASP.NET page using MySql.Data.MySqlClient and its connection, command, and data reader classes. Now I would like to kick it up a notch and start working in OO design principles into my code. The tutorials I've seen seem to be geared toward SQL Server and so I'm spinning my wheels.

Can anybody help me find a good tutorial (or a good book) that spells out in detail how to get the most out of MySQL in an ASP.NET environment. I'm especially interested in learning how to build a data access layer and/or typed datasets.

Database Design
I need the perfect mysql_fetch_assoc function for my db class file, i wan't to use it on the following situation:

I want to select and list some fields from a table in the mysql database.

I want to list them by fieldname and not by number result.

I mean not $result['number'] , i want $result['fieldname']

Here is a little example:
=================================================================
$result = $db->execute("select id,name,text from categories where catid=$catid;");
echo ($result[1]!="") ? "<tr><td> Name: </td><td>{$result[1]}</td></tr>" : "";
=================================================================
I want to list the result by fieldname. Maybe so:

echo ($result['name']!="") ? ............................