php function to return sql results that contain arrays, as an array

Question

So im having a problem (obviously). I have the following MySQL table data

7   USER1       1,1,1,10,1      The Guys Team   8,7,13,14,16
8    USER1      1,1,1,10,1  The Girls Team  7,12,15
10  USER1       1,1,1,10,1  Dog Team    8,7,14,15

I wrote a function to retrieve the data, and return it.

function ShowSetTeams($coach){
    $result = mysql_query("SELECT * FROM `teams` WHERE coach = '$coach'") or trigger_error(mysql_error()); 
    while($row = mysql_fetch_array($result)){ 
        foreach($row AS $key => $value) { $row[$key] = stripslashes($value); } 
            $id = $row['id'];
            $teamname = $row['teamname'];
            $team = $row['team'];
            $event = $row['event'];
            $push .= array($id, $teamname, $team, $event);
    }
    return $push;
}

When i call the function, as below

$info = ShowSetTeams("USER1");

I get this

ArrayArrayArray

I tried echoing $info[0], $info[1], and $info[2], but get this

Arr

So each line in the info array, is the result array. I should be able to do $info[0][0] and get the first ID value, from the first result right?

Fatal error: Cannot use string offset as an array

Im at a loss. How can i get to each of the values of the returned arrays? And more to the point, how could i run a foreach operation on them such as

foreach( $info as $key => $value){
$key[0] //ID
$key[1] //TEAMNAME
$key[2] //TEAM
$key[3] //EVENT
}

Greg · Accepted Answer · 2009-10-12 08:38:27Z

6

You're using string concatenation instead of array notation:

$push[] = array($id, $teamname, $team, $event);

You should also initialise $push = array(); before you start using it.

You're also doing a lot of extra work... you could just do:

function ShowSetTeams($coach)
{   
    $push = array();
    $result = mysql_query("SELECT id, teamname, team, event FROM `teams` WHERE coach = '$coach'") or trigger_error(mysql_error());
    while($row = mysql_fetch_array($result, MYSQL_NUM))
    {
        // I doubt you actually need to run stripslashes on your data...
        $row = array_map('stripslashes', $row);
        $push[] = $row;
    }

    return $push;
}

Unless you have to, I wouldn't use re-key it to a numerically indexed array either - you're just making it harder to understand in your later code. Use mysql_fetch_assoc() to do this

edited Oct 12, 2009 at 8:38

answered Oct 12, 2009 at 7:58

Greg

323k55 gold badges378 silver badges338 bronze badges

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

5 Comments

mrpatg Over a year ago

I will explore this further later on, but thanks for the advice. Too late to switch it all now though.

markus Over a year ago

hi greg, why do you use the array_values? wouldn't that de-assoc the array? it seems he wants the info as an associative array. so why not just $push[] = $row?

Greg Over a year ago

@tharkun I used array_values because that is what @Patrick's code does

Gumbo Over a year ago

@Greg: But with mysql_fetch_array you will have all values twice.

Greg Over a year ago

I thought he was using assoc... guess I'm so used to it I just read it everywhere heh. Fixed

smack0007 · Accepted Answer · 2009-10-12 08:01:09Z

1

I think they problem is with the line:

 $push .= array($id, $teamname, $team, $event);

That treats $push as a string and concatenates an array which gets turned into a string. Try:

 $push[] = array($id, $teamname, $team, $event);

answered Oct 12, 2009 at 8:01

smack0007

11.4k8 gold badges45 silver badges48 bronze badges

Comments

Gumbo · Accepted Answer · 2009-10-12 08:01:21Z

1

You’re using a string concatenation and assignment operator .= that will convert your arrays into strings. Try the array push operator $array[] instead:

function ShowSetTeams($coach) {
    $result = mysql_query("SELECT * FROM `teams` WHERE coach = '$coach'") or trigger_error(mysql_error()); 
    $array = array();
    while ($row = mysql_fetch_assoc($result)) {
        $array[] = array(stripslashes($row['id'], stripslashes($row['teamname'], stripslashes($row['team']), stripslashes($row['event']));
    }
    return $array;
}

answered Oct 12, 2009 at 8:01

Gumbo

657k112 gold badges792 silver badges852 bronze badges

Comments

markus · Accepted Answer · 2009-10-12 08:06:34Z

1

Don't concatenate the $push but use

$push[] = array();

return $push;

A few other remarks:

your database schema is not properly normalised! you should not have strings of userIDs stored in your table but have a reference many-to-many table between the teams table and the players table.
you should never (almost never) use the * selector in queries. You build yourself traps with that. Instead indicate the exact columns you want to retrieve.
you could get the same information without having to put together the $push array yourself. If the database columns are properly named you can use a fetch_assoc and just do $push[] = $row

edited Oct 12, 2009 at 8:06

answered Oct 12, 2009 at 7:58

markus

40.8k23 gold badges100 silver badges143 bronze badges

Comments

Anton · Accepted Answer · 2009-10-12 08:12:22Z

1

You should use mysql_fetch_assoc() instead of mysql_fetch_array()
You should define $push as array by $push = array(); before "while"
You should use $push[] = ... instead of $push .= ...

answered Oct 12, 2009 at 8:12

Anton

262 bronze badges

Collectives™ on Stack Overflow

php function to return sql results that contain arrays, as an array

5 Answers 5

5 Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

5 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related