Re: one click - two actions?

On Nov 13, 2006, at 9:44 PM, Paul Novitski wrote: Paul, Thank you again for your amazingly generous email. I am working on my query as you suggested. I have a joint query that seems to work except that I get the name of the company repeated for each job. Could you please help me figure out how to make it echo the company once and list all the jobs next to it on the same line and start a new line with the next company like you suggested bellow? Here is the code I have now: $query = "SELECT client.companyName, job.jobType, job.pix, job.info, job.url

        	FROM client, job
        	WHERE client.companyId=job.companyId
        	AND client.view='yes'
        	order by client.companyName";

$result = mysql_query($query) or die ("Couldn't execute query"); while ($aaa = mysql_fetch_array($result,MYSQL_ASSOC)) { echo "<span class='navCompany'>{$aaa['companyName']}</span><span class='navArrow'> > </span><span class='navText'><a href='single_page_t10.php?art=".$aaa['pix']."'>{$aaa['jobType']}</a></span><br>\n"; }

I think what you're looking for is JOIN syntax for your queries: http://dev.mysql.com/doc/refman/4.1/en/join.html For example:

        SELECT * FROM client, job
        WHERE client.companyId=job.companyId
        AND (client.status='active' or client.status='old')
        order by client.companyName;

(Note that when you extract fields from more than one table like this, you identify the table that each field belongs to, e.g. client.companyId.) Then you can extract the desired fields from both tables in the same loop because they've been captured together. Your current logic executes a job query for every row of client, which is extremely inefficient. The dataset produced by the join query is going to look something like this:

client.         job.
companyId       companyId
    1           2
    1           3
    1           9
    2           4
    2           5

... In other words, there will be one row for each job record, with the (parent) client fields duplicated each row. You can further improve the efficiency of your query by naming only the fields you need, instead of using * to extract all fields:

        SELECT client.companyName, job.pix, job.jobType, job.url, job.web
        FROM client, job
        WHERE client.companyId=job.companyId
        AND (client.status='active' or client.status='old')
        order by client.companyName;

Once you execute the join query, your PHP loop can cycle in a similar way, echoing a company name and then listing all the job types until a new company name occurs, etc. You've got other problems, however. If you look at your HTML source, you'll see markup like this: <span class='navCompany'>Builtworks</span><span class='navArrow'> </span> <span class='navText'><a href='single_page.php?art=btw_logo.jpg'>logo</a></span> <span class='navText'><a href='single_page.php?art='></a></span> <span class='navText'><a href='single_page.php?art='></a></span> <span class='navText'><a href='single_page.php?art='></a></span> <span class='navText'><a href='single_page.php?art='></a></span> <br><span class='navCompany'>Citizens Bank / eProperty</span><span class='navArrow'> > </span> <span class='navText'><a href='single_page.php?art=ctz_web1.jpg'>website</a></span> All those empty hyperlinks aren't doing anything but making your download heavier than it has to be. I think you need to test your 'jobType' fields and output only those that aren't blank. Good luck, Paul --PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php