0

Hi guys, i need some help with a too complaicated query for my little brain.
I have the following query:

$query = "
(SELECT pic, text, main,  gr, min(nr) AS nr FROM info WHERE (gr>0) AND (stl LIKE '$stl') AND ((quantity >= 0 and utg!='J') or (quantity > 0 and utg ='J')) GROUP BY gr)
UNION
(SELECT pic, text, main,  gr, nr FROM info WHERE (gr=0) AND (stl LIKE '$stl') AND ((quantity >= 0 and utg!='J') or (quantity > 0 and utg ='J')))
UNION
(SELECT pic, text, main,  gr, min(nr) AS nr FROM info WHERE (gr>0) AND (enh LIKE '$stl') AND ((quantity >= 0 and utg!='J') or (quantity > 0 and utg ='J')) GROUP BY gr)
UNION
(SELECT pic, text, main,  gr,  nr FROM info WHERE (gr=0) AND (enh LIKE '$stl') AND ((quantity >= 0 and utg!='J') or (quantity > 0 and utg ='J')))
ORDER BY nr";

which works perfectly, the prob is that the system has gotten more complicated with the time and $stl (which is a result from one other query) now is actually more results. So if before $stl was = 1, now its also =2 and =3 and i need all that selected by the query.
I thought of making a string of the results like:

$rowCount = mysql_num_rows($resultpage);
if ($rowCount > 0) {
$row = mysql_fetch_array($resultpage);
$stlString = 'stl LIKE '  . $row['stl'];
for ($i = 1; $i < $rowCount; $i++) {
$row = mysql_fetch_array($resultpage);
$stlString .= ' OR stl LIKE '  . $row['stl'];
}
}

which will return *stl like 1 or stl like 2 or slt like 3... etc* (which is still incorrect, missing the () around) but i don't know the right syntax/way to insert that string into the query (or build more to the string so it returns the whole query and what to do after), instead of just having (stl LIKE '$stl') .
I also thought of simply having some for-loop wrapping the whole big query+code after it, so it gets one query for each $stl, but the prob is that i need only one query because i need the numrows of that query.
Highly appreciate any help!

2
Contributors
3
Replies
4
Views
9 Years
Discussion Span
Last Post by Dalli
0

So you're saying the field 'stl' can be like 1 or 2 or 3 or 4 instead of where is was only like 1 before? Couldn't you group several like statements with OR?

((stl like '$st1') OR (stl like '$st2') OR(stl like '$st3'))

I might be missing what you're trying to do.

0

$stl comes from a query, which is: table in db:

||name|stl||
||John|101||
||John|102||
||Ann|506||
||Ann|908||

$querypage = "SELECT * FROM groups WHERE name='$groups' ";  //$groups is for example John
$resultpage = mysql_query ($querypage)
   or die ("Couldn’t execute query.");
$rowpage = mysql_fetch_array ($resultpage);
$stl = $rowpage['stl'];

Before there was only one entry per name in that table, now they could be quite few of them. So i can't simply use that (stl LIKE '$stl') anymore (for the second, big query), i need to pick up the result from $querypage and build a query that says (stl LIKE 101) OR (stl LIKE 102) etc.. for each number there is for the name John in the groups table (if for example $groups is John).

0

For some other lost soul that reads that, answer was:

$rowCount = mysql_num_rows($resultpage);
if ($rowCount > 0) {
$row = mysql_fetch_array($resultpage);
$stl = $row['stl'];
$stlString = '(stl LIKE \''.$row['stl'].'\')';
$enhString = '(enh LIKE \''.$row['stl'].'\')';
for ($i = 1; $i < $rowCount; $i++) {
$row = mysql_fetch_array($resultpage);
$stlString .= ' OR (stl LIKE \''.$row['stl'].'\')';
$enhString .= ' OR (enh LIKE \''.$row['stl'].'\')';
}
}

And big query:

$query = "
(SELECT pic, text, main,  gr, min(nr) AS nr FROM info WHERE (gr>0) AND ($stlString) AND ((quantity >= 0 and utg!='J') or (quantity > 0 and utg ='J')) GROUP BY gr)
UNION
(SELECT pic, text, main,  gr, nr FROM info WHERE (gr=0) AND ($stlString) AND ((quantity >= 0 and utg!='J') or (quantity > 0 and utg ='J')))
UNION
(SELECT pic, text, main,  gr, min(nr) AS nr FROM info WHERE (gr>0) AND ($enhString) AND ((quantity >= 0 and utg!='J') or (quantity > 0 and utg ='J')) GROUP BY gr)
UNION
(SELECT pic, text, main,  gr,  nr FROM info WHERE (gr=0) AND ($stlString) AND ((quantity >= 0 and utg!='J') or (quantity > 0 and utg ='J')))
ORDER BY nr";
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.