Date: 01/27/08     Keywords: mysql, sql

sort of a sequel to my previous post.

I'm importing a .csv file into mysql table. All entries in the original flie are in the following format: "price", separated by commas. After they are imported, the number of quotation marks doubles, i.e. in my table every entry looks like ""price"", which sucks! I need to export this into Matlab or other interfaces, or simple Excel spreadsheet, and what I get is "price" rather than price!

Could anyone help here?

cheers,

j_a

Source: http://community.livejournal.com/mysql/124524.html

Date: 01/26/08     Keywords: mysql, database, sql

hi mates,

there's a problem I have, so would be great if someone could help me.

I'm trying to import a .csv table into MySQL database. Following the instructions, I started a  new schema and a new table, which has exactly the number of columns as the table I'm importing. Then I run the following syntax:

LOAD DATA INFILE "F://Documents and Settings//Jah Alarm//Desktop//Bart//HAYForecast2005-3.csv"
INTO TABLE hay_2005
FIELDS TERMINATED BY ",";

I get the following message: Duplicate entry '"HAY2201 "' for key 1

And as a matter of fact only the first entry from the table is imported.  Besides, all the  entries look like "x" rather than just x

What am I doing wrong here?

cheers,

j_a

Source: http://community.livejournal.com/mysql/124330.html

Date: 01/25/08     Keywords: mysql, sql

Hi all,

is ist OK for MySQL that index lenght are larger than a data in a table?

Source: http://community.livejournal.com/mysql/124051.html

Date: 01/16/08     Keywords: no keywords

xvar xnum
abc 1a
abc 2b
abc 3c
wer 2a
sdf 3b
sdf 2c

SELECT xvar, xnum FROM table GROUP BY xvar

results:
abc 1a
wer 2a
sdf 3b

SELECT xvar, xnum FROM table GROUP BY xvar ORDER BY xnum DESC

results:
abc 1a
wer 2a
sdf 3b

How do I sort the grouped columns? I need this:

abc 3c
wer 2a
sdf 3b

Source: http://community.livejournal.com/mysql/123155.html

Date: 01/15/08     Keywords: mysql, sql

A table exists where the same id can be issued more than once. Each id has an accompanying color.

SQL to build table:

 CREATE TABLE `xorder` (
`id` INT( 3 ) NOT NULL ,
`color` VARCHAR( 20 ) NULL ,
) ENGINE = MYISAM 

 INSERT INTO `xorder` (
`id` ,
`color`
)
VALUES (
'1', 'green'
), (
'2', 'red'
), (
'3', 'black'
), (
'1', 'yellow'
), (
'2', 'blue'
), (
'1', 'orange'
), (
'2', 'white'
), (
'2', 'purple'
), (
'4', 'blue'
), (
'5', 'gray'
)

id	color
1	green
1	yellow
1	orange
2	red
2	blue
2	white
2	purple
3	black
4	blue
5	gray

Source: http://community.livejournal.com/mysql/122903.html

Date: 01/10/08     Keywords: mysql, database, sql

What kind of Collation setting should I use for my Comments section in my MySQL database?

It defaulted to:  latin1_swedish_ci

is this right?

Signed:  Frustrated

-------------
Update
------------

It IS right...and indeed I am a lummox!

-------------
/Update
------------

Source: http://community.livejournal.com/mysql/122656.html

Date: 12/12/07     Keywords: mysql, sql, linux

[cross posted to linux]
  I keep getting the MySQL AB emails about training coming to my town, but never go.  I'd like to formalize my knowledge with the MySQL but at the sametime I've got a pretty bad impression of almost all certification and training courses, and the one provided by AB seems like a top gun program ( 5 days to learn how to be a Admin) which makes me wonder if it will be effective.   Anyone here actually ever taken one of their courses?

Source: http://community.livejournal.com/mysql/122231.html

Date: 12/04/07     Keywords: no keywords

I'm writing a script to display Widgets and Values.  There are two tables in play here.  The first is my WidgetLookup table.  It contains widget_id and widget_description.   The second, WidgetSales, contains customer orders, with a widget_id column among other things. 

The report needs to display *all* Widgets from the WidgetLookup table, with a corresponding count of total Widgets sold and unique customers per widget. 

So something like this works perfect as long as every Widget is represented in the WidgetSales table in the supplied date range.

            SELECT
                COUNT(WidgetSales.customer_id) AS total_customers,
                COUNT(WidgetSales.widget_id) AS total_widgets,
                WidgetLookup.widget_description
            FROM
                WidgetSales
                LEFT JOIN WidgetLookup ON WidgetLookup.widget_id = WidgetSales.widget_id
            WHERE
                WidgetSales.sale_date = '2007-12-24'
            GROUP BY
                WidgetSales.widget_id

The problem is, not every Widget is sold in the given date range.  So this query will only display rows for items sold.  I need to display -all- Widgets, with "0" displayed on rows that didn't have sales.  Sounds easy but I can't figure out how to structure my query.

Any help or pointers are greatly appreciated.   I've stripped down my actual example, so if I need to post more detail please let me know. 

Source: http://community.livejournal.com/mysql/122012.html

Date: 11/29/07     Keywords: no keywords

SELECT
    COUNT( * )
FROM
    customers
    LEFT JOIN orders ON customers.id = orders.id
WHERE
    YEAR( orders.order_date ) =2007 AND
     orders.total <= 1500
GROUP BY
   orders.id

SELECT COUNT(*) FROM (query above) AS grand_total;

Source: http://community.livejournal.com/mysql/121631.html

Date: 11/06/07     Keywords: no keywords

My host is telling me this query is the one that's blocking the server and that is wrong, but for the life of me I don't know why:

UPDATE ncis_stories SET hits = hits+1, story_date = story_date WHERE id = 00960

Do you have any idea? Many thanks in advance.

Source: http://community.livejournal.com/mysql/121198.html

Date: 11/05/07     Keywords: no keywords

How could I SELECT rows where a phrase "yellow flowers" occurs 2 or more times in a TEXT field?

Source: http://community.livejournal.com/mysql/120861.html

Date: 10/17/07     Keywords: no keywords

A company is currently storing one colorID for each product. The colorID is included in the main product_list table. Reporting on different colors is pretty straightforward:

SELECT * FROM product_list WHERE colorID=1 OR colorID=2;

SELECT
    *
FROM
    product_list
    LEFT JOIN product_color ON product_color.productID = product_list.productID
WHERE
    product_color.colorID IN (1,2)

REPORT:
productID      colorID
1                     1
2                     2
3                     2
4                     1
4                     2

REPORT:
productID      colorID
1                     1
2                     2
3                     2
4                     1, 2

Source: http://community.livejournal.com/mysql/120639.html

Date: 09/30/07     Keywords: php, html, database, sql, web

Hi

I have an extremely large SQL dump file (541MB) that I am trying to run and drop the data into a database. I have attempted to use Big Dump which has worked for other databases in the past however I now run into the following error:

Fatal error: Allowed memory size of 41943040 bytes exhausted (tried to allocate 16385 bytes) in /home/netyello/public_html/bigdump.php on line 514

Source: http://community.livejournal.com/mysql/120080.html

Date: 09/24/07     Keywords: mysql, sql

Following with my previous post (thanks to everyone who answered!) I've been looking around and I've seen there are a lot of unused open tables. mysql_stat says 244 open tables while SHOW OPEN TABLES lists 66. This seems a lot of tables. There is a way to close all these tables? Could it be part of the reason the max_user_connections is reached so quickly?

Also, my host says they updated my connections from 100 to 200. Is that much?

Many thanks in advance!

Source: http://community.livejournal.com/mysql/119588.html

Date: 09/16/07     Keywords: mysql, sql

    Hello. I am trying to learn how to solve a complicated (well, for me, I guess :)) ) problem in MySQL. But to do this I first try to figure out, how to solve a simpler problem. May somebody help me? I'll try to formulate it below
    Let's say I have a table with student_name, quiz_date and grade (Pass/Fail). (Each student can have more than 1 quiz, not necessary the same number of quizes).  I need to create a list of students, whose grades match the following pattern Pass/Fail/Pass in the adjacent(!) dates. 
  There are 2 main problems for me: 1) How to compare dates. 2) What is the right way to match patterns for a single student. 

I would appreciate somebody giving me the decent way to solve this problem. Thank you a lot.

Source: http://community.livejournal.com/mysql/119367.html

Date: 09/15/07     Keywords: php, mysql, database, sql

I have a recurring problem with my site. It gets to max_user_connections very quickly. In php they advised me to make rapid-fire connections, and it works better than before, but it still builds up regularly. My host says that sometimes queries block the server. This is now a really serious problem and I'm in trouble with my host.

This happens since I tried to normalize the database by splitting information on other tables, instead of having fields with comma-separated lists. Thing is, now some query require several JOIN to get all the necessary information. Example:

$info_query = "SELECT $stories_table.id, $stories_table.series_id, $stories_table.story_num, $stories_table.story_title, $stories_table.pairing_extra, $stories_table.characters_extra, $stories_table.rating, $stories_table.spoilers, $stories_table.challenge, $stories_table.complete,
$series_table.user_id, $series_table.series_title,
$users_table.name,
$chapters_table.id as chid, CONCAT_WS('$chapter_separator', $chapters_table.chapter_num, $chapters_table.chapter_title) AS chapter_title, $chapters_table.chapter_num, $chapters_table.summary, $chapters_table.notes, DATE_FORMAT($chapters_table.date, '$date_format') AS date_f,
$ratings_table.name as rating_name
FROM $stories_table
LEFT JOIN $series_table ON $series_table.id=$stories_table.series_id
LEFT JOIN $users_table ON $users_table.id=$series_table.user_id
LEFT JOIN $chapters_table ON $stories_table.id = $chapters_table.story_id
LEFT JOIN $ratings_table ON $ratings_table.id = $stories_table.rating
WHERE $chapters_table.story_id = $story_id AND $chapters_table.chapter_num = $chapter_num";

Could this be the source of my problems? If so, do you know any alternative that doesn't load the server that much? I'm getting really desperate with this.

My host uses mysql version 4.0.27.

Thanks!

Source: http://community.livejournal.com/mysql/119223.html

Date: 09/10/07     Keywords: php, mysql, sql

     I am puzzled by the following issue. I run MySQL on my personal computer (Windows) and use phpMyAdmin to work with it. However, every attempt to use COUNT - produces the syntax error. phpMyAdmin doesn't recognize COUNT as a command. Moreover, every time I try to use tbl_name.field_name instead of just field.name I get the syntax error as well. However, if I copy exact the same SQL code (Copy/Paste) to DOS command line - everything seems to work great.  How can it be at all? Does anybody have an idea? Thank you.
    Windows XP, PHP 5.2.0, MySQL 5.0.27, PHPMyAdmin 2.9.1.1 Installed EasyPHP 1. 8

Source: http://community.livejournal.com/mysql/118843.html

Date: 09/09/07     Keywords: sql

The question from the beginner. Let's assume I have a table of students and classes they take. I know, how to retrieve number of classes for each student. 
        SELECT student COUNT(class) FROM tbl GROUP BY student;

 I need to select all student who take 3 or more classes. What is the SQL syntaxis to say WHERE COUNT > 3? 

Thank you.

Source: http://community.livejournal.com/mysql/118761.html

Date: 09/07/07     Keywords: no keywords

Several large tables linked by a common ID contain duplicate rows (across all tables).

Is there a way to identify these rows with a SELECT query?

[B]t1[/B]
id val
1 3.4
2 4.4
3 4.4

[B]t2[/B]
id val
1 1.1
2 10.3
3 10.3

SELECT t1.id FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE (???)

Source: http://community.livejournal.com/mysql/118315.html

Date: 09/05/07     Keywords: software, database, shopping

A friend of mine uses an interesting technique that I'm curious about. In addition to the initial database tables, his software (scripts, program, whatever) adds and deletes additional tables as needed to store other mostly temporary information. For example, with a shopping cart, he adds a temporary table for each user to have their own table with the items they've added.

I'm really not too keen on doing things this way. Is anything even remotely like this common and/or good practice?

Source: http://community.livejournal.com/mysql/118152.html