Date: 03/27/08     Keywords: no keywords

Hi all. Can anyone please tell me what the difference is between these two selects?


SELECT * FROM `table`

and

SELECT * FROM `table` WHERE 1

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

Date: 03/22/08     Keywords: database, asp

I need to design a database to store a large number of numeric markers and am interested in feedback on how best to do this.

There are currently 80+ markers that need measuring, and additional markers will likely be added in the future. Each set of markers is associated with a test_ID (containing date of collection, ect). Also, each marker will have meta-descriptive info stored in a separate table.

Reporting and performance are important aspects of this system. I need to be able to generate percentiles, max/min, standard deviation, and other statistics on a given date range. I will also need to query against certain markers with values above or below certain ranges.

An existing (old) database was created years ago with each marker as a separate column - we've worked with this but any time new markers are added I have to spend several days revising & testing scripts. I want to avoid this pitfall in the new system if possible.

There will be a LOT of data stored in the database, so performance and optimization are key objectives.

So, my initial thought was to store markers in a table like this:

CREATE TABLE results (
id int(20) NOT NULL auto_increment,
test_id int(10) NOT NULL,
marker_id int(3) default NULL,
marker_value decimal(8,3) default NULL,
marker_second_value decimal(8,3) default NULL,
PRIMARY KEY (id),
KEY test_id (screen_id),
KEY marker_id (analyteid)
)

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

Date: 03/11/08     Keywords: mysql, sql

*long story cut out*

Anyone know of a company that uses replication as a way of backing up a MySQL db?  The current solution isn't hacking it right now.

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

Date: 02/21/08     Keywords: mysql, browser, database, sql, web

Hey all

I'm having difficulties connecting to an online database.

I uploaded it online using the webhost's database manager.

As I would need update the database very frequently, therefore going through the webhost way (using the database manager to update) is pretty troublesome... I thought of connecting to the database directly from MySQL query browser. 

So I have the following details (they are all correct, as given and checked by webhost):
Database name
Username
Password
Server IP

When I start up MySQL query browser, it shows me this "Connect to MySQL Server Instance" window, prompting me for:
Stored Connection (i filled it up with 'nameofdatabase' @ 'server IP')
Server Host (i filled it up with 'server IP')
Port (it's 3306 by default, so i left that number there)
Username (i filled it up as given by webhost)
Password (self explanatory, filled that up too.)
Default Schema (i filled it up with database's name)

After filling all those up, I SHOULD be able to connect to my database. Instead, I keep getting this error:
"Access violation at address 00342F02 in module 'libmysql.dll'. Read of address 00000004."

What exactly does this error mean??? I really need to connect to the database ASAP. Would appreciate any form of help. Thanks!

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

Date: 02/02/08     Keywords: php, mysql, sql

Hi!

According to my host, this query is blocking the mysql by causing a max_user_connections error, but I can't see the problem in it. Can anyone help me, please?

(The code is PHP)

$query = "SELECT $entries_table.fic_title, $entries_table.id, $entries_table.fic_url, $entries_table.wip, $entries_table.notes, $entries_table.date, $entries_table.hits,
$fic_cats_table.fic_id, $fic_cats_table.cat_id
FROM $fic_cats_table
LEFT JOIN $entries_table ON $entries_table.id = $fic_cats_table.fic_id
WHERE $fic_cats_table.cat_id = $cat AND $entries_table.date = '$date' ";

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

Date: 01/31/08     Keywords: no keywords

hi,
new to this community, first post in here. i was wondering if anyone out there that can help me thru IMs on trying to get multiple categories per entry. right now, i have one category per entry and i have no idea how to get multiples.

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

Date: 01/31/08     Keywords: no keywords

I have two tables with the same dimensions (say, prices for two consecutive years, 2004 and 2005)-specifically, same number of columns.  Is  there any way to combine  these two tables in one? Somehow didn't find it in Toad help...

thanks,

j_a 

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

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