mysql discussion

Recent Posts View Recent Posts | View Archived Posts

Message from Sarah
Most recent post: 10/28/2007
3 authors and 4 replies.

Hi -

I am relatively new to mySQL. I am trying to loop through a bunch of
XML feeds, pull out some info and place it into a mySQL table. I am
able to loop through the feeds, pull out the relevant info and print
it to my browser with no problem using simpleXML for the parsing.
When I try to do an insert into mySQL it'll only pull the first item
and date from each page instead of all of the data. I have no idea
why this'd be. Any ideas? Here is the code I have so far:

<?php

$con2 = mysql_connect("localhost","user", "password");
if (!$con2)
{
die('Couldn't connect: ' . mysql_error());
}
mysql_select_db("Database", $con2);

$entriesinsert = "0";

$resultBlogsMarket = mysql_query("SELECT * FROM myspacepages");

while($rowMarket = mysql_fetch_array($resultBlogsMarket))
{
$myspaceid = $rowMarket['friendid'];
$myspacemarket = $rowMarket['market'];
$myspaceblogURL = "http://blog.myspace.com/blog/rss.cfm?friendID=".
$myspaceid;

// Load and parse the XML document
$rss = simplexml_load_file($myspaceblogURL);
// Here we will put a loop to include each item's title and date
foreach ($rss->channel->item as $item)
{
$title = $item->title;
$titlequotes = "'".$title."'";
$pubDate = $item->pubDate;
$formattedDate = "'".date("Y-m-d H:i:s",
strtotime($pubDate))."'";
$myspaceidquotes = "'".$myspaceid."'";

$resultBlogs = mysql_query("SELECT friendid, title, pubDate FROM read more about Parse XML and insert into mySQL

Message from Abandoned
Most recent post: 10/28/2007
3 authors and 8 replies.

Hi..
I want to do index in database.
My table:
id(int) | id2(int) | w(int) | d(int)

My query:
select id, w where id=x and id2=y (sometimes and d=z)

I have too many insert and select operation on this table.
And which index type can I use ? Btree, Rtree, Gist or Hash ?
Also I want to unique (id, id2)..
Now this is my index. is it give me good performance ?
CREATE UNIQUE INDEX ind1 ON test USING btree (id, id2)
CREATE INDEX ind2 ON test USING btree (id)
CREATE INDEX ind3 ON test USING btree (id2)
CREATE INDEX ind4 ON test USING btree (w)
CREATE INDEX ind5 ON test USING btree (d)

I'm too sorry my bad english.
King regards.. read more about Which index can I use ?

Message from Jack Vamvas
Most recent post: 10/28/2007
2 authors and 2 replies.

I'd like to create a database schema of an existing mysql db - I have access
to phpMyAdmin .

Is there way via sql statement to derive the (data types, foreign keys, )
of existing tables?
--

Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com read more about create database schema

Message from Stefano Schuppli
Most recent post: 10/28/2007
3 authors and 3 replies.

Hello everyone,
I resolved the problem related to stressed apache making it scalable.

Now I have the problem with mysql.

How could I make it working fast enough with an unlimited number of
ussers?
Yes,... improving hardware ok but the problem remains. It will be full
and low at the end.

The updates are 7% and the selects are 93% of the total. (it's a
typical web2.0 site) read more about Make it scalable

Message from 182719
Most recent post: 10/27/2007
2 authors and 2 replies.

Hi, I have this huge database of millions of records ... mixed in are
dozens and dozens, maybe hundreds of entries that are in all or mostly
uppercase. I don't want to lowercase them, or delete them.... just
ID them so they can be normalized. Is there any method to search
across a table for instances where more than 1 consecutive letter is
simply uppercase?

like

THE dog jumped over
THe dog jumped over
The DOg jumped over

.... all'd be noticed .... can this be done or is this beyond the
capability of a query search?

Best regards read more about Is this kind of query possible? :-)

Message from lavrov2005
Most recent post: 10/27/2007
2 authors and 2 replies.

I need to implement an application license server for my company. Each
application will periodically update the license server with its
status via an appache server. The license server will eventually scale
up to 40million-100million records.

Here is my thought: Rather than storing everything on one mysql
server, I'll spread the 40-100million records over N high end mysql
servers. I'll also assign a unique UUID to each application. When
the application contacts the appache server, the appache server will
hash the UUID to a particular mysql server.

Does this sound like a scalable approach ?

Sergei read more about I need some mysql implementation suggestion [40 million+ record license server]

Message from herbasher
Most recent post: 10/27/2007
3 authors and 5 replies.

Hello!

I have three tables, mapping out a n:n relationship of authors and the
books they worked on:

table 1: authors (id, name)
table 2: authorships (author_id, book_id)
table 3: books (id, name, bestseller tinyint)

Here's two different queries I want to run:

1. Select each author, and how many books he has worked on.
2. Select each author, and how many bestseller books (bestseller = 1)
he has worked on.Not exactly sure how to do this, can someone help me out with this?
Thank you,
Rob read more about Counting number of associated many-to-many items

Message from dennis.sprengers
Most recent post: 10/27/2007
2 authors and 3 replies.

Consider the following table named "stats_dt". It stores an hourly
summary of hits and sessions on the server:

+------------+------+----------+------+
| date | hour | sessions | hits |
+------------+------+----------+------+
| 2007-09-02 | 10 | 6 | 140 |
+------------+------+----------+------+
| 2007-09-03 | 10 | 6 | 140 |
+------------+------+----------+------+
| 2007-10-19 | 10 | 6 | 140 |
+------------+------+----------+------+
| 2007-10-19 | 11 | 2 | 54 |
+------------+------+----------+------+
| 2007-10-22 | 15 | 1 | 22 |
+------------+------+----------+------+
| 2007-10-22 | 16 | 5 | 150 |
+------------+------+----------+------+
| 2007-10-23 | 12 | 6 | 210 |
+------------+------+----------+------+
| 2007-10-24 | 19 | 4 | 53 |
+------------+------+----------+------+
| 2007-10-24 | 13 | 8 | 65 |
+------------+------+----------+------+
| 2007-10-25 | 7 | 2 | 37 |
+------------+------+----------+------+
| 2007-10-26 | 7 | 3 | 10 |
+------------+------+----------+------+
| 2007-10-26 | 9 | 1 | 17 |
+------------+------+----------+------+

I want to count the number of hits and sessions in the past week, so I
wrote in message:

SELECT `date`, SUM(hits), SUM(sessions)
FROM stats_dt
WHERE YEARWEEK(`date`) = YEARWEEK(CURDATE())
GROUP BY `date` DESC

The resultset looks like:

+------------+-------------- read more about Sum all sums

Message from erkules
Most recent post: 10/26/2007
2 authors and 2 replies.

Using "show open tables" I see some tables locked. But I cant find
the locking process. When using "show processlist" I cant get any hint
which of the processes holds the lock (or is waitiing for the lock).

How can I get the process-IDs of th processen holding (and waiting)
for a lock?chears
Silvio read more about Finding locking process

Message from Florian Lindner
Most recent post: 10/26/2007
4 authors and 6 replies.

Hallo,
I use phpMyAdmin (most recent version) to adminstrate an MySQL 5.0 server.

I have created an user wuhr who permission for the DB wuhr\_%:
SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY
TABLES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EXECUTE

Now I'm suprised that the user is permitted to create new databases. CREATE
gives according to phpMyAdmin the permission to create tables and DBs. Now
I want to the user to be able to create tables but not DBs.

How can I do that?

Thanks,

Florian read more about Permission to create DB

Message from Mauro
Most recent post: 10/26/2007
2 authors and 3 replies.

Hi all,
anyone can say me something for give me the right way in obtain what I put
in the object?
The batch procedure must run on a Linux Box.
In this linux box, there are operative php page that read from the MsSql
database, and all works good (mssql_connect,
mssql_select_db .... )
What I need is create a copy of some tables from the MsSql db to one new
MySql db.
The MySql db can be renew all the time that the batch procedure run.
Thanks at all for your collaboration,
regards,
Mauro. read more about Copy data from MsSql database to a MySql database in batch mode.

Message from kath
Most recent post: 10/26/2007
3 authors and 7 replies.

Hi,
I have a array of string(say array is of length 1000). I want to
compare those string in array with one table column
- whether that table column has a string
if yes
don'thing.
if no
then insert that string into table.
- whether table has obsolete row i.e, the one present in table and
not in array
then delete that row.

How do I go about this, because I see, it isn't feasible to loop
through array and search table to find new string OR loop through each
row from table to find some obsolete row

How can I accomplish this task more feasibly(without running query for
each string, for comparission)? Is there any way to find this kind of
problem. I would've been easy if I had to compare two tables(with
UNION and INTERSECT), but it isn't the case.thanks,
kath. read more about how to compare array of String with column of a table

Message from kristincollins77
Most recent post: 10/26/2007
4 authors and 4 replies.

I have a SQL Enterprise database that I need to import into a MySQL
database. I generated a .sql file using the SQL Enterprise generate
script tool, and when I try to import the file. I get the following
error.

ERROR 1064 (42000) at line 1: You've an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the
right syntax to use near 'IF EXISTS (SELECT name FROM
master.dbo.sysdatabases WHERE name = N'lobster')
DR' at line 1
"

Any help is appreciated.

Thanks! read more about Import .sql file into MySQL

Message from jackal_on_work
Most recent post: 10/26/2007
2 authors and 2 replies.

HI faculties,
I have a table tbl_users that has charset set to UTF8. The
password column in this table, should store passwords in encrypted
form. For doing this, I am using the AES_ENCRYPT() function. To my
surprise, when I tried the following statement, no data got inserted
for the password column.

These are the statements:

create table tbl_users
(
id bigint(10),
login varchar(10),
password char(10)
) default charset = utf8;

insert into tbl_users values(1, 'dan', aes_encrypt('dan', 'test'));

select * from tbl_users;

select aes_decrypt(password, 'test') from tbl_users;The last select statement gives me a NULL value.

I have tried seeing the character set settings using the following:

show variables like '%char%'

Variable_name
Value
------------------------
-------------------------------------------------------
character_set_client
utf8
character_set_connection
utf8
character_set_database
utf8
character_set_filesystem
binary
character_set_results
utf8
character_set_server
latin1
character_set_system
utf8
character_sets_dir C:\Program Files\MySQL\MySQL Server 5.0\share
\charsets\What can be the possible problem? Please suggest.

Thanks in advance
Jackal read more about Encryption with UTF8 charset issue

Message from Ken
Most recent post: 10/26/2007
2 authors and 2 replies.

I recently had some difficulty with the --defaults-extra-file flag so
I thought I'd share my experiences and point out an area where the
documentation can be improved. All of the following pertains to MySQL
5.0.45

I wanted to avoid putting my password on the command line so I read
the mysql(1) man page regarding the --password flag. It refers me to
6.6, "Keeping Your Password Secure". Section 6.6 is actually, "Disk
Issues" so I turn to section 5.8.6 and get to "Keeping Your Password
Secure". [1] After reading this section, I decide that I'd like to
use an option file to store my password and pass the name of the
option file on the command line. I turn to section 4.3.2, "Using
Option Files" which introduces me to the --defaults-extra-file. Here
are some command lines.

Original Command Line
mysql --user=user --password=password ...

New Command Line (after reading 4.3.2)
mysql --user=user --defaults-extra-file=mysql.cnf ...

The first command works and the second gives me this error.

mysql: unknown variable 'defaults-extra-file=mysql.cnf' [2]

I pore through the manual looking for clues to no avail. I read the
man page for mysql and it does not even mention the flag. [3] I try
`mysql -?` but my shell expands the ? and I get an error. mysql --
help finally leads me to the answer when it lists --defaults-extra-
file under the heading, "The following options may be given as the
first argument:". Now my command line looks like this and it read more about --defaults-extra-file

Message from panchettone
Most recent post: 10/26/2007
3 authors and 14 replies.

Hi, I'm trying to run this query:

--->SELECT COUNT(v.id) as C, v.title, v.desc, v.cat, u.username, t.team_name
FROM videos AS v
INNER JOIN users AS u ON v.user_id = u.user_id
INNER JOIN team_members tm ON tm.user_id = u.user_id
INNER JOIN teams AS t ON tm.team_id = t.team_id

but I get an error
[
#1064 - You've an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near '.title, v.desc' at line 1
]

If I omit COUNT (but I need it...), it perfectly works.
What am I doing wrong? How could I rewrite my query? Thanks! read more about query with COUNT (what am I doing wrong?)

Message from ctobini
Most recent post: 10/25/2007
2 authors and 2 replies.

Hello,

I have a problem when setting the innodb_log_file_size variable in
my.cnf (MySQL 5.0 with Ubuntu 7.04, 2GB RAM).

When I start MySQL I have a 'failed' error status.

Here is the InnoDB variables I fixed in the conf file :

innodb_data_file_path = ibdata1:1000M:autoextend
innodb_buffer_pool_size = 1024M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 256M
innodb_log_buffer_size = 8M

I commented line by line the InnoDB variables, only
innodb_log_file_size makes the server crash at startup.

Could you see what the problem could be ?

Thanks,

C. Tobini read more about innodb_log_file_size variable makes the server crash

Message from Marco Cotroneo
Most recent post: 10/25/2007
2 authors and 5 replies.

Salve!
Esiste un modo "automatico" per criptare e decriptare I dati in
database mysql?

Grazie
Marco read more about Criptazione dati

Message from Jack
Most recent post: 10/25/2007
3 authors and 4 replies.

I want to embed MySql server in my shareware application, and I have the
following questions-
1. Is it common to do so in a Windows PC under XP or Vista?
2. Does it has a silent installation (which means that the user does not has
to treat the MySql installation as it installed in the background)?

Regards

Jack read more about MySql server in my shareware application

Message from Daniel Kaplan
Most recent post: 10/25/2007
3 authors and 5 replies.

So I have my database and I read text from a webpage then put it in a record
including a field that is dataype TEXT.

Of course I lose line breaks, so what happens is that the following
sentence:

This is line 1...

... and this is line 2

becomes "This is line 1... ... and this is line2"

Is there some function I can call in Perl or something else I can do so I
store and keep the format of the text?

Many thanks.... read more about Saving linebreaks in a TEXT field

Message from MZ
Most recent post: 10/25/2007
3 authors and 12 replies.

Hello!

I am not an admin of my database, but I care of this database.
I have a web page which inserts records into the mysql database about history of
just viewed banners.
I have written a script which let my page display banners in order. Every
reloading web page should make different banner display. That`s why I store
history of every banner of every section and I display banner which were viewed
the oldest time ago.
I don`t know why my mysql database sometimes don`t insert data into the database
and that`s why my web page shows every time the same banner. If I leave my
database for a few minutes and then reload the page, there are records added
into the database.

In the past I took care of Microsoft Access databases and there was something as
compacting. What should I do make my mysql database response quicker? How can I
compact my database and should I do this during working hours when many people
use my web page? Is it needed to do it i.e. at night where fewer people look
into my web page?

Please help me, I really apprieciete your efforts.
Thank you
Marcin read more about Too fast web page for mysql database

Message from laredotornado
Most recent post: 10/25/2007
2 authors and 2 replies.

Hi,

I'm using MySQL 5.0 and have InnoDB tables. How do I select a random,
single row of data from a given table, "T"?

Thanks, - Dave read more about Selecting a random row

Message from BankHacker
Most recent post: 10/24/2007
3 authors and 8 replies.

I am trying to find out why MySQL has to read MYD data file when just
doing an index query like this:

SELECT COUNT(*) FROM articles WHERE MATCH (title,body) AGAINST
('keyword');

This is a table with just 2 fields and it has been indexed with full-
text option this way:

CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL
PRIMARY KEY, title VARCHAR(200), body TEXT, FULLTEXT (title,body) );

I was though MYD file was read only when querying for data not stored
in MYI file, but that idea seems to be wrong.

Any explanation or hint? Thanks in advance.

P.S.: You may get further details in http://mysql-full-text.blogspot.com/ read more about How to avoid slow reading MYD file?

Message from lawpoop
Most recent post: 10/24/2007
2 authors and 2 replies.

Hello all -

I have a table with a column that has a UNIQUE constraint. This column
is a code number that a user uses to log into a website.

The value is only good for 2 days, so I figure 5 characters is long
enough to keep someone from guessing a code. Also, since we do not want
someone guessing codes from a sequence, I'm using the RAND() function
( along with a few others to create a zero-padded five digit number )
to get a random number:

INSERT INTO Cards ( code_number ) VALUES ( LPAD( FLOOR( RAND() *
100000 ), 5, '0' ) )

My concern is that I could get a failed insert if the RAND() guesses a
number that happens to exist already in the table. Is this a concern?
How could I fix it without using a pattern, or referencing existing
codes, so that the user could not guess the pattern?

Bonus question: Is there a control statement that I could use in PHP
to continuously re-try my existing statement on failure, until RAND
comes up with an unused code number? read more about UNIQUE constraint and RAND() function?

Message from nino9stars
Most recent post: 10/24/2007
3 authors and 5 replies.

Hello,

I have tried searching for this subject, but can never seem to find
exactly what I'm looking for. Here is the situation I have at hand.

I want to find a quick (hopefully painless) way to copy information
that I have stored in the database. Here is how the information is
stored:

TABLE: LeagueInfo
league_id auto_increment primary key
league_name, etc...

TABLE: Teams
team_id auto_increment primary key,
teams_league_id (ties to LeagueInfo's league_id)
team_name, etc...

TABLE: Players
players_id auto_increment primary key
players_team_id (ties to Teams' team_id)
players_name, etc...

TABLE: Stats
stats_id auto_increment primary key
stats_player_id (ties to Players' player_id)
stats_game_id, etc

Since a lot of the information provided is tied to each other, how can
I ensure that when I "duplicate" the entire league, all the
appropriate ties stay consistent, and all the new copied information
will have the appropriate new auto_incremented fields. For example:

If I have a league called Football League with 2 teams (Team1 and
Team2), and they have 5 players each with varying rows of stats for
each player, how can I ensure that the new league (let's call it
Basketball League) will easily duplicate all the information.

The reason I'm doing this is because the program I'm running has the
same teams and people join different leagues. All the team and player
information stays the s read more about Is there a quick way to copy database information?

Message from Mukesh Singh
Most recent post: 10/24/2007
2 authors and 2 replies.

What's the command and syntax to rename a table/view/database in MySQL
5.0? read more about Renaming an object

Message from hugo
Most recent post: 10/24/2007
3 authors and 3 replies.

Ok, I know it was totally dumb...

But now when I go to http://localhost/phpmyadmin/index.php

I get this message:

Error

MySQL said: Documentation
#1045 - Access denied for user 'root'@xxxxxxxxxxx'localhost' (using password:
NO)

I had not set up any other passwords and I'm just learning at the
moment, so I have no idea how to get back into mySQL.

Can anyone help??

Thanks,

Hugo read more about Help I deleted root@xxxxxxxxxxx!

Message from Bob Bedford
Most recent post: 10/24/2007
2 authors and 2 replies.

Hi all,

I have to link a person's address to the city table to know the state but
unfortunately the same ZIP may be in 2 different states (do not ask me
why....)

So I may have this in ZIP table:
ZIP CITYNAME STATE
1410 Thierrens VD
1410 Correvon VD
1410 Prevendavaux FR

So linking the city like this:
select STATE from person
left join city on person.ZIP = city.ZIP returns 2 rows.

I have tried to add a fulltext index in the city table and doing that

select STATE from person
left join city on (person.ZIP = city.ZIP and MATCH(city.city)
AGAINST(person.cityname))
is not allowed as AGAINST need a string and can not work with a field name.

How can I fix this ?

Thanks for answering.

Bob read more about subquery a city

Message from Peter
Most recent post: 10/23/2007
2 authors and 2 replies.

Am I right in think flush is a good thing and it simply refeshes
everything?.

ie. if I have deleted lots and tables, data and users etc in the past few
months.

Is it helpful if I do a flush /All to 'refresh mysql?

Or is my understanding of the flush term incorrect?

tia read more about My understanding of Flush?

Message from howa
Most recent post: 10/23/2007
2 authors and 4 replies.

In the past, InnoDB has problem with SMP machine, is this problem
sovled yet? If yes, in what version?

THanks. read more about Is InnoDB safe to run in SMP system?

Message from jean-michel bain-cornu
Most recent post: 10/22/2007
2 authors and 2 replies.

Hi,

I'm looking for a tool giving the capability to browse the database data
by the tables relations.
For instance, if I have a customer table with a key linking on the
customer's city, if I click on a customer row, I'd get the corresponding
city data, and so on if the city table contains links to other tables.

I tried google, mysql web site, and sourceforge, and I got nothing.

Thanks
jm read more about Data driven relations browsing

Message from WebSQL - staff
Most recent post: 10/22/2007
4 authors and 4 replies.

This is a web based application that can be installed on a web server in
your environment to manage your database over a browser.

At moment is available only a demo, try it now and let us know what you
think about.

Home page: http://www.websq.eu
Demo page: http://www.hostingJAVA.it/-websql/

Regards,
WebSQL staff. read more about [ANN] WebSQL - web based database administrator tool

Message from UKuser
Most recent post: 10/22/2007
2 authors and 8 replies.

Hi,

What am I doing wrong with this statement?

UPDATE company
SET new_test =
concat_ws(
(
Select Property from `properties` where `Company_ ID`=1
)
,'#')
WHERE
company.`Company_ ID`=1

I want it to insert Property into the field new_test separated by a #
but it errors on me.

Any help'd be great.

Thanks

A read more about Subquery returns more than 1 row

Message from damezumari
Most recent post: 10/22/2007
5 authors and 8 replies.

With the where condition

like '%hell%'

records with 'hello', 'shell', etc will be returned in addition to
records where 'hell' is found as a word by itself.

How do I write a condition that will ONLY return records where 'hell'
is found as a word by itself?

Regards,

Jan Nordgreen read more about Query to return record only if search text is found as whole word

Message from hugo
Most recent post: 10/22/2007
5 authors and 8 replies.

Hello,

I am trying to create a data model to represent meals and menus. As
part of this, I have tables:

Ingredient
Quantity
measurement
Method
Equipment

Recipe
Dish
Meal
Menu

a recipe can contain multiple ingredients (each with quantities and
measurements)
a dish can contain multiple recipies
A meal can contain multiple dishes and so on.

I currently relate the the ingredients and Recipe tables using an
'IngredientList' table which contains fields:

ID
RecipeID
IngredientID
Quantity
Measurement ID

This means that for a given recipe there will be multiple rows in the
'IngredientList' table.

I'm then intending to go on and create 'RecipeList' which will
contain:

ID
Dish ID
RecipeID

and so on.

My question is that it seems like the original 'Dish' table is almost
pointless, as it'll contain hardly any information. the fields will
be:

ID
Name
Type

Am I going in the right direction?

Your help is greatly appreciated.

Thanks,

Hugo read more about Relating tables - Another novice question

Message from Bruce B.
Most recent post: 10/21/2007
3 authors and 3 replies.

I am attempting to select first and last name from MySQL database newemail
using the code below.

<?php
$name = $_POST['name'];
$Fname = $_POST['Fname'];

echo "The passed value is $name $Fname\n";$query = "SELECT emailadd from newemail where name, Fname = '$emailadd'";
and I get an error message.Error Message:

The passed value is bixby bruce SELECT emailadd from newemail where name,
Fname = ''
You've an error in your SQL syntax. Check the manual that corresponds to
your MySQL server version for the right syntax to use near ' Fname = ''' at
line 1

Can anyone help? TIA read more about Select clause

Message from Legend
Most recent post: 10/20/2007
3 authors and 4 replies.

I was wondering if it is possible to group linked elements using SQL
directly. For example, if I have some table like:

Column A | Column B | Column C | Column D
1 | 2 | A11 | A32
2 | 3 | A13 | A22
3 | 4 | A14 | A23
5 | 6 | A19 | A24
7 | 8 | A18 | A25
1 | 9 | A17 | A26

I want to group all linked items together. So in the above case my
output'd look something like:
Table 1:
1
2
3
4
9

Table 2:
5
6

Table 3:
7
8

or maybe something like with the above one still preferred:
1,2,3,4,9
5,6
7,8
extracted into another table...

One other question I had was, can a database have a million tables? I
mean, if I had many such groups,'d it be efficient to create a
table for each group or is there any other approach? read more about Grouping Elements

Message from Cloink
Most recent post: 10/20/2007
3 authors and 3 replies.

Hi,

I'd like to make a MySQL user session always be in transactional-mode,
i.e. have autocommit OFF always.

I have not found a way of doing this - is it possible, or do I just
always have to type
SET AUTOCOMMIT=0;
every time I logon?

There does not appear to be an option in the config file to set this;
neither does there appear to be any way of running a standard script
at logon.

I'm on Windows - I thought I might be able to pass in a login-file on
the command line (using a shortcut) as though running in batch mode,
but then it just runs the script and exits straight away!

No more ideas.

Help appreciated,
Cheers,
Clark. read more about No AUTOCOMMIT by default?

Message from olaf870
Most recent post: 10/20/2007
2 authors and 2 replies.

Hello
is there a quick MySql-Statement to select all rows from a table which
dont have an correspondent record with the same key in a second
table?
kind regards read more about Detect not exitistant

Message from michaelnewport
Most recent post: 10/20/2007
2 authors and 3 replies.

On 25 Nov 2006, 23:46, "michaelnewp...@xxxxxxxxxxx"
wrote in message:
wrote in message:
> Gang,
> It is, after all Friday. I'm waiting for Michael Newport to
> point out how there are more jobs for MySQL than Ingres
> on the job boards.
fromwww.jobserve.com, contract/perm, last 7 days
sql server 8659
oracle 3945
access 1201
sybase 1045
progress 924
db2 346
mysql 297
informix 91
postgres 42
adabas 22
ingres 15
rdb 5
did I pass the test :?)
=====================================================
take 2
fromwww.jobserve.com, contract/perm, last 7 days
oracle 3343 - down 600
sql server 2528 - the original figure seems suspect, perhaps I forgot
the " 's
access 979 - down 200
progress 920 - same
sybase 641 - down 400
mysql 384 - up 100
db2 296 - down 50
informix 52 - down 40
adabas 25 - same
ingres 16 - same
postgres 11 - down 30
rdb 5 - same
same time next year :)

=====================================================
take 3
from www.jobserve.com, contract/perm, last 7 days

oracle 3202
"sql server" 3191
sybase 672
mysql 484
db2 320
informix 55
postgresql 28
ingres 23
adabas 18
rdb 3

forget access and progress, too m read more about comparing db job markets

Message from sparky
Most recent post: 10/20/2007
4 authors and 8 replies.

I have the following view, and'd like to do a group by date to
find out what is the current size of production. I can do the group
by week (dt), but that returns 1 barn 7 times per week, so it sums it
up 7 times. How can I do this group by and use the distinct to have
only one barn per grouping?

ID Barn_ID Size StartDate Received StartDate_p_Life dt
1 1 5000 08/02/2007 1 22/10/2007 10/18/2007
5 2 4000 17/10/2007 1 12/08/2008 10/18/2007
6 3 6000 15/05/2007 1 11/11/2007 10/18/2007
1 1 5000 08/02/2007 1 22/10/2007 10/19/2007
5 2 4000 17/10/2007 1 12/08/2008 10/19/2007
6 3 6000 15/05/2007 1 11/11/2007 10/19/2007
1 1 5000 08/02/2007 1 22/10/2007 10/20/2007
5 2 4000 17/10/2007 1 12/08/2008 10/20/2007
6 3 6000 15/05/2007 1 11/11/2007 10/20/2007

maybe it's not even possible to use the distint - I can not quite figure
it out. read more about distinct in a group by Date

Message from strawberry
Most recent post: 10/20/2007
3 authors and 4 replies.

Just curious, is this expected behaviour?:

mysql> SELECT GREATEST(SLEEP(3),SLEEP(4));
+-----------------------------+
| GREATEST(SLEEP(3),SLEEP(4)) |
+-----------------------------+
| 0 |
+-----------------------------+
1 row in set (8.71 sec) read more about The Big Sleep

Message from Legend
Most recent post: 10/20/2007
2 authors and 2 replies.

I have a table in this format:

Column A | Column B
1 | 2
1 | 3
1 | 4
2 | 5
.....
.....

What I want to do is something like this:

If in Column A there is a change in the number i.e. from the above
table there were many 1s which transitioned into a 2. I want to record
this change. I want to extract these two rows into another table. How
can I make this possible? read more about Previous Row Comparison

Message from Legend
Most recent post: 10/20/2007
2 authors and 2 replies.

I have two tables:

Table Name: table1
Table Rows: a b

Table Name: table2
Table Rows: a b c d e f

Initially c of table2 is empty. I want to actually fill it up from
table1 using some statement like:

UPDATE table2 SET c={SELECT a FROM table1 WHERE b=d}

Where d belongs to table2.

Is this possible? read more about Need some urgent help in using UPDATE command in SQL

Message from nobody
Most recent post: 10/20/2007
2 authors and 2 replies.

I got a full backup of all my mysql databases to a dumpfile dumpfile.sql

how do I restore only one database from that dump file instead of
everything?

thanks read more about restore one database from full backup

Message from omeldoid
Most recent post: 10/20/2007
3 authors and 4 replies.

i have two tables that share a field with items. I can count the
occurences for each table like "select count(item), item from items1
group by item" and the same for items2. now I want to compute the
ratio between the item count for each item from items1and items2. is
this possible in one query? if not, how'd you suggest I approach
this problem? any pointers much appreciated,

andrej read more about combine two queries and compute from values

Message from spamtheaussie
Most recent post: 10/20/2007
4 authors and 8 replies.

Hi,
I'm trying to figure out why MySQL won't use my index when I do a <
comparison, but will use it when I do an = comparison.
I thought a BTREE index should work for < comparisons?

mysql> create index start_date_index using BTREE on
case_sessions(start_date);
Query OK, 423608 rows affected (12.99 sec)
Records: 423608 Duplicates: 0 Warnings: 0

mysql> explain SELECT * FROM case_sessions where start_date <
'2007-06-02 08:00';
+----+-------------+---------------+------+------------------+------
+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
+----+-------------+---------------+------+------------------+------
+---------+------+--------+-------------+
| 1 | SIMPLE | case_sessions | ALL | start_date_index | NULL |
NULL | NULL | 423608 | Using where |
+----+-------------+---------------+------+------------------+------
+---------+------+--------+-------------+
1 row in set (0.00 sec)

mysql> explain SELECT * FROM case_sessions where start_date =
'2007-06-02 08:00';
+----+-------------+---------------+------+------------------
+------------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys |
key | key_len | ref | rows | Extra |
+----+-------------+---------------+------+------------------
+------------------+---------+-------+------+-------+
| read more about Indexes with [code]< or >[/code] comparisons

Message from pim
Most recent post: 10/19/2007
2 authors and 2 replies.

Dear All,

I just can not seem to get myself a nice query.
I have a table with data about people meeting eachother. They meet
randomly and now I want to show a history, about who you met and how
many times.

Table and contents could be as followed:

userleft_id | userright_id

1 2
2 4
5 2
6 1
4 2

As you can see, users 4 and 2 met eachother twice, the rest only once.
So if I want to show user 2 who he met results should be:

User 2 met:

1 1 time
4 2 times
5 1 timeHow can I get this result from here? The table as thousands of rows.
Of course, I could retrieve everything in PHP and compare everything
but I guess it could be done in SQL as well.

In another table the names of all these people can be found which I
want to have as well in a JOIN.
Normally, I'd use

INNER JOIN usernames ON meetings.userleft_id=usernames.user_id

but this time I don't know whether it is userleft_id or userright_id
of who I want to have the name.Anyone have a suggestion?Kind regards,Pim Zeekoers read more about Complicated COUNT or DISTINCT with JOIN, Anyone?

Message from hugo
Most recent post: 10/19/2007
3 authors and 4 replies.

Hello,

I have been designing a schema for a web app that will allow users to
create a weekly meal plan and shopping list.

At the heart of the design is the model for recipe data.

I currently have a hierarchy of:

Menu eg. Mozzarella Salad, Beef Casserole , Apple Flan w. Raspberry
cream.
Meal eg. Apple Flan w. raspberry cream
Dish eg Apple Flan
Recipe - eg pastry case, apple sauce, topping
Ingredient - eg flour

At the lowest level - ingredient, I also have:

Measurement
Method
Equipment tables

My question is, how do I connect these tables? I have created an
'ingredient list' table, which links the ingredient, measurement and
recipe tables and contains:
recipe ID
Quantity
Measurement ID
Ingredient ID

Do I need to create list tables to link every other set of tables up
the hierarchy?
How do other systems deal with hierarchies?
Can you help?

Thanks you for your help.

Hugo read more about Dealing with hierarchies - A novices question.

Message from Jo
Most recent post: 10/19/2007
2 authors and 2 replies.

Hello,

I have a german stopword list.
My Problem is that stop words containing
special characters like
german umlaute (=E4,=FC, etc.) aren't treated as stopwords.

What could be the reason for this?

Any help appreciated.

Jo read more about stopword list ingores german umlaute

7/24/2008 10:52:25 PM