Page 1 of 1

[SOLVED] Comparing 2 DB and displaying results

Posted: Wed May 21, 2008 9:18 am
by Janco
Hi there, me again

I have written a tool where users can be created from templates and so on. I have added a section where you can search user by template but the query doesn't display the correct users or has no output what so ever here is the code, excuse the "dirty coding"

I have a drop down box using similar code as below to populate the template number and it is posted but the code below is suppose to fetch the posted template number:

Code: Select all

$query="select * from templates.bw_templates where user_number='$template'";
            $tempres=mysql_query($query) or die ("Cannot Extract Template". mysql_error());
            while ($temprow=mysql_fetch_array($tempres)) {
                                    $ops1= $temprow['user_phm'];
                                    $ops2= $temprow['user_deb'];
                                    $ops3= $temprow['user_cre'];
                                    $ops4= $temprow['user_stk'];
                                    $ops5= $temprow['user_inv'];
                                    $ops6= $temprow['user_pur'];
                                    $ops7= $temprow['user_csh'];
                                    $ops8= $temprow['user_res'];
                                    $ops9= $temprow['user_hot'];
                                    $ops10= $temprow['user_pos'];
                                    $ops11= $temprow['user_sal'];
                                    $ops12= $temprow['user_del'];
                                    $ops13= $temprow['user_loy'];
                                    $ops14= $temprow['user_level'];
                                    $ops15= $temprow['user_entry_menu'];
                                    $ops16= $temprow['user_prt_no'];
                                    $ops17= $temprow['user_pass_change'];
                                    $ops18= $temprow['user_utility_only'];
                                    $ops19= $temprow['user_logon'];
                                    $ops20= $temprow['user_pos_qty'];
                                    $ops21= $temprow['user_accept'];
                                    $ops22= $temprow['user_help'];
                                    $ops23= $temprow['user_screen_save'];
                                    $ops24= $temprow['user_training'];
                                    $ops25= $temprow['user_pass_date'];
                                    $ops26= $temprow['user_upper_case'];
                                    $ops27= $temprow['user_log_out'];
                                    $ops28= $temprow['user_sms'];
                                    $ops29= $temprow['user_mail'];
                                    $ops30= $temprow['user_sms_cnt'];
                                    $ops31= $temprow['user_scan_id'];
                                    $ops32= $temprow['user_manager_yn'];
                                    $ops33= $temprow['user_superv_yn'];
                                    $ops34= $temprow['user_cashier_yn'];
                                    $ops35= $temprow['user_deb_clerk_yn'];
                                    $ops36= $temprow['user_cre_clerk_yn'];
                                    $ops37= $temprow['user_stk_clerk_yn'];
                                    $ops38= $temprow['user_ord_clerk_yn'];
                                    $ops39= $temprow['user_rep_yn'];
                                    $ops40= $temprow['user_waiter_yn'];
                                    $ops41= $temprow['user_rest_cashier_yn'];
                                    $ops42= $temprow['user_driver_yn'];
                                    $ops43= $temprow['user_pharmacist_yn'];
                                    $ops44= $temprow['user_assistant_yn'];
                                    $ops45= $temprow['user_op1'];
                                    $ops46= $temprow['user_op2'];
                                    $ops47= $temprow['user_op3'];
                                    $ops48= $temprow['user_op4'];
                                    $ops49= $temprow['user_op5'];
                                    $ops50= $temprow['user_op6'];
                                    $ops51= $temprow['user_op7'];
                                    $ops52= $temprow['user_op8'];
                                    $ops53= $temprow['user_op9'];
                                    $ops54= $temprow['user_op10'];
                                    $ops55= $temprow['user_op11'];
                                    $ops56= $temprow['user_op12'];
                                    $ops57= $temprow['user_op13'];
                                    $ops58= $temprow['user_op14'];
                                    $ops59= $temprow['user_op15'];
                                    $ops60= $temprow['user_op16'];
                                    $ops61= $temprow['user_op17'];
                                    $ops62= $temprow['user_op18'];
                                    $ops63= $temprow['user_op19'];
                                    $ops64= $temprow['user_op20'];
                                    $ops65= $temprow['user_op21'];
                                    $ops66= $temprow['user_op22'];
                                    $ops67= $temprow['user_op23'];
                                    $ops68= $temprow['user_op24'];
                                    $ops69= $temprow['user_op25'];
                                    $ops70= $temprow['user_op26'];
                                    $ops71= $temprow['user_cashier_no'];
                                    $ops72= $temprow['user_manager'];
                                    $ops73= $temprow['user_fixed_prn'];
                                    $ops74= $temprow['user_pharm_name'];
                                    }
 
$srctmp = $_POST['template_no']; //from the form page
 
if ($srctmp !== NULL) {
 
$srcquery="select user_number, user_name, user_level, user_entry_menu from uni_bw_usr where user_deb='$ops2' AND user_stk='$ops4' AND user_inv='$ops5' AND user_del='$ops12' AND user_level='$ops14' AND user_entry_menu='$ops15' AND user_pass_change='$ops17' AND user_accept='$ops21' AND user_help='$ops22' AND user_manager='$ops32' AND user_superv_yn='$ops33' AND user_cashier_yn='$ops34' AND user_stk_clerk_yn='$ops37' AND user_rep_yn='$ops39' AND user_op1='$ops45' AND user_op2='$ops46' AND user_op5='$ops49' AND user_op6='$ops50' AND user_op7='$ops51' AND user_op11='$ops55' AND user_op14='$ops58' AND user_op15='$ops59' AND user_op16='$ops60' AND user_op18='$ops62' AND user_op24='$ops68' AND user_op25='$ops69' order by user_branch_cd";
 
mysql_query($srcquery) or die (mysql_error());
the above output is sent to to a HTML Table that is suppose to display the detail from the select query called "$srcquery" but some templates aren't displayed. I've tried specifying all the columns but still have the same result.

I don't know how to fix this, I've tried everything that I could think of but my knowledge is quite limited.

Re: Comparing 2 DB and displaying results

Posted: Fri May 23, 2008 4:35 am
by Janco
Sorry, but I was quite unclear what I actually wanted to know, I'll try to give more details.

The query that I've given is divided into 2 parts, one queries the templates database to retrieve the required template. The software has 78 different options and menus that it calls depending on the user settings which is retrieved/populated from the templates.

After the template settings are retrieved the fields are populated and a new row is inserted into a different database with the user name, user number and options and menus. Unfortunately I did not design the DB so where some of you might think that with the use of the template why not create an extra column which will, with the creation of a user, add the template name or number which I've thought of but what about the existing users in the DB? The second query is to try match fields/columns of the template DB to the user DB and then depending on the result display insert the template name and that is where things keep going wrong.

I've exported all the templates to a CSV file to try to find fields that will distinguish the templates from each other and took those fields and used it in the code to try to determine which template a or the user has but on some template there are no results then I went and specified all 78 columns but still had the same result. To give an example: on the web page there is a section where you can search users by templates i.e. if you want to see which users have manager's access, now the template used is 9001, so on the web page I specify template 9001 but no results are displayed and I don't know why because I have echoed the query and the variables are populated correctly which means that there is something wrong with the search query itself and if someone can point me in the right direction I'd appreciate it. If you want me to zip the entire app with the DB and send it please let me know....it's about 1.5MB.

Re: Comparing 2 DB and displaying results

Posted: Fri May 23, 2008 4:52 am
by Janco
I've attached a xls file with all the templates and their options so that it makes more sense what I need to do.

Re: Comparing 2 DB and displaying results

Posted: Fri May 23, 2008 5:30 am
by Janco
Now I'm completely confused... not that is something new!

I have exported the details for template 9006, please refer to the attachment of previous post, and took the column details to create this query, will be submitted at end of post.

When I ran it on the users DB it had no result - nothing new there, but when I ran it on the template DB it also had no result and the columns and column values where taken from the DB - Please help?

Code: Select all

select user_number,user_name,user_branch_cd from bw_templates where user_phm='N' AND user_deb='N' AND user_cre='N' AND user_stk='Y' AND user_inv='N' AND user_pur='N' AND user_csh='N' AND user_res='N' AND user_hot='N' AND user_pos='N' AND user_sal='N' AND user_del='Y' AND user_loy='N' AND user_level='3' AND user_entry_menu='2' AND user_prt_no='0' AND user_pass_change='Y' AND user_utility_only='Y' AND user_logon='N' AND user_accept='S' AND user_help='Y' AND user_screen_save='N' AND user_training='N' AND user_upper_case='N' AND user_log_out='N' AND user_sms='N' AND user_mail='N' AND user_manager='Y' AND user_superv_yn='N' AND user_cashier_yn='N' AND user_deb_clerk_yn='N' AND user_cre_clerk_yn='N' AND user_stk_clerk_yn='N' AND user_ord_clerk_yn='N' AND user_rep_yn='N' AND user_waiter_yn='N' AND user_rest_cashier_yn='N' AND user_driver_yn='N' AND user_pharmacist_yn='N' AND user_assistant_yn='N' AND user_op1='N' AND user_op2='N' AND user_op2='N' AND user_op3='N' AND user_op4='N' AND user_op5='N' AND user_op6='N' AND user_op7='N' AND user_op8='N' AND user_op9='N' AND user_op10='N' AND user_op11='N' AND user_op12='N' AND user_op13='N' AND user_op14='N' AND user_op15='N' AND user_op16='N' AND user_op17='N' AND user_op18='N' AND user_op19='N' AND user_op20='N' AND user_op21='N' AND user_op22='N' AND user_op23='N' AND user_op24='N' AND user_op25='N' AND user_op26='N' AND user_manager='N'

Re: Comparing 2 DB and displaying results

Posted: Mon May 26, 2008 1:08 am
by Janco
Found the problem.

I specified the wrong column, I should've specified user_manager_yn instead of user_manager in the PHP query.

All working fine.