473,372 Members | 1,047 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

Advance joined query, need help

Hi all,

I've been working on a listing problem and I can't figure out how to work it out. I have looked far and wide on the web to find answers, but I'd like other peoples input on my project in the whole. I really need MySQL wizz to give me a hand (and maybe refer me to books to get me to the wizz level myself).

First off, english is a second language to me and sometimes my sentences might be a little awkward. Please forgive me. Mon français est bien meilleur.

Description of database
My database is pretty large and I tried to design it following the best practice (using a lot of many-to-many relationship tables). It holds the description of over 200 stores, with opening hours, services, locations, etc. I am trying to create a AJAX store search engin, with Google Map display, where the user can choose from a number of fields the conditions of his search. When he validates his search, the query is sent to the server, which retrieves the list and update the map.

The conditions are:
  • Postal Code
  • City id
  • Service id
  • Opening hours

Next is the schematic description of the table store_info
| idStore | ... | idCity | openingHourMonday | closingHourMonday | openingHourTuesday | closingHourTuesday | ... |

The schematic description of the table store_service
| idService | vchLabel | ... |

The schematic description of the table store_info_service
| idService | idStore |

The schematic description of the table store_info_postalcode
| vchPostalCode | idStore |

(NOTE: those Postal Codes are based on the flyer distribution list, so it doesn't contain all the postal code of the country, and some user input can return NULL).

the Script
Getting the information out with JOINED query is not a problem, the trouble is getting the right data out. Getting the conditions expressions right is what I need.

So, I've built my PHP script to compose my query in parts ($SELECT, $FROM, $WHERE). Based on the GET parameters, I add to the query string the needed expressions to refine the query. I unite the parts in $query, and run it. Its been working OK, but not up to my linking. I wan't it bullet proof.

Here is the base query:

Expand|Select|Wrap|Line Numbers
  1. SELECT i.*, 
  2. v.`vchLabel` AS ville, 
  3. FROM `stores_info` AS i 
  4. JOIN `geo_ville` AS v ON i.`idVille` = v.`idVille` 
  5. WHERE i.`swActiv`=1 AND (p.idLangue = 'FR' OR p.idLangue IS NULL) 
  6. GROUP BY i.`idStore`;
Here are some of my solutions:

1. Postal code: If a user input a postal code which returns no data, needs to be repeated with a substring of the P.C. (eg.H1H1H1 -> H1H1H_). Using LIKE in

Expand|Select|Wrap|Line Numbers
  1. SELECT i.*, 
  2. v.`vchLabel` AS ville, 
  3. FROM `stores_info` AS i 
  4. JOIN `geo_ville` AS v ON i.`idVille` = v.`idVille` 
  5. JOIN `stores_cp` AS cp ON i.`idStore` = cp.`idStore` 
  6. WHERE i.`swActiv`=1
  7. AND cp.`cp` LIKE 'H1H1H1' 
  8. GROUP BY i.`idStore`;
return 0

Expand|Select|Wrap|Line Numbers
  1. SELECT i.*, 
  2. v.`vchLabel` AS ville, 
  3. p.`vchLabel` AS province 
  4. FROM `stores_info` AS i 
  5. JOIN `geo_ville` AS v ON i.`idVille` = v.`idVille` 
  6. JOIN `stores_cp` AS cp ON i.`idStore` = cp.`idStore` 
  7. WHERE i.`swActiv`=1
  8. AND cp.`cp` LIKE 'H1H1H_' 
  9. GROUP BY i.`idStore`;
return 1 store

2. Closing time: the user can ask for stores open now and for the next 30, 60, or 300 minutes (5hre). It could be any number of minutes, but if it goes later then midnight, the closing time is removed and the user receives a warning.

Expand|Select|Wrap|Line Numbers
  1. SELECT i.*, 
  2. v.`vchLabel` AS ville, 
  3. FROM `stores_info` AS i 
  4. JOIN `geo_ville` AS v ON i.`idVille` = v.`idVille` 
  5. WHERE i.`swActiv`=1
  6. AND '13:20:05' BETWEEN i.`wednesdayopen` AND i.`wednesdayclose` 
  7. AND '18:20:05' < i.`wednesdayclose` 
  8. GROUP BY i.`idStore`;
3. idCity: the user can choose multiple cities from a drop-down list. The id get passed to the request in the form of a list separated by coma. I then use the IN() statement

Expand|Select|Wrap|Line Numbers
  1. SELECT i.*, 
  2. v.`vchLabel` AS ville, 
  3. FROM `stores_info` AS i 
  4. JOIN `geo_ville` AS v ON i.`idVille` = v.`idVille` 
  5. WHERE i.`swActiv`=1
  6. AND i.`idVille` IN (401,1102) 
  7. GROUP BY i.`idStore`;
Returns the list of all the stores located in city 401 or 1102.

And thats where it gets complicated.

Trouble #1: The services
The user has checkbox associated with the idService. The idServices get sent to the request in the same form as the idCity (eg. 1,4).

At first I used the IN() statement, but realised that it got the store that offers service 1 OR 4. I needed the stores that offered the two services.

So I used = to compare the string to a subquery (SELECT GROUP_CONCAT(idServices) FROM store_info_service WHERE idStore = $idStore), but of course a store that offers services 1,2,3,4 was not returned, but it DID offer the services requested. I tried the IN() construct with this subquery, but nothing conclusive came out.

Trouble #2: Concurrent search clauses
The user can select more then one condition for his search. For exemple, he could look for the stores open in the next hour, around his own postal code (eg. H1H___).

It has been a requisite of this project that the user receives listing whatever the cost. If one condition is not met, it is bypassed to give the stores corresponding to the other conditions. A javascript alert informs the user of that.

How do I structure such a script? I've made it work with TIME and POSTAL CODE, doing repeated queries until a result is found.

Expand|Select|Wrap|Line Numbers
  1. while (no result){
  2.      if (isset(T)){
  3.           unset(T);
  4.           try again;
  5.      }else{
  6.           reset(T);
  7.      }
  9.      if (isset(PC)){
  10.           truncate PC;
  11.           try again;
  12.      }
  13. }
Of course, this is a simplified version of my PHP script. That way, I try with the T, without the T, reducing PC every 2 queries. It seems pretty hard and time consumming right now, imagine when I add CITY and SERVICE to the equations.

Does anyone have an idea how I could simplify this process?

One hypothesis was to run the 4 queries separately and cross-referenced the results in PHP, only keeping the result sets which have common results, and keeping a warning on the defective queries. If a query is really messed up, I could keep only one condition and return these results, based on semantic priority (Postal code > City > Opening hours > Services).

As you can see this is a though one. I'd really like to be able to wrap my mind around problems of the kind and come up with effective solutions. I tried to find books only about queries (not administration). If you have any recommandation, go ahead.

Dec 20 '06 #1
0 2422

Sign in to post your reply or Sign up for a free account.

Similar topics

by: Mike Leahy | last post by:
Hello all, This question is related to updating tables - is there any way to calculate or update the values in a column in a table to the values in a field produced by a query result? An...
by: Michael | last post by:
I have a query that uses two joined tables. The query contains data. I would like to pull in a data drom another table but when I add that table to the query design and make the join,the existing...
by: GGerard | last post by:
Hello I have the following tables joined on a one (Field4) to many (Field3) relationship Table1 Field1 / Field2 / Field3 1 Bob 2 2 Fred ...
by: Ersin Gençtürk | last post by:
hi , I have 2 tables , tUser and tUserRole and I have a query like : select * from tUser inner join tUserRole on tUserRole.UserId=tUser.UserId where tUser.UserId=1 this will return a user...
by: Ike | last post by:
I have a simple query of joined tables that is failing to give me any rows of data (though, in checking by hand, it certainly should). Essentially, I am trying to return all rows from `ups` that...
by: zMisc | last post by:
Are there any tricks in updaitng a JOINed recordset? I joned to tables and when I try to change a field on the recordset and update it, I get this error: "Unknown column 'CCDE' in 'where...
by: Frank List | last post by:
Hi, I've run into this problem many times and have not found a good solution yet. Here's what I have: table ParentTable - the "1" table table ChildTable- the "many" table ParentTable...
by: kiss07 | last post by:
Hi friends, I want to select records those are joined date is 23/03/07 in a table. This is simple query .i need answer ..I have an date function error in myside. and another...
by: KTosser | last post by:
I have two tables, one contains contacts and the other has all the events and years that the contacts participated in. What I want is to be able to choose the years, say 2005 and 2006, and get all...
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.