using a for loop within SELECT statement?

[micky125]

Hi all, i've been pondering with an idea for my system to allow the user to make reports depending on what he needs. His input screen will be a series of select options corresponding to the table columns. There will be the same number of select statements on screen as there are tables / columns in my database.
e.g

Table 1 = id, date, age (etc.....)
Table 2 = make model (etc....)
-----
-----
Table N = etc

_______________ _________
Table1
select1 select2 select3

Table2
select4 select5

etc.

so the user can add really any number of these say up to 30(although will never get a report with this amount but keeps options open).

The problem i can foresee is in using mysql ( SELECT (for($i=1, $i<=30, i++) etc. if i can get this working it will mean i would not have to hard code say even up to 10 queries.

As I am new to this programming in PHP and mySQL i was wondering if anyone could point me in the right direction as i feel php code cannot be used within sql code.

I am using PHP 5 with HTML.

Any help in this matter is much appreciated as it is the last thing i have to do so i can finish my project :)

[pbmods]

Heya, Micky.

A query is just a string that PHP passes to the SQL server. You can modify it however you want before you pass it off to your query() method.

E.g.,:

[code=php]
for( $i = 0; $i < 99; ++$i )
{
$res = $db->query("SELEC T `stuff` FROM `table` WHERE `id` = '{$i}' LIMIT 1");

// Do stuff with $res
}
[/code]

Alternatively, you might be able to use a join in your SQL query and do away with all the extra query calls altogether. This would be a topic for the MySQL forum.

[micky125]

thanks for the quick reply pbmods
so will ur code give me the same as:

table 1 ----- 1 select option
table 2 ----- 3 select options

mysql( SELECT option1 option2 option3 option4 option5
FROM table1, table2
WHERE (the user will input two values here that will do this)
so that i can then make a report that will read

option1 option2 option3 option4 option5
record1
record2
-----
-----
recordN.

I think u have probably solved it i just cant seem to get my head around the code :(

[pbmods]

It sounds like you want to be able to report on items in one table that match criteria in a second table. Is that correct?

For example:

[code=php]
/** Open the HTML table. */
echo '
<table>
';

/** Run a query to fetch all options that the User selected. */
$selectedItems =
$db->query
(
"
SELECT
`ItemID`
, `Name`
FROM
`Items`
WHERE
`Description` LIKE '%{$searchTerm} %'
"
);

/** For each item in the result... */
foreach( $selectedItems as $rowItem )
{
/** ... output a header row for the item... */
echo "
<tr>
<th colspan=\"3\">{ $rowItem->Name}</th>
</tr>";

/** ... then run a separate query to fetch the options for that item. */
$itemOptions =
$db->query
(
"
SELECT
`Options`.`Opti onID`
, `Options`.`Name `
, `Options`.`Adju stment`
FROM
`Map_ItemOption s`
LEFT JOIN `Options`
USING (`OptionID`)
WHERE
`Map_ItemOption s`.`ItemID` = '{$row->ItemID}'
ORDER BY
`Options`.`Name ` ASC
"
);

/** For each option that we were able to fetch for the item... */
foreach( $itemOptions as $rowOptions )
{
/** ... output the information we fetched for that option. */
echo "
<tr>
<td>{$rowOption s->OptionID}</td>
<td>{$rowOption s->Name}</td>
<td>{$rowOption s->Adjustment}</td>
</tr>";
}
}

/** Close our HTML table. */
echo '
</table>
';
[/code]

[micky125]

yeh thats spot on so it is. im going to implement now. Cheers for the help!