MYSQL :: Sorting A Table According To User Criteria.

Returning Rows In Left Table Based On Mutliple Criteria In Right
This is starting to get to me. I'm sure there's a simple way of handling what i'm trying to do, but someone might be able to help out quicker than spending another hour searching and testing for this. Here's the problem:
Simplified tables:
ARCADES
=======
ID,
name
GAMES
======
ID,
name
ARCADES_GAMES
=============
ID,
arcade_iD
Games_ID
Straightforward enough so far right? I'm trying to get all arcades that have ALL games in a passed in set of game_id's. So for instance I might want all arcades that have the games 11 and 14, but it must have both those.
I can do soemthing like...

SELECT a.name
FROM arcades a
WHERE EXISTS(SELECT 1 FROM arcades_games
WHERE arcade_id=a.id AND game_id IN (11,14))
But that'll return all rows that match ANY of (11,14) rather than ALL of 11,14.

It all comes down to the simple thing of getting rows in a table where all criteria from a list is met, but any advice on how i would do something like this?

Unnecssarily Sorting Whole Table
This simple command always takes 0.30 seconds or longer:

mysql> select foo from footable order by -foo limit 20;

(get the 20 most recent foo's)

This however, always takes 0.0 seconds:

mysql> select foo from footable where foo>'2004-01-10' order by -foo
limit 20;

It looks like in the first example, MySQL is sorting the whole table.
But the column in question is indexed (confirmed by explain select...).
The manual says it should stop at 20 rows because I am using a LIMIT.

foo is of type datetime.

Any ideas how I can get more 0.00 second queries? Giving a start date is
not eloquent, nor can I necessarily give one. It doesn't seem like I
should need to, either.

Sorting A Parent Table
What is the best way to order the records in a table according to the number of child records they each have in a given child table? ex:

Table Parent:
id: name:
0 Michael
1 John
2 Alex

And the child table has a foreign_key that references the Parent table

Table Child:
parent_id: name:
1 Ann
1 Sue
1 Jan
0 Sara
2 Tess
0 Jill
1 Alli

I want to sort the Parent table records in the order of how many child records they have (John - 4, Michael - 2, Alex -1)

Optimize Table And Forced Sorting....
I've optimised a table but I'd like a table to be optimized and sorted according to the primary key in asc What should I do?

Indexing Table Forces Repair By Sorting
I have a problem whereby i am loading a large table into MySQL - approx 3 million rows

I then create indexes on most of the columns including normal and full text indexes on certain columns that I need to sort and search.

The indexing operation takes an age (about 7-8 mins) on each column, whether it is a normal or fulltext index. Example:

mysql> show full processlist;
| Id | User | Host | db | Command | Time | State | Info
|
| 1196 | root | PAULS:2078 | registries | Query | 457 | Repair by sorting | CREATE INDEX VALNAMEidx ON FULLTAB_113 (VALNAME
(64)) |
5 rows in set (0.01 sec)

I have tried SET MYISAM_SORT_BUFFER_SIZE=1024000000 - this has no affect

I have also tried creating the indexes when I create the table but I get the same thing.

The tables are populated by load data local infile and no additional rows are added once populated I am running MySQL 5.0.11 on a dual core pentium 2.4Ghz with 2GB RAM Any ideas how I can speed this up?

Get Selected Record Position After Sorting In Limit-table Browser?
In a table browse control with [first] [prev] [next] [last] buttons and a LIMIT setting, the records in a db table are devided and browsed in virtual pages.

In the GUI for these pages a record can be selected, and columns can be sorted.

When I sort with a record selected, changes are big that the record ends op on a page that is currently not displayed. And I like to switch to that page automatically so the selected record remains in view.

My question:
How can I find out what the selected record position or number would be, after sorting the table's total data?
Then I can calculate on what page the record is after sorting en adust this in the table browse control.

Limiting Results Per Criteria
At the moment I have a script that runs through links in several categories and orders them with the most recent first. The links are added at different times so some categories have more recent links than others. However, I want the same number of links from each category to be displayed, say 20, so I can't just limit the total number of links because the categories will not be balanced.

My previous solution was to query each category individually to obtain the results but with a large number of categories this is not efficient. So my question is, how can I focus this into one query and limit the number of results per category. Code:

The MySQL Release Criteria
Compared to other Open Source projects, could anyone please tell me the process which MySQL goes through before a new build can be released?
i.e. Apache you got Developers/ Committers, PMC Members, etc all working together to ensure releases are qualified and bug free, etc.
How does this process work with MySQL? I have been looking all over and cannot seem to find any information on how this project is managed, or what levels of management a build travels through.

Select With HUGE Criteria
what the best method is for doing a select where i have say 40,000 items of criteria.
eg select * from clients where 1 or 2 or 3 or 4 ... or 40,000.
I can buid the query up no problem using php, but the query would be huge. Is there a better way of doing this or should it be no problem for mysql.

Query With Multiple Criteria
Please tell me why this query will not work. It makes sense to me.

INSERT INTO DAILY ( FULLNAME, FNAME, LNAME, GENDER, TITLE, COMPANY, ADDR1, CITY, ST, ZIP, G1, G2, [UPPER], UPPERFULL, FLEN, LLEN, F1, F2, F3, F4, F5, F6, F7, F8, F9, F10, F11, F12, F13, F14, F15, L16, L17, L18, FNAMEPOSS, ACCEPTED, [SESSION] )
SELECT RON.FULLNAME, RON.FNAME, RON.LNAME, RON.GENDER, RON.TITLE, RON.COMPANY, RON.ADDR1, RON.CITY, RON.ST, RON.ZIP, RON.G1, RON.G2, RON.UPPER, RON.UPPERFULL, RON.FLEN, RON.LLEN, RON.F1, RON.F2, RON.F3, RON.F4, RON.F5, RON.F6, RON.F7, RON.F8, RON.F9, RON.F10, RON.F11, RON.F12, RON.F13, RON.F14, RON.F15, RON.L16, RON.L17, RON.L18, RON.FNAMEPOSS, RON.ACCEPTED, INVTABLE.SESSION
FROM RON, INVTABLE
WHERE (((RON.FULLNAME)=[INVTABLE].[NAME]) AND ((RON.ACCEPTED) Is Null));

If I remove RON.ACCEPTED Is Null it works. WHY?

Searching On Multiple Criteria
I'm building a calendar application and have created the appointment function that allows you to add appointments.I have a 'startTime' and 'endTime' field which allows me to set the duration of the appointment.
What I'm not sure how to do is to search on the following criteria:
1) WHERE startTime > $endTime OR startTime < $startTime
2) AND (endTime NOT BETWEEN $startTime AND $endTime) (** this is the one I'm really having troubles with!)
I'm not sure whether or not I can put those all in one query or whether I should create multiple queries.

Wildcard Search Criteria
I have a field in a table that has a person's first names (e.g. Lara Michelle). I can do a wild card search on the first name but is there a way to do a wild card search on the second name.

Grab Criteria Data
I have 30 columns which hold either a 0,1 or 2. I want to take only the tables that have ones and add them together... How can i do this without the worlds longest WHERE/AND sql call?

Concat Columns Based On Certain Criteria Using Only SQL?
I have a big table (15 million records). Each row has a parsed address (number, prefix, street, type, suffix) and I need to take these separate pieces and concatenate them. However, often times a few of those pieces will be blank, so I want to prevent any unnecessary spaces.

I'm using MySQL 4.1. I found some documentation for using if statements for MySQL 5.0, tho I dont know if any of it works in 4.1.

The basic psuedo code of what I want to do is this:

SQL Join - Ignore Value That Matches Certain Criteria
I have two tables, agreement and inventory. agreement table contains AgreementID, StartDate, EndDate, InventoryID. inventory table contains InventoryID and Description. I am using MySQL ver 3.23 so I can't do nested SELECT statements. Here's some sample data:

inventory
------------
1, Honda Civic
2, Mazda 626
3, Toyota Camry

agreement
---------------
1, 2004-11-02, 2004-11-30, 1
2, 2004-11-12, 2004-11-16, 2
3, 2004-12-05, 2004-12-07, 1

Problem: Given a date, getting a list of InventoryID that tells what cars are available for that date. I am not sure if this problem is solvable using a single query in MySQL ver 3.23. I came up with the following query, however, from above sample data, this query returns InventoryID's 1 and 3. It return's 1 because the query does not filter out record # 3 from the agreement table. Code:

Search Criteria In Join Clause (one To Many)
I've got a problem with a join clause. I have three tables: artikler, kategori and kategoriLink.

The table artikler has an unique field called artikelID

The table kategori has an unique field called kategoriID

The table kategoriLink has an unique field called kategoriLinkID and two other fields called artikelID and kategoriID

The following query returns 0 hits eventhough kategoriLink contains entries fulfilling both criterias in the select statement.

SELECT artikler.* FROM artikler INNER JOIN kategoriLink ON (kategoriLink.artikelID = artikler.artikelID) WHERE kategoriLink.kategoriID = 4 AND kategoriLink.kategoriID = 1

Does anyone have a solution to this?

Using The AND Operator For Addt'l Search Criteria
How would I go about adding an AND clause to this query to return all rows where strName column values are in $in_list array This is what I have so far:

$in_list = "'".join("','",$cen_chiefs)."'"; //$in_list is an array

$query_cen_chiefs = "SELECT * FROM central WHERE CONCAT(strName,'
',strCity,' ',strState) IN({$in_list})"; //This works w/o the extra query

I tried query 5 different ways and none worked, this is one that failed:

"SELECT * FROM central WHERE CONCAT(strName,' ',strCity,' ',strState)
IN({$in_list}) AND WHERE (strName) IN({$in_list}) ORDER BY conName"; //With addt'l search

Extracting Data Within A Column That Satifies Certain Criteria
I have a recordset in which there is a column called 'FileName' which contains paths e.g. C:mySqlmySqlest.exe. I would like to select all records that have certain extensions e.g. all records that end with ".exe" or ".txt". How do I go about this? I wrote SELECT ... FROM...WHERE FileName like (*.exe), but it said I have an error in my syntax. I am using mySQL version 4.1.

Sql Failed To Return Results For Simple Criteria
Just can't believe. A simple query I wrote to retrieve info from a mysql database failed to return results to my php webpage for some searching phrases. for example none of the following works:

$sql="Select title From bookdata Where title LIKE '%HACCP%'"
$sql="Select title From bookdata Where title LIKE '%ISO 9001%'"
$sql="Select title From bookdata Where title LIKE '&bake%'"

$sql="Select title From bookdata Where title REGEXP 'HACCP%'"
$sql="Select title From bookdata Where title REGEXP 'ISO 9001'"
$sql="Select title From bookdata Where title REGEXP 'bake'"

but all the following work
$sql="Select title From bookdata Where title LIKE '%ISO%'"
$sql="Select title From bookdata Where title REGEXP 'ISO'"

It is just a plain query except i added Match Against in the Select part in order to sort by relevance.

Relating User Table With Content Table
I have a mysql table with the curriculum vitae (CV) information of about 50 researchers. I would like each researcher to be able to edit and update his/her information. In order to do this I need to create a membership system for the researchers to access their respective CV page.

Before I do anything I want to plan this out, hopefully with a little help of more experienced developers and programmers. So I am open to any ideas.

This is the thing that stumps me the most right now:

Right now I have a CV table (with all the curriuculum vitae information) and need to create a users table that will somehow link with the CV table. Eventually, what I would like is with a login and password (that they choose) they will automatically open their CV page. These are the questions I have:

What fields in the user table should I use to link the user table with the CV table?
Once that is established, how, by providing their login and password can I direct them to their own page to update? Should one of the fields in one of the tables contain the (already existing) address of their CV page?

Deleting Rows That Meet Criteria Involving Two Tables
I need to delete all rows from Table1 for which some criteria also involving Table2 are met. I tried something like DELETE FROM Table1 WHERE id in ( SELECT id from Table1, Table2 WHERE Table1.this = Table2.that). Unfortunately this gives me an error suggesting that I can't modify a table that's in the subquery.

I have a workaround involving a temporary table but would prefer a one-shot query to achieve this result.

User Table In 5.0
I have a question about the "grande" entries in the 'user' table of
MySql 5.0.0-alpha.What is this?
Also, why are there two rows with "root" (one with localhost and one
with "grande")? Why two entries?

Question About User Table In 5.0
I have a question about the "grande" entries in the 'user' table of
MySql 5.0.0-alpha.

What is this?

Also, why are there two rows with "root" (one with localhost and one
with "grande")? Why two entries?

User Table And Version
I am using mysql 3.23.54 that came bundled with RH9. When I do a show =
databases there is no user table, I am working through the reference =
manual for 4.0 is the reason there is no user table because of the =
version? also I have tried updating to version 4.0 using the RPM =
available from the mysql site but on logon the version still says =
3.23.54 and still no user table; any ideas?

Mysql User Table?
I have number of customer and they should have different access levels, some would access only few pages. I thought if i create a table:users in my database: maindata-> table: loginuser: will solve the problem, but something is not right when i saw the default mysql user table.

maindata->table loginuser: columns are
user id autoincrement
userfname varchar
userlname varchar
email varchar
userurl varchar
accessgroup (tinyint) = 1,2,3,4
Depending on this value they will access the specific url with.

However after reading code below, I am bit confused about storing all users, should i use default mysql user table cause it has got all privaleges by default ie., insert, del, modify etc. and sounds more secured cause if by mistake users can type admin.php url? I am new to all this and sort of confused.

PHP Code: ....

Mysql User Table For Authentication
What is the best way to store usernames/password information within a mysql Table? Is there a way to encrypt the columns that house this data?

Usernames And Password In User Table
I had multiple rows in 'User' table in mysql DB.

namely
% root <no password>
localhost root <no password>
% <null> <null> //anonymous user

Now i want to connect to the server through my application.

I deleted anonymous user, and entry for % root
and set password for localhost root.
Then i created a normal user
'% testuser testpwd' with all the previliges.

So that if i want to connect to the server from any other machine than server, i have to use 'testuser' account. In my application i am connecting to server using 'testuser'.

Database - User Table - Propagation
I've started a new website and I want to have a database that works with all my other user tables in my other databases. This is my idea.

Background. Example Setup Tree
--Main Page:
-->Forums
-->Mail
-->Collaboration
-->Bug Reporting
-->Etcetera
-->Etcetera
-->Etcetera

*NOTE: Each subsection has it's own database with their own user tables

-------------------------------------------------------------------------------

UserX visits my site. There's a register screen and UserX performs the standard Registration which is then added to the userdb in the usertable.

I want to run a query that will update all subsequent user tables of their own databases.

So, one query or multiple queries will register UserX's info into all those databases.

Once the person is logged into the site, the don't need to register nor login into the other portions of my site, they just click on 'said' link and have access to that portion of the site.

Creating A User-defined Table
I am wanting to create a user defined table in mySQL. The general idea is that they all would have an asset number (PK). After that, the items can be different.

For instance, I may be keeping track of a table, in paticular it's color, size, and cost. OR I might keep track of a computer system, the OS it has installed, who owns it, etc.

Specifying A User To Be Able To GRANT On A Specific Table
I tried giving said user GRANT power on a table, but it isn't working.

He currently has SELECT-only on this table, and nothing more.

the command: GRANT GRANT OPTION ON db.table TO username

Any idea why it isn't working?

Restore Default User Table
For some reason the mysql database does not contain any tables;

How do i restore the default user table?

I tryed to reinstall mysql:

yum remove mysql httpd php php-mysql mysql mysql-server

yum install mysql httpd php php-mysql mysql mysql-server

service mysqld start

[root@localhost] ~ : mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 30 to server version: 4.1.20

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> show databases;
+----------+
| Database |
+----------+
| mysql |
| test |
+----------+
2 rows in set (0.00 sec)

mysql> use mysql;
Database changed
mysql> show tables;
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'mysql'
mysql>

But i still dont have permission to create a new database, cause mysql database have no tables.

Configure 2 Sites To Access One User Table
I have two databases on the same server with different domains. I would like both sites to access the same user database table. They have the same fields.

I thought that it might just be done with an mysql command, which will point one table to the other.

Create User Table With An Encrypted Variable
I'm trying to come up with a user database for logging onto my server where the passwords stored in the table under the column "password" are encrypted. I also need another script I have to understand when it's been 30 days since the password was changed, so I need to capture the date the password is changed. I've looked at a few reference manuals and tried the code:
CODEcreate database auth;
use auth;
create table auth (
name varchar(7) not null,
pass varchar(9) ENCRYPT not null,
fullname varchar(30) not null,
date datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
deptnumber varchar(4),
phonenumber varchar(10),
cellnumber varchar(10),
primary key (name)
);
insert into auth values
('name', ENCRYPT 'pass', 'fullname', 'NOW()', 'deptnumber', 'phonenumber', 'cellnumber', );

insert into auth values
( 'someuser', ENCRYPT ('test12345'), 'Joe User', 'NOW()', '3421', '504 257-0000', 'none' );

Table Creation Based On User Login
I'm trying to build a website that will allow the addition of users (w/ username, password, etc.).

That seems simple enough.

But is it possible to set something up (in PHP, for example) that will create a new table in the database for each new user?

Ex. John Smith signs up, is verified, and a PHP script (or Java, or whatever) creates a table specifically for him.

I know I'll need a master table of usernames, passwords, etc. These new tables would store certain user-specific data, such as preferences and other data the user might want to restrict access to.

I know I could store all of this information in a master table, but I'm trying to think ahead. Worst case scenario, if there are millions of users (unlikely, but not impossible), I'd hate to have all that data in one table, but if I have to, I can.

Error 1036: Table 'user' Is Read Only
For some odd reason, the user table that contains all the priveleges and such is read only... I can't write to it... thus I can't change my password or permissions or anything of the sort.

I've tried reinstalling the databases, but no luck, same thing... i checked permissions on /var/lib/mysql/mysql/ and all the files under it and i gave write access 2 mysql.mysql... so i dont know whats going on.

Finding Field In Table That Matches User Supplied Input
I've come across match/against, field, and locate. I've been unable to use these to match against columns.

As an example, I have a table with two columns, an index number and a domain name. The user supplies a FQDN (fully qualified domain name). I would like to compare all of the domainname rows to see if they match against the FQDN and return a go/no-go value.

The match function is close, but works in reverse. I cannot see a way to get locate or field to work with fields.

Root User Denied Access To Fetch User List.
I can still log onto MySQL administrator or the command line program but I somehow appear to have lost all priveliges to alter or view the user list from this account. Even under the command line interface I can login but it tells me access is denied when I try to alter "root" permissions on a database.

I cannot seem to log onto any of the other accounts created but obviously if I could get into the user admin I could solve this.

Retrive Category Names Where Categoryid Is Different From User To User
I am trying to retrive category names where categoryid is different from user to user

I know LIKE works with one int(1), but I want it to work with more ints (1,3,5)
SELECT * FROM `categories` WHERE categoryid LIKE (1) Working

SELECT * FROM `categories` WHERE categoryid LIKE ( 1, 5 ) Not working

Copy User Privileges To A Newly Created User.
I have just created a user with specific permissions to certain tables
in different databases. Now I have to create serveral more users with
the same permission. Is there a command in MySQL that allows me to
create the new user by copying the privileges from a template user?

Getting Record Before Or After The Record That Meets The Criteria
In a query I want to get all the results and then order those results by last name, but then I want to filter those results down to only the record that comes before or after the record that has 'empno' = '1259'.

I want to get all the results already ordered and then filter them down to one record either before or after (depending on what is needed) the record where 'empno' = '1259'.

Deleted Root User From 'user' Database!
I so stupidly deleted the root user from my mysql > user database. Now I can't login to mysql. I uninstalled the rpm and reinstalled it but it still won't let me setup a new root user. How can I delete the mysql data files so that I start again, fresh? Where are these files located? I can't find them anywhere after removing the RPM but they are obviously there because upon reinstalling the RPM I'm still in the same position--I try to do

mysqladmin -u root password 'password'

but it says "Access denied for user root@localhost"

Deleted Root User From Mysql.user !!!
While trying to grant access to root user, i deleted all entries for root from mysql.user.

I've tried to use root% (anonimous built-in administrator user) but to no good).

Is there another admin user i could use to run mysql monitor and insert root records to mysql.user? I've read

Could I use a copy of the mysql.user table?

I'm using version 5.0.16-standard on Linux Fedora Core 3.

Before ruinin the root user, i made a backup of a databasefrom the same server (not mysql database); i was wondering if that backup contains the mysql.user information somehow?

User@localhost And User@domain.com
I have been following a tutorial for a mail server which uses mysql, mainly postfix

all through the tutorial i have been asked to put a user and password in the files for the database..

which i have done

root and pass1

in the log files the error shows as

warning access denied for user root@domain.com ( using password : yes )

when i log on from the shell, to mysql

and purpously get the password wrong, I get
access denied for user root@localhost ( using password : no )

i have checked the password is correct..

is the part after the @ the issue, and how can i tell mysql that root@domainname.com is a valid user???

Selecting User,password From User
When I do: select User,Password from user in MySQL, I get encrypted password. How can I see the real value?

And, what is the proper (secure) way to access MySQL database from PHP or other scripting languages?

Sorting
I have a database with 5 teams and each team has 'n' categories and each
category has 5 sub-categories.

i.e TeamOne = Ottawa, Category 1 = Player Name, Sub-Categories = Goals,
assists, ppg, gwg, points.

Team0ne
Category 1
Sub 1
Sub 2
Sub 3
Sub 4
Sub 5
Category 2
Sub 1
Sub 2
Sub 3
Sub 4
Sub 5
Etc.

From the examples above I am trying to add the all the sub-categories
together for each category for each team in descending order.

To put it simpler - I want a total of all the goals scored for each team
and display them in descending order. I have been able to put a query
together that gets me the totals, but I can not get them in decending
order.

The query below is in a for loop in PHP and cycles through each team and
does give me the totals, just not in descending order (actually the
order is the way they are numbered in the database);

$query = "select team.name, sum(goals) from roster join reference join
team where team.idn=reference.idn and reference.idp=roster.idp and
position = 'F' and team.idn = 1 group by team.name";

Sorting
I have two columns, one is an int and the other is a date, I want the records to show first the null values in the int column and have that sorted by date, then the rest of the rows sorted by date.

I tried doing

order by int asc, date asc, but it only orders the null values properly.

so this should be the result:
date, int
3/12  null
3/13  null
3/8   5
3/9   4
3/10  4
3/11  5