MYSQL :: Quick Queries

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

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.

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.

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 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 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 & 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 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.

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";

Complex Queries Versus Multiple Simple Queries
I am constructing a database to contain information about stories posted on my site. Information included will be things like title, author(s), genre(s), story codes, synopsis, etc.

I worked out that storing this information properly, so that it can all be searched on, could take as many as ten tables.

My question is this: Is a single complex query really better (more efficient for the server) than multiple simple queries? In other words, I may need the information for as many as 25 or even 50 stories for a single page.

Is it better to get all of the information out of a single, massive, complex query, or is it acceptable to get the information essentially one story at a time, which could mean 25 or 50 simple queries...?

Many Smaller Queries Vs. Large Comlicated Queries
I am wondering if any one can help me. I have a page that will run around 85 smaller queries but if i combine the queries it will go down by almost half. This page is a high traffic page and I don't a complicated query taking up mysql resources while it created a temp table and such. My question is this:

Is it better for mysql to run a lot of smaller queries (ex: simple selects with zero or one join, group by) or one larger complicated query with everything combined.

The thing i have to keep in mind is that the mysql selects are comming off the localhost that the web server is also running on so they share the same resources.

Quick "WHERE"
Part of a WHERE criteria I'm trying to construct needs to return only those records where a date field in my adkey table contains a date older than a user-specified date (submitted as variable {sRemoveDate} from a web-page form). The following works:
CODEWHERE
(adkey.D_RemoveDate < '{sRemoveDate}' Or
adkey.D_RemoveDate is null)

Queries Per Second: 135.73
According to Runtime Information:
Queries per second: 135.73
Is this an acceptable value, or is this a sign that something is probably wrong?
The site gets about 300k page views per day.

How Many DB Queries Is Too Many?
Is it ridiculuous to run 20 DB queries on a page that isnt accessed that often? What is the general rule when it comes to db queries? How many is too many?

Best GUI For Queries
I currently use myqlcc instead of query browser, but have been looking for a replacement that might combine some of the best features of both (plus one still being updated). I use the prog for writing and running queries, not so much for administering the db.

I don't hear a lot written about them, but these are what I consider to be the best features of mysqlcc, which I can't find replicated in other progs. Can anyone recommend a GUI that has at least some of them?

--Table/field highlighting (recognizes real db data and highlights it when entered)
--Autocomplete (shows me possible matches from partial entries with a keystroke)
--Parentheses highlighting (lets me see what is in a given start/end parenthesis)
--update/delete results messages (shows xx rows matched, yy rows affected)
--Tabbed multi-threading (of course)
--Ability to grab all/some results and copy to the clipboard for export to Excel (not all have this)

Anytime I try to google favored applications, I come up with Navicat, which seems to have the budget to buy search results; doesn't make it a good program (does anyone like it?).

2 SQL Queries
2 questions I'm hoping someone will be kind enough to help me with:

Firstly, is it possible to do something like:
SELECT * FROM (SELECT `table_name` FROM `table_list` WHERE `type` = 4 LIMIT 1);

I'm trying to select the table name to use by querying another table.

Secondly, I'm trying to use variables to tidy up another query:
SELECT @r := TRIM(TRAILING '_' FROM `long_room`) AS 'Room'
...
WHERE @r LIKE 'WL214';

Am I using variables in the right way? This doesn't return anything and using "'Room' LIKE 'WL214'" doesn't work. I know I can use the whole Trim statement in the Where clause, but I want a tidier way of doing it.

Sql Queries
I want to do a query where I make totals of colums , and insert these
totals into another table.

for example :

SELECT sum(val1) AS tot_1, sum( val2 ) AS tot_2
FROM tableA ;

and the values tot_1 and tot_2 I want to insert into another table

INSERT INTO tableB SET tot_2003 = tot_1, tot_2004 = tot_2;

How can I do this?

WHERE (xyz OR Abc) Queries?
I'm just wondering if it's possible to include some sort of "OR" type condition in MySQL queries? I'm assuming it can be done, but I can't seem to find anything, and of course typing MYSQL +"OR" into a search engine doesn't bring the most specific of answers.

Sql Queries
I want to do a query where I make totals of colums , and insert these
totals into another table. for example :

SELECT sum(val1) AS tot_1, sum( val2 ) AS tot_2
FROM tableA ;

and the values tot_1 and tot_2 I want to insert into another table

INSERT INTO tableB SET tot_2003 = tot_1, tot_2004 = tot_2;

How can I do this?

How To Put These Two Queries Together?
Howto put these two queries together?

sql = "SELECT DISTINCT medium_id, COUNT(tag) AS count FROM tags WHERE
tag = 'test' GROUP BY medium_id ORDER BY count DESC"

This query is working.

Id like to try something like:

sql = "SELECT * FROM media WHERE id IN (SELECT DISTINCT medium_id FROM
tags WHERE tag = 'test')"

But how can I order the resut by quantity (counts of tag)?

Queries
Is it possible in MySQL to create and save queries in the back end DB.

I am used to MS Access, in which you can put together a complex query and save it in the DB Backend. Then use the query in a ASP page instead of long SQL.

Queries
I have written a basic .sql script to create a particular table. I have used this script on SQL2000 and all is fine. I am running mysql 1.4 and am trying to find out how to run the script in mysql admin without success. The readme files aren't very useful and the interface is not giving any clues.

1 Or 2 Queries
I have an established schema of databases and tables.
A requested feature came up, that would allow for a user to add an item to the database, and not have it display on the website.
Would it be better for the site to run a query along the line of
SELECT * FROM db.table WHERE item=item AND view='yes'
or
Get the data then run a query against a separate table to see if the part is in the not viewable database

2 Queries
it won't run both queries and unite them - it's ignoring 'where approved='yes')? :
Code:

$sql = "SELECT CONCAT(firstname , ' ' ,lastname) AS FullName, username, homepage FROM customer WHERE approved='yes' AND lastname LIKE 't%' OR username LIKE 't%' ORDER BY lastname ASC";

Max Queries
Using JDBC and a connection pool, MySQL 5.0 on Debian Sarge, does anyone have any prior knowledge or a suggestion - should I limit the number of queries per connection? If so - to what number? (I've already got a maximum time to live for a connection, so at any case I'm not going to reuse a connection forever).
Is using and reusing a connection memory-consuming, tending to grow over time?

Date Queries
I have an events calendar that I am working on and need to be able to query the database for a specified month, for example:

march 2005

How would my query string look:

$query = "SELECT event
            FROM calendar_events
            WHERE event_date >= 2005-3-01
            AND event_date <= 2005-3-31";

The reason why I ask, is because I saw this, when looking at an example:

Automated Queries
I'm wanting certain queries to execute at a particular time of the day.
For example, I want to have records with a Pending status deleted from the database every night at 11:59PM.
I'm using phpMyAdmin for my MySQL database administration.

Multiple Queries?
well not really sure how to explain this one.
I wrote a reate me script. One tha tallow syou to add a photo and leave comments for a photo. What I want to do is allow the admin to search thorugh all the comments. sure, easy query to just search comments, but I want them to be allow to search for a photo and grab the commetns on that specific photo.
the photo table is linked to the comment table by photo.picid=comment.subjectid. I also want to grab the user info in this and that is linked to both tables really. but I could use the comment table like this, comment.postedby=user.id.
now the photo names are the users id along with the date. so it would be like so
1_2006_10_25_1340.jpg
so i thought I would let the user search for the user id and grab all photos with that id and load the comments as well. but I am following short. I get the photos, but no comments, or I get the comments but short on photos and the comments are the same comment for both photos. various mixed results
The trick is that on the same search form I will allow them to search just comments, easy, or the date of the comment, or with the user so they can see all of the comments by said user. but getting the difference between all searchers is killing me cause I want to do only one query if I could.
I don't think the table structure is important as you know th erelationship between the tables. but if you need it let me know.
if any of this made sense what idea would you have for the query?

MySQL Queries
MySQL has no subqueries for version I'm using, some 4.x.y.z

I've an employee table containing academics of many people. I need pull out second maximum percentage data from db records. Suppose 2 or more people have same marks distribution. So, how can I query MySQL?

Is It Possible To Turn These Queries Into Just One?
UPDATE categories set orderid=1 WHERE carid=3
UPDATE categories set orderid=2 WHERE carid=5
UPDATE categories set orderid=3 WHERE carid=6
UPDATE categories set orderid=4 WHERE carid=7
UPDATE categories set orderid=5 WHERE carid=8
UPDATE categories set orderid=6 WHERE carid=1
UPDATE categories set orderid=7 WHERE carid=2
UPDATE categories set orderid=8 WHERE carid=4

Combining 2 Queries
product_info
product_codevarchar(50)
sizing_chartIDtinyint(3)

sizing_chart_lookup
IDtintyint(3)
vendorcodevarchar(5)
HTMLlongtext

I would like to have a query that does this:
select where product_code = 'XX-XXX'

if sizing_chartID > 0 OR NULL rows returned
return the sizing_chart_lookup.HTML WHERE sizing_chartID = sizing_chart_lookup.ID
else
return the sizing_chat_lookup.HTML WHERE sizing_chart_lookup.vendorcode = 'PHPVariable'

I can do this with 2 queries easily and just use PHP to do all the checks, but I think I'm making this harder than it has to be. I would think 1 sql query could do this.

JOIN Vs 2 Queries
Which would be the most efficient way of dealing with this problem:

For a single productid, you want the name of the product from 1 table, and you want a list of user_comments regarding the product, from another table.

The 2 items of information are disparate, but the 2 queries can be combined very easily. Does it make sense to do so?

I'm thinking that in pure sql then no, the result set doesn't really make a lot of sense, but in a dynamic programming environment maybe it saves time spent interfacing with the database etc, and you can just pull data from the result set in whatever form necessary.

In this situation the user_comments table is pretty small, maybe the join makes less sense if the table were bigger?

Given the small size of the table I'm sure it won't make a noticable difference, but maybe I should rethink some larger problems.

Can These 2 Queries Be Merged?
I have three tables, like so:

books authors books_to_authors
----- --------- ----------------
book_id author_id book_id
title name author_id
I want to display the title of each book, with the authors underneath each one, like so:
Cradle
Arthur C. Clarke, Gentry Lee

Notes from a Big Country
Bill Bryson

Mad Frank's London
Frankie Fraser, James MortonThe only way I can work out that will display this is using the code below, but it seems like a really bad way of doing it, as it has to do a separate query for each book in order to get the authors.

PHP

// Get the books
$result = mysql_query("
    SELECT books.book_id
         , books.title
      FROM books
    GROUP
        BY books.book_id
    ORDER
        BY books.book_id DESC
");

// Show the books
while ( $row = mysql_fetch_object($result) ) {
    echo $row->title . '<br />'
    $i = 1;

    // Get the authors
    $result2 = mysql_query("
        SELECT books.book_id
             , authors.author_id
             , authors.name
             , books_to_authors.book_id
             , books_to_authors.author_id
          FROM books
             , authors
             , books_to_authors
         WHERE books.book_id = books_to_authors.book_id
           AND authors.author_id = books_to_authors.author_id
           AND books.book_id = '" . $row->book_id . "'
    ");

    // Show the authors
    while ( $row = mysql_fetch_object($result2) ) {
        if ( $i == mysql_numrows($result2) ) {
            echo $row->name . '<br /><br />'
        } else {
            echo $row->name . ', '
        }
        $i++;
    }
}

Capitalized Queries
just out of curiosity.. is there any difference (in terms of execution speed, etc.) in using SELECT * FROM table WHERE ..... as opposed to select * from table where ......

Joining To Sql Queries
SELECT custid FROM tours WHERE tourid = '[tourid]'
SELECT avatar,logo FROM customer WHERE custid = '[custid]'
basically i get a customer id from the tours database for a specific tour, and then i need to get the avatar and logo of that customer. i know there must be a way to do this in one statement..

Merge 2 Queries To 1
I have 2 queries that both work fine by themselves, but I need to merge them to one:

2 Queries In 1 Function?
Is it possible to query the database twice in the same function?

If so is it possible to use results form the first query IN the second?

Is There A Difference Between These Queries?
SELECT * FROM table1 WHERE col1 = $value

SELECT col1, col2, col3, col4, ... FROM table1 WHERE col1 = $value
The 2nd query basically just lists all the columns in the table, same as the first. Is there any difference in running either query? Any performance gains from using the 2nd query?

Date Queries
SELECT * FROM tbl_hire
WHERE hire_out >= "2006-06-01"
AND hire_in <= "2006-06-30"

I have created a calendar with coldfusion which scrolls through the months. So I can click backwards into the past and forwards into the future.

I am using 'tbl_hire' to store items that have gone out on hire and also store the date when the item is due back. So when I scroll through the calendar I need to be able to see all the items that have been sent out and therefore not available to rent out.

I have 2 questions or queries,

1) how do I tell the sql what month I am on - so If I've scrolled back to May 2001 what do I need to have in the SQL?

2) at the moment with the SQL above if I have an item booked from "2006-06-05" to "2006-07-05" it won't display it as not being available to rent - is "<=" incorrect command to use?

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?