[SOLVED] mysql_fetch_array problem, and if and else statement problem

MasterACE14 · October 6, 2007

Evening Everyone,

I have a script that I basically want it to "echo" 4 columns from my database per user. And I want to change the results from one of the "columns" using PHP, dynamically from a integer into a string. And I want it all to be displayed in a table.

Currently the script is only echo'ing the last user in the database into a table. And nothing is echo'd at all when I try to change the integer into a string. Here is my script:

<?php
// display information for each player in a table
$link = mysql_connect( "localhost", "ace_ACE", "*****" );
if ( ! $link ) {
die( "Couldn't connect to MySQL: ".mysql_error() );
}

mysql_select_db( "ace_cf", $link )
or die ( "Couldn't open 'ace_cf': ".mysql_error() );

$result = mysql_query( "SELECT * FROM cf_users " );
$num_rows = mysql_num_rows( $result );

while ($a_row = mysql_fetch_array( $result ) ) {

	$all_name = stripslashes($a_row['username']);
	$all_rank = stripslashes($a_row['rank']);
	$all_race = stripslashes($a_row['race']);
	$all_money = stripslashes($a_row['money']);


}

// change the players race from a integer into a string
if($all_race == 0) {
return "British SAS";
}
elseif($all_race == 1) {
return "Navy Seal";
}
elseif($all_race == 2) {
return "Russian Spetsnaz";
}
elseif($all_race == 3) {
return "Australian SAS";
}

?>

<div id = "attack">


<table align="center" class = "fix">

<tr>
<td colspan="4">
<center><b>Players</b></center>
</td>
</tr>

<tr>

<td><b>Name</b></td>

<td><b>Rank</b></td>

<td><b>Race</b></td>

<td><b>Money</b></td>

</tr>

<tr>
<td colspan="4">
</td>
</tr>

<tr>

<td><?php echo $all_name; ?></td>

<td><?php echo $all_rank; ?></td>

<td><?php echo $all_race; ?></td>

<td><?php echo $all_money; ?></td>

</tr>

</table>


<?php 

mysql_close( $link );

?>

</div>

darkfreaks · October 6, 2007

try using mysql_fetch_assoc instead

teng84 · October 6, 2007

<?php
// display information for each player in a table
$link = mysql_connect( "localhost", "ace_ACE", "*****" );
if ( ! $link ) {
die( "Couldn't connect to MySQL: ".mysql_error() );
}

mysql_select_db( "ace_cf", $link )
or die ( "Couldn't open 'ace_cf': ".mysql_error() );

$result = mysql_query( "SELECT * FROM cf_users " );
$num_rows = mysql_num_rows( $result );
echo '<table align="center" class = "fix">
<tr>
<td colspan="4">
<center><b>Players</b></center>
</td>
</tr>

<tr>

<td><b>Name</b></td>

<td><b>Rank</b></td>

<td><b>Race</b></td>

<td><b>Money</b></td>

</tr>';

while ($a_row = mysql_fetch_array( $result ) ) {

	$all_name = stripslashes($a_row['username']);
	$all_rank = stripslashes($a_row['rank']);
	$all_race = stripslashes($a_row['race']);
	$all_money = stripslashes($a_row['money']);
	echo '<tr>
		<td colspan="4">
		</td>
		</tr>

		<tr>

		<td> $all_name</td>

		<td> $all_rank</td>

		<td>$all_race</td>

		<td>$all_money</td>

		</tr>';
}
echo '</table>';


// change the players race from a integer into a string
if($all_race == 0) {
return "British SAS";
}
elseif($all_race == 1) {
return "Navy Seal";
}
elseif($all_race == 2) {
return "Russian Spetsnaz";
}
elseif($all_race == 3) {
return "Australian SAS";
}

?>

<div id = "attack">






<?php 

mysql_close( $link );

?>

</div>

anujgarg · October 6, 2007

MasterACE14

it seems you are not looping your queries correctly..... just make table in your while loop

roopurt18 · October 6, 2007

Think very carefully about what your original code was doing. You were querying the database. Then you looped over the results and assigned each to a variable. But then you didn't do anything with the variable! So it doesn't matter if the query pulled 1 row, 10 rows, or 5 million. You were constantly overwriting the variables without using them and this would just continue until the last one; which is why it was only printing the last record in your DB.

teng84 correctly moved the table generation before the loop and then correctly creates each row inside the loop, with one problem. He has embedded the variables directly in the string, which is OK if the string begins and ends with double quotes; his uses single quotes however so it won't work.

Also, I'm fairly your if / else if / else if / etc is meant to be a part of the loop. I'm guessing that one of your fields is an int that correlates to a string. You should place your if block in the loop just before echo'ing the row. Instead of returning you should assign to a variable and print that variable in the loop. You only use return when exiting a function, which it doesn't appear your code is doing. Some how I think you got the idea that you have to use return to execute the code in the if or something like that.

MasterACE14 · October 6, 2007

I've got it working exactly how I wanted it to now

but how do I limit it to show 20 users per page? is that something I do in the MySQL Query, or is that to do with PHP ?

roopurt18 · October 6, 2007

Both.

http://www.phpfreaks.com/tutorials/43/0.php

MasterACE14 · October 6, 2007

I have integrated parts of the script from the tutorial you linked me to.

But I am recieving an error I have never got before:

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-5, 5' at line 1

and I think my URL's for the hyperlinks near the bottom of the page are wrong, my website URL ends in ?page=attack, but I need to make it end in something like ?page=attack?$id or whatever it is.

here's my code:

<?php // display information for each player in a table
$link = mysql_connect( "localhost", "ace_ACE", "*****" );
if ( ! $link ) {
die( "Couldn't connect to MySQL: ".mysql_error() );
}

mysql_select_db( "ace_cf", $link )
or die ( "Couldn't open 'ace_cf': ".mysql_error() );

// workout the page pagination limits etc
    $limit          = 5;               
    $query_count    = "SELECT count(*) FROM cf_users";    
    $result_count   = mysql_query($query_count);    
    $totalrows      = mysql_num_rows($result_count);  

if(empty($page)){
        $page = 1;
    }

$limitvalue = $page * $limit - ($limit); 
    $query  = "SELECT * FROM cf_users LIMIT $limitvalue, $limit";        
    $result = mysql_query($query) or die("Error: " . mysql_error()); 

    if(mysql_num_rows($result) == 0){
        echo("Nothing to Display!");
    }


//print "<p>$num_rows people have money</p>\n";
echo '<div id = "attack">


<table align="center" class = "fix">

<tr>
<td colspan="4">
<center><b>Players</b></center>
</td>
</tr>

<tr>

<td><b>Name</b></td>

<td><b>Rank</b></td>

<td><b>Race</b></td>

<td><b>Money</b></td>

</tr>

<tr>
<td colspan="4">
</td>
</tr>';


while ($a_row = mysql_fetch_assoc( $result ) ) {

	$all_name = stripslashes($a_row['username']);
	$all_rank = stripslashes($a_row['rank']);
	$all_race = stripslashes($a_row['race']);
	$all_money = stripslashes($a_row['money']);

// change the players race from a integer into a string
if($all_race == 0) {
$all_race = "British SAS";
}	
elseif($all_race == 1) {
$all_race = "Navy Seal";
}
elseif($all_race == 2) {
$all_race = "Russian Spetsnaz";
}
elseif($all_race == 3) {
$all_race = "Australian SAS";
}

echo	'

<tr>

<td>' . $all_name . '</td>

<td>' . $all_rank . '</td>

<td>' . $all_race . '</td>

<td>$' . $all_money . '</td>

</tr>';
}
echo '</table>';

if($page != 1){ 
        $pageprev = $page--;
        
        echo("<a href=\"http://www.crikeygames.com.au/conflictingforces/index.php?page=attack&page=$pageprev\">PREV".$limit."</a> "); 
    }else{
        echo("PREV".$limit." ");
    }

$numofpages = $totalrows / $limit; 
    
    for($i = 1; $i <= $numofpages; $i++){
        if($i == $page){
            echo($i." ");
        }else{
            echo("<a href=\"http://www.crikeygames.com.au/conflictingforces/index.php?page=attack?page=$i\">$i</a> ");
        }
    }


    if(($totalrows % $limit) != 0){
        if($i == $page){
            echo($i." ");
        }else{
            echo("<a href=\"http://www.crikeygames.com.au/conflictingforces/index.php?page=attack?page=$i\">$i</a> ");
        }
    }

    if(($totalrows - ($limit * $page)) > 0){
        $pagenext = $page++;
         
        echo("<a href=\"http://www.crikeygames.com.au/conflictingforces/index.php?page=attack?page=$pagenext\">NEXT".$limit."</a>"); 
    }else{
        echo("NEXT".$limit); 
    }
    
    mysql_free_result($result);

mysql_close( $link );

echo '</div>';

MasterACE14 · October 6, 2007

bump

MadTechie · October 6, 2007

try this

<?php // display information for each player in a table
$link = mysql_connect( "localhost", "ace_ACE", "*****" );
if ( ! $link ) {
die( "Couldn't connect to MySQL: ".mysql_error() );
}

mysql_select_db( "ace_cf", $link )
or die ( "Couldn't open 'ace_cf': ".mysql_error() );

// workout the page pagination limits etc
    $limit          = 5;
    $vpage = (int)$_GET['vpage'];
    $query_count    = "SELECT count(*) FROM cf_users";    
    $result_count   = mysql_query($query_count);    
    $totalrows      = mysql_num_rows($result_count);  

    if(empty($vpage)){
        $vpage = 1;
    }

    $limitvalue = $vpage * $limit - ($limit); 
    $query  = "SELECT * FROM cf_users LIMIT $limitvalue, $limit";        
    $result = mysql_query($query) or die("Error: " . mysql_error()); 

    if(mysql_num_rows($result) == 0){
        echo("Nothing to Display!");
    }


//print "<p>$num_rows people have money</p>\n";
echo '<div id = "attack">


<table align="center" class = "fix">

<tr>
<td colspan="4">
<center><b>Players</b></center>
</td>
</tr>

<tr>

<td><b>Name</b></td>

<td><b>Rank</b></td>

<td><b>Race</b></td>

<td><b>Money</b></td>

</tr>

<tr>
<td colspan="4">
</td>
</tr>';


while ($a_row = mysql_fetch_assoc( $result ) ) {

	$all_name = stripslashes($a_row['username']);
	$all_rank = stripslashes($a_row['rank']);
	$all_race = stripslashes($a_row['race']);
	$all_money = stripslashes($a_row['money']);

// change the players race from a integer into a string
if($all_race == 0) {
$all_race = "British SAS";
}	
elseif($all_race == 1) {
$all_race = "Navy Seal";
}
elseif($all_race == 2) {
$all_race = "Russian Spetsnaz";
}
elseif($all_race == 3) {
$all_race = "Australian SAS";
}

echo	'

<tr>

<td>' . $all_name . '</td>

<td>' . $all_rank . '</td>

<td>' . $all_race . '</td>

<td>$' . $all_money . '</td>

</tr>';
}
echo '</table>';

if($page != 1){ 
        $pageprev = $page--;
        
        echo("<a href=\"http://www.crikeygames.com.au/conflictingforces/index.php?page=attack&vpage=$pageprev\">PREV".$limit."</a> "); 
    }else{
        echo("PREV".$limit." ");
    }

$numofpages = $totalrows / $limit; 
    
    for($i = 1; $i <= $numofpages; $i++){
        if($i == $vpage){
            echo($i." ");
        }else{
            echo("<a href=\"http://www.crikeygames.com.au/conflictingforces/index.php?page=attack?vpage=$i\">$i</a> ");
        }
    }


    if(($totalrows % $limit) != 0){
        if($i == $vpage){
            echo($i." ");
        }else{
            echo("<a href=\"http://www.crikeygames.com.au/conflictingforces/index.php?page=attack?vpage=$i\">$i</a> ");
        }
    }

    if(($totalrows - ($limit * $vpage)) > 0){
        $pagenext = $vpage++;
         
        echo("<a href=\"http://www.crikeygames.com.au/conflictingforces/index.php?page=attack?vpage=$pagenext\">NEXT".$limit."</a>"); 
    }else{
        echo("NEXT".$limit); 
    }
    
    mysql_free_result($result);

mysql_close( $link );

echo '</div>';

MasterACE14 · October 6, 2007

thats abit better. It is now displaying only 5 users. But the links at the bottom are alittle stuffed up.

here's what it says:

PREV5 1 NEXT5

PREV5 is a hyperlink, when I click it. The page goes from /index.php?page=attack to /index.php?page=attack&vpage=attack

and it still shows the same users. When it should really be linking NEXT5 to go to the next page which would show the rest of the users.(as their is only 10 users currently)

MadTechie · October 6, 2007

mybad, i missed one

change

if($page != 1){ 
        $pageprev = $page--;

to

if($vpage != 1){ 
        $pageprev = $vpage--;

MasterACE14 · October 6, 2007

I've fixed that, now neither of them are links :-\ , and its showing theirs 1 page, when their should be 2.

MadTechie · October 6, 2007

your need to post the full code, of what you have done

MasterACE14 · October 6, 2007

<?php
// display information for each player in a table
$link = mysql_connect( "localhost", "ace_ACE", "*****" );
if ( ! $link ) {
die( "Couldn't connect to MySQL: ".mysql_error() );
}

mysql_select_db( "ace_cf", $link )
or die ( "Couldn't open 'ace_cf': ".mysql_error() );

// workout the page pagination limits etc
    $limit          = 5;
    $vpage = (int)$_GET['vpage'];
    $query_count    = "SELECT count(*) FROM cf_users";    
    $result_count   = mysql_query($query_count);    
    $totalrows      = mysql_num_rows($result_count);  

    if(empty($vpage)){
        $vpage = 1;
    }

    $limitvalue = $vpage * $limit - ($limit); 
    $query  = "SELECT * FROM cf_users LIMIT $limitvalue, $limit";        
    $result = mysql_query($query) or die("Error: " . mysql_error()); 

    if(mysql_num_rows($result) == 0){
        echo("Nothing to Display!");
    }


//print "<p>$num_rows people have money</p>\n";
echo '<div id = "attack">


<table align="center" class = "fix">

<tr>
<td colspan="4">
<center><b>Players</b></center>
</td>
</tr>

<tr>

<td><b>Name</b></td>

<td><b>Rank</b></td>

<td><b>Race</b></td>

<td><b>Money</b></td>

</tr>

<tr>
<td colspan="4">
</td>
</tr>';


while ($a_row = mysql_fetch_assoc( $result ) ) {

	$all_name = stripslashes($a_row['username']);
	$all_rank = stripslashes($a_row['rank']);
	$all_race = stripslashes($a_row['race']);
	$all_money = stripslashes($a_row['money']);

// change the players race from a integer into a string
if($all_race == 0) {
$all_race = "British SAS";
}	
elseif($all_race == 1) {
$all_race = "Navy Seal";
}
elseif($all_race == 2) {
$all_race = "Russian Spetsnaz";
}
elseif($all_race == 3) {
$all_race = "Australian SAS";
}

echo	'

<tr>

<td>' . $all_name . '</td>

<td>' . $all_rank . '</td>

<td>' . $all_race . '</td>

<td>$' . $all_money . '</td>

</tr>';
}
echo '</table>';

echo '<center>';

if($vpage != 1){ 
        $pageprev = $vpage--;
        
        echo("<a href=\"http://www.crikeygames.com.au/conflictingforces/index.php?page=attack&vpage=$pageprev\">PREV".$limit."</a> "); 
    }else{
        echo("PREV".$limit." ");
    }

$numofpages = $totalrows / $limit; 
    
    for($i = 1; $i <= $numofpages; $i++){
        if($i == $vpage){
            echo($i." ");
        }else{
            echo("<a href=\"http://www.crikeygames.com.au/conflictingforces/index.php?page=attack?vpage=$i\">$i</a> ");
        }
    }


    if(($totalrows % $limit) != 0){
        if($i == $vpage){
            echo($i." ");
        }else{
            echo("<a href=\"http://www.crikeygames.com.au/conflictingforces/index.php?page=attack?vpage=$i\">$i</a> ");
        }
    }

    if(($totalrows - ($limit * $vpage)) > 0){
        $pagenext = $vpage++;
         
        echo("<a href=\"http://www.crikeygames.com.au/conflictingforces/index.php?page=attack?vpage=$pagenext\">NEXT".$limit."</a>"); 
    }else{
        echo("NEXT".$limit); 
    }
    
    mysql_free_result($result);

echo '</center>';

echo '</div>';

?>

MadTechie · October 6, 2007

found a slight bug

do you mean the links not showing ?

try the below

<?php
// display information for each player in a table
$link = mysql_connect( "localhost", "ace_ACE", "*****" );
if ( ! $link ) {
die( "Couldn't connect to MySQL: ".mysql_error() );
}

mysql_select_db( "ace_cf", $link )
or die ( "Couldn't open 'ace_cf': ".mysql_error() );

// workout the page pagination limits etc
    $limit          = 5;
    $vpage 			= (int)$_GET['vpage'];
    $query_count    = "SELECT count(*) FROM cf_users";    
    $result_count   = mysql_query($query_count);    
    $totalrows      = mysql_num_rows($result_count);  

    if(empty($vpage)){
        $vpage = 1;
    }

    $limitvalue = $vpage * $limit - ($limit); 
    $query  = "SELECT * FROM cf_users LIMIT $limitvalue, $limit";        
    $result = mysql_query($query) or die("Error: " . mysql_error()); 

    if(mysql_num_rows($result) == 0){
        echo("Nothing to Display!");
    }


//print "<p>$num_rows people have money</p>\n";
echo '<div id = "attack">


<table align="center" class = "fix">

<tr>
<td colspan="4">
<center><b>Players</b></center>
</td>
</tr>

<tr>

<td><b>Name</b></td>

<td><b>Rank</b></td>

<td><b>Race</b></td>

<td><b>Money</b></td>

</tr>

<tr>
<td colspan="4">
</td>
</tr>';


while ($a_row = mysql_fetch_assoc( $result ) ) {

	$all_name = stripslashes($a_row['username']);
	$all_rank = stripslashes($a_row['rank']);
	$all_race = stripslashes($a_row['race']);
	$all_money = stripslashes($a_row['money']);

// change the players race from a integer into a string
if($all_race == 0) {
$all_race = "British SAS";
}	
elseif($all_race == 1) {
$all_race = "Navy Seal";
}
elseif($all_race == 2) {
$all_race = "Russian Spetsnaz";
}
elseif($all_race == 3) {
$all_race = "Australian SAS";
}

echo	'

<tr>

<td>' . $all_name . '</td>

<td>' . $all_rank . '</td>

<td>' . $all_race . '</td>

<td>$' . $all_money . '</td>

</tr>';
}
echo '</table>';

echo '<center>';

if($vpage != 1){ 
        $pageprev = $vpage--;
        
        echo("<a href=\"http://www.crikeygames.com.au/conflictingforces/index.php?page=attack&vpage=$pageprev\">PREV".$limit."</a> "); 
    }else{
        echo("PREV".$limit." ");
    }

$numofpages = $totalrows / $limit; 
    
    for($i = 1; $i <= $numofpages; $i++){
        if($i == $vpage){
            echo($i." ");
        }else{
            echo("<a href=\"http://www.crikeygames.com.au/conflictingforces/index.php?page=attack&vpage=$i\">$i</a> ");
        }
    }


    if(($totalrows % $limit) != 0){
        if($i == $vpage){
            echo($i." ");
        }else{
            echo("<a href=\"http://www.crikeygames.com.au/conflictingforces/index.php?page=attack&vpage=$i\">$i</a> ");
        }
    }

    if(($totalrows - ($limit * $vpage)) > 0){
        $pagenext = $vpage++;
         
        echo("<a href=\"http://www.crikeygames.com.au/conflictingforces/index.php?page=attack?vpage=$pagenext\">NEXT".$limit."</a>"); 
    }else{
        echo("NEXT".$limit); 
    }
    
    mysql_free_result($result);

echo '</center>';

echo '</div>';

?>

MasterACE14 · October 6, 2007

yep the links aren't showing :-\ , this is very bizarre, its still showing their is only 1 page :-[

PREV5 1 NEXT5

MadTechie · October 6, 2007

at this part

// workout the page pagination limits etc
    $limit          = 5;
    $vpage 			= (int)$_GET['vpage'];
    $query_count    = "SELECT count(*) FROM cf_users";    
    $result_count   = mysql_query($query_count);    
    $totalrows      = mysql_num_rows($result_count);

add this below

echo $totalrows;

see what we get

MasterACE14 · October 6, 2007

that echo's "1" very strange...

MadTechie · October 6, 2007

try

SELECT count(*) FROM cf_users

in phpmyadmin see what happens

MasterACE14 · October 6, 2007

it returns "10", which is correct.

MadTechie · October 6, 2007

the strange thing is

    $query_count    = "SELECT count(*) FROM cf_users";    
    $result_count   = mysql_query($query_count);    
    $totalrows      = mysql_num_rows($result_count);

hasn't changed!

ok try changing to

    $query_count    = "SELECT count(*) as totalcount FROM cf_users";    
    $result_count   = mysql_query($query_count);
$tmp = mysql_fetch_array($result_count);
$totalrows   = $tmp['totalcount'];

MasterACE14 · October 6, 2007

now things are getting interesting...

it now displays:

PREV5 1 2 NEXT5

but when I am at the first page, NEXT5 and 2 are both links. I click on NEXT5 and I am automatically logged out, but the URL changes to the page?attack&vpage=2 or whatever it is. It doesnt change to the homepage URL.

But if I click the "2" it takes me to that page, same URL and everything is fine and dandy.

now when im on the second page, PREV5, 2 and NEXT5 are links, it logs me out on PREV5 and NEXT5 and when I click 2, well it refresh's the page because I am already their.

I'm guessing the URL's in the coding aren't completely correct?

MadTechie · October 6, 2007

last bug i can see

if(($totalrows - ($limit * $vpage)) > 0){
        $pagenext = $vpage++;
         
        echo("<a href=\"http://www.crikeygames.com.au/conflictingforces/index.php?page=attack?vpage=$pagenext\">NEXT".$limit."</a>"); 
    }else{

to

if(($totalrows - ($limit * $vpage)) > 0){
        $pagenext = $vpage++;
         
        echo("<a href=\"http://www.crikeygames.com.au/conflictingforces/index.php?page=attack&vpage=$pagenext\">NEXT".$limit."</a>"); 
    }else{

MasterACE14 · October 6, 2007

Thats fixed it , their is no more login out, and from what I can see is PREV5, 1, 2 and NEXT5 are mixed up, like when im on page 2, NEXT5 is linking to page 1. And 1 isn't linked when im on 2, but 2 is linked to 1. So its all working now, I just gotta unjumble them

Thankyou so much MadTechie and everyone else who helped me with my Dilemma

Regards ACE

Sign In

[SOLVED] mysql_fetch_array problem, and if and else statement problem

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information