Page 1 of 1

list records from mysql database

Posted: Thu Aug 28, 2008 5:39 pm
by benniss
I have inherited a site written using PHP. I am new to this and have found that part of the code doesn't do what it is meant to. The code is meant to pull teams from the mysql database and order them by team name. However the only entries that are displayed are those belonging to the first team name no others display. :( Could someone tell me what is wrong with the code. The code before and after this works ok. Thanks in advance.

Code: Select all

if($eventtype=='Team') {?>
 
<table class="dsR52" border="0" cellspacing="0" cellpadding="4">
 <tr>
  <td class="text" width="200">
   <div align="left">Name</div>
  </td>
  <td class="text dsR28">Team</td>
  <td class="text dsR51">School</td>
 </tr>
</table>
 
<?php
 
include ('../config/connector.php');
$query="SELECT * from entries where eventcode='$eventcode' and school='$schoolname' group by 'eventteam'";
$query_result = mysql_query ($query);
$num_records = @mysql_num_rows ($query_result);
$result = @mysql_query ($query);
if ($num_records > 0) {
 
while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
 
$subeventteam=$row[5];
$subeventcode=$row[1]; ?><?php
 
include ('../config.php');
$querylist="SELECT * from entries where eventcode='$subeventcode' and eventteam='$subeventteam'"; 
$resultlist = @mysql_query ($querylist);
$num_recordlist = @mysql_num_rows ($resultlist);
 
while ($row3 = mysql_fetch_array($resultlist, MYSQL_NUM)) {
 
$studentname=$row3[8];
$eventteam=$row3[5];
$school=$row3[6]; ?>
<table class="dsR49" border="0" cellspacing="0" cellpadding="4">
 <tr>
  <td class="text" width="200"><?php echo $studentname;?></td>
  <td class="text"><?php echo $eventteam;?>
  
<?php
if ($num_recordlist>$max) { echo "<font color='red'>(".$num_recordlist.")</font>"; } 
elseif ($num_recordlist<$min) { echo "<font color='red'>(".$num_recordlist.")</font>"; } 
else { echo "<font color='green'> (".$num_recordlist.")</font>"; }
?>
 
  </td>
  <td class="text dsR51"><?php echo $school; ?></td>
 </tr>
</table>
 
<?php }
echo '<hr noshade="noshade" size="1" width="100%" />';?><?php }}else{?>
<p></p>
<table width="100%" border="0" cellspacing="0" cellpadding="4">
 <tr>
  <td class="text"><?php echo 'There are NO entries for event '.$eventcode;?></td>
 </tr>
</table>
<?php }?><?php}else

Re: list records from mysql database

Posted: Fri Aug 29, 2008 8:44 am
by mrgooding
hi there - to test what's happening, i'd suggest echoing the number of rows (echo $num_records; and echo $num_recordlist;) each sql query retrieves. If it's only 1, then the sql statement is going to be your problem (or the actual data in your database), if not, it's probably the way the data is being looped through, displayed.

Hope this helps (i'm pretty new at this too!).

Re: list records from mysql database

Posted: Fri Aug 29, 2008 6:03 pm
by califdon
benniss wrote:I have inherited a site written using PHP. I am new to this and have found that part of the code doesn't do what it is meant to. The code is meant to pull teams from the mysql database and order them by team name. However the only entries that are displayed are those belonging to the first team name no others display. :( Could someone tell me what is wrong with the code. The code before and after this works ok. Thanks in advance.

Code: Select all

...
[color=#008040]$query="SELECT * FROM entries WHERE eventcode='$eventcode' AND school='$schoolname' GROUP BY 'eventteam'";[/color]
...
[color=#008040]$querylist="SELECT * FROM entries WHERE eventcode='$subeventcode' AND eventteam='$subeventteam'"; 
...[/color]
The 2 lines I have highlighted in green form two queries to the database. The first one will retrieve records from the table "entries" which contain specific values in 2 fields named "eventcode" and "school", and group them by a field named "eventteam". The second one will retrieve records from the same table which contain specific values for the "eventcode" field and the "eventteam" field. If the table contains records for more than one "eventteam", the first query should retrieve all of them that match the "eventcode" and "school" data values. The second query will never retrieve records for any "eventteam" except the one it is searching for.