Indie-Resource.com

Hello all. I have encountered a problem, and have spent a while trying to fix it, but cannot get it.

The problem is, where the WHILE statement is, it should list all moves in the database that have a playerid of 2 and position of Strike.
There are two moves in the database that meet this criteria, they are:
-Punch
-Kick

When I execute the code, I choose Strike and a table is displayed, but only shows "Punch" which works fine, but doesn't display Kick. Its like the while loop is terminating, but its condition is still true. Can anyone see the problem?

You have to use slightly different syntax to display multiple results from a single query. It goes something like this:
As you can see, you must run the WHILE statement with mysql(i)_fetch_row(), not array, because array tries to grab the entire result, whereas row only grabs one result at a time. The only thing is is that you cannot refer to each column by name, as you would with an associative array (like $row["health"] will not work). Instead, you have to use the more cumbersome method of referring to column names by number. So the first column's value would be $row[0], second would be $row[1], etc. Hope this helps!

EDIT: Oh, and of course if you use mysql_fetch_row() instead of mysqli_fetch_row(), you need to drop the reference to the database connection ($db in my code), since the mysql extension assumes the connection, whereas mysqli demands it explicitly. However, really everyone should consider using mysqli full time, as it is much more secure, faster and more robust than mysql extensions.

Jackolantern wrote:You have to use slightly different syntax to display multiple results from a single query. It goes something like this:

Code: Select all

$sql = "SELECT * FROM playermonster WHERE playerid = '$playerid'";
if ($result = mysqli_query($db, $sql)) {       //you don't have to use mysqli. You can still use mysql, but I prefer mysqli
    while ($row = mysqli_fetch_row($result)) {    
        echo($row[13]);
        echo($row[14]);
        echo($row[2]);
        echo($row[3]);
    }
} 

As you can see, you must run the WHILE statement with mysql(i)_fetch_row(), not array, because array tries to grab the entire result, whereas row only grabs one result at a time. The only thing is is that you cannot refer to each column by name, as you would with an associative array (like $row["health"] will not work). Instead, you have to use the more cumbersome method of referring to column names by number. So the first column's value would be $row[0], second would be $row[1], etc. Hope this helps!

EDIT: Oh, and of course if you use mysql_fetch_row() instead of mysqli_fetch_row(), you need to drop the reference to the database connection ($db in my code), since the mysql extension assumes the connection, whereas mysqli demands it explicitly. However, really everyone should consider using mysqli full time, as it is much more secure, faster and more robust than mysql extensions.

Thanks once again Jack. I used the code from another page i have, which does display all rows, so just assumed it would work the same.
i might start using mysqli if its faster and safer

You're welcome!

The performance increases are modest (however, they may be noticeable at huge traffic levels), but the security benefits are very real. For example, (and you may already know how this works, but just for informational purposes for those who don't) to make a SQL injection attack, a malicious user finds a place on your site with a query like this:
And all they have to do is enter something like "'; DROP TABLE `users`;", effectively sending 2 separate SQL queries to the MySQL database when your code only expected to send 1! Now they have deleted one of your tables!

The beauty of the mysqli extension in this situation is that the above SELECT query, and all other regular queries, can only accept a single SQL query at a time, and will error out if it receives more. A separate syntax is used to run more than 1 query at a time! Of course you should still scrub all input and assume that mysqli won't save you from SQL injection attacks, it is nice to know that if you miss something, it is not a huge exploit waiting to happen!

Do I need to change everything to mysqli? As I am getting this error message


Warning: mysqli_query() expects parameter 1 to be mysqli, resource given in

In my connect.php I am using the variable $db the same as you have.

EDIT: Oh wait I re-read your previous post, I am trying to query 2 things, playerid and position. That means I won't be able to use mysqli then huh?

Jackolantern wrote:As you can see, you must run the WHILE statement with mysql(i)_fetch_row(), not array, because array tries to grab the entire result, whereas row only grabs one result at a time.

mysql(i)_fetch_array() gets a row, not the entire result set.

alexrules01 wrote:Can anyone see the problem?

You're missing the ending quote for href, that's probably the issue.

alexrules01 wrote:Do I need to change everything to mysqli?

It's better not to mix mysql and mysqli. If you want to use mysqli stuff, only use mysqli stuff.

alexrules01 wrote:I am trying to query 2 things, playerid and position. That means I won't be able to use mysqli then huh?

Mysqli can handle that fine.

Winawer wrote:

Jackolantern wrote:As you can see, you must run the WHILE statement with mysql(i)_fetch_row(), not array, because array tries to grab the entire result, whereas row only grabs one result at a time.

mysql(i)_fetch_array() gets a row, not the entire result set.

How do you use it to fetch multiple results? It doesn't seem to work the same way as mysqli_fetch_row() does.

@Alex:

As Winawer was saying, it will work fine. What I mean is that mysqli cannot by default take 2 separate queries, separated with semi-colons, at once. Each individual query can query on as many things as you want, and can be as complex as you can write!

Jackolantern wrote:

Winawer wrote:

Jackolantern wrote:As you can see, you must run the WHILE statement with mysql(i)_fetch_row(), not array, because array tries to grab the entire result, whereas row only grabs one result at a time.

mysql(i)_fetch_array() gets a row, not the entire result set.

How do you use it to fetch multiple results? It doesn't seem to work the same way as mysqli_fetch_row() does.

@Alex:

As Winawer was saying, it will work fine. What I mean is that mysqli cannot by default take 2 separate queries, separated with semi-colons, at once. Each individual query can query on as many things as you want, and can be as complex as you can write!

Oh yea I get it, thanks for clearing it up! It wasn't working as I missed a ' thanks Winawer for pointing that out.

Jackolantern wrote: How do you use it to fetch multiple results? It doesn't seem to work the same way as mysqli_fetch_row() does.

It works the same way. Actually, mysql_fetch_array( $result, MYSQL_NUM ) works exactly the same way as mysql_fetch_row. Using MYSQL_ASSOC it works the same way as mysql_fetch_assoc(). And the same for mysqli_fetch_array with MYSQLI_ASSOC, MYSQLI_NUM and MYSQLI_BOTH.

Hmm...I wonder why I could never get it to work then.