By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,758 Members | 1,272 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,758 IT Pros & Developers. It's quick & easy.

Problem with PHP query using multiple tables

chumlyumly
P: 9
Hello -

Please help! I'm creating search pages using Dreamweaver 8, PHP, and MySQL database with Mac OS X. I think my code is good, but when I run my query, I come up with no data, even though I know there is data in the database that should be coming up.

I'm searching two tables that are linked with a foreign key (member_id). The members table is the main table (where "member_id" is an auto increment key) , and the specialty_groups table uses "member_id" to reference which member has what specialty.

I've created a LEFT JOIN with USING (member_id). I've tried modifying the code by hand several times, and still come up with "no data."

My code (as created by Dreamweaver 8) is below:

Expand|Select|Wrap|Line Numbers
  1. <?php
  2. $varspecialty_rsmembersearchresultsm = "%";
  3. if (isset($_POST['mbrsearchspecialty'])) {
  4.   $varspecialty_rsmembersearchresultsm = $_POST['mbrsearchspecialty'];
  5. }
  6. $varfirstname_rsmembersearchresultsm = "%";
  7. if (isset($_POST['mbrsearchfname'])) {
  8.   $varfirstname_rsmembersearchresultsm = $_POST['mbrsearchfname'];
  9. }
  10. $varmbrlname_rsmembersearchresultsm = "%";
  11. if (isset($_POST['mbrsearchlname'])) {
  12.   $varmbrlname_rsmembersearchresultsm = $_POST['mbrsearchlname'];
  13. }
  14. $varmbrfirm_rsmembersearchresultsm = "%";
  15. if (isset($_POST['mbrsearchfirm'])) {
  16.   $varmbrfirm_rsmembersearchresultsm = $_POST['mbrsearchfirm'];
  17. }
  18. $varmedia_rsmembersearchresultsm = "%";
  19. if (isset($_POST['mbrsearchmedia'])) {
  20.   $varmedia_rsmembersearchresultsm = $_POST['mbrsearchmedia'];
  21. }
  22. $varfirstname_rsmembersearchresultsm = "%";
  23. if (isset($_POST['mbrsearchfname'])) {
  24.   $varfirstname_rsmembersearchresultsm = (get_magic_quotes_gpc()) ? $_POST['mbrsearchfname'] : addslashes($_POST['mbrsearchfname']);
  25. }
  26. $varmbrlname_rsmembersearchresultsm = "%";
  27. if (isset($_POST['mbrsearchlname'])) {
  28.   $varmbrlname_rsmembersearchresultsm = (get_magic_quotes_gpc()) ? $_POST['mbrsearchlname'] : addslashes($_POST['mbrsearchlname']);
  29. }
  30. $varmbrfirm_rsmembersearchresultsm = "%";
  31. if (isset($_POST['mbrsearchfirm'])) {
  32.   $varmbrfirm_rsmembersearchresultsm = (get_magic_quotes_gpc()) ? $_POST['mbrsearchfirm'] : addslashes($_POST['mbrsearchfirm']);
  33. }
  34. $varmedia_rsmembersearchresultsm = "%";
  35. if (isset($_POST['mbrsearchmedia'])) {
  36.   $varmedia_rsmembersearchresultsm = (get_magic_quotes_gpc()) ? $_POST['mbrsearchmedia'] : addslashes($_POST['mbrsearchmedia']);
  37. }
  38. $varspecialty_rsmembersearchresultsm = "%";
  39. if (isset($_POST['mbrsearchspecialty'])) {
  40.   $varspecialty_rsmembersearchresultsm = (get_magic_quotes_gpc()) ? $_POST['mbrsearchspecialty'] : addslashes($_POST['mbrsearchspecialty']);
  41. }
  42. mysql_select_db($database_connection, $connection);
  43. $query_rsmembersearchresultsm = sprintf("SELECT member.member_id, member.firstname, member.lastname, member.businessname, specialty_group.member_id FROM member LEFT JOIN specialty_group  USING (member_id) WHERE member.firstname LIKE '%%%s%%' AND member.lastname LIKE '%%%s%%' AND member.businessname LIKE '%%%s%%' AND member.media_contact = '%s' AND specialty_group.specialty = '%s'", $varfirstname_rsmembersearchresultsm,$varmbrlname_rsmembersearchresultsm,$varmbrfirm_rsmembersearchresultsm,$varmedia_rsmembersearchresultsm,$varspecialty_rsmembersearchresultsm);
  44. $rsmembersearchresultsm = mysql_query($query_rsmembersearchresultsm, $connection) or die(mysql_error());
  45. $row_rsmembersearchresultsm = mysql_fetch_assoc($rsmembersearchresultsm);
  46. $totalRows_rsmembersearchresultsm = mysql_num_rows($rsmembersearchresultsm);
  47. ?>
  48.  
Thank you in advance for taking the time to help.

Best,

Charity
Aug 15 '07 #1
Share this Question
Share on Google+
9 Replies


pbmods
Expert 5K+
P: 5,821
Heya, Charity. Welcome to TSDN!

Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.         `member`.`member_id`,
  3.         `member`.`firstname`,
  4.         `member`.`lastname`,
  5.         `member`.`businessname`,
  6.         `specialty_group`.`member_id`
  7.     FROM
  8.     (
  9.             `member`
  10.         LEFT JOIN
  11.             `specialty_group`
  12.                 USING
  13.                     (`member_id`)
  14.     )
  15.     WHERE
  16.     (
  17.             `member`.`firstname` LIKE '%%%s%%' -- valid - sprintf()
  18.         AND
  19.             `member`.`lastname` LIKE '%%%s%%'
  20.         AND
  21.             `member`.`businessname` LIKE '%%%s%%'
  22.         AND
  23.             `member`.`media_contact` = '%s'
  24.         AND
  25.             `specialty_group`.`specialty` = '%s'
  26.     )
  27.  
I see nothing immediately wrong with the structure of your query,

Try adding these lines right after you create the query:
Expand|Select|Wrap|Line Numbers
  1. $query_rsmembersearchresultsm = "SELECT ... ";
  2.  
  3. echo $query_rsmembersearchresultsm, '<br /><br />';
  4.  
Does the query look right, or are there any missing fields? Try dropping that query into your MySQL client app and see if it is getting results.
Aug 15 '07 #2

chumlyumly
P: 9
Hello and thank you for responding.

I copied the text below and entered it directly into my MySQL client admin and got the response "empty recordset."

Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.         `member`.`member_id`,
  3.         `member`.`firstname`,
  4.         `member`.`lastname`,
  5.         `member`.`businessname`,
  6.         `specialty_group`.`member_id`
  7.     FROM
  8.     (
  9.             `member`
  10.         LEFT JOIN
  11.             `specialty_group`
  12.                 USING
  13.                     (`member_id`)
  14.     )
  15.     WHERE
  16.     (
  17.             `member`.`firstname` LIKE '%%%s%%' -- valid - sprintf()
  18.         AND
  19.             `member`.`lastname` LIKE '%%%s%%'
  20.         AND
  21.             `member`.`businessname` LIKE '%%%s%%'
  22.         AND
  23.             `member`.`media_contact` = '%s'
  24.         AND
  25.             `specialty_group`.`specialty` = '%s'
  26.     )
  27.  
I'm not sure where exactly I should put the code below:

Try adding these lines right after you create the query:
Expand|Select|Wrap|Line Numbers
  1. $query_rsmembersearchresultsm = "SELECT ... ";
  2.  
  3. echo $query_rsmembersearchresultsm, '<br /><br />';
  4.  
Should I put the code into my .php page after the code I already have, or elsewhere? Sorry - I'm confused! When I put it in my page as it is now after the code I have, the top of the page (as posted) says, "SELECT ..."

Thanks again!

Charity
Aug 15 '07 #3

chumlyumly
P: 9
Hello - I'm back with an edit to my original post.

So I've gotten the query to work by replacing all "="s with "LIKE." However, I'm now running into another problem.

I'm using LEFT JOIN to join my 'members' table and my 'specialty_groups' table (USING member_id). This is supposed to return all rows from the first table ('members'), whether or not there's a match in the second table ('specialty_groups').

Instead, what I'm getting is only rows that are in the 'specialty_groups' table.

I've tried replacing LEFT JOIN with RIGHT JOIN to see if that makes a difference, and it doesn't. I still only get records that have a match on the 'specialty_groups' table.

My code is below:
Expand|Select|Wrap|Line Numbers
  1. <?php
  2. $varspecialty_rsmembersearchresultsm = "%";
  3. if (isset($_POST['mbrsearchspecialty'])) {
  4.   $varspecialty_rsmembersearchresultsm = $_POST['mbrsearchspecialty'];
  5. }
  6. $varfirstname_rsmembersearchresultsm = "%";
  7. if (isset($_POST['mbrsearchfname'])) {
  8.   $varfirstname_rsmembersearchresultsm = $_POST['mbrsearchfname'];
  9. }
  10. $varmbrlname_rsmembersearchresultsm = "%";
  11. if (isset($_POST['mbrsearchlname'])) {
  12.   $varmbrlname_rsmembersearchresultsm = $_POST['mbrsearchlname'];
  13. }
  14. $varmbrfirm_rsmembersearchresultsm = "%";
  15. if (isset($_POST['mbrsearchfirm'])) {
  16.   $varmbrfirm_rsmembersearchresultsm = $_POST['mbrsearchfirm'];
  17. }
  18. $varmedia_rsmembersearchresultsm = "%";
  19. if (isset($_POST['mbrsearchmedia'])) {
  20.   $varmedia_rsmembersearchresultsm = $_POST['mbrsearchmedia'];
  21. }
  22. $varfirstname_rsmembersearchresultsm = "%";
  23. if (isset($_POST['mbrsearchfname'])) {
  24.   $varfirstname_rsmembersearchresultsm = (get_magic_quotes_gpc()) ? $_POST['mbrsearchfname'] : addslashes($_POST['mbrsearchfname']);
  25. }
  26. $varmbrlname_rsmembersearchresultsm = "%";
  27. if (isset($_POST['mbrsearchlname'])) {
  28.   $varmbrlname_rsmembersearchresultsm = (get_magic_quotes_gpc()) ? $_POST['mbrsearchlname'] : addslashes($_POST['mbrsearchlname']);
  29. }
  30. $varmbrfirm_rsmembersearchresultsm = "%";
  31. if (isset($_POST['mbrsearchfirm'])) {
  32.   $varmbrfirm_rsmembersearchresultsm = (get_magic_quotes_gpc()) ? $_POST['mbrsearchfirm'] : addslashes($_POST['mbrsearchfirm']);
  33. }
  34. $varmedia_rsmembersearchresultsm = "%";
  35. if (isset($_POST['mbrsearchmedia'])) {
  36.   $varmedia_rsmembersearchresultsm = (get_magic_quotes_gpc()) ? $_POST['mbrsearchmedia'] : addslashes($_POST['mbrsearchmedia']);
  37. }
  38. $varspecialty_rsmembersearchresultsm = "%";
  39. if (isset($_POST['mbrsearchspecialty'])) {
  40.   $varspecialty_rsmembersearchresultsm = (get_magic_quotes_gpc()) ? $_POST['mbrsearchspecialty'] : addslashes($_POST['mbrsearchspecialty']);
  41. }
  42. mysql_select_db($database_connection, $connection);
  43. $query_rsmembersearchresultsm = sprintf("SELECT member.member_id, member.firstname, member.lastname, member.businessname, specialty_group.member_id FROM member LEFT JOIN specialty_group  ON member.member_id = specialty_group.member_id WHERE member.firstname LIKE '%%%s%%' AND member.lastname LIKE '%%%s%%' AND member.businessname LIKE '%%%s%%' AND member.media_contact LIKE '%%%s%%' AND specialty_group.specialty LIKE '%%%s%%' GROUP BY member.member_id", $varfirstname_rsmembersearchresultsm,$varmbrlname_rsmembersearchresultsm,$varmbrfirm_rsmembersearchresultsm,$varmedia_rsmembersearchresultsm,$varspecialty_rsmembersearchresultsm);
  44. $rsmembersearchresultsm = mysql_query($query_rsmembersearchresultsm, $connection) or die(mysql_error());
  45. $row_rsmembersearchresultsm = mysql_fetch_assoc($rsmembersearchresultsm);
  46. $totalRows_rsmembersearchresultsm = mysql_num_rows($rsmembersearchresultsm);
  47.  ?>
  48.  
Any ideas? I'd be ever so grateful.

Thanks! Charity
Aug 18 '07 #4

pbmods
Expert 5K+
P: 5,821
Heya, Charity.

You have this in your WHERE clause:
Expand|Select|Wrap|Line Numbers
  1. specialty_group.specialty LIKE '%%%s%%'
  2.  
Which will automatically reject any rows where 'specialty_group'.'specialty' is null.

To fix this, you might want to change your query slightly:
Expand|Select|Wrap|Line Numbers
  1. (specialty_group.specialty LIKE '%%%s%%' OR specialty_group.specialty IS NULL)
  2.  
Aug 18 '07 #5

chumlyumly
P: 9
Thank you!!

You totally rock. That solved it.

Thank you thank you thank you.

Now another question - completely related. This query is on a page that lists the search results as the member's first name, last name, and business name. People can then click on the member's first name to see their entire record (address, phone numbers, etc.).

I'm running the query from two tables - the parent table is the 'member' table, and the child table is the 'specialty_groups' table.

Whenever I run a search for a member that has specialties (which are a search parameter and are housed in the specialty_groups table), their entire record comes up. That's great.

However, when I search for a member that doesn't have any specialties in the specialty_groups table, their member_id (i.e. the 'recordID') is not passed on to the page that gives all of that member's details. The link looks like this:

http://www.aialosangeles.org/databas....php?recordID=

The new code as I have it is now this:

Expand|Select|Wrap|Line Numbers
  1. <?php
  2. $varspecialty_rsmembersearchresultsm = "%";
  3. if (isset($_POST['mbrsearchspecialty'])) {
  4.   $varspecialty_rsmembersearchresultsm = $_POST['mbrsearchspecialty'];
  5. }
  6. $varfirstname_rsmembersearchresultsm = "%";
  7. if (isset($_POST['mbrsearchfname'])) {
  8.   $varfirstname_rsmembersearchresultsm = $_POST['mbrsearchfname'];
  9. }
  10. $varmbrlname_rsmembersearchresultsm = "%";
  11. if (isset($_POST['mbrsearchlname'])) {
  12.   $varmbrlname_rsmembersearchresultsm = $_POST['mbrsearchlname'];
  13. }
  14. $varmbrfirm_rsmembersearchresultsm = "%";
  15. if (isset($_POST['mbrsearchfirm'])) {
  16.   $varmbrfirm_rsmembersearchresultsm = $_POST['mbrsearchfirm'];
  17. }
  18. $varmedia_rsmembersearchresultsm = "%";
  19. if (isset($_POST['mbrsearchmedia'])) {
  20.   $varmedia_rsmembersearchresultsm = $_POST['mbrsearchmedia'];
  21. }
  22. $varfirstname_rsmembersearchresultsm = "%";
  23. if (isset($_POST['mbrsearchfname'])) {
  24.   $varfirstname_rsmembersearchresultsm = (get_magic_quotes_gpc()) ? $_POST['mbrsearchfname'] : addslashes($_POST['mbrsearchfname']);
  25. }
  26. $varmbrlname_rsmembersearchresultsm = "%";
  27. if (isset($_POST['mbrsearchlname'])) {
  28.   $varmbrlname_rsmembersearchresultsm = (get_magic_quotes_gpc()) ? $_POST['mbrsearchlname'] : addslashes($_POST['mbrsearchlname']);
  29. }
  30. $varmbrfirm_rsmembersearchresultsm = "%";
  31. if (isset($_POST['mbrsearchfirm'])) {
  32.   $varmbrfirm_rsmembersearchresultsm = (get_magic_quotes_gpc()) ? $_POST['mbrsearchfirm'] : addslashes($_POST['mbrsearchfirm']);
  33. }
  34. $varmedia_rsmembersearchresultsm = "%";
  35. if (isset($_POST['mbrsearchmedia'])) {
  36.   $varmedia_rsmembersearchresultsm = (get_magic_quotes_gpc()) ? $_POST['mbrsearchmedia'] : addslashes($_POST['mbrsearchmedia']);
  37. }
  38. $varspecialty_rsmembersearchresultsm = "%";
  39. if (isset($_POST['mbrsearchspecialty'])) {
  40.   $varspecialty_rsmembersearchresultsm = (get_magic_quotes_gpc()) ? $_POST['mbrsearchspecialty'] : addslashes($_POST['mbrsearchspecialty']);
  41. }
  42. mysql_select_db($database_connection, $connection);
  43. $query_rsmembersearchresultsm = sprintf("SELECT member.member_id, member.firstname, member.lastname, member.businessname, specialty_group.member_id FROM member LEFT JOIN specialty_group  ON member.member_id = specialty_group.member_id WHERE member.firstname LIKE '%%%s%%' AND member.lastname LIKE '%%%s%%' AND member.businessname LIKE '%%%s%%' AND (member.media_contact LIKE '%%%s%%' OR member.media_contact IS NULL) AND (specialty_group.specialty LIKE '%%%s%%' OR specialty_group.specialty IS NULL) GROUP BY member.member_id", $varfirstname_rsmembersearchresultsm,$varmbrlname_rsmembersearchresultsm,$varmbrfirm_rsmembersearchresultsm,$varmedia_rsmembersearchresultsm,$varspecialty_rsmembersearchresultsm);
  44. $rsmembersearchresultsm = mysql_query($query_rsmembersearchresultsm, $connection) or die(mysql_error());
  45. $row_rsmembersearchresultsm = mysql_fetch_assoc($rsmembersearchresultsm);
  46. $totalRows_rsmembersearchresultsm = mysql_num_rows($rsmembersearchresultsm);
  47.  ?>
  48.  
Any ideas or suggestions are appreciated!
Aug 18 '07 #6

pbmods
Expert 5K+
P: 5,821
Heya, Charity.

Glad to hear you got it working! Good luck with your project, and if you ever need anything, post back anytime :)
Aug 18 '07 #7

chumlyumly
P: 9
An addendum to my previous edited post:

When I create my query in my recordset in Dreamweaver and run a test, records are shown; however, under the columns 'member.member_id' and 'specialty_group.member_id', there is no information...unless the record has rows in the 'specialty_group' table.

If the person doesn't have any specialties listed in the 'specialty_group' table, then their member.member_id doesn't show up!

Why, why, why??

Thanks for your help!
Aug 19 '07 #8

pbmods
Expert 5K+
P: 5,821
Heya, Charity.

What does your query look like now?
Aug 19 '07 #9

P: 1
If the person doesn't have any specialties listed in the 'specialty_group' table, then their member.member_id doesn't show up!
I am facing the similar issue, did you find out the solution for this...???
Sep 22 '07 #10

Post your reply

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