retrieve information from multiple rows with php after query-ing

Question

Ok, this is the last part I'm having trouble with in this project (my first php/mysql attempt). The trouble comes in the last 'while' loop. I haven't been able to find a way of assigning values to my array of variables. While when working with one row in the table this format works to get multiple columns, I am looking for a way to get one field from multiple (3) rows. Thoughts?

$x=1;
do{
$sql = "SELECT * FROM`".$tableName."` WHERE `StopId`=".$stopId." AND `Time` >='". $time. "' LIMIT 3";   
$result = mysql_query($sql, $conn);

if (!$result){
    echo "DB Error, could not query the database\n";
    echo 'MySQL Error: ' . mysql_error();
    exit;
}

This is the part that is causing me the trouble. It assigns the last result (because it's limited to 3 results) to all of the values. If I change the limit to 1 or 2, it makes all the values either the first or second results. I want the first one in time1, the second one in time2 and the third in time3.

    while ($row = mysql_fetch_array($result)) {
         $time1[$routeName[$x]] = $row[Time];
         $time2[$routeName[$x]] = $row[Time];
         $time3[$routeName[$x]] = $row[Time];

}

    $x++;
}while(!is_Null($routeName[$x]));

EDIT:

The table is in the format:
 | IdNum | StopId | Time  |
 |   1   |   1    | 12:44 |
 |   2   |   2    | 13:15 |
 |   3   |   1    | 12:55 |
 |   4   |   2    | 14:15 |
 |   5   |   1    | 13:20 |

and so on. If I was to have $stopId = 1 and $time = 12:30 I would want to be able to assign:

 "12:44" to $time1[$routeName[$x]]
 "12:55" to $time2[$routeName[$x]] 
 "13:20" to $time3[$routeName[$x]]

Also I should add that the query statement is functional.

Edit: Sorry about that I changed $routeNum to $routeName after the original posting to help clarify what the variable contained. It's just a string title that forms part of the table name.

I think youre going about this the wrong way... Im pretty sure ive done similar things as what youre attempting and never have i used a do/while structure to achieve it. Can you give us an example of the data for the 3 rows and the array structure you are trying to create? — prodigitalson
– prodigitalson, Commented Jul 15, 2011 at 23:49
also what are the entries in $routeName and $columnName? — prodigitalson
– prodigitalson, Commented Jul 15, 2011 at 23:57

Ben · Accepted Answer · 2011-07-16 00:55:03Z

1

Well you are looping over only one row at a time remember. So, if you set all three variables at once, they are all going to be the values of the last row. I don't know where your $routeName variable is coming from?? try:

$x = 0;
while ($row = mysql_fetch_array($result)) {
     $routeName[$x] = $row['Time'];          
$x++;
}

I don't know how you want to format it? If you must have it formatted into separate variables instead of an array, I think you can do this, however I don't know why you would want to format it like that.

$x = 0;
while ($row = mysql_fetch_array($result)) {
     ${time.$x[$routeName[$x]]} = $row['Time'];          
$x++;
}

If you

answered Jul 16, 2011 at 0:55

Ben

7457 silver badges23 bronze badges

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

1 Comment

prodigitalson Over a year ago

+1... An array is definitely they way to go. In general anytime you are tempted to do $var1, $var2, $var3, etc. its generally an indicator that you should be using numerically indexed array and then accessing the values by key like $var[0].

Collectives™ on Stack Overflow

retrieve information from multiple rows with php after query-ing

}

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

}

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related