Forum Moderators: coopster
I run 3 databases name citations_1st, citations_2nd, and citations_3rd (I work mall security - woot - and I track whatever parking citations we hand out).
Anyways, all 3 databases are broken down by watch, the only difference is the DB name and the field data stored inside. Basically what I am trying to do is run a search for license plates accross all 3 shifts without running 3 different searches. I think a JOIN is needed but I'm too newby to understand it.
I tried "select tablename1.columnname, tablename2.columnname where license_num = $forminput"
and got like 400 responses when I should have gotten 2. :D Any tips?
You are using a cartesian product [webmasterworld.com] when you don't specify any JOIN criteria. Is there any way to link the tables together?
More importantly, why separate the three groups of data into three tables? If the information is the same, I would just create one table and add the 'shift' as another column.
If it is three tables in one database, in addition to JOINs, a MERGE type table might work for you, discussed here: Cross table MySQL Query help [webmasterworld.com].
MERGE type tables will not work across databases, however. Also, you won't know which constituent table the data came from unless there is some field data in there that will give you a clue.
Now since this system is already live I would have to export the data (CSV file I assume?) from all 3 tables, then create a new table (or rename one of the 3 to a more formal global-style name) with a "watch" column (I'm thinking a smallint(1), then re-import the data correct and manually enter the watch number?
Now right now the way the web page is setup is I have a navbar that lets you choose from adding a citation to the DB, or search for citations via the start of a month and then the end of a month (that search is for a monthly report we do) or search in any of the columns for a string.
Now because I work with people that fear computers I have to make them choose which watch they are inputting data in, and currently I run 3 directories (and because of that 3 tables) to handle that. Now could I make a drop down box that sets a global variable that automatically gets inserted into the table along with the rest of their data?
And with that global variable I think I can run an if statement that would print out what watch they're putting data in for like so
if ($watch = 1){
echo "Entering 1st Watch Parking Citations";
}
elseif ($watch = 2){
echo "Entering 2nd Watch Parking Citations";
}
elseif ($watch = 3){
echo "Entering 3rd Watch Parking Citations";
}
else {
echo "No Watch Selected!";
echo "code to go back and select a watch";
}
Sorry I was brainstorming and needed a place to write :D Thanks for your input coopster!
Also on an unrealted note my next project is a Tenant Emergency Contact list. I know enough to create it in one table, but would multiple tables be better?
My current train of thought on multiple tables for it would be
Store table with store name and number, then a manager table with their name and phone number, then an assistant manager table with name and phone number.
Seems overly complicated for a simple "contacts" style Db.... ok nm thanks for your time again. :)
