Multiple DB queries to different tables are botching my page

Report · Apr 05, 2010

Hello everybody,

This one's going to be a bit difficult to explain clearly, but I will do my best.

ADDED: After posting this thread, I have been browsing a lot of forums and tutorials. I see a lot of praise for JOIN but I see nothing of lookup tables, as David Powers was so kind to show us in PHP Solutions. I like the lookup table and it works for me rather well. I'm curious as to why it gets no play yet the JOIN command does? Further, many uses of JOIN that I am seeing appear, to me as a novice, to be solveable with WHERE and AND as opposed to JOIN. Gotta go now, my temples are splitting. Cheers!

Okay, I have a TOC (table of contents) page I am working on as part of my online photo gallery. When you land on the preceding page for category selection, you have three choices. Picking one sends you to the TOC and with the associated query string value, the TOC page opens with the appropriate data.

I have eleven separate queries that collect the information to stock the page. It looks beautiful when it works, and all of this works when there is only ONE gallery presented. It's when I get into presenting many per page that the queries collide. And yes, all of the tables I will mention next are part of the main DB.

Please note, the 'names' of the tables shown are casual references, not the actual table names.

From my 'galleries' table, I pull these records using the first three queries:

1. Total number of galleries that match the value of the query string

2. Retrieving a subset of data (in this case, x-amount of galleries to display per page)

3. Gallery names and descriptions

The above three queries work in harmony although I can not get the number of galleries that I want displayed on a page (if I want 3 to show up and there are 4 total, I get all 4). I do, however, get all of the appropriate data for the above and it looks fantastic.

Next, when I add any or all of these queries, the aabove-mentioned info gets botched.

From my 'galleries' and 'photos' tables, I pull these records:

4. First representative thumbnail per gallery

5. Second representative thumbnail per gallery (I use two, it makes the layout look nice. These are just to give you an idea of what you'll see if you select this gallery)

6. The total number of photos in any photoset belonging to the category of galleries being displayed.

From my 'model names', 'model lookup' and 'galleries' tables I pull these records:

7. Model name/names associated with any specific gallery

From my 'subject names', 'subject lookup, and 'galleries' tables I pull these records

8. The subject, which is an initial set of 'categories' (used in the page header, title and some links).

'category names', 'category lookup, and 'galleries' tables I pull these records:

9. Category name (used in the page header and title)

'photographer names, 'photographer lookup' and 'galleries' tables I pull these records:

10. Photographer name associated with any specific gallery

'mua (make-up artist) names, 'mua lookup' and 'galleries' tables I pull these records:

11. Make-up artist name associated with any specific gallery

Now, if at this point, you are wanting to smack me over the head asking, 'Why are you doing it this way?' please note that I am extremely new to this and am open to suggestions that help to streamline things. Still, these eleven separate queries are all dealing with specific data types and unless there's some magical way to combine these all into one query (which to date my research has shown to not exist), I'm stuck with eleven queries.

Out of frustration, I have tried adding $result->free_result(); after my queries, but they bomb the page when I go to test it locally. What is the proper time to use this command, and what is the proper syntax?

Also, is it necessary to have more than one connection ($conn = dbConnect('query');) per page? Should I have one preceding every query? Would just one for all eleven queries do the job? I have to say, for the page that worked completely only displaying one gallery and all of this lovely data, one connection did the job. Is this bad practice?

I apologize if this is the least bit confusing. My descriptive prowess is not at all what it usually is due to woeking on this project dya in and day out...me brain is fried. However, I am making progress, but now, this is my tallest hurdle yet.

Thanks to any and all of you who can help!

Sincerely,

wordman

Report · Apr 06, 2010

Hi

First the simple part -

Also, is it necessary to have more than one connection ($conn = dbConnect('query');) per page?

It is not necessary to have more than one connection string per page, but the code you are showing is for the execution of a query, which should be included for every query then closed.

Now for the more complicated section, (it gave me an headache just reading it ), but I can see why trying to expain this would be confusing.

Using JOIN would solve your problem, (possibly) but the proper use of JOINs is one of the more complex of sql features and normally requires the use of both INNER and OUTER JOINs to work correctly. As I am not a writer trying to explain this adequately is beyond my competence in writing, and yes I do agree with you about what can be found on the web on the subject, (totally inadequate) as I have not found anything I could point you to.

The other possibility is to convert your sql query's to be part of a php function, then use a combination of if/else/switch statements to execute the sql queries as required.

My explanations on sql and php are not the best, which is why I do not answer in this section very often !

PZ

www.pziecina.com

Report · Apr 06, 2010

PZ,

But thank you for taking the time to reply. I did get some insight from your answer and for that I am grateful.

The code I pasted in IS php.

I will definitely look into if/else/switch...as for the headache, my head was splitting when I posted this message after a logn day of long days on this project. Fortunately I learn something each day that lets me continue, then it seems as though spend the rest of that day figuring it out.

Thank you again, for taking the time.

Sincerely,

wordman

Report · Apr 06, 2010

>I'm curious as to why it gets no play yet the JOIN command does?

First of all, JOIN is not a command. It's a statement. It's a way of combining table columns using the JOIN "keyword" within the FROM clause.

>Further, many uses of JOIN that I am seeing appear, to me as a

>novice, to be solveable with WHERE and AND as opposed to JOIN.

You can either use the JOIN keyword, or join tables within the WHERE clause. Both yield the same results. In the past, the only option was to use the WHERE clause, but things like outer join syntax varied among different DBMS's. Later versions of the ANSI standard introduced the JOIN keyword which standardized join syntax. But you can use whichever you are more comfortable with as most query optimizers will give the same results.

>Still, these eleven separate queries are all dealing with specific data

>types and unless there's some magical way to combine these all into

>one query (which to date my research has shown to not exist), I'm stuck

>with eleven queries.

If you are populating a page with data, then I assume that the data on the page is related in some way. If the data is related, then there must be a way to combine the queries. It may not make sense to combine ALL of them, but I guarantee that running 11 queries for a page is the wrong approach. Looking at your examples, it seems like this is achieveable.

I would really urge you to step back and learn some more SQL. JOINS are not an advanced topic. There is plenty of good material on the web for learning SQL, or you can find some great books if that's more your style.

Report · Apr 06, 2010

Bregent,

As always, many thanks. I will definitely do some research today on the JOIN thing. With regards to your correction of JOIN being a statement, not a command, I appreciate that. I am a detail person and I like to speak accurately.

I checked out some forums and I saw mention of the large number of queries used to build a stock WordPress home page. It was high er than my 11, so that's why I figured I wasn't completely off base.

It's tough still finding all the right info, half of the Google results I get are forum posts where they are using SQL code,and I'm writing in PHP. I'm SO close to nailing this though.

Yes, all the data mentioned in my opener are related in one form or another. Through the advice on lookup tables I got from both David and you, I was able to keep my DB structure simple and free of repetitive data.

Now it looks as though I have some tough work ahead. I'll keep you all posted. Many thanks!

Sincerely,

wordman

Report · Apr 06, 2010

UPDATE: A breakthrough!

By using a different manner of table structure and some 're-wording' in my SELECT query, I have gotten back three of the missing pieces of data.

Cautiously I shall proceed. this is very exciting!

Cheers,

wordman