MYSQL SELECT statement with variable based on PHP for loop

Question

Currently, $selection outputs the following: MIN(Bale_ID), MIN(Incoming_Moisture) which is exactly what it should be outputting (they're names from another table). However, when I put $selection into the mysql_query $data1, it seems to just be reading the last value (MIN(Incoming_Moisture)) and only displays the results for that. How do I get the query to read the entire array of elements in $selection? Thank you!!

while ($row1 = mysql_fetch_array($fieldnames1)) {   
$fields = $row1['fields1']; 
$explode = explode(',',$fields);

if ($row1) {
    for ($i=0; $i<$minrows; $i++) {
        if ($i<$minrows-1){
            $comma = ", ";
        }
        else {
            $comma = "";
        }
        //$selection = "MIN(".$explode[$i].")".$comma;
        //echo $selection;
        $data1 = mysql_query("SELECT MIN(".$explode[$i].")".$comma." from data WHERE (fchmitimestamp LIKE CONCAT(@year,'%',@month,'%',@day,'_________'))");
        $all1 = mysql_num_fields($data1); //return # of columns; for some reason is returning "1" right now.

        while ($row2 = mysql_fetch_array($data1)) {
            for ($col=0; $col<$all1; $col++) {
                echo $all1;
                echo "<td>Min: " . $row2[$col] . "</td>";
            }
            echo "</tr>";
        }
    }       
}
}
echo "</table>";

if your storing a comma delimited string in the db, the structure is wrong — user557846
– user557846, Commented Jul 21, 2011 at 21:21
I'm teaching myself php and mysql as I go through this project, so I'm not sure what the correct structure is. Any suggestions? — Ashton
– Ashton, Commented Jul 21, 2011 at 21:41
perhaps you should get the hang of the idea what you are trying to achieve. Write down what problem you are trying to solve with this program (like you would describe it to a stranger) - thats called analysis and also gets your head together — Imre L
– Imre L, Commented Jul 21, 2011 at 22:17

Marc B · Accepted Answer · 2011-07-21 21:20:07Z

1

Look at the order of operations in your code:

loop {
     ... fetch data ...
     ... assign results to $data1 ...
}

Nowhere in your loop do you output or save the results you've got in $data1, so each iteration of the loop overwrites the results of the previous iteration - in other words, only the LAST iteration's results will be stored.

answered Jul 21, 2011 at 21:20

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Ashton Over a year ago

That makes sense, so i've been playing with it more... do you have any suggestion of how to alter my code to fix that?

Marc B Over a year ago

Move the second while loop inside the first loop, so that right after you get your $data1 result, you start processing it immediately.

Ashton Over a year ago

I actually just did that -- but the problem remains. $all1 is outputting 1, which is a problem. Also, even if I increase $all1 to a number larger than 1 in the last for statement, the output is blank while it should really have a value from my query.

Ashton Over a year ago

Ok - i've updated the code with a couple of changes... moved the last while statement and such. Have the same problems, though.

Marc B Over a year ago

It couldn't ever BE anything other than 1. You pull out a field, run a query, pull out another field, run a brand new query, etc... You only ever fetch one field at a time.

Imre L · Accepted Answer · 2011-07-21 22:31:03Z

0

you are running the query once per for loop cycle (1 field at a time) and since first ones yields in SQL error because of the trailin comma, these will not be echoed except the last one.

-- notice the error in first query
SELECT MIN(Bale_ID), from data WHERE (fchmitimestamp LIKE CONCAT(@year,'%',@month,'%',@day,'_________'))
SELECT MIN(Incoming_Moisture) from data WHERE (fchmitimestamp LIKE CONCAT(@year,'%',@month,'%',@day,'_________'))

use var_dump($selection) instead of echo $selection to see yourself

edited Jul 21, 2011 at 22:31

answered Jul 21, 2011 at 22:09

Imre L

6,29126 silver badges32 bronze badges

1 Comment

Ashton Over a year ago

This was it!!!! Got it!!!! Oh my gosh, if we were all in the same place I'd buy you all ice cream. So glad to get that done.

Collectives™ on Stack Overflow

MYSQL SELECT statement with variable based on PHP for loop

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related