[SOLVED] MySQL acceptable queries per page.

[StrangeWill]

So I've been working on trying to get the information I want out of one query, but the way the database is structured seems to make it extremely difficult to do such.

 

I don't even know if I can restructure it in a way to get the information I want out of one query.

 

 

So in my mind I say "why not just run more than one query?", alas how many queries is too many to run during a page load? They're all pretty simple queries (two pieces of data, one left join, where an ID is a value).

 

Problem is I could need to do this some 20-30 times, or even more.

 

Is this okay, or should I be looking at another method?

 

 

 

Just in case another method is suggested, I have this (for example) as the table:

id | parentid | text
1  | 0        | Sometext
2  | 1        | ChildText
3  | 1        | OtherChildText
4  | 2        | ChildChildText

 

Currently I can left join the table again on tablejoined.parentid = table.id, alas this only gets me one deep, and also returns a lot of data (will return the id and text of the parent again and again and again for every child item, seems like a waste of memory/time).

 

I would need something that is recursive till there are no children.

[Ken2k7]

Can you show us a code or give us a scenario of what you're trying to do?

[StrangeWill]

Heh, just gave it.

[bluejay002]

do you have a sample query that would guide us what you want to get?

[StrangeWill]

Currently I'm roughly running with this (sorry if it isn't exact, but it's a general idea, I don't have the SQL statement right in front of me at the moment..)

 

SELECT table.*, tablechild.* FROM table LEFT JOIN table AS tablechild ON tablechild.parentid = table.id

 

However that only gets me one child deep.

 

 

I could just do a loop:

for(each record)

{

SELECT table.* FROM table WHERE parentid=currentTableID

}

[fenway]

Well, you can keep on left join-ing, but this model of data organization is not meant to be recursive -- there's the list adjacency model, though.

[StrangeWill]

Well, you can keep on left join-ing, but this model of data organization is not meant to be recursive -- there's the list adjacency model, though.

Yeah, and it also tends to send way more data than is actually needed, and ends up requiring a hefty overhead of dealing with the data.

 

Should I just loop through queries, or create a separate table, then do a call if the parent HAS children?

[fenway]

Well, how many levels deep are we talking about, realistically?

[StrangeWill]

Well, how many levels deep are we talking about, realistically?

Max of 2-3, no more.

[fenway]

Then you can self-join in that many tables.

[StrangeWill]

Okay, I'll just do that. Thanks.