Ok, mySQL nerds…

How the heck do you step through a table row by row when the table has no unique values or index?

I can do this and pull what I need out of the table, but I need to use a field out of each row to look up data from another table, which this won't allow doing that.

Using the above, as soon as I add lines 9 and 10 inside the FOR NEXT loop, it kills the loop process as it now thinks it's reading all data from the new table, which doesn't work.


This would be the normal way of doing what I need to do, but because there is no unique values or index, this won't work.


Both of the code examples above is not the actual code I'm using, which I'm not able to show.

Table with no unique index can be problematic 

What do you want, all records from data with username from users?
Instead of 2 queries, did you try one? And with one I mean JOINS https://www.w3schools.com/sql/sql_join.asp





 

gbWilly said

Table with no unique index can be problematic 

What do you want, all records from data with username from users?
Instead of 2 queries, did you try one? And with one I mean JOINS https://www.w3schools.com/sql/sql_join.asp
 

From “Post #13,541”, December 15th 2025, 8:33 PM

The code inside the FOR NEXT loop (first code block), and the code inside the IF ENDIF loop (second code block) is made up, except for the
 
There is one field that I need to use the result from to look up the result in another table to add to what I already have. It looks like the inner join is what I need - if the FOR NEXT loop survives the accessing the other table. I'll have to play with it.

Hi Sholzy,
You're killing the for-next-loop because you are re-initating the MGlobal.hResData object.
Either add a temporary result object (MGlobal.hResDataTmp ) for the second query or change the solution.   

How many users are going to be found in the initial query ? - I suspect 1

I agree with GBWilly - a join of the appropriate type may be best (Inner,Left,Right)

To give a full solution, I'd need to understand what the objective is

( written long hand like - take all the users from table X then extract.....from table y )




 

Quincunxian said

Hi Sholzy,
You're killing the for-next-loop because you are re-initating theMGlobal.hResData object.

Yeah, I know that's killing the loop, that's why I posted to get some suggestion on how to fix it.

My actual code (that I can't post here) works, until I add in the SELECT statement inside the FOR NEXT loop.

Quincunxian said

Either add a temporary result object (MGlobal.hResDataTmp ) for the second query or change the solution.

So where would I add in the temporary result object?

Quincunxian said

How many users are going to be found in the initial query ? - I suspect 1

I have a table with 35 rows. In each row I need to match a name to an ID.

Quincunxian said

I agree with GBWilly - a join of the appropriate type may be best (Inner,Left,Right)

To give a full solution, I'd need to understand what the objective is

( written long hand like - take all the users from table X then extract…..from table y )

I'm working with 2 tables - table_1 and table_2. table_1 is 35 rows with no unique values. table_2 has 100+ rows, indexed on col_1.

I'm pulling data from table_1 to put into a GridView (for simplicity - 3 columns - col_1, col_2, col_3). This works with no issues.

• table_1 only contains the data for col_1 and col_3 but not col_2. (col_1 has several duplicate values. col_3 may or may not contain duplicate values)
• table_2 has the data for col_1 and col_2, but not col_3.
• table_1 and table_2 both contain the same data in col_1.

Using data from table_1, col_1 I need to pull in the corresponding data from table_2, col_2 using col_1 to put in the Gridview col_2.

(it was much easier to write the code than to write it out longhand.   )

" it kills the loop process as it now thinks it's reading all data from the new table, which doesn't work."
It is reading data from the new table is your problem caused by using the same variable for storing the resultset :

• For iRow = 1 To iCount
•     iRecordID = iRow
•     MGlobal.hResData = MGlobal.hDB.Exec("SELECT user_id FROM data WHERE user_id = &1", iRow)
•     If MGlobal.hResData.Available Then
•         MGlobal.hResData = MGlobal.hDB.Exec("SELECT * FROM data WHERE user_id = &1", iRow)
•         iUserID = MGlobal.hResData!user_id
•         sField_5 = MGlobal.hResData!location
•         sField_7 = MGlobal.hResData!website
•         sField_8 = MGlobal.hResData!twitter
•         MGlobal.hResData = MGlobal.hDB.Exec("SELECT username FROM users WHERE user_id = &1", iUserID)
•         sMember = MGlobal.hResData!username
•         Wait 0.01
•         UpdateDB()
•     Endif
• Next

In below lines change MGlobal.hResData into MGlobal.hResUser

•         MGlobal.hResData = MGlobal.hDB.Exec("SELECT username FROM users WHERE user_id = &1", iUserID)
•         sMember = MGlobal.hResData!username

In MGlobal do:

Public hResUser As Result

Try it and report back 

Didn't properly check the code in my first reply, it was late, and it's tricky to spot in someone elses code.


 

That was the missing piece to the puzzle.  That small change made the difference.   

Quincunxian said

Hi Sholzy,
You're killing the for-next-loop because you are re-initating the MGlobal.hResData object.
Either add a temporary result object (MGlobal.hResDataTmp ) for the second query or change the solution.   

From “Post #13,544”, December 15th 2025, 9:41 PM

Quin already pointed it out, I now see