Accessing rows more than once

[charger9]

I have a query:

$query_rs = "SELECT * FROM info WHERE pid=$RemoteID";
$rs = mysql_query($query_rs, $server) or die(mysql_error());
$row_rs = mysql_fetch_assoc($rs);
$totalRows_rs = mysql_num_rows($rs);

In the info table I have a column called infotype. I want to be able to show all data related to that specific infotype. Currently I have:

Heading 1:

<? do {
 if $row_rs['infotype']==1 {
echo $row_rs['data']; }
} while ($row_rs = mysql_fetch_assoc($rs)); ?>

Heading 2:

<? do {
 if $row_rs['infotype']==2 {
echo $row_rs['data']; }
} while ($row_rs = mysql_fetch_assoc($rs)); ?>

etc...

I know my query will only return data for one of these statements. How can I tweak it to use the same query to get data under each heading?

[Matthew1980]

Hi there charger9,

Well this is new syntax on me! I wasn't aware that you could use a while in such a way :)

I have only one criticism for you at this point, and the only reason I raise this, is because I speak from experience; Not all servers support short tags: <? ?>, this is something that varies from server to server, and as this is something sensitive, you may want to change to full tags: <?php ?>, purely for server compatibility. Because if you have your version working on one server, and then migrate to a server that doesn't support short form; this will stop you script dead.

So, for less headaches (and proper coding) use full tags - it will save you headaches in the longrun.

Ok, your code.

You want to echo two different outcomes, from one query? Is that about right? This is where the while loops don't look right to me.. But a quick ROTM confirms that it is good

Also, when doing an if:-

if(Conditions){
//case if true
}
else{
//case if false
}

You need the parenthesis there. Other than that I'm not sure what you are trying to achieve.

Cheers,
MRb

[charger9]

Thanks for the feedback, I value your experience! Let me see if I can explain this another way.

Say my query returns all data where $RemoteID is 1. The columns in that table are id, pid, infotype, sequence and data. For instance:
id | pid | infotype | sequence | data
0 | 1 | 1 | 0 | the first piece of data under infotype 1
1 | 1 | 2 | 0 | the first piece of data under infotype 2
1 | 1 | 2 | 1 | the second piece of data under infotype 2
2 | 1 | 3 | 0 | the first piece of data under infotype 3

I have a page designed and want all the information related to specific infotypes to show up under a header:

Infotype 1 Header
the first piece of data under infotype 1

Infotype 2 Header
the first piece of data under infotype 2
the second piece of data under infotype 2

I'm not very experienced in PHP as I'm just trying to piece things together based on my limited previous experience. Can you point me in the right direction?

Thanks!

[rocknbil]

<?php 
$results=null; 
$query_rs = "SELECT * FROM info WHERE pid=$RemoteID"; 
$rs = mysql_query($query_rs, $server) or die(mysql_error()); 
while ($row_rs = mysql_fetch_assoc($rs)) { 
 $col_1 = ($row_rs['infotype']==1)?$row_rs['data']:'-'; 
 $col_2 = ($row_rs['infotype']==2)?$row_rs['data']:'-'; 
 $col_3 = ($row_rs['infotype']==3)?$row_rs['data']:'-'; 
 $results .= "<tr><td>$col_1</td><td>$col_2</td><td>$col_3</td></tr>\n"; 
} 
// 
if ($results) { 
 echo " 
 <table> 
 <tr><td>Heading 1</td><td>Heading 2</td><td>Heading 3</td></tr> 
 $results 
 </table> 
 "; 
} 
else { echo "<p>Oops, no results.</p>"; } 
?> 

May need debugging . . . .

Edit: We sumul-posted, updated for infotype 3. Not sure what you're up to there but may be a more efficient and flexible way to set up the database. But if it works, it works.

[charger9]

Thanks rocknbil. However when I implement your code, I get a table full of dashes so something isn't quite working and I'm not sure how to debug.

[rocknbil]

Hmmm . . . . it should work, debug like so?

<?php  
$results=$debug=null;  
$debug .= "ID: $RemoteID<br>"; 
$query_rs = "SELECT * FROM info WHERE pid=$RemoteID";  
$rs = mysql_query($query_rs, $server) or die(mysql_error());  
while ($row_rs = mysql_fetch_assoc($rs)) {  
 $debug .= " id: " . $row_rs['id'] . " type: " . $row_rs['infotype'] . "<br>"; 
 $col_1 = ($row_rs['infotype']==1)?$row_rs['data']:'-';  
 $col_2 = ($row_rs['infotype']==2)?$row_rs['data']:'-';  
 $col_3 = ($row_rs['infotype']==3)?$row_rs['data']:'-';  
 $results .= "<tr><td>$col_1</td><td>$col_2</td><td>$col_3</td></tr>\n";  
}  
//  
if ($results) {  
 echo "  
 <table>  
 <tr><td>Heading 1</td><td>Heading 2</td><td>Heading 3</td></tr>  
 $results  
 </table>  
 $debug 
 ";  
}  
else { echo "<p>Oops, no results.</p>"; }  
?>  

The only think I can think of ATM is the data is not present as you expect, OR it may be a varchar/char field and it's expecting an integer? Shouldn't' be the case, PHP should still cast a 1 as an integer . . . probably a wasted effort but you can try

($row_rs['infotype']=="1")

etc. I'd be surprised if that made any difference.

[charger9]

Thanks! Now I'm making progress. I've changed my formatting a little. All of the data is showing up. However, I'm getting a Undefined variable error for all my $results assignment statments.

Here is my current code:

$results=null;
$query_rs = "SELECT * FROM phoneinfo WHERE pid=$RemoteID";
$rs = mysql_query($query_rs, $server) or die(mysql_error());

while ($row_rs = mysql_fetch_assoc($rs)) { 
 $col_1 = ($row_rs['infotype']==1)?$row_rs['data']:''; 
 $col_2 = ($row_rs['infotype']==2)?$row_rs['data']:''; 
 $col_3 = ($row_rs['infotype']==3)?$row_rs['data']:'';
 $col_4 = ($row_rs['infotype']==4)?$row_rs['data']:''; 
 $col_5 = ($row_rs['infotype']==5)?$row_rs['data']:''; 
 $col_6 = ($row_rs['infotype']==6)?$row_rs['data']:''; 
 $col_7 = ($row_rs['infotype']==7)?$row_rs['data']:''; 
 $col_8 = ($row_rs['infotype']==8)?$row_rs['data']:''; 
 $col_9 = ($row_rs['infotype']==9)?$row_rs['data']:''; 
 $col_10 = ($row_rs['infotype']==10)?$row_rs['data']:''; 
 
 $results1 .= $col_1; 
 $results2 .= $col_2; 
 $results3 .= $col_3;
 $results4 .= $col_4;
 $results5 .= $col_5;
 $results6 .= $col_6;
 $results7 .= $col_7;
 $results8 .= $col_8;
 $results9 .= ($col_9!='')?"<ul><li>$col_9</li></ul>":'';
 $results10 .= ($col_10!='')?"<ul><li>$col_10</li></ul>":'';

} 
//
<h2>Heading 1</h2>
<?php echo "$results1'; ?>
<h2>Heading 2</h2>
<?php echo "$results2'; ?>
etc.

I really appreciate all your help.

[charger9]

Stepping away from my screen has allowed me to solve my problem. I needed to set the $results variables as null as rocknbil previously coded. Here's the updated WORKING code:

$results1 = null;
 $results2 = null;
 $results3 = null;
 $results4 = null;
 $results5 = null;
 $results6 = null;
 $results7 = null;
 $results8 = null;
 $results9 = null;
 $results10 = null;
 
$query_rs = "SELECT * FROM phoneinfo WHERE pid=$RemoteID";
$rs = mysql_query($query_rs, $Warehouse) or die(mysql_error());

while ($row_rs = mysql_fetch_assoc($rs)) { 
 $col_1 = ($row_rs['infotype']==1)?$row_rs['data']:''; 
 $col_2 = ($row_rs['infotype']==2)?$row_rs['data']:''; 
 $col_3 = ($row_rs['infotype']==3)?$row_rs['data']:'';
 $col_4 = ($row_rs['infotype']==4)?$row_rs['data']:''; 
 $col_5 = ($row_rs['infotype']==5)?$row_rs['data']:''; 
 $col_6 = ($row_rs['infotype']==6)?$row_rs['data']:''; 
 $col_7 = ($row_rs['infotype']==7)?$row_rs['data']:''; 
 $col_8 = ($row_rs['infotype']==8)?$row_rs['data']:''; 
 $col_9 = ($row_rs['infotype']==9)?$row_rs['data']:''; 
 $col_10 = ($row_rs['infotype']==10)?$row_rs['data']:''; 
 
 $results1 .= $col_1; 
 $results2 .= $col_2; 
 $results3 .= $col_3;
 $results4 .= $col_4;
 $results5 .= $col_5;
 $results6 .= $col_6;
 $results7 .= $col_7;
 $results8 .= $col_8;
 $results9 .= ($col_9!='')?"<ul><li>$col_9</li></ul>":'';
 $results10 .= ($col_10!='')?"<ul><li>$col_10</li></ul>":'';

} 
//
<h2>Heading 1</h2>
<?php echo "$results1'; ?>
<h2>Heading 2</h2>
<?php echo "$results2'; ?>
etc.