MYSQL :: QUICK AND EASY ONE

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?

Quick Join
I have two tables Matches (m_id, m_winner_id, m_loser_id) and Users (u_id, u_name). m_winner and m_loser_id are foreign keys.

Now I want the querry result to look like this: Match ID | Winner Name | Loser Name.

I did a join query:
Select matchs.id, users.name From matchs, users Where matchs.winner_id = users.id

How do I get the loser name?

Quick Fix 4 MySQL Pro
I will have a few instances like this and since I'm new to MySQL, perhaps a good example will help down the road. All I want to do is display a list of the first and last name of the names in my DB so they appear like so:

John Smith
Suzie Johnson
Etc...

first and last are two separate fields in the DB so I just need to join them together. The code I have now gives me this error:
PHP Notice: Undefined index: first in e:etc...

<?php
// Request the names in db
$result = mysql_query("SELECT artist.first, artist.last FROM artist");
if (!$result) {
echo("No artists posted at this time");
exit();

code...

Quick Join
I have a quick SQL question:
I have two tables Matches (m_id, m_winner_id, m_loser_id) and Users (u_id, u_name). m_winner and m_loser_id are foreign keys. Now I want the querry result to look like this: Match ID | Winner Name | Loser Name.

I did a join query:
Select matchs.id, users.name From matchs, users Where matchs.winner_id = users.id
How do I get the loser name?

Quick PhpMyAdmin
I believe I got phpMyAdmin setup to work right, I used HTTP authentication, and I get a log in screen when I goto the url. How do I set up an account for that?

GRANT ALL PRIVILEGES ON acct1_db.* TO 'Brian'@localhost IDENTIFIED BY 'password';

Is that right? I wish to host websites off this computer and have multiple users. Do I need to log in to the controluser account to do it? Or am I way off?

Quick Query
this one shoud be easy, but I just can't think of the best way (other than writing a php script to do it). I have a CMS and need to change one word in the description field for 64 rows. For example, where we now have 64 entries with "Bilbo" in the middle of the data, we need to change it to say "Frodo" instead. (Actually, I need to replace a misspelled javascript toLowerCase() call...darned non-programmers! They used toLowercase() - note the lower case C)

Quick Queries
Is it possible to use a regular expression or similar in a mySQL query, to make 'fuzzy' comparisons, or is LIKE the best we can hope for?

Quick COUNT()
The MySQL documentation says:

COUNT(DISTINCT expr,[expr...])

Returns a count of the number of different non-NULL values.
How do i get it to INCLUDE null values? I am using COUNT with the DISTINCT bit as well, and i want the NULL rows to count as one as well.

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 Tip: Switching Field On/off
Ever wanted to change a database field (typically TINYINT(1)) from 1 to 0 or from 0 to 1 depending on the current value?

No need for two queries. I just thought of this 'clever' way to take care of it. Never saw that done this way before, and should have thought earlier about this.

UPDATE articles
SET published = ABS(published - 1)
WHERE id = x

How To Dump (quick) A Table Into Another One.
On MySQL, I've got a table I use as 'wild card'. I mean, I store
data into it and, at the end, I dump all the date into 'production'
table.

Until now, I perform it following these steps:
1) I empty 'production' table
2) I make a SELECT of every rows of the 'wild card' table and an
INSERT into 'production' table.
3) I empty 'wild card' table.

But I've got problem: during SELECT+INSERT time, 'production' table
is not full and there's a data lack.

Quick Ones To Speed Up Access
------=_NextPart_000_0032_01C35FC2.62884DE0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

I am querying from a table containing more than 40,000 records. Earlier =
when the records were 10,000 it was taking 9 sec and now after 1 year =
and 40,000 records its taking 30 sec. Code is the same.=20

I am pretty sure that it has something to do with database only. No body =
can think of spending 30 secs for retrieving values from tables unless =
it's very huge in the sense of millions of records. =20

I don know whether I have to modify my database or do some sort of =
restructuring or reindexing so as to make it fast enough.

Is there some methods or optmization which can be applied to this =
database which hasn't been touched since design to enhance the spped.=20

If there are some quick ones but valued alottttttt please let me know.

Dump (quick) A Table Into Another One
On MySQL, I've got a table I use as 'wild card'. I mean, I store
data into it and, at the end, I dump all the date into 'production'
table.

Until now, I perform it following these steps:
1) I empty 'production' table
2) I make a SELECT of every rows of the 'wild card' table and an
INSERT into 'production' table.
3) I empty 'wild card' table.

But I've got problem: during SELECT+INSERT time, 'production' table
is not full and there's a data lack.

I'm looking for a faster way. Does anybody know it? Thank you very
much.

PS: I was told about 'mysqldump', but I do not know if it's faster
than mine.

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.

Select Row From Table, EASY
I'm drawing a blank and can't find it my Sams 24 hour book.

I've got a player name and stats in a DB so a row in the database would look like
Chris Brown 300 1200 5 40 325 2

I need to select(display PHP) Chris Brown and everything associated with him.

I had this all setup and haven't thought of it in years and now....geesh I just feel like an idiot as nothing I've tried works and I know it's connect to DB then one line of code, a select statement.

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 Date Subtraction Question
When I do the following, I get the number of days in difference between two dates however for the dates that have expired, I get the number of days back negated, ie -3 for example?

What I actually want is that if there has been an expiration, I want the result for a given row to be 0. Anyone got an idea how to?

// timestamp is datetime creation of record
// expires is date in future, ie 7, 14, 21 or 28 days in that increments
mysql> select day( expired ) - day( now() ) as days from polls;
Here is an example of the data returned,

+------+
| days |
+------+
| 7 |
| 7 |
| -3 |
| 7 |
| 7 |
| 21 |
| 7 |
| 7 |
| 14 |
| 21 |
| 7 |
| 21 |
| 7 |
| -3 |
| 21 |
| -3 |
| 7 |
| 7 |
| 7 |
| 14 |
| -3 |
+------+

Quick Question - Id Mediumint(8) - Does The (8) Matter?
just a quick question really..

Just setting up 1 of my tables and just wondering if it matters if i set my id to mediumint(8) but i only ever get to 6, will this effect anything in anyway?

Same goes for - address varchar(50) and say that no address ever reachs 50?

Quick Mysql Auto_increment Question
i noticed that after adding some test data into my table the autoincrement is working fine. but after i delete them, even though the table is empty again the new data i put in will have primary key thats in auto_increment as if the old data was there.

for example i added in 2 rows. now i have an id 1 and id 2. i delete them and the table has no rows. but now i add another two rows after wards and they get assigned id 3 and id 4. is there anyway for me to make the counting start over??? thanks for any replies or thoughts on this.

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.

Another Easy(for You, Hard For Me) Select/join
Table 1 indexed on ID.
Table 2 is a chat log. Colums: From, To

I'd like to find out who is NOT chatting.
So, I'd like to select all ID in Table1 that are NOT found in either column From or To in Table2

So far I'm using two selects, but this doesn't seem right.

Quick Question About Reseting Demo Database?
I would like to set up a cron job to reset my demo database on a regular basis. Is there a way to export an sql file via phpmyadmin, and run it as a whole via the PHP mysql commands? The only way I can see to do it, I have to split up the exported sql from phpmyadmin into individual queries. I'd like to just process the entire .sql file at once, as it would make maintenence much easier.

Multiple WHERE X='$x' In A Query. (Quick Answer Needed)
My query works when I only use one WHERE instance, but not with both, and i know I have data that matches both criteria. Is this the proper way to pull results that match 2 criteria?

$query = "SELECT * FROM Cartridges WHERE TYPE='$TYPE_Query' & PRINTER='$PRINTER_Query' ORDER BY ID ASC";

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 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?

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

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!

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.