help with mysql_fetch_array??

[WLW]

I changed the code to:

<?php 
$last_memno = "";
while($row_rs_members = mysql_fetch_array($rs_members)) { 
if ($last_memno != $row_rs_members["memno"])
?> 
  <?php echo $row_rs_members['memno']; ?> | 
  <?php echo $row_rs_members['stage_last']; ?> |
  <?php echo $row_rs_members['stage_first']; ?> | 
  <?php echo $row_rs_members['specialty']."<br>\n"; 
  $last_memno = $row_rs_members["memno"];  

}?>

...and now the page displays:

0101001 | Alfaro | Manny | Composer
0101002 | Wilson | Carla | Announcer
0101002 | Wilson | Carla | Interpreter/Translator
0101002 | Wilson | Carla | Narrator
0101003 | Jones | Nancy | Costume Designer
0101004 | Davis | Fred | 
0101005 | Smith | Jordan | 
0101006 | Austin | Barbara | Narrator
0101007 | Young | David | 
0101008 | Carson | Nadine | 

This is almost right... The first record should have "Acting Coach" in the last column, since it is the first record in the output. It seems to have skipped record #1, and displayed record #2. Next, as you can see, memno 0101002 still displays it's duplicates. It should only have displayed the first of those records, ignoring the other 2.

In essence, it seems to be simply displaying every record, except the first one.

I also tried the code exactly as Thomas suggested, and the display became:

0101001|Alfaro|Manny|Acting Coach
0101001|Alfaro|Manny|Acting Coach
0101001|Alfaro|Manny|Acting Coach
0101001|Alfaro|Manny|Acting Coach
0101001|Alfaro|Manny|Acting Coach
0101001|Alfaro|Manny|Acting Coach
0101001|Alfaro|Manny|Acting Coach
0101001|Alfaro|Manny|Acting Coach

[tsinka]

try this code:

<?php  

$last_memno = ""; 
while($row_rs_members = mysql_fetch_array($rs_members)) {  

  if ($last_memno != $row_rs_members["memno"])  {
?>  

    <?php echo $row_rs_members['memno']; ?> | 
    <?php echo $row_rs_members['stage_last']; ?> | 
    <?php echo $row_rs_members['stage_first']; ?> | 
    <?php echo $row_rs_members['specialty']."<br>\n"; ?>
<?PHP
    $last_memno = $row_rs_members["memno"];   

  }
}
?> 

The code is ok just to test ... but I'd suggest to use one echo instead of several <?php echo ... ?> lines. This makes it easier to find such problems like the "missing curly braces" one.

Thomas

[WLW]

Thomas, I'm not sure you noticed, but I revised my previous post... Can you review it again?

[tsinka]

my fault, sorry ...

while($row = mysql_fetch_array($rs_members)) {   

  if ($last_memno != $row["memno"])  { 
    echo $row['memno']."|"  

        .$row['stage_last']."|"  

        .$row['stage_first']."|" 
        .$row['specialty']."<br>\n"; 

$last_memno = $row["memno"];     
  }  

}  

Remove the line
$row_rs_members = mysql_fetch_assoc($rs_members);
outside the while loop if it is still in your code...

Thomas

[WLW]

Removing that line did the trick!

Thanks Thomas!!

Now, please, can you help me work the code into the "real" page, which is quite a bit more complex then the test page.

See attachment in my next post...:queasy:

[tsinka]

You can post the code attached as file to your post. That makes it easier to read the thread. I'd suggest to remove the last two posts and to post the file as attachment (there's a file input below the textarea).

Thomas

[WLW]

I am attaching the page.

[WLW]

While the code works well with the simple display code that we, I have been unable to get the same code to work with the more complex display code of the page that I am actually using.

The filtering code as it works now starts with the first record, then decides whether or not to display it. If yes, it passes that row to the display code, then loops back and picks the second record, etc., until all the records have been filtered.

MY QUESTION:
Since I'm having trouble figuring out how to wrap this filtering code around my display code, I am wondering if it's possible to filter my entire recordset BEFORE it is processed by the display code? That is, filter the recordset, store it as a new recordset, then pass the data in the new recordset to the code that displays the final page.

[tsinka]

How does the filter code look like ? There might be a solution by modifying the SQL query itself.

Thomas

[WLW]

Hi Thomas. Thanks for the reply.

You were helping me with this a few weeks ago, and I haven't heard from you in a while.

If you review the previous replies to this thread, I think you will have everything you need to analyze my problem, including the full query, and the filtering code.

[tsinka]

I'm modifying your code now ... one question: Which MySQL version do you have installed ?

Thomas

[WLW]

I installed MySQL well over a year ago. Where would I look to determine which version?

Thanks.

[tsinka]

Try this one to get the version info:

$conn = mysql_connect("host","user","pass");
echo mysql_get_server_info();

Thomas

[WLW]

version 4.0.18-nt is running on my computer.

I'm not sure which version is running on the actual server. I'll call the host company to find out.

[tsinka]

Ok,

one solution:

change the order by part to

ORDER BY members.memno, members.stage_last, members.stage_first

(add members.memno)

Replace the do { ... } while () loop with

$memno = '';
while (...) {
  if ($row_rs_members['memno'] != $memno) {
    $memno = $row_rs_members['memno'];
  } else {
    continue;
  }
  ....
  ....
}

Alternatively, add
GROUP BY members.memno
to the query (and do not change the while loop). That will group the results by memno to get the data for each memno once.
If you use the modified while loop like shown above your page calculations will be wrong because they're based on the ungrouped result set.

I found some errors in your code. One question about the GET paramters eye1, hair1 and sing1. Do the fields in the table contain exactly the values defined in the arrays in the php code ?

Thomas

Thomas

[WLW]

Originally posted by tsinka
Alternatively, add
GROUP BY members.memno
to the query (and do not change the while loop). That will group the results by memno to get the data for each memno once.
If you use the modified while loop like shown above your page calculations will be wrong because they're based on the ungrouped result set.

Thomas

Thomas,

I think I see what needs to happen. I feel like a fool it's so simple...

I changed the last appended statement to:

$query_rs_members .= " GROUP BY members.stage_last, members.stage_first";

This filters the recordset as I need it for display purposes.

I feel like such a dummy for wasting so much of your time.

Now, if I can impose on you again...

I am trying to implement a search on the items in the specialty column, which I added via the LEFT JOIN.

For the new search parameter, I appended the WHERE statement with

" AND (specialmem.specialy LIKE '%')

But, since the specialmem table only contains values for members who add a choice to it, some members (memno) are not included in that table.

So now, when the SQL runs, it builds a recordset that only includes memno contained in the specialmem table, rather than include all memno, which makes the LEFT JOIN useless.

I have a menu box (named otherT1) on the search page that has the available choices for the search. If I can get this search to work, I'd like to somehow allow for multiple choices to be selected. I think a list box can handle that.

[tsinka]

Are the two fields the first name and last name of the members ? You might run into troubles if yes and two different members have the same name. In that case try:

GROUP BY members.memno ORDER BY members.stage_last, members.stage_first

In line 155 of the code you posted you have

} elseif ($my_sing = "n/a"){

I think that should be

} elseif ($my_sing == "n/a"){

While messing around with your code I changed the script a little bit. I couldn't really test it ... find attached the script.

About the other problem:

Using only LEFT JOINS should do the trick because a LEFT JOJN allows you to also select records that don't have matching records in another table. I'll post an example, soon.

Thomas

[tsinka]

No ... I thought about your query a little bit. You should change the logic of your script so that it doesn't add a where clause for fields that don't matter (depending on the search string a member submitted).

Generally: Using "WHERE acolumn LIKE '%'" is useless, you can remove it. Change the logic so that it doesn't add a where clause if it would be "%".

Looking at the LEFT JOIN part:

Only add the LEFT JOIN and any specialmem WHERE clause if someone wants to search for useful information in that table. The same for all other parts of the WHERE clause (at least age_L,stage_last,category and region).

Additionally, don't use LIKE if you want to exactly match a value in a field (especially if certain columns in the table only contain well known values like maybe members.singer).

Thomas

[WLW]

Thomas,

Your modified code is completely foreign to me, and I'd prefer (at this time) to stick with the code I sent you. I may be able to analyze/understand what you've done in the future.

I chose to add the new search as follows:

//array of all possible VALID talent types 
$talent_type = array("n/a","Acting Coach","Announcer","Composer","Costume Designer","Dance Coach","Interpreter/Translator","Narrator","Musician","Playwright","Screenwriter","Stunt Coordinator","Stunt Person"); 

$my_talent = "%";  //set default 

//check to see if the form has a value and that value is in the array 
if ((isset($_GET['otherT1']))&&(in_array($_GET['otherT1'],$talent_type))){ 
   $my_talent = $_GET['otherT1']; 
} 

//check the value of the talent and include it in the sql if a valid option is choosen 
if ($my_talent != "n/a"){ 
    $query_rs_members .= " AND (specialmem.specialty = '$my_talent')"; 
} elseif ($my_talent = "n/a"){ 
    $query_rs_members .= "";
}
//
$talent_type2 = array("n/a","Acting Coach","Announcer","Composer","Costume Designer","Dance Coach","Interpreter/Translator","Narrator","Musician","Playwright","Screenwriter","Stunt Coordinator","Stunt Person"); 

$my_talent2 = "%";  //set default 

//check to see if the form has a value and that value is in the array 
if ((isset($_GET['otherT2']))&&(in_array($_GET['otherT2'],$talent_type2))){ 
   $my_talent2 = $_GET['otherT2']; 
} 

//check the value of the singer and include it in the sql if a valid color is choosen 
if ($my_talent2 != "n/a"){ 
    $query_rs_members .= " OR (specialmem.specialty = '$my_talent2')"; 
} elseif ($my_talent2 = "n/a"){ 
    $query_rs_members .= "";
}

In general, does the code I've been using have any serious problems that might slow things down or leave my database vulnerable?

[tsinka]

The way you check for valid form values looks ok but there's some redundant code.

<?PHP
//array of all possible VALID talent types
// tsinka: I removed n/a from the array since we
// don't want to add anything to the query if n/a
// has been submitted. So n/a can be seen as an "invalid"
// option
$talent_type = array("Acting Coach","Announcer","Composer","Costume Designer","Dance Coach","Interpreter/Translator","Narrator","Musician","Playwright","Screenwriter","Stunt Coordinator","Stunt Person"); 

$my_talent = array();

//check to see if the form has a value and that value is in the array
if ((isset($_GET['otherT1']))&&(in_array($_GET['otherT1'],$talent_type))){ 
   $my_talent[] = "specialmem.speciality = '".$_GET['otherT1']."'"; 
} 

//check to see if the form has a value and that value is in the array
if ((isset($_GET['otherT2']))&&(in_array($_GET['otherT2'],$talent_type2))){ 
   $my_talent[] = "specialmem.speciality = '".$_GET['otherT2']."'"; 
}

// tsinka: any valid talent ?
// if yes: add specialmem WHERE clause to the query.
if (!empty($my_talent)) {
  $query_rs_members .= "AND (".implode(" OR ",$my_talent).")";
}
?>

Which type of inputs do you use for otherT2 and otherT1 ? You could use a multiple select or check boxes so users can select more than two talent types.

Thomas