By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,949 Members | 1,869 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,949 IT Pros & Developers. It's quick & easy.

using a for loop within SELECT statement?

P: 36
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 :)
Sep 15 '08 #1
Share this Question
Share on Google+
4 Replies


pbmods
Expert 5K+
P: 5,821
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.,:

Expand|Select|Wrap|Line Numbers
  1. for( $i = 0; $i < 99; ++$i )
  2. {
  3.   $res = $db->query("SELECT `stuff` FROM `table` WHERE `id` = '{$i}' LIMIT 1");
  4.  
  5.   // Do stuff with $res
  6. }
  7.  
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.
Sep 15 '08 #2

P: 36
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 :(
Sep 15 '08 #3

pbmods
Expert 5K+
P: 5,821
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:

Expand|Select|Wrap|Line Numbers
  1. /** Open the HTML table. */
  2. echo '
  3. <table>
  4. ';
  5.  
  6. /** Run a query to fetch all options that the User selected. */
  7. $selectedItems =
  8.   $db->query
  9.   (
  10.     "
  11. SELECT
  12.          `ItemID`
  13.        , `Name`
  14.   FROM
  15.        `Items`
  16.  WHERE
  17.        `Description` LIKE '%{$searchTerm}%'
  18.     "
  19.   );
  20.  
  21. /** For each item in the result... */
  22. foreach( $selectedItems as $rowItem )
  23. {
  24.   /** ... output a header row for the item... */
  25.   echo "
  26. <tr>
  27.   <th colspan=\"3\">{$rowItem->Name}</th>
  28. </tr>";
  29.  
  30.   /** ... then run a separate query to fetch the options for that item. */
  31.   $itemOptions =
  32.     $db->query
  33.     (
  34.       "
  35. SELECT
  36.             `Options`.`OptionID`
  37.           , `Options`.`Name`
  38.           , `Options`.`Adjustment`
  39.   FROM
  40.               `Map_ItemOptions`
  41.     LEFT JOIN `Options`
  42.           USING (`OptionID`)
  43.  WHERE
  44.           `Map_ItemOptions`.`ItemID` = '{$row->ItemID}'
  45.  ORDER BY
  46.           `Options`.`Name` ASC
  47.       "
  48.     );
  49.  
  50.     /** For each option that we were able to fetch for the item... */
  51.     foreach( $itemOptions as $rowOptions )
  52.     {
  53.       /** ... output the information we fetched for that option. */
  54.       echo "
  55. <tr>
  56.   <td>{$rowOptions->OptionID}</td>
  57.   <td>{$rowOptions->Name}</td>
  58.   <td>{$rowOptions->Adjustment}</td>
  59. </tr>";
  60.     }
  61. }
  62.  
  63. /** Close our HTML table. */
  64. echo '
  65. </table>
  66. ';
  67.  
Sep 17 '08 #4

P: 36
yeh thats spot on so it is. im going to implement now. Cheers for the help!
Sep 18 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.