Problem with PHP query using multiple tables

[chumlyumly]

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:

<?php
$varspecialty_rsmembersearchresultsm = "%";
if (isset($_POST['mbrsearchspecialty'])) {
  $varspecialty_rsmembersearchresultsm = $_POST['mbrsearchspecialty'];
}
$varfirstname_rsmembersearchresultsm = "%";
if (isset($_POST['mbrsearchfname'])) {
  $varfirstname_rsmembersearchresultsm = $_POST['mbrsearchfname'];
}
$varmbrlname_rsmembersearchresultsm = "%";
if (isset($_POST['mbrsearchlname'])) {
  $varmbrlname_rsmembersearchresultsm = $_POST['mbrsearchlname'];
}
$varmbrfirm_rsmembersearchresultsm = "%";
if (isset($_POST['mbrsearchfirm'])) {
  $varmbrfirm_rsmembersearchresultsm = $_POST['mbrsearchfirm'];
}
$varmedia_rsmembersearchresultsm = "%";
if (isset($_POST['mbrsearchmedia'])) {
  $varmedia_rsmembersearchresultsm = $_POST['mbrsearchmedia'];
}
$varfirstname_rsmembersearchresultsm = "%";
if (isset($_POST['mbrsearchfname'])) {
  $varfirstname_rsmembersearchresultsm = (get_magic_quotes_gpc()) ? $_POST['mbrsearchfname'] : addslashes($_POST['mbrsearchfname']);
}
$varmbrlname_rsmembersearchresultsm = "%";
if (isset($_POST['mbrsearchlname'])) {
  $varmbrlname_rsmembersearchresultsm = (get_magic_quotes_gpc()) ? $_POST['mbrsearchlname'] : addslashes($_POST['mbrsearchlname']);
}
$varmbrfirm_rsmembersearchresultsm = "%";
if (isset($_POST['mbrsearchfirm'])) {
  $varmbrfirm_rsmembersearchresultsm = (get_magic_quotes_gpc()) ? $_POST['mbrsearchfirm'] : addslashes($_POST['mbrsearchfirm']);
}
$varmedia_rsmembersearchresultsm = "%";
if (isset($_POST['mbrsearchmedia'])) {
  $varmedia_rsmembersearchresultsm = (get_magic_quotes_gpc()) ? $_POST['mbrsearchmedia'] : addslashes($_POST['mbrsearchmedia']);
}
$varspecialty_rsmembersearchresultsm = "%";
if (isset($_POST['mbrsearchspecialty'])) {
  $varspecialty_rsmembersearchresultsm = (get_magic_quotes_gpc()) ? $_POST['mbrsearchspecialty'] : addslashes($_POST['mbrsearchspecialty']);
}
mysql_select_db($database_connection, $connection);
$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);
$rsmembersearchresultsm = mysql_query($query_rsmembersearchresultsm, $connection) or die(mysql_error());
$row_rsmembersearchresultsm = mysql_fetch_assoc($rsmembersearchresultsm);
$totalRows_rsmembersearchresultsm = mysql_num_rows($rsmembersearchresultsm);
?>
 

Thank you in advance for taking the time to help.

Best,

Charity

[pbmods]

Heya, Charity. Welcome to TSDN!

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%%' -- valid - sprintf()
        AND
            `member`.`lastname` LIKE '%%%s%%'
        AND
            `member`.`businessname` LIKE '%%%s%%'
        AND
            `member`.`media_contact` = '%s'
        AND
            `specialty_group`.`specialty` = '%s'
    )
 

I see nothing immediately wrong with the structure of your query,

Try adding these lines right after you create the query:

$query_rsmembersearchresultsm = "SELECT ... ";
 
echo $query_rsmembersearchresultsm, '<br /><br />';
 

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.

[chumlyumly]

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."

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%%' -- valid - sprintf()
        AND
            `member`.`lastname` LIKE '%%%s%%'
        AND
            `member`.`businessname` LIKE '%%%s%%'
        AND
            `member`.`media_contact` = '%s'
        AND
            `specialty_group`.`specialty` = '%s'
    )
 

I'm not sure where exactly I should put the code below:

Try adding these lines right after you create the query:

$query_rsmembersearchresultsm = "SELECT ... ";
 
echo $query_rsmembersearchresultsm, '<br /><br />';
 

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

[chumlyumly]

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:

<?php
$varspecialty_rsmembersearchresultsm = "%";
if (isset($_POST['mbrsearchspecialty'])) {
  $varspecialty_rsmembersearchresultsm = $_POST['mbrsearchspecialty'];
}
$varfirstname_rsmembersearchresultsm = "%";
if (isset($_POST['mbrsearchfname'])) {
  $varfirstname_rsmembersearchresultsm = $_POST['mbrsearchfname'];
}
$varmbrlname_rsmembersearchresultsm = "%";
if (isset($_POST['mbrsearchlname'])) {
  $varmbrlname_rsmembersearchresultsm = $_POST['mbrsearchlname'];
}
$varmbrfirm_rsmembersearchresultsm = "%";
if (isset($_POST['mbrsearchfirm'])) {
  $varmbrfirm_rsmembersearchresultsm = $_POST['mbrsearchfirm'];
}
$varmedia_rsmembersearchresultsm = "%";
if (isset($_POST['mbrsearchmedia'])) {
  $varmedia_rsmembersearchresultsm = $_POST['mbrsearchmedia'];
}
$varfirstname_rsmembersearchresultsm = "%";
if (isset($_POST['mbrsearchfname'])) {
  $varfirstname_rsmembersearchresultsm = (get_magic_quotes_gpc()) ? $_POST['mbrsearchfname'] : addslashes($_POST['mbrsearchfname']);
}
$varmbrlname_rsmembersearchresultsm = "%";
if (isset($_POST['mbrsearchlname'])) {
  $varmbrlname_rsmembersearchresultsm = (get_magic_quotes_gpc()) ? $_POST['mbrsearchlname'] : addslashes($_POST['mbrsearchlname']);
}
$varmbrfirm_rsmembersearchresultsm = "%";
if (isset($_POST['mbrsearchfirm'])) {
  $varmbrfirm_rsmembersearchresultsm = (get_magic_quotes_gpc()) ? $_POST['mbrsearchfirm'] : addslashes($_POST['mbrsearchfirm']);
}
$varmedia_rsmembersearchresultsm = "%";
if (isset($_POST['mbrsearchmedia'])) {
  $varmedia_rsmembersearchresultsm = (get_magic_quotes_gpc()) ? $_POST['mbrsearchmedia'] : addslashes($_POST['mbrsearchmedia']);
}
$varspecialty_rsmembersearchresultsm = "%";
if (isset($_POST['mbrsearchspecialty'])) {
  $varspecialty_rsmembersearchresultsm = (get_magic_quotes_gpc()) ? $_POST['mbrsearchspecialty'] : addslashes($_POST['mbrsearchspecialty']);
}
mysql_select_db($database_connection, $connection);
$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);
$rsmembersearchresultsm = mysql_query($query_rsmembersearchresultsm, $connection) or die(mysql_error());
$row_rsmembersearchresultsm = mysql_fetch_assoc($rsmembersearchresultsm);
$totalRows_rsmembersearchresultsm = mysql_num_rows($rsmembersearchresultsm);
 ?>
 

Any ideas? I'd be ever so grateful.

Thanks! Charity

[pbmods]

Heya, Charity.

You have this in your WHERE clause:

specialty_group.specialty LIKE '%%%s%%'
 

Which will automatically reject any rows where 'specialty_group'.'specialty' is null.

To fix this, you might want to change your query slightly:

(specialty_group.specialty LIKE '%%%s%%' OR specialty_group.specialty IS NULL)
 

[chumlyumly]

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:

<?php
$varspecialty_rsmembersearchresultsm = "%";
if (isset($_POST['mbrsearchspecialty'])) {
  $varspecialty_rsmembersearchresultsm = $_POST['mbrsearchspecialty'];
}
$varfirstname_rsmembersearchresultsm = "%";
if (isset($_POST['mbrsearchfname'])) {
  $varfirstname_rsmembersearchresultsm = $_POST['mbrsearchfname'];
}
$varmbrlname_rsmembersearchresultsm = "%";
if (isset($_POST['mbrsearchlname'])) {
  $varmbrlname_rsmembersearchresultsm = $_POST['mbrsearchlname'];
}
$varmbrfirm_rsmembersearchresultsm = "%";
if (isset($_POST['mbrsearchfirm'])) {
  $varmbrfirm_rsmembersearchresultsm = $_POST['mbrsearchfirm'];
}
$varmedia_rsmembersearchresultsm = "%";
if (isset($_POST['mbrsearchmedia'])) {
  $varmedia_rsmembersearchresultsm = $_POST['mbrsearchmedia'];
}
$varfirstname_rsmembersearchresultsm = "%";
if (isset($_POST['mbrsearchfname'])) {
  $varfirstname_rsmembersearchresultsm = (get_magic_quotes_gpc()) ? $_POST['mbrsearchfname'] : addslashes($_POST['mbrsearchfname']);
}
$varmbrlname_rsmembersearchresultsm = "%";
if (isset($_POST['mbrsearchlname'])) {
  $varmbrlname_rsmembersearchresultsm = (get_magic_quotes_gpc()) ? $_POST['mbrsearchlname'] : addslashes($_POST['mbrsearchlname']);
}
$varmbrfirm_rsmembersearchresultsm = "%";
if (isset($_POST['mbrsearchfirm'])) {
  $varmbrfirm_rsmembersearchresultsm = (get_magic_quotes_gpc()) ? $_POST['mbrsearchfirm'] : addslashes($_POST['mbrsearchfirm']);
}
$varmedia_rsmembersearchresultsm = "%";
if (isset($_POST['mbrsearchmedia'])) {
  $varmedia_rsmembersearchresultsm = (get_magic_quotes_gpc()) ? $_POST['mbrsearchmedia'] : addslashes($_POST['mbrsearchmedia']);
}
$varspecialty_rsmembersearchresultsm = "%";
if (isset($_POST['mbrsearchspecialty'])) {
  $varspecialty_rsmembersearchresultsm = (get_magic_quotes_gpc()) ? $_POST['mbrsearchspecialty'] : addslashes($_POST['mbrsearchspecialty']);
}
mysql_select_db($database_connection, $connection);
$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);
$rsmembersearchresultsm = mysql_query($query_rsmembersearchresultsm, $connection) or die(mysql_error());
$row_rsmembersearchresultsm = mysql_fetch_assoc($rsmembersearchresultsm);
$totalRows_rsmembersearchresultsm = mysql_num_rows($rsmembersearchresultsm);
 ?>
 

Any ideas or suggestions are appreciated!

[pbmods]

Heya, Charity.

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

[chumlyumly]

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!

[pbmods]

Heya, Charity.

What does your query look like now?

[surabisantosh]

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...???