PHP - function for mysql_fetch_assoc

Question

If I do this in PHP, it works fine and loops as expected:

$rs = mysql_query($sql);
while ($row = mysql_fetch_assoc($rs)){
    writeme("UserID: " . $row["UserID"]);
}

But I keep wanting to abstract this out into a function I have called ExecuteQuery:

function ExecuteQuery($sql){
$result = mysql_query($sql);

if ($result) {
    if($result != 1){
        return mysql_fetch_assoc($result); // return recordset
    }
}else{
    $message  = 'Invalid query: ' . mysql_error() . "<br>";
    $message .= 'Whole query: ' . $sql;
    echo $message;
    die();
}

}

This function works great in 2 out of 3 scenarios:

1- Works great for a query that returns 1 row, and I can access like this:

$rs = ExecuteQuery($sql);

$foo = $rs["UserID"];

2- Works great for a sql statement that returns no records, like an UPDATE or DELETE.

3- But when I try to get back a recordset that returns multiple records, and then loop through it, I get an infinite loop and my browser crashes. Like this:

$rs = ExecuteQuery($sql);
while ($row = $rs){
    writeme("UserID: " . $row["UserID"]);
}

How can I modify my while loop so it advances to each new record in the recordset and stops after the last record? I'm sure it's a dumb little thing, but I'm not expert with PHP yet. I'd really like my ExecuteQuery function to be able to handle all 3 scenarios, it's very handy.

Haven't seen a good solution yet...still hoping.

HerrimanCoder
– HerrimanCoder

2012-09-19 21:29:58 +00:00
Commented Sep 19, 2012 at 21:29 — HerrimanCoder
– HerrimanCoder, Commented Sep 19, 2012 at 21:29
didnt the solution I provided work?

Teena Thomas
– Teena Thomas

2012-09-20 12:41:22 +00:00
Commented Sep 20, 2012 at 12:41 — Teena Thomas
– Teena Thomas, Commented Sep 20, 2012 at 12:41

Teena Thomas · Accepted Answer · 2012-09-19 20:57:23Z

1

try foreach($rs as $row){ instead of while ($row = $rs){

edited Sep 19, 2012 at 20:57

answered Sep 19, 2012 at 20:43

Teena Thomas

5,2091 gold badge15 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

bobwienholt Over a year ago

This will not fetch the next row. It will simply iterate over the column values in a single row of results.

Teena Thomas Over a year ago

why wouldn't it fetch the next row, thats what foreach does, loops through the entire array. php.net/manual/en/control-structures.foreach.php

HerrimanCoder Over a year ago

I think you have the params backward. I get this: Warning: Invalid argument supplied for foreach()

Teena Thomas Over a year ago

$rs being the resultset. foreach($rs as $row){ writeme("UserID: " . $row["UserID"]); } doesn't work?

HerrimanCoder Over a year ago

OK, looks like the original post was edited. It used to be foreach($row as $rs). The new (edit) way sort of works, but it iterates way too many times, and I think it's actually looping every character of every string or something odd.

|

bobwienholt · Accepted Answer · 2012-09-19 20:52:56Z

0

mysql_fetch_assoc() only returns one row of the result. To get the next row, you need to call mysql_fetch_assoc() again. One thing you could do is have your ExecuteQuery function return an array of arrays:

$rows = array();
while ($row = mysql_fetch_assoc($result) !== false) {
   $rows[] = $row;
}
return $rows;

Also, you should not use the mysql_* functions as they are deprecated. Try using PDO or mysqli_* instead.

answered Sep 19, 2012 at 20:52

bobwienholt

17.6k3 gold badges43 silver badges48 bronze badges

Comments

Zathrus Writer · Accepted Answer · 2012-09-19 20:44:09Z

0

Don't use while, use foreach:

$rs = ExecuteQuery($sql);
foreach ($rs as $row){
    writeme("UserID: " . $row["UserID"]);
}

answered Sep 19, 2012 at 20:44

Zathrus Writer

4,3315 gold badges30 silver badges53 bronze badges

1 Comment

HerrimanCoder Over a year ago

I thought you were on to something, but now my looper goes about 20 times when there are only 2 records. I think it's looping every character in every string.

Collectives™ on Stack Overflow

PHP - function for mysql_fetch_assoc

3 Answers 3

6 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related