Question about queries within loops

Anon

Hi,

I am running a loop where for each iteration, I run a select query in mysql where (mysql variable) = $i. The problem I am having is that the query name is the same each time, and for whatever reason I am getting the correct number of rows in my loop, but they are all processing the same query (the first one, where $i=1) instead of doing a different query.
Hence, I get $i number of rows, but the same results on each line.

Is there a way to reset the query results so I can use the same query name each time, or is there a way to set a dynamic name for each query?
Any help would be much appreciated.

Code looks something like this:

$i=1;
while ($i<$col_num)
{
$query = "SELECT * from pboard WHERE list_cat=$i";
$result2 = mysql_query($query);
while ($row=mysql_fetch_array($result))
{

$data[]= $row['title'];
}
$i++;
}

Thanks,

Siobahn

Anon

$query = "SELECT title from pboard WHERE list_cat >= 1 and list_cat < " . $col_num . " order by list_cat;
$result = mysql_query($query);
while ($row=mysql_fetch_array($result))
{
$data[]= $row['title'];
}

Anon

The reason I need to loop is because this info is getting dumped into an existing fixed-length array.... However, there may not be any records for a particular list_cat , so I need to loop so that I can put a placeholder into the array - otherwise, the array breaks....

I don't know any other way to do this -
The full code looks like this...

$i=1;
while ($i<$col_num)
{
$query = "SELECT * from pboard WHERE list_cat=$i";
$result = mysql_query($query);

if no item in row for column, write a blank header

if (mysql_num_rows($result) == 0)
{
$data[] = " ";;
}
else
{
while ($row=mysql_fetch_array($result))
{
$data[]= $row['title'];
}
}
$i++;
}

Thanks again for any help you may have...

siobahn

Anon

You can keep track of whether there are any gaps in the sequence.

$query = "SELECT title,list_cat from pboard WHERE list_cat >= 1 and list_cat < " . $col_num . " order by list_cat ";
$result = mysql_query($query);
$p = 0;
while ($row=mysql_fetch_array($result))
{
if ( $row['cat_list'] - $p > 1 )
while ( $p++ < $row['cat_list'] )
$data[] = " ";
$data[]= $row['title'];
$p = $row['cat_list'];
}

Anon

Lars,

Thanks so much for your prompt responses... it is much appreciated... but I tried using your code and it doesn't seem to work - it doesn't reserve any blank headers, and it's pulling out the data out of order even though I've got the sort order running...

Any other suggestions?

Thanks

Siobahn

[deleted]

Lars's idea is nice, but I don't get how he did it.

I'd do it like this:

Run a query to get all the rows ordered by cat.

Set a cat-counter to the first cat

loop1:
Get a row from the resultset.
Write a placeholder to the array.
Check: is this a row for the curret cat-counter?
Yes: put the data into the array
No:
Loop2:
Increase the cat-counter
Write a placeholder
Check: is this a row for the current cat-counter?
Yes: put the data into the array, exit loop2
No: continue loop2
continue loop1

A forum, a FAQ, email notification, what else do you need?