Query Help Looping Through Records

SQL Looping Query
I am pulling my hair out trying to crack a bit of SQL to query some tables. I could easily sort it out if I could just do a simple subquery like this:

SELECT DISTINCT(ArtistNameTable2.ArtistName) AS ANm, ArtistNameTable2.ID FROM SongTable2, ArtistNameTable2, SongPaidFor
WHERE SongTable2.ArtID = ArtistNameTable2.ID
AND SongPaidFor.SongID = SongTable2.ID
AND SongPaidFor.CustID IN (SELECT DISTINCT(CustID) AS CID FROM SongPaidFor WHERE SongID = 205 AND CustID <> 0)

But! I can't use subqueries on the version of MySQL I'm using.

So, I have 3 tables:

SongPaidFor
ArtistNameTable2
SongTable2

When a song is paid for, the following type of data gets entered into the SongPaidFor table e.g let's assume we're looking at SongID 205:

SongID..CustID
205.....128
205.....218
205.....388
205.....392
205.....396
205.....397

Fine so far -- what I am trying to do is to say, for songID 205, customers also bought songs from the following artists...

So - to start with, we have the SongPaidFor table which shows which customers bought which song.

I also have the following tables:

ArtistNameTable2 - containing:
================
ID (ArtistID)
ArtistName

and also:

SongTable2 - containing:
==========
ID (SongID)
SongName

So these 2 tables can be accessed via a simple bit of SQL - e.g - to find the Artists customer 128 also purchased tracks from, I could do this:

SELECT DISTINCT(ArtistNameTable2.ArtistName) AS ANm, ArtistNameTable2.ID
FROM SongTable2, ArtistNameTable2, SongPaidFor
WHERE SongTable2.ArtID = ArtistNameTable2.ID
AND SongPaidFor.SongID = SongTable2.ID
AND SongPaidFor.CustID = 128

But - how on earth can I loop through the output to generate a DISTINCT list of artists that the customers who bought track 205 also like?

I have done a simple bit of SQL to loop through to work out the ArtistName and ArtistID that the customers also like:

ID = request("ID") ''e.g.songID 205
SQL1 = "SELECT DISTINCT(CustID) AS CID FROM SongPaidFor WHERE SongID = "&ID&" AND CustID <> 0"
SET RS1 = oConn.Execute(SQL1)

DO WHILE NOT RS1.EOF

CustID = RS1("CID")

SQL2 = " SELECT DISTINCT(ArtistNameTable2.ArtistName), ArtistNameTable2.ID FROM SongTable2, ArtistNameTable2, SongPaidFor "
SQL2 = SQL2 & " WHERE SongTable2.ArtID = ArtistNameTable2.ID "
SQL2 = SQL2 & " AND SongPaidFor.SongID = SongTable2.ID "
SQL2 = SQL2 & " AND SongPaidFor.CustID = "&CustID

SET RS2 = oConn.Execute(SQL2)

name_loop = ""

DO WHILE NOT RS2.EOF

thisname = RS2("ArtistName")

name_loop = name_loop & thisname & "<br />"

RS2.MoveNext
Loop

RS2.Close:set RS2=nothing

total_loop = total_loop & name_loop

RS1.MoveNext
Loop

RS1.Close:Set RS1=nothing


But - it's still a completely crap approach, because at the end of it, I just get a big list of text, which cannot be manipulated in any way:

e.g:

The High Teas
semble
Gavin Molton (instrumental)
Gavin Molton
Ruin the Rain
Charlies Horse
The Blue Review
Water
Middle Men
akimbo
The Barnacles
StrangeCloud
Gavin Molton
Flame Into Being
Gavin Molton (instrumental)
Ray Saunders
Flame Into Being
Gavin Molton
Gavin Molton (instrumental)
Au Revoir Simone
Cut Copy
Seamless Pink
rinôçérôse
Zap Mama
Nada Surf
DJ Shadowman
Nermin feat. Ayaah
Nomad
Gavin Molton

With this - I cannot strip out duplicates, or anything else - it's just a list of text. Any ideas?

Can anyone else see a way out of this? Sorry this is so long and messy. If you've read this far, thanks!

Jim

---

Looping Certain Database Entries
I need to determine how many links there are in a database table. Then loop the links to be displayed in the menu. Can someone please help me with this? Thanks in advance.

---

Looping Through Results - Different Display For The First 3?
I have a simple news database table (id,title,body,date)

There is a page I've created which should show the first 3 recent articles in full and then only links to the others.

what is the neatest way to do this ? (php/mysql)

---

Looping Through Database Objects
I am trying to create a process that loops through all the databases on my server (75) and run the same set of sql statements on each database. Here is what I have so far...

begin
declare dbname char(15);

SELECT schema_name INTO dbname FROM information_schema.schemata WHERE schema_name like "acc340%" LIMIT 1;

while dbname IS not null do (this is what doesn't work)
SELECT dbname;

SELECT schema_name INTO dbname FROM information_schema.schemata WHERE schema_name > dbname AND schema_name like "acc340%" LIMIT 1;
end while;
end

The loop works but since the select into never changes the value to null after it runs out of data, my loop continues forever. Is there a way to get around this annoying problem?

---

Looping Through Dates Without Table
Is there a way to have mysql simply output all the dates between 2007-10-10 and 2007-10-15 without using a single table?

Resulting rows would be :

2007-10-10
2007-10-11
2007-10-12
2007-10-13
2007-10-14
2007-10-15

---

Write A Script With Looping Constructs
I want to write a script to insert some rows in a database. The user of the script will login to mysql and paste the script at this point.

Is there a way to use looping constructs, variables, etc. Iam thinking of a PL/SQL equivalent.

---

Looping Through Insert Statements Is Timing Out, Alternative?
I am looping through and creating about 600 inserts/executes in PHP and it's timing out.

I have tried aggregating them into one string that ends up like:
INSERT INTO table VALUES(val1, val2,...);INSERT INTO table VALUES(val3, val4,...);INSERT INTO table VALUES(val5, val6,...);...

But is errors out.

I am using PHP5 and MySQL 4.1. How can I get around that? Is there an alternative?

If prepared statement is a way around it, any easy examples to look at?

---

How Do I Calculate A New Value From 2 Columns Whilst Looping A Table
I have a single table and i would like to calculate two columns (imp and clicks) while looping through the results.

this is what the table would look like...

campaign | imp    | clicks
A        | 200000 | 100     
B        | 100000 | 40
C        | 50000  | 10
D        | 300000 | 200

and here's what i would like to achieve with a mysql query and php. I would like to create a new column CTR% and calculate the CTR for each row by looping through the table...

campaign | imp    | clicks | CTR %
A        | 200000 | 100    | 0.05
B        | 100000 | 40     | 0.04
C        | 50000  | 10     | 0.02
D        | 300000 | 200    | 0.06


i've tried a few things that i've read up on by googling but none really worked (based on using a while loop). now i would like to wipe the slate clean and get your suggestions on what to do??

---

Looping Through MySQL To Change The Id (using PHP) To Equal The Number Of Rows
When deleting a row from the database, that id is now missing.
So what I'm trying to do is update in a loop (maybe an sql loop if
there is one) of all of the id numbers - sort of like renaming them.
It did partly work because all the id's were set to 22. Thats because
there was 22 rows.

Here's the code I used:

---

Deleting Records Upon Query... (!?!)
Sorry for the confusing thread title. Here's what I am trying to do...

Read the last six added records for a particular criteria, and delete the rest. Now consider I have around 1000 unique "criteria" and this query is executed a few times a day for each unique... should I delete it on each read or put it in a cron job and schedule it for every night.

Here's a sample MySQL syntax for my selectquery:


SELECT * FROM table1
WHERE field1 = sometext
ORDER BY some_time_field ASC
LIMIT 0,6
So the questions, again, are:Can I join this SELECT query with a DELETE, and delete all field1s with "sometext" which were not selected by the query?If I can't, how would I do it with a seperate DELETE query? i.e. Leave the last six records for field1 = sometext and delete the rest?Do I really need to order by some_time_field? Or does MySQL have an internal schema to sort records by their "internal" creation date?

---

Count Records Within Query
I have the following information in table 'Test1':

field1  field 2
000001  000000
000002  000001
000003  000000
000004  000000

I want to write a query that will only display the unique field 2 records and as well provide a count of how many records have that id.

output should look like

field2  recno
000000    3
000001    1

can some one help with the trick to make this work?

---

Query Cannot Retrieves All The Records
I've got this problem, this query is used to create a combo wich display all months where at least a news is been submited (a kind of hostory menù), the fact is that it can display max the months wich correspond to the 107 record submited.

The query:

select date_format(data,"%b/%Y")as textMonth, month(data)as month,dayofmonth(data)as day,year(data)as year from archivio where category = "Rassegna Stampa" group by month

Just to be shure I've also tried the query above on mysql browser, and the result is the same. Can you tell me if there's a kind of limit?

---

Query And Non Existent Records
I have a mysql table texts which keeps textlines in different languages.

columns: textId, languageCode, textline

The application adds records for specific languages as:

textId 1
languageCode: EN
textline: "Username"

another record could be:
textId 1
languageCode: DE
textline "Anwendername"

Now at a certain pojnt in the app I have to check whether for a specific textId there are textline filled in for specific languageCode. For example: Is there a textline for records with textIds 120, 124 and 134. And are these records present for EN and FR (French). I can query as:

Select count(*) from texts where textline != '' and textId in (120,124,134);

But what when the records are there in ENglish and German but not in French?

Any ideas how to solve this?

---

Query To Get Records In One Table But Not In Another One.
I have this query that works but now I want to refine it so that it only selects records in the loaninfo table that do not have a record in the achtransfer table.  I not sure how to do that.  Here is the query I have now....

---

Total Like Records Query
Ime probably missing the boat here so please be patient. I am trying to create a query to display the total number of records with the same parent name.
Eg
Total records for author Eddings = 10

So in effect i want to count all the entries and display only the numeric figure of books for that author. I think its supposed to look something like:

Select
sum(Eddings) $total
From books

---

Query Total Records In 1 DB With 5 Tables:
In PHPmyadmin there's an option that shows you the amount of records in all tables, how can I did this via a query?

---

MySQL Query For All Records With NULL Value
I have recently been trying to extract a few products from a database (that is quite big), so that I can find products that have a specific NULL Value, what kind of a SQL statement would allow me to do this?

---

Update A Set Of Records In One Single Query
My records contain a position, from 1 to n. This position is not the primary key.
In normal conditions, the position of this records should be as follows:
1, 2, 3, 4, 5, 6, 7, 8, 9 . etc...

One of my utitities is meant to check the consistency of the table -
one of the things it has to do is scan the table as follows:

SELECT position FROM employe_records WHERE employe_id = ? ORDER BY position

From the result of this query I verify each entry (JDBC) one by one, looking for inconsistancies. In a for() loop I look at the value of position and check that they start from 1 and are incremented by one for each record.

At the first inconsistency I come accross I want to run a query to update the records so it is consistant again - it is something like this:

UPDATE employe_records SET position = updatedValue FROM
employe_records WHERE employe_id = ? ORDER BY position

This is not valid query of course - my question is what should my query look like?! How can I make 'updatedValue' incremented by one automatically so each of the records are updated from 1 to n.

---

How Can I Update A Set Of Records In One Single Query
My records contain a position, from 1 to n. This position is not the primary key.
In normal conditions, the position of this records should be as follows:

1, 2, 3, 4, 5, 6, 7, 8, 9 .etc...

One of my utitities is meant to check the consistency of the table - one of the things it has to do is scan the table as follows:

SELECT position FROM employe_records WHERE employe_id = ? ORDER BY position

From the result of this query I verify each entry (JDBC) one by one, looking for inconsistancies. In a for() loop I look at the value of position and check that they start from 1 and are incremented by one for each record.

At the first inconsistency I come accross I want to run a query to update the records so it is consistant again - it is something like this:

UPDATE employe_records SET position = updatedValue FROM
employe_records WHERE employe_id = ? ORDER BY position

This is not valid query of course - my question is what should my query look like?! How can I make 'updatedValue' incremented by one automatically so each of the records are updated from 1 to n.

---

Query For All Records With Datetime In 2005?
I have a table with a datetime field. I'd like to query all records for 2005 based on that field. I'm having trouble coming up with a where clause that does this.

I'm on MySQL 4.1

---

Select Last N Records Matching A Query.
I am trying to select the last n records in a database which conform to criteria selected by a user. I gather that there is no "BOTTOM" equivalent to the "SELECT TOP" command. I've tried "SELECT... ORDER BY... LIMIT X,Y" but MySQL doesn't seem to take a second parameter for the LIMIT statement. Is there any way round this?

---

List The Number Of Records In Query
I have a very simple query. I want to list a bunch of names & how old they are. (two columns) I want to add a third column that basically counts how many records there are.

Example:

1. Bill Jones - 55
2. Bob Smith - 56
3. Steve Gates - 25

Can I do this in mysql or should I add the code in PHP, and how do I do it?

---

Query To Display Records Over A Certain Number?
think my brain is now officially fried as i can not come up with this query....

i want to display records from my table where the record count is say over 5.

real simple...two columns a and b.  10 records in the table....

---

IN Query Showing All Records If Used With Same Field Twice
I have 2 tables programs and programType. programs has 5 records and programType has no records. When I run a query like "SELECT * FROM programs where id in (select programid from programType) and id in (select programid from programType)" I get all records But When I try to run "SELECT * FROM programs where id in (select programid from programType)" Then I get no records.

---

Counting Records In 2 Tables Using 1 Query
I have these 2 queries.

SELECT count(*) gifts
FROM gift g
WHERE g.this and g.that

SELECT count(*) events
FROM events e
WHERE e.this and e.the other thing

is there a way to put these into one query.....

SELECT count(g.*) gifts, count(e.*)
FROM gift g, event e
WHERE . . . .

so far nothing seems to be working .....

---

Flag Records Not To Be Reused After Query
I'm a MySQL novice, and I don't even know if there is a way to do this.

I have a simple table containing just 2 fields and about 15,000 entries...one contains an ID (which is just an incremental number) and the other a phrase.

I will be doing queries selecting 50 random entries from the table. Somehow, once those 50 are selected and returned, I would like to flag them so that they cannot be selected again UNTIL I exhaust the entire list. So, I'd like to get 50 random at a time, until all 15,000 are gone, and then start over.

If that isn't possible, I suppose I can just select the first 50 every time and delete the first 50 when I am done.

---

Duplicate Records Delete Query
I have a table with duplicate records but the key of the duplicate records is different, that is:

The key of one of the record has the format:

Rssss

The key of the second record has the format:

ssss

How can I delete both records from the table?

---

Query To Get Matched And Mismatched Records
this is my first time on the forums and i need help with my dbase. i need to match fullnames from Table1 with the fullnames in a QryCustomer. Table1 contains only the fullname(one field) whereas QryCustomer contains a list of fullname with their respective Account# and Customer#..i need to match the fullnames and a separate query to get the mismatched names.

---

Update Query On 14000 Records
Haing moved a large site from asp and access to php and mysql, I would like to change the way of my fields is delimited.
Currently, the field contains user selection seperated by commas
e.g. selection1, selection2, selection3
I would like to update this field on every row in the table and change all commas to pipes
e.g. selection1|selection2|selection3
The table contains over 14000 records, and the field in question can contain anything up to 30 selections.
Is this possible to do?

---

Sql Query For Count Records Of Current Week
I have a registration form that saves the registration date on a datetime column. I would like make a query to know how many records are in the table that where for the current week. For example, I'm on week 25 and I want to know how many records I have for this week without the need of specifying the from and to dates. Is there a way to do this?

---

How To Specific Number Of Records To Retreive In A Query?
My item file contains too many records to display. I want to limit the display to only 20 records at a time.

In the Select statement, how do I specify the the first 20 records, 2nd 20, etc.?

---

Selecting Records Younger Than 24 Hours Using SQL Query?
I have a TIMESTAMP(14)-field in my db and I need a quick way to select records younger than 24 hours, WITHOUT using PHP to convert around. I'm pretty sure this one should be quite easy, played around with something like:

SELECT fields FROM db WHERE 'timestamp'>NOW()-INTERVAL 1 DAY)

for hours right now (with DATE, EXTRACT, BETWEEN..AND etc.), and I either got error in my queries or bogus results.

---

How To Show All Records In MYSQL Query Using Default Value
I'm working on somesite and i face this problem:

i want to send a parameter entered by a user from text box that called (txt1) to the query below ....

---

Query By Dates To Find History Records
what is the correct way to query on a date field in a table to pull a value that is older than 3 days?

Date field format is 2007-04-04

I only want records where the date is 2007-04-01 or earlier.

---

Buliding A Query:selecting Unique Records
I am nearly positive that there is some way to have mysql select only the first occurance of a data entity on a select.  

Maybe a better explanation is given by example.  Think of having a table of employees, with one of the columns being their bosses email address.  Now I want to do a select statement on the bosses email addresses that only returns one entry for each bosses email, even though that bosses email address will occour > 1 in the table. (more than 1 employee has the same boss...)

I could parse out the duplicates when I get to PHP but I would rather have mysql do the work for me.

---

Query To Result Out The Newly Inserted Records To Txt File
Can any one help me with a query to result out the newly inserted records from a MYSQL table to a text file on a hourly basis.

---

Using A Query To Exclude Records From One Table, If The Field Value Exists In Another
I'm very new to SQL, and wondered if someone could help me out:

I am building a query which contains two tables. One table has details of everything everyone owns.

The other table is a list of things which people own, but that I don't want to see.

Is it possible to design the query so that I see all of what everyone owns, except the items listed in the other table?

---

Using Limit On Grouped Query With Large Number Or Records
I have a query that use a "group by" clause that returns 600+ queries

however if I add a LIMIT 500,50

It returns 0 records. I tested LIMIT 450,50 and it returned only 34 records.

Is there something limiting the limit? Is there an option in mysql that can increase this limit?

---

1 Line Query To Delete Specific Records From Multiple Tables
On clients machine, currently to delete on trainee record it runs 10 queries to delete records from 10 tables. At the time of running all queries, server shows (104) Connection reset by peer. An error condition occurred while reading data from the network.

I think it because of running 10 queries at a same time. Is there any possibility that through one line of query we can delete record from 10 tables.

I've tried following query

DELETE FROM table1, table2, table3, table4, table5, table6, table7, table8, table9, table10 WHERE empID = 11;

But it gives ' error in query.

---

Query Table A And Return Records Not Found In Table B.
Given table A and table B.  Table A is a master table and table B is a list of authority or reference which is fed by selecting records from table A (via an UI I wrote in PHP).

The interface is working fine but I am left with one problem.    If I need to edit my table of authority, the source table (table A) should return ONLY entries not previously selected.

That said, if the A.key is in B.key, records from table A matching B.key should be omitted.

I could do this by looping through the query results from table A and querying table B, if no match, show A.key.  This I think is a bit antiquated and possibly too involved.

Is there a joint query command I can use?

---

Count Related Records, But Show Records With NO Related Records Also
cl_items
========
it_id (pk)
it_ownerid
it_name

cl_offers
========
off_id (pk)
off_itid (fk) -> to it_id
off_whoid

My query needs to output ALL of the records in cl_items AND still show how many offers are on each item (from cl_offers)

I can't get what I want through the GROUP BY because I want to show the records in cl_items which DO NOT have any offers on them yet also.

Is there any way to do this with mysql only?

---

All Records From Table A - All Records From Table B - Join Alike Records
I am by no means a SQl Jedi as will be apparent by my question, but I
can usually figure out a select statement on my own. I have one today
though that really has me stumped. I am working in MySQlL 5.

In My first select statement I get all my records from Table B
SELECT
`table_A`.`ITEM`,
`table_A`.`DECSCRIPTION`,
`table_A`.`UM`,
`table_A`.`PHASE`,
`table_B`.`Qty`,
`table_B`.`Calc` as calculated
FROM
`table_A`
Inner Join `table_B` ON `req_itemlist`.`ITEM` = `table_B`.`ItemID`

In my second statement I get my records that match in this case phase
401 in Table B and all my Table A records for phase 401.

SELECT
`table_A`.`ITEM`,
`table_A`.`DECSCRIPTION`,
`table_A`.`UM`,
`table_A`.`PHASE`,
`table_B`.`Qty`,
`table_B`.`Calc` as calculated
FROM
`table_A`

Left Outer Join `table_B` ON `req_itemlist`.`ITEM` = `table_B`.`ItemID`
Where
table_A.PHASE In ('401' )

Now I need to combine the Data of both recordsets. I need EVERYTHING in
Table B, but I also need All Table A records that match the phase
selection....

Can I write this one query or do I need to use a Temp table?

---

How To Query Records That Have Same Words But Maybe Different Words Order?
I am trying to find a way to do a select query that can find records that have same words in any orders. e.g. "hello good morning", "good hello morning", "morning good hello"... they should all returned by the select query.

Please don't tell me that I can use "or" inside select query. It's because what if the string has 10 words? There are tons of permutations.

---

Delete Records Without Matching Records
I have a one to many relationship in my database. I would like to delete records in one table if there are no matching records in another table.

---

How Can I Make A Query Like Microsoft Access, And A Query From A Query
I am new to MYSQL and am trying to understand how to make queries... I am moving from Microsoft Access where it is GUI driven and easy!

I can make a simple single query using MYSQL Query Browser, say:

qry1: SELECT ID, Area FROM data GROUP BY Area

How can I store this as a query inside MYSQL, rather than having to code it each time?

In Microsoft Access I could enter a variable ($VARIABLE) and then pass by code to the query:

qry2: SELECT ID, $VARIABLE FROM data GROUP BY $VARIABLE

How can I store this as a query and then pass the variable from code?

In Microsoft Access I could base a query on the results of another query, so following example above:

qry3: SELECT qry1.Area, data.ID FROM qry1 INNER JOIN data ON qry1.Area = data.Area;

How can I store this as a query in MYSQL.

---

Can't See Old Records
I really don't know much about mysql.
We have an hardware device performing bandwidht management which
we didn't even know it is using mysql until it was broken down.

After we solved the problem, we saw that the operationg system running in the device is linux (and old version) and the database is mysql.

The problem is that we can't see the old data eventhough when I
view the binary ISD file I can see the whole data including the olds.
But the management program doesn't show the old data. When I reenter new definitions from the management program, I can see that they are written into the ISD files. ISD files contains both old and new records but I can see the only new files.

Does that mean the index files are curropted?
If so, How can I recreate indexes or recover them?

---

Records That Are Only From 3 Pm To 6 Pm
I have a field in my table called time which is a datetime field.

The format of the data is like these below:
2006-07-12 17:55:19
2006-07-11 16:31:34
......

I would like to get every records that are only from 3 pm to 6 pm. Is there any query where I can check for the hour of a datetime field?

---

Top 5 Records - ASP
I have an ASP application talking to a MySQL database. All works fine but:

1) I can't figure out how to select just the latest 5 records in a table.

2) SELECT LAST_INSERT_ID() doesn't work, it just returns 0.

3) I need to select the 'recordid' (held in the first field and is the PK) of the latest record.

---

Getting Last 12 Records
I am trying to get just the last 12 records from a table. I have tried this but get no results.

SELECT TITLE, ARTIST, STYLE, MEDIUM, FORMAT, CATEGORY, WIDTH, HEIGHT, SPRICE, FILENAME, NUM FROM PICTURES WHERE CUSNUM='YES' AND ARTALLOW<>'NO' ORDER BY DESC LIMIT 12

---