SQL Query Results with Numbers for each field

billhobbs

I need to get the number that an item would be in a search query with 1 being the first number, 2 being second, 3 being third, etc. In the data sorting. For example:
If my database is of domestic animals and I search for dogs I might have 5 different types of dogs in my database. I need the results to show up like so in my repeat field...
1. Jack Russell ----the id for this might be 25
2. German Shepard ----the id for this might be 26
3. Terrier ----the id for this might be 29
4. Doberman ----the id for this might be 32
5. Collie ----the id for this might be 40

So what I need are the numbers 1-5 each being associated with the correct dog. I have a gallery that has to have the items numerically listed from 1-whatever without number breaks. 1,2,3,4,5 works but 1,2,4,3,5 would not allow the gallery to work correctly.

bradgrafelman

Start a counter variable at 1, and increment it after every row you retrieve from the result set.

billhobbs

I'm not sure how you do that. My sql statement is pretty simple

SELECT * FROM galleryimages WHERE gimageGalleryPID = GalleryPID

I just need a number starting with 1 that is associated with each data field.

bradgrafelman

$counter_variable = 1;

while(you_do_something())
{
    // do whatever you want

$counter_variable++;
}

billhobbs

I pretty much use Dreamweaver to create my sql statements so I'm not the most knowledgeable of php writers. I tried several things using your script but nothing worked. Here is my script I'm using you'll notice there is a place for the number if you would be so kind as to show me how I can get that number in that place I would greatly appreciate it.

$colname_rsResults = "-1";
if (isset($_GET['id'])) {
  $colname_rsResults = (get_magic_quotes_gpc()) ? $_GET['id'] : addslashes($_GET['id']);
}
mysql_select_db($database_PowerCMSConnection, $PowerCMSConnection);
$query_rsResults = sprintf("SELECT * FROM galleryimages WHERE gimageGalleryPID = %s", GetSQLValueString($colname_rsResults, "text"));
$rsResults = mysql_query($query_rsResults, $PowerCMSConnection) or die(mysql_error());
$row_rsResults = mysql_fetch_assoc($rsResults);
$totalRows_rsResults = mysql_num_rows($rsResults);
$totalCount = $row['gimageGalleryPID'];

?>
<body>
<?php do { ?>
  <span class="style1">NUMBER GOES HERE<?php echo $row_rsResults['gimageThumb']; ?><br></span>
  <?php } while ($row_rsResults = mysql_fetch_assoc($rsResults)); ?>
</body>

bradgrafelman

What did you try? You only need to do two things:

Initialize the counter variable before the loop (hence the "$var = 1" line in my example code above).
Increment the counter variable at the end of each loop iteration (hence the "$var++" in my example code).

(Okay, technically there are three things, but the third would just be "Output the number wherever you want.")

EDIT: Also, when posting PHP code, please use the board's [noparse]

..

[/noparse] bbcode tags as they make your code much easier to read and analyze.

billhobbs

I copied the code you gave and put it in the script changing the you_do_something to

$row_rsResults = mysql_fetch_assoc($rsResults)

then in the place of NUMBER GOES HERE I put

echo $counter_variable

bradgrafelman

Can you post a new copy of what your code looks like after you've made those changes?

billhobbs

$colname_rsResults = "-1";
if (isset($_GET['id'])) {
  $colname_rsResults = (get_magic_quotes_gpc()) ? $_GET['id'] : addslashes($_GET['id']);
}
mysql_select_db($database_PowerCMSConnection, $PowerCMSConnection);
$query_rsResults = sprintf("SELECT * FROM galleryimages WHERE gimageGalleryPID = &#37;s", GetSQLValueString($colname_rsResults, "text"));
$rsResults = mysql_query($query_rsResults, $PowerCMSConnection) or die(mysql_error());
$row_rsResults = mysql_fetch_assoc($rsResults);
$totalRows_rsResults = mysql_num_rows($rsResults);
$totalCount = $row['gimageGalleryPID'];
$counter_variable = 1;

while($totalRows_rsResults = mysql_num_rows($rsResults))
{
    // do whatever you want

$counter_variable++;
} 

?>
<body>
<?php do { ?>
  <span class="style1"><?php echo $counter_variable ; ?><?php echo $row_rsResults['gimageThumb']; ?><br></span>
  <?php } while ($row_rsResults = mysql_fetch_assoc($rsResults)); ?>
</body>

bradgrafelman

Er.. why did you create a separate loop (one whose loop condition doesn't make any sense)? The point was to add the two changes to your existing loop. I wasn't providing you code that you can copy-and-paste (I always try to not do that, actually) but rather comprehend and use as a guide to modify your existing code.

billhobbs

I'm not a huge php programmer. Typically I just use Dreamweaver to write the php for my sql statement whether it's a Join, Count etc. and I then grab the binding information from the recordset. But since I couldn't find any php statement like COUNT() that gives me the number that's why I came to this forum. I know how to write simple session variables and if/else statements but other that than my php skill s are pretty basic.

Is this where your code should be? Thanks for your help.

<?php $colname_rsResults = "-1";
if (isset($_GET['id'])) {
  $colname_rsResults = (get_magic_quotes_gpc()) ? $_GET['id'] : addslashes($_GET['id']);
}
mysql_select_db($database_PowerCMSConnection, $PowerCMSConnection);
$query_rsResults = sprintf("SELECT * FROM galleryimages WHERE gimageGalleryPID = %s", GetSQLValueString($colname_rsResults, "text"));
$rsResults = mysql_query($query_rsResults, $PowerCMSConnection) or die(mysql_error());
$row_rsResults = mysql_fetch_assoc($rsResults);
$totalRows_rsResults = mysql_num_rows($rsResults);
$totalCount = $row['gimageGalleryPID'];
?>
<body>
<?php do { ?>
  <span class="style1"><?php echo $counter_variable ; ?><?php echo $row_rsResults['gimageThumb']; ?><br></span>
  <?php } $counter_variable = 1;

while($totalRows_rsResults = mysql_num_rows($rsResults))
{
    // do whatever you want

$counter_variable++;
}  ?>
</body>

billhobbs

Okay I think I got it to work. Anyway it appears to work. Is this right?

<?php $colname_rsResults = "-1";
if (isset($_GET['id'])) {
  $colname_rsResults = (get_magic_quotes_gpc()) ? $_GET['id'] : addslashes($_GET['id']);
}
mysql_select_db($database_PowerCMSConnection, $PowerCMSConnection);
$query_rsResults = sprintf("SELECT * FROM galleryimages WHERE gimageGalleryPID = %s", GetSQLValueString($colname_rsResults, "text"));
$rsResults = mysql_query($query_rsResults, $PowerCMSConnection) or die(mysql_error());
$row_rsResults = mysql_fetch_assoc($rsResults);
$totalRows_rsResults = mysql_num_rows($rsResults);
$totalCount = $row['gimageGalleryPID'];
$counter_variable = 1;
?>
<body>
<?php do { ?>
  <span class="style1"><?php echo $counter_variable++ ; ?><?php echo $row_rsResults['gimageThumb']; ?><br></span>
  <?php } while ($row_rsResults = mysql_fetch_assoc($rsResults)); 
  ?>
</body>

<?php
mysql_free_result($rsResults);
?>

bradgrafelman

Sorry - got distracted with other work.

Yes, that appears to be correct.

billhobbs

Thanks.