select statement not finding the variable to search with

[pete2000]

ok, here goes, i am doing a select statement but i only want to display the information which has the same $custID as the website user. but when i do the query and echo it out, it gives me a blank for the $cusID, here is the statement i am using:

function viewpreleague($table, $where){

/* function to view a table in a html table, with column headings 
$where is an optional SQL where clause used to limit the rows selected*/

global $DBHOST, $DBUSER,$DBPASSWORD,$DBDATABASE;

/* first connect to the database */
$dbcnx = myconnect();

/* now get the column headers */
$fields = mysql_list_fields($DBDATABASE, $table, $dbcnx);

$columns = mysql_num_fields($fields);

/* start a table to put things in then loop round and put in the headers */
?> <table border="1"> <?php
for ($j = 0; $j < $columns; $j++) { ?>
  		<td> <?php echo(mysql_field_name($fields, $j)); ?> </td>
	<td> <?php };
?> <tr> <?php

/* now get the data */
		$query = "SELECT * FROM $table $where WHERE $table.CUST_ID = \"$custID \" ORDER by Pos asc ";
	echo $query;
	if (! mysql_query($query))
        printf ("Error: %s<br>%s<br>", mysql_error (), $query);
		$result = mysql_query($query);


/* and work through the data row by row creating a line in the table */
$i = 0;
while ($i < mysql_num_rows ($result)) {
	$row = mysql_fetch_array($result);
	for ($j = 0; $j < $columns; $j++) { ?>
      	<td> <?php echo($row[mysql_field_name($fields, $j)]); ?> </td>
 		<td> <?php
 	};
 	?> <tr>  <?php
    $i++;
};

	/* now close the table */
?> </table> <?php
};

i was wondering what i need to do to get the statement to view the $custID. the $custID is stored in the user table.

[drew010]

chage
global $DBHOST, $DBUSER,$DBPASSWORD,$DBDATABASE;
to
global $DBHOST, $DBUSER,$DBPASSWORD,$DBDATABASE, $custID;

$custID is blank because the function doesnt have access to it. you need to declare it as global so it can use it.

[pete2000]

thanks for the quick reply drew010, i changed that and it is still blank. in another script i got it looking at the $cust_ID and this is it:

$query = "SELECT CUST_ID from $table_users WHERE EMAIL = \"$Ema\"";
//echo $query;
	$result=mysql_query($query);
    while ($row = mysql_fetch_assoc($result))
{
$custID = $row["CUST_ID"];
 echo("Welcome ");   echo ($custID);
//echo("Welcome ");  echo $row["CUST_ID"];

}
	if (! $result) printf ("Error: %s<br>", mysql_error ());

$query = "DELETE FROM $table_preleague
       		WHERE $table_preleague.CUST_ID = \"$custID \"";
echo $query;
		$result=mysql_query($query);
		if (! $result) printf ("Error2: %s<br>", mysql_error ());

this code is from the page which runs straight before it, so i don't get why it doesn't see it? the delete query works fine and sees the $custID

[drew010]

if they are on separate pages you will need to do the
$query = "SELECT CUST_ID from $table_users WHERE EMAIL = \"$Ema\"";
query again on the page that doesnt see it.

im not quite sure of how your full code looks and where the functions are and when the $custID variable becomes available.

[pete2000]

hey drew010, i did what you said and put the query on the page which didn't see it and it is working now, thank you ever so much for your help 😃

also, i have got another little problem using the select statement and that is when i use it, it displays the limited data but all the column headers so do you know how to get rid of the redundent column headers?

here is the code i am using

function viewBottomSix($table, $where){
/* function to view a table in a html table, with column headings 
$where is an optional SQL where clause used to limit the rows selected*/

global $DBHOST, $DBUSER,$DBPASSWORD,$DBDATABASE;

/* first connect to the database */
$dbcnx = myconnect();

/* now get the column headers */
$fields = mysql_list_fields($DBDATABASE, $table, $dbcnx);

$columns = mysql_num_fields($fields);

/* start a table to put things in then loop round and put in the headers */
?> <table border="1"> <?php
for ($j = 0; $j < $columns; $j++) { ?>
  		<td> <?php echo(mysql_field_name($fields, $j)); ?> </td>
	<td> <?php };
?> <tr> <?php

/* now get the data */
$query = "SELECT Pos,clubID,P,Pts FROM $table $where ORDER by pos asc LIMIT 18 , 24;";
	if (! mysql_query($query))
        printf ("Error: %s<br>%s<br>", mysql_error (), $query);
		$result = mysql_query($query);

/* and work through the data row by row creating a line in the table */
$i = 0;
while ($i < mysql_num_rows ($result)) {
	$row = mysql_fetch_array($result);
	for ($j = 0; $j < $columns; $j++) { ?>
      	<td> <?php echo($row[mysql_field_name($fields, $j)]); ?> </td>
 		<td> <?php
 	};
 	?> <tr>  <?php
    $i++;
};

	/* now close the table */
?> </table> <?php
};

thank you again

[drew010]

i think it has to do with this line:
<?php echo($row[mysql_field_name($fields, $j)]); ?>
the function mysql_field_name tells you what the name of the specified field index is. since you are doing this in the loop, it will be done for every result. i think you should change that to:
<?php echo($row[$j]); ?>

[pete2000]

hey drew010, thank you again for you help, but.....i have said that i want three column headers and that works ok, but the data goes under the 1st, 3rd and 4th column and i can't see why?
so the data needs to move accross to occupy the 1st, 2nd and 3rd columns, here is the updated code:

function viewAverageRating($table, $where){
/* function to view a table in a html table, with column headings 
$where is an optional SQL where clause used to limit the rows selected*/

global $DBHOST, $DBUSER,$DBPASSWORD,$DBDATABASE;

/* first connect to the database */
$dbcnx = myconnect();

/* now get the column headers */
$fields = mysql_list_fields($DBDATABASE, $table, $dbcnx);

$columns = mysql_num_fields($fields);

/* start a table to put things in then loop round and put in the headers 
?> <table border="1"> <?php
for ($j = 0; $j < $columns; $j++) { ?>
  		<td> <?php echo(mysql_field_name($fields, $j)); ?> </td>
	<td> <?php };
?> <tr> <?php*/

	?> <!-- create the table headings -->

	<TABLE border = 1> <TR> 
  	<TD ALIGN="center"> <em>Player ID</TD>
  	<TD ALIGN="center"> <em>Average Rating</TD>
  	<TD ALIGN="center"> <em>Position Played</TD>
  	</em></TR>
<?php 

/* now get the data */
$query = "SELECT playerID, averageRating, positionPlayed FROM $table $where ORDER by averageRating desc LIMIT 0 , 10;";
if (! mysql_query($query))
        printf ("Error: %s<br>%s<br>", mysql_error (), $query);
$result = mysql_query($query);

/* and work through the data row by row creating a line in the table */
$i = 0;
while ($i < mysql_num_rows ($result)) {
	$row = mysql_fetch_array($result);
	for ($j = 0; $j < $columns; $j++) { ?>
      	<td> <?php echo($row[$j]); ?></td>
 		<td> <?php
 	};
 	?> <tr>  <?php
    $i++;
};

/* now close the table */
?> </table> <?php
};

thank you again 🙂

[drew010]

i think your problem is here:

$i = 0; 
    while ($i < mysql_num_rows ($result)) { 
        $row = mysql_fetch_array($result); 
        for ($j = 0; $j < $columns; $j++) { ?> 
              <td> <?php echo($row[$j]); ?></td> 
             <td> <?php 
         }; 
         ?> <tr>  <?php 
        $i++; 
    };

try changing it to

$i = 0; 
    while ($i < mysql_num_rows ($result)) { 
        $row = mysql_fetch_array($result);
        echo "<tr>\n"; 
        for ($j = 0; $j < $columns; $j++) {
             echo "<td> {$row[$j]}</td>\n";
         }; 
         echo "</tr>\n"; 
        $i++; 
    };
</td>

i think there was some problem with td's and tr's being opened and closed in your code. hopefully this will do it for you.

[drew010]

p.s. there is also a little trick you can use to simplify your loop for getting the rows from mysql_fetch_array. it will remove the need for the counter variable. it may be a little confusing at first but it can make things a little easier for you and require one less counter variable.
what you had is:
$i = 0;

while ($i < mysql_num_rows ($result)) {

$row = mysql_fetch_array($result);
and you can make it
while($row = mysql_fetch_array($result)) {

basically what that will do is loop as long as mysql_fetch_array is sending data, because when fetch array has no more rows, it returns false and the while loop will break out.
it keeps you from having to think about incrementing and worrying about the count.

[pete2000]

hey drew010, thanks alot for helping, it is working now 😃 if there is anything i can help you with, get in touch, as long as it isn't anything to do with php :p