MySQL PHP Left join help!!

[benrob82]

Hi can anyone help me with this query

$getcampaign = mysql_query("select * from prospect_list_campaigns");
while ($gotcampaign = mysql_fetch_row($getcampaign))
{
    $getprospect = mysql_query("select id,contact_id from prospect_lists_prospects where prospect_list_id = '$gotcampaign[2]'");
    while ($gotprospect = mysql_fetch_row($getprospect))
    {
        $getcontacts = mysql_query("select id,first_name,last_name from contacts where contacts.id = '$gotprospect[1]' AND deleted = 0 order by last_name desc");
        while ($gotcontacts = mysql_fetch_row($getcontacts))
        {        
            $getcocontacts = mysql_query("select id,account_id from accounts_contacts where contact_id = '$gotprospect[1]'");
            while ($gotcocontacts = mysql_fetch_row($getcocontacts))
            {            
                $getcompany = mysql_query("select id,name from accounts where accounts.id = '$gotcocontacts[1]' AND deleted = 0 group by accounts.name order by accounts.name asc");
                while ($gotcompany = mysql_fetch_row($getcompany))
                {
                    $count++;
                    echo "$gotcompany[1]<br>";
                }        
            }
        }
    }
}

I need to get this as a join sql query but I cant get my head around it.

Please.

 

[jpadie]

two thoughts:

1. you might be better off asking this question in the mysql forum.

2. the way i get complex queries done most of the time is to use the MS access query builder on tables linked through to the mysql database via odbc. once the query returns what you want you just select the SQL view and cut and paste.

 

[benrob82]

I've tried on the mysql forum but no one seemed to have an answer with the PHP included so I thought I'd give it a try here.

I'll give the second option a try.

 

[jpadie]

i have noticed that you get better results in the mysql forum if you quote your table structure and the structure of the output you want (rather than an abstracted php code).

 

[mrsbean]

Yes, you're in the wrong forum, but as a tip, I use MS Access to create my queries for MySQL. You can use the odbc connection to MySQL and create the queries visually (join the tables, select the columns, etc.). Then you just grab the SQL from the SQL view and dump the code into your PHP.

MrsBean