trouble with query'ing in a foreach loop

[learningsql]

If it matters, my server is running php v 5.
This snippet of code is part of a larger page which works fine on its on. Here's what I can successfully display when I insert this snippet of code:

<? 
$wkrpcodesearch= mysql_query("SELECT catwkrps FROM category WHERE lcat_id='$cat'") or die(mysql_error()); // fetches numerical data example '100,101' 
while($row = mysql_fetch_array($wkrpcodesearch)) 
{extract($row); 
$catwkrpsnew = $catwkrps;} 

$cat_rev_codes = array($catwkrpsnew); //displaying as an array the list of wkrps associated with a category number 

foreach($cat_rev_codes as $code) 
{ 
print " $code, "; 
} 
?> 

The result will print '100, 101, ' which is successful.

What I want to do is to be able to perform a query within the loop to get additional info related to '100' and '101'. For example, I want to search table 'wkrp' where '100' equals 'wkrpcode' and display two pieces of related info, 'wkrp_name' and 'wkrp_url' and then display them in a html text link.

So here's what I came up with:

<? 
$search1= mysql_query("SELECT catwkrps FROM category WHERE lcat_id='$cat'") or die(mysql_error()); 
while($row = mysql_fetch_array($search1)) 
{extract($row); 
$catwkrps = $row["catwkrps"]; 

$cat_rev_codes = array($catwkrps); //displaying as an array, the list of wkrps associated with a category number 

foreach($cat_rev_codes as $code) 

{ 
$search2 = mysql_query("SELECT wkrp_name, wkrp_url FROM wkrp WHERE wkrpcode = '$code'") or die(mysql_error()); 
while($row = mysql_fetch_array ($search2)) 
{extract($row); 
$wkrp_name = $row["wkrp_name"]; 
$wkrp_url = $row["wkrp_url"]; 

print "<a href='$wkrp_url' alt='Visit this link for more info.'>$wkrp_name</a>"; 
} 
} 
} 
?> 

But I get the following error message:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '101' at line 1

I've been scratching my head over this one since yesterday. Can anybody help out? Thanks in advance.

[HalfaBee]

try putting $code istead of '$code'

[learningsql]

Hi HalfaBee,

My original code did not have the $code wrapped in single quotes. I still got that error. Someone suggested on another forum to wrap $code. It didn't look right to me but I did it anyway. Still didn't work.

So now I have it not wrapped in single quotes. Iam still getting the same error.

I've been doing some reading about using foreach loops with arrays. It says that "Variables assigned with an element value and key, are available in the body of the loop." Could this mean that the foreach loop I am using is incorrect? Where I am using

foreach (array_expression as $value) { // body of loop }

maybe I should be using

foreach (array_expression as $key => $value) { //body of loop }

Comments?

[learningsql]

I tried something else. I changed the value of $catwkrps from 100, 101 to just 100. The script then works to retrieve the query information and to print the html that I wanted.

But...that doesn't solve the problem of the array functioning properly. If the array has more than one value (example 100,101) this whole bit of coding fails.

Can someone help me debug this?

[Sxooter]

What you really want is probably a join.

i.e.

select * from table1 join table2 on (table1.id=table2.id) where table1.lastname = 'Smith'

or something like that.

If you have to run a query for each element you got back from the first query, you're doing it wrong.

[maxpup979]

heh- Actually Sxooter, they probably want to use a LEFT join so that they get all of the results from the first query, along with the additional data from the second...🙂

Although I may be making a poor assumption...🙂

And how the heck ya doin?

[Sxooter]

maxpup979 wrote:

heh- Actually Sxooter, they probably want to use a LEFT join so that they get all of the results from the first query, along with the additional data from the second...🙂

Although I may be making a poor assumption...🙂

And how the heck ya doin?

True true, that may be what he needs. Basically, the theory should be to mangle data against each other other as much as possible in the db until it's pretty much in the right order and such and then spit it out to the app to display. The less work php does putting sets together, and the fewer and larger the queries the db runs the better. To a point. 🙂

And I'm doing fine. Glad to see you poke your head up there mp...

[learningsql]

Thank you for the suggestion. I'll read up on it and let you know.
Thanks again.

[learningsql]

I just can't get it. Maybe I'm not setting up the attributes for the tables correctly. If I cite an example, maybe it will help.

I am working with a modeling agency database about their models. Each model has a catalog of photos. And the photos were developed by many different photographers. What I have created so far:
table1 = table of model names // table1 lists reference codes for photographers (100, 101 etc)
table2 = table of all models photos
table3 = table of photographers // table3.wkrp_name and table3.wkrp_url is the contact info for the photographer

When a model's web page is displayed, her photos appear as queried from table2. I can query table1 to obtain the list of phtograper codes and get it to display on the models web page as '100, 101'. But what I am trying to achieve is to search table3 for '100' and output the contact info for the photographer 100 as a text hyperlink, then output for photgrapher 101.

That's why I'm thinking a query loop has to be performed after creating an array of (100,101). Can someone tell me where I am going wrong?

Thanks again.

[Sxooter]

So, look for the things that link those tables together. you can join more than just two tables by the way.

table1 = table of model names // table1 lists reference codes for photographers (100, 101 etc)
table2 = table of all models photos
table3 = table of photographers // table3.wkrp_name and table3.wkrp_url is the contact info for the photographer

How is table2 related to one of these other tables?

Assuming that we got from 1 to 3 to 2, we could do something like:
select x,y,z from models m
left join photographers p
on (m.p_id=p.id)
left join photos s
on (p.id=s.p_id)

or something like that.

[learningsql]

You have no idea how much I appreciate your replies and assistance. Thanks.

To answer your question, table2 is the bulk of the data (photos) and is really only related to table1 in the sense that when the webpage calls upon table1 for a model's data to be displayed based on 'table1.lmodel_id', the webpage queries table2 for 'table2.model_id' where 'table2.model_id = table1.lmodel_id' to display photos, text etc on the webpage.

This whole objective is to add an attribute to the table1 (called table1.modelwkrps) so that when a model has more than one photographer in her portfolio, I update that 'table1.modelwkrps' attribute.

Each photographer has a code (100, 101, etc). If the model has two photogrpahers in her portfolio, then 'table1.modelwkrps' would equal "100,101". Table3 is created to store the photographer contact info.

I know that table1 and table2 work very well together. I added table3 thinking this is the most efficient way to achieve this objective.

What I can do so far by myself is when the webpage calls up the models info 'table1.lmodel_id' it also retreives 'table1.modelwkrps' and it does display 100,101 but only as standard text.

What I am trying to do is get the web page to query table3 and to look through attribute 'table3.wrkpcode' for '100' and '101' individually, pull out the name and url info, and to display photographers contact info on each models page.

That's why I was thinking the loop was required. The query would select 'table3.wkrp_name' and 'table3.wrkp_url' in order to display the hyperlink text for photographer #100, then display hyperlink text for photographer #101.

So I don't think that last query to wrote is relavent, but I could be wrong.

Relevent attributes:
table1.modelwkrps // string variable of photographer codes for each model, string could contain just one code, or many codes (+30)
table3.wkrpcode // distinct numerical code for each photographer
table3.wkrp_name // photographers name
table3.wkrp_url // photographers url contact

I obviously could be missing something, so if you are lost on a piece of data, let me know. Thanks again.

[learningsql]

This code produces what I want only if table1.modelwkrps contains just one photographers code (ie. 100). If I wanted to add an additional photographers code to table1.modelwkrps (ie. 100,101) I get nothing to display.

$cat= 19; // distinct value that represent model in table1

$search1= mysql_query("SELECT modelwkrps FROM table1 WHERE lmodel_id='$cat'") or die(mysql_error());
while($row = mysql_fetch_array($search1))
{extract($row);
$catwkrps = $row["catwkrps"];

$cat_rev_codes = array($catwkrps); //displaying as an array, the list of wkrps associated with a category number

foreach($cat_rev_codes as $code)

{
$search2 = mysql_query("SELECT wkrp_name, wkrp_url FROM table3 WHERE wkrpcode = '$code'") or die(mysql_error());
while($row = mysql_fetch_array ($search2))
{extract($row);
$wkrp_name = $row["wkrp_name"];
$wkrp_url = $row["wkrp_url"];

print "<a href='$wkrp_url' alt='Visit this link for more info.'>$wkrp_name</a>";
}
}
}

I also tried adding wkrpcode to $search2 = mysql_query("SELECT wkrp_name, wkrp_url ... but that didn't work either.