Newbie Help

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Snoegoer
Forum Newbie
Posts: 3
Joined: Tue Jun 03, 2008 2:23 pm

Newbie Help

Post by Snoegoer »

I have the peice of code below that returns what I am looking for. However there are 98 different prod_name entries I need to do this for. Is there any way to query by prod_type and output all the co_names for each prod_name entry?

Thank you in advance for any assistance!

Code: Select all

 
<?php
include("adminincludes/adminphpheaders_inc.php");
 
//open DB
    include("../includes/opendb_inc.php");
    $strSQL="SELECT * FROM products INNER JOIN companies USING (co_uid) WHERE prod_type='Gifts Novelties & Stationery' AND prod_name='Bed & Bath Sets & Blankets' ORDER BY co_name";
    $result=mysql_query($strSQL) or die(mysql_error());
    $strXML="<bedbathblankets>\n";
    while ($row=mysql_fetch_array($result))
        {
        $strXML.="\t<co_name>".$row["co_name"]."</co_name>\n";
        }
    $strXML.="</bedbathblankets>\n";
?>
 
Last edited by Weirdan on Tue Jun 03, 2008 4:49 pm, edited 1 time in total.
Reason: php tags
User avatar
deejay
Forum Contributor
Posts: 201
Joined: Wed Jan 22, 2003 3:33 am
Location: Cornwall

Re: Newbie Help

Post by deejay »

i think what your needing to do is make the mysql query looser, as in after the WHERE you don't keep your search as tight. In fact if you leave out the WHERE altogether then it should list all your product rows.

Hope that helps
Snoegoer
Forum Newbie
Posts: 3
Joined: Tue Jun 03, 2008 2:23 pm

Re: Newbie Help

Post by Snoegoer »

I forgot to mention there are 9 different prod_types. Total products table has 40,000 entries. I need to narrow it by prod_type first. I figure 9 different files is the easiest way to narrow that down.

So I would like to keep WHERE prod_type='???" part, but not the AND prod_name='???' part in the query. Which to me means I need to move it out of the query and further into the statment I'm just not sure where to move it and how to write it in.

Someone here mentioned functions but I nor they know how?

Essentially instead of pulling 98 files with company names, I want to pul 1 with 98 different sections of company names.
User avatar
deejay
Forum Contributor
Posts: 201
Joined: Wed Jan 22, 2003 3:33 am
Location: Cornwall

Re: Newbie Help

Post by deejay »

is there a reason why you feel you need to move 'AND prod_name' somewhere. If you want to drop it then just leave that out of the query

Code: Select all

 
<?php
include("adminincludes/adminphpheaders_inc.php");
 
//open DB
include("../includes/opendb_inc.php");
$strSQL="SELECT * FROM products INNER JOIN companies USING (co_uid) WHERE prod_type='Gifts Novelties & Stationery'  ORDER BY co_name";
$result=mysql_query($strSQL) or die(mysql_error());
$strXML="<bedbathblankets>\n";
while ($row=mysql_fetch_array($result))
{
$strXML.="\t<co_name>".$row["co_name"]."</co_name>\n";
}
$strXML.="</bedbathblankets>\n";
?>
 
 
if you want to limit the searchs per page then pagination is what you'll need. Best way is to do a search on here or google for pagination.
Snoegoer
Forum Newbie
Posts: 3
Joined: Tue Jun 03, 2008 2:23 pm

Re: Newbie Help

Post by Snoegoer »

Thanks again, My rookiness might be showing through here, it would probably have helped if I wasn't brought in half way throught the project.

With that piece I end up with a list of company names (co_names) but they are not in catergories (prod_names).

(Previous code only writes one of many segments to XML)

Final xml output I was looking for would look something like this:

<Gifts Novelties & Stationery>
<Bed & Bath Sets & Blankets>
<co_name>ABC</co_name>
<co_name>XXX</co_name>
<co_name>XYZ</co_name>
</Bed & Bath Sets & Blankets>
<Art>
<co_name>123</co_name>
</Art>
</Gifts Novelties & Stationery>
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Newbie Help

Post by califdon »

Snoegoer wrote:I have the peice of code below that returns what I am looking for. However there are 98 different prod_name entries I need to do this for. Is there any way to query by prod_type and output all the co_names for each prod_name entry?
What you're looking for is the GROUP BY clause: http://www.tizag.com/mysqlTutorial/mysqlgroupby.php
Post Reply