MYSQL :: Select Row From Table, EASY

Easy SELECT With OR Takes A Very Long Time
We've got a database with about 1000000 books. A query in the table
BOOKS for the TITLE 'java' is very fast. We have a fulltext index on
the column TITLE.

However, if we want to do a exact same query and include the rule that
the book with ISBN '0131016210' always should be included - then the
query take several seconds to finish:

SELECT * FROM C_BOOK WHERE MATCH(NAME) AGAINST ('java') AND (ISBN LIKE
'0%' OR ISBN LIKE '1%') OR ISBN = '0131016210'

ISBN is the PRIMARY KEY.

Is there anything I can do about this?

Help With A (probably) Easy Query
I am trying to write a query to run through phpmyadmin. What I have is this:

In table "vb_user" I want to pull the data in field "username" and copy that username in another table called "vb_userfield" in a field called "field5". Both tables share the field "userid" which is how the relation is made. I know this is probably very simple but man... I just can't seem to pull it off.

I've got about 500 members and I really want to populate that field "field5" with thier username without having every one of them doing it manually.

Easy Query...probably
I've got three tables:

- events
- tasks
- docs

Events are made up of one or more tasks and tasks may or may not have docs.

What I need to do is get all the events with associated tasks and docs.

QUICK AND EASY ONE
im fairly new to php/mysql so please bear with me
I am trying to upload a txt.file created from an excel spreadsheet using the following query:

LOAD DATA INFILE 'path/file.txt' INTO TABLE my_table;

im getting a syntax error!
what could be wrong? are there any other attributes i should be adding to this query? i also tried uploading a csv version of the same file and get the same error.

Easy Way To Delete Duplicates?
I had 2 membership db's. One had all the members (65K) db2 had all the members that have registered on the website. I am wanting to consolidate the two.

Basically i took both, and merged them together into their own db. now the membership list is about 80k. 20k or so of that are duplicates. My idea was to export the thing as .xls and remove the duplicates via Microsoft Excel. However, Excel has a limit of 65Kish rows. so i can't really go about that.

Pros/cons (easy ?)
Say you have a double opt in mailing list, of which the subcriber list is
store in the db. Im still somewhat of a newb, so bear with me... are there
any pros/cons as to keeping the 'unverified' subscribers in a different
table, aside from organization?
I did just that, where, when they subscribe, they are put in a temp table,
and them moved to the real table once verified (and the temp entry deleted).
At the time, I did this thinking organization of data... but as an
afterthought, am thinking I just did a lot of unecessary coding... ... and
am thinking just an y/n field for verified or not in the primary table would
suffice.

It's not actually a mailing list, just thought that'd be the easiest
example.... the main table holds about 1000 entries, and there about 10-20
temps/days. The only con to my setup I can think of (again, as an
afterthought) is deleting the unverified entries after a week and the extra
resources of going through the entire db searching for the week-old
unverifieds, but with the size of my db, I dont even think that is a con in
my case.

Did I waste a lil time there w/ the extra table?

Create User Easy?
Is there an easy way I can just create a MySQL User... and give him the privledges to only do things to databases that he from now on creates..?

Or is it gonna be every time I create a database I would have to give him privledge to that database? What type of route would I have to go with that...

Also when his user connects to the MySQL Server is there a way I can do a SHOW DATABASES and show only the databases that he has created? He needs to also be able to Create/DELETE/APPEND/UPDATE any database that he creates.

What's the easiset way about going around this route? Am I going to have to Grant him Permissions on every database that he creates? Does the root account have to grant the permissions?

I Think It's An Easy To Fix Sql Syntax Problem
can anybody help me :

@mysql_query("INSERT INTO leden WHERE gebruikersnaam = ".lekubb($_POST['gebruikersnaam'])." (status) VALUES (Global Admin)") or die(mysql_error());

Ik get this error :
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE gebruikersnaam = t (status) VALUES (Global Admin)' at lin

You can see the script online at http://www.webmasterhelp.be/systeem/testje.php
When you fill in something and press enter you'll see the error.
If you need more info or something you can mail me at arne_dejongh2@hotmail.com or post something here.

Easy Collation Changing.
A while ago, I messed around with collations etc, without understanding the rammifications.Ended up with some tables swedish, some general and others, but all were under Latin1. After some time a few queries started breaking. Anyway, I played and tinkered and eventually managed to fix.
The problem was that I couldn't go and Alter thousands of columns manually, so a PHP script was created, and works fine.
But for the sake of knowledge, is there a way to change the Character Set and Collation stuff of an entire database(s) quickly and easily using SQL?

Quick And Easy Update Query? Or Not Possible?
I need to do a global change to a column in my database. I know this is poor planning on my part, but I recently made a change to all of my image extensions for speed purposes.

I have a column called thumbnail_path, and every value ends with .GIF. I would like to change them to all end in .JPG. How can I do this? Is this even possible to do in one update query? or will I need to select, parse w/ php and then update?

Quick & Easy MySQL Sync?
Does anyone know of any quick and easy way to sync a local development mysql server to a remote server and back. I'd like to be able to optionally sync either direction?

Sql Virgin. An Easy Question For U Experts Im Sure
Im a database virgin, i would like to know more but find it quite confusing, although im sure its not!

Im setting up a cms and have installed wamp on my machine and its working. Now the cms has asked me to create a sql database so ive gone to php my admin and put a name in the creat field and created it. Its showing in the left field of phpmy admin.

Now the cms is asking for the name of mysql login and mysql passwoord!

Where the hell do i find that? ive never created one!

Easy Question Re: Type Sizes
Just wondered, what exactly does:
int(11) do
does that mean you could store UP to an 11 digit number in the field?
ie. 01234567890
with the highest possible number:
99999999999

and does the same apply to tinyint and all the other int types?

if that is the case, why would one chose tinyint(1) over int(1)?

and also I guess the same goes with text/varchar types.

Easy Way To Get Time Zone Offset
I need to get the standard offset value from GMT for any given time zone name in the mysql.time_zone_name table. Is there an way in MySQL to do this?

For example:
I have 'US/Eastern' time zone. I know that the value I'm looking for is -5. How can I get this out of MySQL. I don't care about DST offset, just standard time offset.

How Do I Employ This Easy Access Query Technic
I Access it is very easy to build queries off of queries built of of even more queries.
with only a few certain limitations it works quite well and is easy in the Access Graphic interface.

I am completely self taught in Access so while I can build an entire enterprize application from scratch, I am a little sketchy on some of the technical terms. I think what I am doing in Access is called "layered" queries.

HOWEVER, whatever you call it, I am not so certain I'll be able to enjoy the same functinality with the MySQL database I have recently be asigned to admin.

First of all, the database, while in MySQL is accessed (for me) only through an interface in the UBB Forums SQL Commands control panel.

Secondly, while I can write and save standard text SQL queries, HOWEVER the saved queries are not necessarily saved with a certain name and do not appear to be able to be referenced in other queries. The saved queries are in a seperate table. As a side note, I can tell that the previous admin knew, or did even even less than I becasue the index key for my very first saved query was 1.

sigh....

Anyway, becasue the site owner has not added my email address to the contact info for the site's web host, I do not have access to the main MySQL control panel in the home folder. I only have access to the forum tables through the UBB Threads control panel.

I am considering attempting to make some tables to use in place of the layered queries, however I am uncertain if they will be in the same folder and thus if I'll actually be able to access them through the UBB Threads control panel.

However, I am wondering if anybody can share with me anything I might be missing as to how I can, or even IF I can layer queries in MySQL.

An example of what I want to do is this:

Desired result is to know the count of posts by hour of the day, filtered by Year and by month, BUT ONLY GROUPED BY HOUR OF DAY.

In Access, I could easily build a first query that filters the posts table to the year and month(s) desired, and then build my 'Group By Hour/Count of Posts' query off of it.

Short of actually creating a new table with the filtered data, can anybody share with me how I might be able to do the same thing in MySQL?

Easy Way To Enter 20000 Records To Mysql Db
I'm looking for the easy way to enter a lot of information to Mysql Db for me the best way is like in access you just paste the buffer (from excel) to the table you selected and it does it.

Very Easy Question - Assigning A Base A Username And Password In PHPmyadmin
Maybe I am just getting very tired all of a sudden, but I have forgotten how to assign and username and password to a MySQL base - can someone please put me out my misery on how to do this

SELECT INNER JOIN Performance VS Single Table SELECT
I have designed my database using a somewhat oriented approach. Rows are objects, and different type of objects are in different tables, but since my objects share a common set of fields like ExpireDate, Archived, Draft, CreateDate, CreatorID, etc; I have an 'objects' table with these columns, and I have set up a foreign key in other tables where there is a need for a row-to-row integrity.

Now my question is, since I have to SELECT using INNER JOIN with object and the corresponding table, merely to filter out archived rows, I am wondering if I would be better off actually putting the common fields into each respective table and get rid of the 'objects' table altogether. I mean, is it more job for the server to actually JOIN the tables for each SELECT versus having a clean design with object oriented approach ?

How bad is my design, and what are the recommendations of experts who obviously were tempted to create an object oriented database design?

How Do I Rewrite A Slow Subquery Into A Fast Join? (Prolly Real Easy For A Non-noob To Answer)
I used subquerys because they made more sense to me, until the table got "a lot" of data in it (not really... just 1000 entries) - then all querys including subquerys slowed down to 4-5 secs EACH!! :) This is insanely slow.

What I am doing here below is looking into what messages a user has already read in the subquery, so that none of the ones he already has read will EVER again show up for him.

So, how do I rewrite this subquery:

NOT IN (SELECT reffen FROM $readt WHERE sender = $nr)

From this entire SELECT:

SELECT halfref, brokensms, DATE_FORMAT(arrived, '%y'), DATE_FORMAT(arrived, '%m'), DATE_FORMAT(arrived, '%d'), DATE_FORMAT(arrived, '%H'), DATE_FORMAT(arrived, '%i'), priv FROM $halft WHERE sender = $nr and halfref NOT IN (SELECT reffen FROM $readt WHERE sender = $nr) order by id desc limit 1

Into a faster join of some sort?

I'd aprichiate if you told me what each part of the rewrite actually does, because I been reading about joins for a day now and still don't get them at all!

Trying Abort One Select Accessing One Table Locked By Lock Table.
I'd like to configure one time to abort one transaction that are accessing one table locked by lock table write command ex:

Transaction A
mysql> start transaction;
mysql> lock table mytable write;

After I'll start new transaction B (another connection)

mysql> start transaction;
mysql> select * from mytable;

It's will become long time to wait...

My transaction B never "die".

I tried to configure innodb_lock_wait_timeout=20 in my.ini without success,

Rights To Create Table, Select, Then Drop Table..
I have a need to get data from the db that requires me to=20

1) do a select and create a new table with the results=20
2) run a query against that new table=20
3) drop the new table=20

I have a script on my server that does this using the root account that has all on *.* for the db. It works fine.=20

I now want to get these results on a web page.=20 I want to create a new db user for my .php web page to use to connect to the db that only has the needed priviledges on that specific db to get the job done.=20

what priviledges do I need to give that user?=20

currently I have the following but the user can't even log into the db from the command line..=20

mysql> show grants for user;=20
+-----------------------------------------------------------------------
---------+=20
| Grants for user@% |=20
+-----------------------------------------------------------------------
---------+=20
| GRANT USAGE ON *.* TO 'user'@'%' IDENTIFIED BY PASSWORD
'6fe4c0ab2cf30ae3' |=20
| GRANT SELECT, INSERT, UPDATE, CREATE, DROP ON `db1`.* TO 'user'@'%' |=20
+-----------------------------------------------------------------------
---------+=20
2 rows in set (0.00 sec)=20

when I do a "show grants for user", what should I see to allow what I want?

SELECT From A Table Depending On A Result From Another Table
This may sound a bit weird so i'll explain:

I have three tables:

tbl1foobar
------------------
ididid
type enum('foo', 'bar')infoinfo
typeid

Now I want to get the info from either foo or bar depending on tbl1.type
Is it possible to do this in one select statement?

Return 1 Row With Concatenated String (was "Really Easy But My Minds Gone Blank")
I'm creating a website for my local American Football team and the database structure is as follows:

CREATE TABLE players
(
`PlayerId` tinyint unsigned NOT NULL,
`Jersey` tinyint unsigned,
`FirstName` varchar(12),
`LastName` varchar(20),
`ContactNumber` varchar(12),
`Email` varchar(40),
PRIMARY KEY(`PlayerId`)
);

CREATE TABLE playerposition
(
`PlayerId` tinyint unsigned NOT NULL,
`PositionId` tinyint unsigned NOT NULL,
PRIMARY KEY (`PlayerId`, `PositionId`)
);

CREATE TABLE positions
(
`PositionId` tinyint unsigned NOT NULL,
`PosAbbr` varchar(3) NOT NULL,
`PosLong` varchar(30) NOT NULL,
PRIMARY KEY (`PositionId`)
);
Now each player can play a multitude of positions say PlayerA can play QB, RB and TE. My query is as follows:

SELECT pl.PlayerId, Jersey, FirstName, LastName, PosAbbr
FROM players pl, playerposition pp, positions po
WHERE pl.PlayerId = pp.PlayerId
AND pp.PositionId = po.PositionId
Now it will return 3 rows for PlayerA, 1 row for each position they play. What I want it to return is 1 row with a concatenated string of their positions something like QB/RB/TE. My mind has just gone blank. Can anybody enlighten me on this.

P.S. I'm running 4.1 on the server.

Select All Elements In One Table That Do Not Appear In Another Table
Say that I have a table A with column colA and table B with colB.

How do I select all elements of colA in table A that are not in colB of table B?

Select All Records From One Table That Does Not Appear In Another Table
I need to select all records from tableA that does not appear in tableB. I am using the following query that does work but is very very slow. Is there anything i can do to speed up the query?

select tableA.ID, tableA.Name, tableA.Surname
from tableA where tableA.ID != ALL (select tableB.ID FROM tableB where tableB.status = 'inserted' or tableB.status= 'edited' or tableB.status = 'deleted' );

I have as example 6000 records in tableA and 2000 records in tableB. tableB is used to track which records have been inserted, edited or deleted from another system so the query should return all records that do not exist in the other system yet.

This query runs for about 3mins and just gets slower the more data there is.
Can anyone suggest anything that could make this a sub second response?

2 Table Select.
I have the following query and it erros out saying the select field name is ambigious. The tables are exactly the same. How would I go about fixing it?

I just want it to return the user_id if it returns a value for either table.

SELECT user_id FROM amt_users a, pro_users p WHERE `a.username` = '$username' AND `a.password` = '$password' OR `p.username` = '$username' AND `p.password` = '$password'

2-3 Table Select
I Have 3 tables
-----------------
person
ID
person_name

product
ID
product_name

person_review
ID
product_ID
person_ID
---------------

Basically I a person reviews a product, their ID and the products ID gets put into the person_review table. I want to be able to go through the person_review table, and see what products haven't been reviewed, and what products haven't been reviewed by 'x' user.

So far, this has gotten me, what products a specific person HAS reviewed, but I am unable to get which ones they haven't reviewed. Code:

Select What Is In One Table And Not In The Other
I have a dilema, I have two tables, the primary ID from the first is a foreign ID in the second. How can I structure a query to select only the records from the first table ONLY IF the primary ID of this table is not listed in the secondary table as a foreign key.

Select, Only If Id Is Not In Another Table.
I have two tables that look like this:

Objects(id, description)
Votes(<id>, ip)

Now I want to select rows from objects like this:
SELECT * FROM Objects WHERE id != (SELECT id FROM Votes WHERE ip = 'currentIP')

currentIP is filled in automaticly by PHP, but I'm able to convert a query from MySQL to PHP, so that's not important :)

Doe anyone have a good idea?

How Do I Select < 5 From All Of The Table?
I am trying to veiw all of the table past the first 5 rows....

SELECT * FROM table WHERE * < 5;

I know thats not even close, but does anyone know what im trying to do and have the solution?

Select From Table
Is it possible to do a "select * from table where id=value1 and id=value2"? I have tried this and the query returns no data despite the fact that the table has records that match the criteria in the select?

Select * From Table
Wanted to know if there was a quicker way of doing a search in the table instead of having to do something like:

"SELECT * from table where id LIKE '%$search%' OR thing LIKE '%$search%' etc. Instead, simply select * from the table where ANY column is LIKE $search

I wanted to know this in order to simply shorten the query (and learn if there's a way to do it) and also, so that if I search for two different strings, for example, one that states the date, whereas the other states a person, it would find the query, even though they're both in different columns.

Select From Table
I have a table of colours, one of which is assigned to each user.
I wantr to be able to show the colours that arent already being used by a user, so....
In the user table there is a field named use_colour (which i just save hex value into a varchar field). Colour table is a simple list of available colours.
So I have tried things like:
Code:

$sql = "SELECT * FROM colour,user
WHERE col_colour != use_colour
";
but to no avail.
I basically want to see a list of all the colours from the colour table, excpet those that are in use in the user table

Select From One Table
basically I need to select all records
from one table that don't exists in second table with status 1, but
they can exists in second table with status 0, to be more complicated
there can be in the same record in second table with status 0 and 1
(second table is something like log)

in oracle I can do:
select record from table_1 where record is not in (select record from
table_2 where status=1)

and I'm looking for something similar in mysql

SELECT Table
I am not sure if this is improper or not but here is the problem.
I would like to store along with other data the name of a table storing the extended data. First is this a bad or frowned upon idea. Secondly, is it possible to join the data so that I don't have multiple queries?

Select * From A Table Except
I want to select all the entries in a table except one. Is there something like select * from table EXCEPT x ?

Here's One For You (select From One Table On Cond From Another)
Working on selecting everything from (a condition in) table1 where the related row in table2 meets a certain condition.

table1:

+----+-----------+------------+------+
| id | table2_id | condition | data |
+----+-----------+------------+------+
| 1 | 1 | false | hi |
| 2 | 2 | true | foo |
| 3 | 2 | false | hows |
| 4 | 3 | true | bar |
| 5 | 3 | false | good |
+----+-----------+------------+------+
table2

+----+------------+------+
| id | condition | data |
+----+------------+------+
| 1 | false | abc |
| 2 | true | bca |
| 3 | true | cab |
+----+------------+------+

So I'd like to select * from table1 where condition equals false, if and only if the condition in table2 (where table1.table2_id = table2.id) is true.

desired resultset

+----+-----------+------------+------+
| id | table2_id | condition | data |
+----+-----------+------------+------+
| 3 | 2 | false | hows |
| 5 | 3 | false | good |
+----+-----------+------------+------+
This doesn't seem to work:

SELECT table1.id, table1.table2_id, table1.condition, table1.data FROM table1, table2 WHERE table1.table2_id = table2.id AND table1.condition = 'false' AND table2.condition = 'true'
I think it would be a more involved query than that. An example scenario is a database of computer programs (table2) and screenshots (table1) for approval. The screenshots are linked to the programs. When program condition is true, they have already been approved, as with screenshots. The query would retrieve a listing of unapproved screenshots for any computer program that is already approved. Does this make any sense?

SELECT Where There's No Record In Table
[products]
ID
name

[users]
ID
fname
lname

[products_users_lookup]
uID
pID

Ok, I need the following:

SELECT all products FROM these 3 tables WHERE there's no record in the lookup table.

So I need all products that are not attached to the user account.

Select From Table Where Col Begins With
I am writing a cms and have to correct the way "page names" are being written.

category should be just dog however when these went into the database it also wrote the sub categories such as dog-poodle and dog-beagle.

The feature to rename categories selects data from the category column where category = 'dog' which misses the breeds.

I am a sub on this project and told them to create only one page and play with the editor, well over the weekend they put in over 100 entries.

My question, is there any way to use regular expressions when selecting data.

something like WHERE category '(^dog)'

or do I need to select everything in the col and run a regex agaunst it?

perhaps the most obvious would be to select everything and explode by - and just put them all correctly back into the db.

The reason I ask is that I can see many uses to query the db with regex.

Select Every 3 Rows From A Table.
I have a bit of a problem, Iam doing a match results page with data from mysql.

My table Name: JuniorMatchResults

Data Colums: id, date, venue, position (1,2 or 3), weight.

What i wanto to do is output this information in my website such as you would have the 1st 3 positions outputted in a table, the the next set of 3 in a table and so on.

Select MAX Date From Table
I've got a table that's going to have records spanning a whole week. There's a date field that I'm going to use as a filter to determine which records to display (the most recent). How might I query to get the records with the most recent date only?

I'd prefer to have just one query, but as I'm running mySQL 4.0.17 that might not be possible. This means I can't perform subqueries.

so my records might be like this:

2006-05-31 creole 15
2006-05-31 andy 78
2006-05-30 creole 12
2006-05-30 andy 76

If I was adding in the data manually there would be no issue with what to grab, but the question is further complicated by the data import that I'll be performing once per day around 2am. This site is going to be used by people internationally so I can never be sure WHAT day the person will have.

So, if I suppose that I could do a query to get the MAX(date) from the table, then a second query upon that value. But I'd love to see if there's a better way first.

Select From Table Where Age Is One Week
I think the Subject line is slightly cryptic...

I have a simple query that outputs the records from a table that have dates 'entered' after 2008-04-01:

CODESELECT * FROM table_user w
WHERE w.`entered` >= '2008-04-01';

Create Table From Select
I have a query that need to put all data on this new table, the thing is that every day these table must be empty or delete and create the new table again

SELECT T.time, T.name, T.last, P.company, P.wife FROM T INNER JOIN P ON T.wife = P.wife and T.company = P.company INTO newtable

HOW TO: Select Only The Last 2 Records In A Table.
I'm not very good in mySQL but am trying to modify a script.
It has to do with a log table.
I want to retrieve the last 2 log records (if any) and if the last log
entries are the same as my current (to be) entry, simply update the time in
the last entry. But if not, simply insert the new entry.

My question is how to select the last two entries from the log table?

CREATE TABLE broadcast_log (
id mediumint(8) unsigned NOT NULL auto_increment,
time varchar(20) NOT NULL,
entry varchar(255) NOT NULL,
PRIMARY KEY (id)
);

SELECT COUNT (*) FROM Table
Newbie: SELECT COUNT (*) FROM table

When I run the above query from the command line, it works
fine.

When I run it from my powerbasic program, I can't seem
to get a reasonsible result.

What type of result does count return?
I get something that looks like this:
[ CHR$(133) ][ CHR$(21) ][ CH...

and the type is given as -5...

What is this type?

Select Rows Not In Second Table
I want to select all records in a "User" table that are not in the "Groups" Table.

Select
users.USERID,
users.NAME
From
users
Outer Join groupmembers ON users.USERID = groupmembers.empid
Where
groupmembers.groupid <> '9'

In therory this would return me all users not in group 9, but I am getting an error in my SQL... I dont really understand Left - Right - and Outer joins but I have tried all three... I could do this in 3 queries - Create a Temp Table - Delete Records w/ 9 in group ID ... Select remaing records.... But there must be a better way...