spock9458

OK, this is very close to working, but there are a couple of errors and I can't seem to figure them out. If you look at the results here: http://www.nmlta.org/NewMemDir/testlist3.php - you can see that I'm getting "invalid argument supplied" for lines 172 and 177 of my code. The listing of the company information and the contact information is correct, but then it gives the error when trying to list the branch and branch contact info. Then at the end of all of the company info, all of the branch information is listed together. The data appears to be all there, but the output is not listed as desired. Here is the complete code (minus my "list" functions) with the above line numbers indicated: // Retrieve all the data from the "company" table $query = "SELECT * FROM company LEFT JOIN contact ON company.company_id = contact.company_id WHERE comp_county = 'BERNALILLO' ORDER BY company.comp_name, contact.cont_rank"; $result = mysql_query($query) or die(mysql_error()); // Build the $company_array $company_array = array(); while($row = mysql_fetch_assoc($result)) { if(!isset($company_array[$row['company_id']])) { // Add company specific data here $company_array[$row['company_id']] = array( 'comp_name' => $row['comp_name'], 'comp_uw' => $row['comp_uw'], 'comp_street' => $row['comp_street'], 'comp_csz' => $row['comp_csz'], 'comp_ph' => $row['comp_ph'], 'comp_fx' => $row['comp_fx'], 'comp_email' => $row['comp_email'], 'comp_web' => $row['comp_web'] ); } //Add the company contact info here $company_array[$row['company_id']]['contacts'][] = array( 'cont_name' => $row['cont_name'], 'cont_title' => $row['cont_title'], 'cont_email' => $row['cont_email'] ); } //Run query to get branch info - for ALL companies $query = "SELECT branch.*, contact.* FROM branch JOIN company ON branch.company_id = company.company_id LEFT JOIN contact ON branch.branch_id = contact.branch_id WHERE company.comp_county = 'BERNALILLO' ORDER BY branch.br_name, contact.cont_rank"; $result = mysql_query($query) or die(mysql_error()); //Add branch (and branch contacts) results to $company_array while($row = mysql_fetch_assoc($result)) { $compID = $row['company_id']; $branchID = $row['branch_id']; if(!isset($company_array['branches'][$row['branch_id']])) { $company_array[$compID]['branches'][$branchID] = array( 'br_name' => $row['br_name'], 'br_street' => $row['br_street'], 'br_csz' => $row['br_csz'], 'br_ph' => $row['br_ph'], 'br_fx' => $row['br_fx'], ); } $company_array[$compID]['branches'][$branchID]['contacts'][] = array( 'cont_name' => $row['cont_name'], 'cont_title' => $row['cont_title'], 'cont_email' => $row['cont_email'] ); } // Start building the table for showing results echo "<table border='1' cellpadding='10' cellspacing='0' bgcolor='#f0f0f0' style='margin-left:100px; margin-bottom:20px'>"; // Begin the "foreach" loop for rows in the $company_array foreach($company_array as $company) { echo "<tr><td>\n"; listCompany($company); foreach($company['contacts'] as $contact) #This is line 172 { listContact($contact); } foreach($company['branches'] as $branch) #This is line 177 { listBranch($branch); foreach($branch['contacts'] as $contact) { listBranchContact($contact); } } echo "</td></tr>\n"; } echo "</table>"; Hopefully someone can point out what the errors are, I have been studying the code for over an hour, and I am stumped. Thanks.

Thank you for the tips - I think I understand enough of your suggestion to give it a try. I will do so and report whether I can make this work or not. I appreciate your help!

I have read and learned a lot in the last couple of weeks, working on my membership directory listings. I am close to the final solution, but can't seem to work out the right logic for the final product. Here is what happens: my sample query selects all members from the 'company' table that match a certain county location or alphabet letter, and uses LEFT JOIN to attach all people associated with the company from the 'contact' table. The results of the first query are then loaded into an array, called $company_array, for processing by "list functions" that I have created for the formatted output on the screen... If all I had were company and contact information, I would be done - but that's not the case. Some (but not all) companies have one or more branch locations associated with them, and each branch location may have one or more people associated with them. At some point in my listing process, I have to pause and run a second query to find matching entries from the 'branch' table (using a company_id key) and LEFT JOIN to attach all people associated with the branch from the 'contact' table, and load those results into another array, called $branch_array. I am successful when running the queries, as I have tested a couple of different ways... but I can't get the final result I want, which I'll describe now. The order of events should be: 1) start listing the first company info; 2) following the company info, list all contact info for the people associated with the company;3) check to see if there are one or more branches associated with the company, and if so, list each branch, followed by each contact person associated with the branch; 4) Repeat the process to go through all of the companies found in the first query. My most recent version of the code is as follows: // Retrieve all the data from the "company" table $query = "SELECT * FROM company LEFT JOIN contact ON company.company_id = contact.company_id WHERE comp_county = 'BERNALILLO' ORDER BY company.comp_name, contact.cont_rank"; $result = mysql_query($query) or die(mysql_error()); // Build the $company_array $company_array = array(); while($row = mysql_fetch_assoc($result)) { $company_array[] = $row; } $lastCompany = ''; $lastBranch = ''; // Start building the table for showing results echo "<table border='1' cellpadding='10' cellspacing='0' bgcolor='#f0f0f0' style='margin-left:100px; margin-bottom:20px'>"; // Begin the "foreach" loop for rows in the $company_array foreach($company_array as $row) { // Check if this is a different company than the last one if ($lastCompany != $row['company_id']) { // If this is a different company - change the $lastCompany variable $lastCompany = $row['company_id']; echo "<tr><td><p><b>"; // List the company info only if it is not the $lastCompany listCompany($row); } listContact($row); } // Retrieve all the matching data from the "branch" table $query2 = "SELECT * FROM branch LEFT JOIN contact ON branch.branch_id = contact.branch_id WHERE branch.company_id = '".$row['company_id']."' ORDER BY branch.br_name, contact.cont_rank"; $result2 = mysql_query($query2) or die(mysql_error()); // Check to see if there is a Branch to list if (mysql_num_rows($result2) != 0); { // Build the $branch_array $branch_array = array(); while($row2 = mysql_fetch_assoc($result2)) { $branch_array[] = $row2; } foreach($branch_array as $row2) { if ($lastBranch != $row2['branch_id']) { $lastBranch = $row2['branch_id']; listBranch($row2); } listBranchContact($row2); } echo "</td></tr>"; } echo "</table>"; mysql_close(); The result of running this code can be seen here: http://www.nmlta.org/NewMemDir/testlist2.php which, as you can see, does not get to the branch listing until the last company is listed... I can't seem to figure out where or how to insert my second query, or whether I should use some kind of if/else logic to get it to work the way I need. Any suggestions will be appreciated... thanks.

I have been working on the logic for question 1 in my last post above, and I think I might be on the right track with this: // Retrieve all the data from the "company" table $query = "SELECT * FROM company LEFT JOIN contact ON company.company_id = contact.company_id WHERE comp_county = 'BERNALILLO' ORDER BY company.comp_name, contact.cont_rank"; $result = mysql_query($query) or die(mysql_error()); // Build the $company_array $company_array = array(); while($row = mysql_fetch_assoc($result)) { $company_array[] = $row; } // Retrieve all the matching data from the "branch" table foreach($company_array as $row) { $query2 = "SELECT * FROM branch LEFT JOIN contact ON branch.branch_id = contact.branch_id WHERE branch.company_id = '".$row['company_id']."' ORDER BY branch.br_name, contact.cont_rank"; $result2 = mysql_query($query2) or die(mysql_error()); } // Build the $branch_array $branch_array = array(); while($row2 = mysql_fetch_assoc($result2)) { $branch_array[] = $row2; } I can't verify this because something is wrong later in my code, which I will address after I find out if the above logic would appear to be correct for pre-loading the two arrays I need to deal with. Thanks.

Moving along with my project, I am ready to get to this step suggested by mac_gyver above: "to avoid running any queries inside of loops, that you pre-process the data from the first query (storing it into an array, using the company_id as the main array key.) you can then get all the company_id's out of that array and run one query to get all the branch information at one time. pre-process this branch information and store it into another array using the company_id as the main array key." I have successfully stored the results of my first query in a $company_array by the following code: // Retrieve all the data from the "company" table $query = "SELECT * FROM company LEFT JOIN contact ON company.company_id = contact.company_id WHERE comp_county = 'BERNALILLO' ORDER BY company.comp_name, contact.cont_rank"; $result = mysql_query($query) or die(mysql_error()); // Build the $company_array $company_array = array(); while($row = mysql_fetch_assoc($result)) { $company_array[] = $row; } I now have a two-part question: 1) How do I build that array "using the company_id as the main array key"? and 2) How do I "get all the company_id's out of that array and run one query to get all the branch information at one time"? Remember that not all companies have branches, but the branch table does have a company_id field that matches the company_id field in the company table, whenever there is a branch for the company. I don't understand how to run the second query so that it goes through each row of the $company_array to look for the matches. Please help - thanks.

That is beautiful... now my new code is working. On to the next step in my project, I appreciate the help to understand how to "load" my array - it makes total sense now.

OK, I gave this my best shot... here are the three lines of code that I thought would do that: // Retrieve all the data from the "company" table $query = "SELECT * FROM company LEFT JOIN contact ON company.company_id = contact.company_id WHERE comp_county = 'BERNALILLO' ORDER BY company.comp_name, contact.cont_rank"; $result = mysql_query($query) or die(mysql_error()); $company_array = mysql_fetch_array($result); Prior to changing the code, my loop was: while($row = mysql_fetch_array( $result )) { My goal is to store the query result in the $company_array placeholder for handling by the code that will come later... but I'm obviously not doing it quite right... please let me know what I need to do. Thanks.

So, based on suggestions in another post, I have adjusted my working code to try and hold my results in a $company_array, and test the listing using a foreach loop instead of a while loop, so I can understand this process. Here are the "before" (working) and "after" (not working) sections of code: Before: // Start building the table for showing results using "while" loop $lastCompany = ''; echo "<table border='1' cellpadding='10' cellspacing='0' bgcolor='#f0f0f0' style='margin-left:100px; margin-bottom:20px'>"; // Here is the "while" loop while($row = mysql_fetch_array( $result )) { if ($lastCompany != $row['comp_name']) { // OK we have a new Company to output $lastCompany = $row['comp_name']; # Track the "last" company we processed echo "<tr><td>"; listCompany($row); } // This is the end of the Company info listContact($row); } echo "</td></tr>"; echo "</table>"; And here is the "After": $company_array = mysql_fetch_array($result); // Start building the table for showing results using "while" loop echo "<table border='1' cellpadding='10' cellspacing='0' bgcolor='#f0f0f0' style='margin-left:100px; margin-bottom:20px'>"; // Here is the "while" loop $lastCompany = ''; foreach($company_array as $row) { if ($lastCompany != $row['comp_name']) { // OK we have a new Company to output $lastCompany = $row['comp_name']; echo "<tr><td><p><b>"; listCompany($row); } listContact($row); echo "</td></tr>";} echo "</table>"; I don't understand the results of this, which can be viewed here: http://www.nmlta.org/NewMemDir/testlist.php Please let me know what I've done wrong... thanks.

Thanks, that makes sense... and the functions are now working. I wanted to understand that part of it, so that I can work on the logic now without all of the output clutter... I'll see what I can do, thanks.

This is a specific question about passing array values in functions, and I'm either not doing it right or it can't be done. My project involves a membership directory listing which has company information and contact people at each company, and there is more to do, but I want to understand the concept of using functions for my repetitive code, so I need to know why what I am trying does not work. First I started with a working sample that can be seen here: http://www.nmlta.org/NewMemDir/testjoin.php All I did was take the code that lists the items in my arrays and created two functions: listCompany(), and listContact(), and I call the functions where I think they need to go, but here is what the result is so far: http://www.nmlta.org/NewMemDir/testjoin3.php I am attaching both codes for anyone to review, minus my connection info. I am really interested in figuring this out, so I appreciate your help. Thanks. testjoin.php testjoin3.php

Thanks for the tips, mac_gyver. So, again I am working through step by step, and I have created functions to print out each of my list types: listCompany(), listBranch() and listContact(), and I'm trying to reconstruct my original working code that successfully lists just the company and contact info first. The code appears to work partially, but I think I have a syntax problem with referring to my field names from the array results. This line of code creates an array of my query results, called $row, correct? while($row = mysql_fetch_array( $result )). So before, in my old loop I just echoed each field using $row['field_name']. But now using my functions to echo the results, I'm calling listCompany($row), so I think I can't use the $row identifier again in my function code. Here is the code for my function: function listCompany() { echo $row['comp_name'],"<br />"; if (empty($row['comp_uw'])) { // do nothing } else { echo $row['comp_uw'],"<br />";} echo "</b>",$row['comp_street'],"<br />"; if (empty($row['comp_pobox'])) { // do nothing } else { echo $row['comp_pobox'],"<br />";} echo $row['comp_csz'],"<br />"; echo $row['comp_ph'],"&nbsp&nbsp Fax: ",$row['comp_fx'],"<br />"; if (empty($row['comp_tfree'])) { // do nothing } else { echo "Toll Free: ",$row['comp_tfree'],"<br />";} if (empty($row['comp_email'])) { // do nothing } else { echo "Email: <a href='mailto:",$row['comp_email'],"'>",$row['comp_email'],"</a><br />";} if (empty($row['comp_web'])) { // do nothing } else { echo "<a href='http://",$row['comp_web'],"' target='_blank'>",$row['comp_web'],"</a><br />";} echo "</p>"; } So how do I change my function code to remove the $row identifier here, since I'm calling it with the function. Does this make sense? I'll attach my entire code file in case it helps. Thanks. testlist.php

Still working on my membership directory listing, and my brain can't figure how to control this. My membership consists of company, branch, and contact data in three separate tables. The main queries that will be used on my website are to search for companies by a county location or alphabetically - no problem, I know how to do all that. I have gotten some help from this forum with using a JOIN query to "attach" people from the contact table to their appropriate company, or their appropriate branch - the queries are working fine. Now I'll try to explain my challenge: Step one was to do a company list with contacts, so the query looks like this: $query = "SELECT * FROM company LEFT JOIN contact ON company.company_id = contact.company_id WHERE comp_county = 'BERNALILLO' ORDER BY company.comp_name, contact.cont_rank"; $result = mysql_query($query) or die(mysql_error()); Then we start the output, using a $lastCompany variable to keep track of the company name: $lastCompany = ''; while($row = mysql_fetch_array( $result )) { if ($lastCompany != $row['comp_name']) { // OK we have a new Company to output echo "<tr><td><p><b>"; $lastCompany = $row['comp_name']; # Track the "last" company we processed // The Company data is listed next } #ending of the "if" statement // The data for the contact people is listed next - If the company has more than one contact person, then this repeats for each person } # ending of the "while" loop This first part works perfectly, as can be seen here: http://www.nmlta.org/NewMemDir/testjoin.php The data in my sample county contains five companies, each with one or more contact people, and this part of the listing works great. The problem is that some of the companies also have branch offices, which need to be listed immediately after the contact people at the main company - and each branch may have one or more contact people also. Thus the need for my Sub-Query as follows: $query2 = "SELECT * FROM branch LEFT JOIN contact ON branch.branch_id = contact.branch_id WHERE branch.company_id = '".$row['company_id']."' ORDER BY branch.br_street, contact.cont_rank"; I have tried to use a $lastBranch variable, similar to the $lastCompany above, to track and list all of the contact people at a certain branch, and I think this will work, but the problem is I can't seem to structure everything so that the main company and contact data process first, and then the branch and branch-contact data process second, before moving on to the next company from the original query. If someone has an idea how I can control the flow of this data, I am becoming more confused with everything I have tried... nothing seems to work correctly. For anyone interested, I have attached the complete code for "testjoin2.php" which is my work in progress, which does not work the way I need it to. Any help will be greatly appreciated - Thanks. testjoin2.php

OK, you can disregard my last post, I had some other coding problems that were preventing the query from working right. Now I have worked past those issues, and I think my main query and sub-query are working correctly, but I'm having a major problem with the data output. You can see what is happening here: http://www.nmlta.org/NewMemDir/testjoin2.php My code is getting a bit complicated, so I am "briefing" it here as much as possible, while still presenting the "logic" here so someone may be able to point out my flaws. Obviously something is not in the right place, but I have tried several different ways, and I can't get it to come out right. The intended results should be, within each row of the table output, the main company, the contact people from the main company, the company branch location, the contact people at the branch. Here are the sections of my code: First section of code – run Main Query and output company data: // MAIN QUERY - Retrieve data from the "company" table $query = "SELECT * FROM company LEFT JOIN contact ON company.company_id = contact.company_id WHERE comp_county = 'BERNALILLO' ORDER BY company.comp_name, contact.cont_rank"; $result = mysql_query($query) or die(mysql_error()); // Set to track the "last" company we processed $lastCompany = ''; // Start building the table for showing results using "while" loop echo "<table border='1' cellpadding='10' cellspacing='0' bgcolor='#f0f0f0' style='margin-left:100px; margin-bottom:20px'>"; // Here is the "while" loop while($row = mysql_fetch_array( $result )) { if ($lastCompany != $row['comp_name']) { // OK we have a new Company to output $lastCompany = $row['comp_name']; # Track the "last" company we processed // Output of company data follows } #ending bracket for $lastCompany "if" statement // This is the end of the Company info // Output of contact data follows Second section of code – run Sub-Query and output branch data: //SUB-QUERY - Retrieve all Branch data from the branch table, matching company_id from MAIN QUERY $query2 = "SELECT * FROM branch LEFT JOIN contact ON branch.branch_id = contact.branch_id WHERE branch.company_id = '".$row['company_id']."' ORDER BY branch.br_street, contact.cont_rank"; $result2 = mysql_query($query2) or die(mysql_error()); // Set value for $lastBranch to track the branch in process $lastBranch = ''; // Start the "while" loop for the sub-query results while($row2 = mysql_fetch_array( $result2 )) { if ($lastBranch != $row2['br_street']) { // OK we have a new Branch to output $lastBranch = $row2['br_street']; # Track the "last" branch we processed // Output of branch data follows //This is the end of the Branch info } #ending bracket for "lastBranch" if statement //Output of branch contact data follows } #ending bracket for second "While" loop } #ending bracket for the first "While" loop // Output of ending cell, row and table tags Like I said earlier in this thread, up until the time I added my Sub-Query everything worked perfectly... and I know the problem is somewhere in my structure, but I just can't seem to figure out how to fix it. I will greatly appreciate any help with this - Thanks!

OK, step 1 is successful... I have the desired results working with my limited sample database, you could view the listing here: http://www.nmlta.org/NewMemDir/testjoin.php Some of the companies in my directory have branch offices, and the idea is to start listing the branch information immediately after the contact people listed for the main company. I have a "branch" table with a "company_id" field that ties the branch to the correct company, and in my "contact" table there is a "branch_id" field to tie the contact person to the branch instead of the company record. So step two is to run a second query to select records from the branch table where the branch.company_id = the company_id of the record selected in my first query. I have used this method of query before, but I think in a previous version of MySQL, and I don't think my syntax for it is quite correct. Here is what I'm trying to use for my second query: SELECT * FROM branch LEFT JOIN contact ON branch.branch_id = contact.branch_id WHERE branch.company_id = '".$row['company_id']."' The last part is a single quote ' followed by double quotes " then a . and then the field reference from my first query, followed by a . then double quote " then single quote ' Like I said, this syntax worked in a previous edition of my directory, and right now it will run without any error, but it does not appear to be working because there is no data returned. I would appreciate any help with this, as I am finding it hard to locate a resource online to show the syntax like I'm trying to use. Thanks.

OK, let me see if I can put this into operation - thank you.

I am working on an online directory for a state organization. Members are companies which have contact individuals, and some have branch locations. I am working on this in steps, so that my questions can be specific. My database has a "company" table with company name, address, etc., and a "contact" table with names, titles and email addresses of the individuals. I have been working with a LEFT JOIN query that selects all companies in a particular county from the "company" table, and then finds matching contacts using the company_id field, which produces the intended results - I get a row for each company with the contact person appended, and where there are more than one contact at a company, each row has duplicate company info. My first question is: as I am looping through my results to list (echo) using this: "while($row = mysql_fetch_array( $result ))" I cannot figure out how to list the company information once, but then list only each contact person's info from the rest of the rows for the same company. If anyone can explain how to do this, I'm sure the same logic will work when I start the code for listing the branch locations. Thanks!

Yes, that was the problem... I needed to go into the contact table and add the company ids - things are working well now, and I think with all this help I can simplify my code and make things work better. I will work with it and if I need any more help I will begin a new thread. Thanks to everyone!

OK, making progress... here is my new query: SELECT * FROM company LEFT JOIN contact ON company.company_id = contact.company_id WHERE comp_county = 'BERNALILLO' This works, even in phpMyAdmin. The fields from the contact table are appended to the rows in the results, but the values for every cell come back as "NULL" - any idea why that would happen? Thanks

It must be something I'm doing wrong, and I would really like to understand this so I can better myself. I could not get the query to run in phpmyadmin no matter what I tried. So I created a "test" php file and uploaded to my server, and when it runs it still gives me a syntax error, which reads as follows: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT JOIN contact ON company.company_id = contact.company_id' at line 1" And here is my test code: <?php // Connection to DB mysql_connect("192.168.2.7", "user", "pwd") or die('Cannot connect to the database because: ' . mysql_error()); mysql_select_db ("nmlta_agents"); // Retrieve all the data from the "company" table $query = mysql_query("SELECT * FROM company WHERE comp_county = 'BERNALILLO' LEFT JOIN contact ON company.company_id = contact.company_id") or die(mysql_error()); $result = mysql_query($query); $num_rows = mysql_num_rows($result); echo "$num_rows Rows\n"; mysql_close(); If you can help me see why this is not working, I would appreciate it. Thanks. ?>

Have studied the tutorial provided by Barand above... and I'm trying to work through this logically so I can understand, and taking one step at a time. I have examined the relationships between my data in the company, branch and contact tables. A company can have many branches, and a branch belongs to only one company (one to many), so I have added the "company_id" field to the contact table. So then my first test is to run a query to SELECT * FROM company WHERE comp_county = 'BERNALILLO' (most of the queries will first want to locate all companies in a particular county, then I added this:) LEFT JOIN contact ON company_id I am trying to run the query in phpmyadmin - should this be OK? I get a syntax error, and so I've tried: LEFT JOIN contact ON company_id=company_id, and I've also tried USING instead of ON. I can't get past the syntax errors, and I've really studied the examples. What am I missing? Any help will be appreciated, so I can begin to understand the JOIN usage. Thanks.

Thanks Psycho, I can understand what you're saying. I will work on some queries and see what I can do. I'll be back if I can't figure it out.

That works - I will definitely check this out!

Apparently I do not have the necessary permissions to view the tutorial, is there a subscription? I will look into SQL JOINS, and thanks for that help.

Well, now I'm really going to sound ignorant. That is the initial way I had set up the database, with the exact three tables you have suggested, but I can't wrap my brain around how to pull all of the information in a listing with one query. I had used (and it does work) four "nested" queries - first selecting from the company table, then selecting from the contact table all matching entries, then selecting from the branch table all matching entries, and then another selection from the contact table any that match the branch. Is there another way to do a query that will pull everything from all tables at once? Again I appreciate any advice... Thanks!

I got to thinking - there are a lot of intelligent people who post to these forums, and maybe someone has a better idea for my bigger project that is going on here. Getting the INSERT query to work is only one small part of the picture. I am working on an improved online directory for an organization. The members are companies, who also list the important contact persons at their offices. A few of the companies also have branch office locations, with different contact people, etc. It is important that the data retrieved from a member search is formatted in a particular way... my current script works for that, and you can see a small sample of the data in my "working" model here: http://www.nmlta.org/NewMemDir/bern_member_list.php My database table has to be structured to allow for the largest companies, with the most branches and the most contact people... so right now I have room for 10 main contacts with job title and email addresses, as well as 5 branch companies, each with room for three separate contact people. My code in order to get the format shown in the sample link above is quite cumbersome, with a lot of row checking to see if there is data in the cell, and if not, it passes to the next line. It works, but I'm just wondering if there is a better way to do this. I will attach my php code if someone is interested in looking at it and giving me a better direction. My ultimate goal is even more complicated, as I would like to be able to export the company data to some sort of Word document for using in a printed directory. With my current format, I don't know how to get to a point where I could use multiple columns in a printed document, since the listing now is quite narrow. The companies need to be sorted out by County location, but I have figured out how to do that, I'm just kind of stuck on formatting. I am surprised that I cannot find a few ready-made programs to handle this type of project, but so far I have not. If anyone has some ideas about how to best handle these tasks, I would appreciate any help. Thanks- bern_member_list.php