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
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
