Splitting a php array from mysql query

Question

I have been working on this for hours on end trying to split the resultant mysql query into their usable variables and I just don't know what I am doing incorrectly. I no longer get the resource ID# but now all I get is "array" from the variables when I print_r. I am trying to get the 3 fields from the select statement in their own array and use the values. I have been pulling my hair out trying to get this to create usable data. Any help is greatly appreciated as I feel I could spend many, many more hours tinkering and just seem to be on the wrong track.

    $Query = "SELECT guardian.Email, child.CFName,  guardian.FName
    FROM child
    INNER JOIN uaccount ON child.ANum = uaccount.ANum
    INNER JOIN guardian ON guardian.ANum = uaccount.ANum
    WHERE uaccount.ANum = '$ANum';";

    $result = mysql_query($query);
    $test = mysql_fetch_array($result, MYSQL_BOTH);
    print_r('this is the test '.$test."<br/>");
    while($row = mysql_fetch_assoc($test, MYSQL_BOTH))
    {
        print_r('this is the row '.$row."<br/>");
        foreach($row as $rows)
            {
                $info = mysql_fetch_assoc($rows, MYSQL_BOTH);
                $Email = $info['0'];
                $FName = $info['1'];
                $CFName = $info['2'];

            }
    }

Thank you very much for your assistance thus far. With a combination of everyone's help I have been able to pull the values from the first level of the array with the following code :

            $query = "SELECT guardian.Email, guardian.FName, child.CFName
    FROM guardian
    INNER JOIN uaccount ON child.ANum = uaccount.ANum
    INNER JOIN guardian ON guardian.ANum = uaccount.ANum
    WHERE uaccount.ANum = '$ANum';";

    $result = mysql_query($query);

    $info = mysql_fetch_array($result);
    $Email = $info['0'];
    $FName = $info['1'];
    $CFName = $info['2'];

However, that is only returning me 1/3rd of the data as each account has 3 guardian email addresses related to it. Where I should be getting about 2000 rows returned, I am only getting 670. That is why I was nesting another layer inside the orginal posting and why I was attempting to pull a fetch_assoc from itself. If you cannot pull an array from itself, how do you "de-nest" so to speak? Any and all help is greatly appreciated.

That's not how you do print_r. Change it to print_r($rows); (or echo '<pre> . print_r($rows, TRUE) . </pre>'; — Amal
– Amal, Commented Oct 21, 2013 at 15:22

Oleg Belousov · Accepted Answer · 2013-10-21 15:31:59Z

$emQuery = "SELECT guardian.Email, child.CFName,  guardian.FName
FROM child
INNER JOIN uaccount ON child.ANum = uaccount.ANum
INNER JOIN guardian ON guardian.ANum = uaccount.ANum
WHERE uaccount.ANum = '$ANum'";

$result = mysql_query($query);
$Email = array();
$FName = array();
$CFName = array();
$test = mysql_fetch_array($result, MYSQL_BOTH);
print_r('this is the test '.$test."<br/>");
while($row = mysql_fetch_assoc($test, MYSQL_BOTH))
{
    echo 'this is the row ';
    print_r($row);
    echo '<br/>';
            //$info = mysql_fetch_assoc($rows, MYSQL_BOTH);
            $Email[] = $info['Email'];
            $FName[] = $info['FName'];
            $CFName[] = $info['CFName'];


}

To the best of my understanding this is what you are trying to do.

Some highlights: print_r can only get a reference to a valid php array, no strings included. mysql_fetch_assoc fetches a line out of a result set referenced by a variable, and then moves the reference to point to the next row. You cannot call this function on a the result of it self, as it is not valid syntax.

In general, you'll be better off using PDO or mysqli_ functions at least, as it is by far more secure, by allowing you to use parameter binding instead of just using use input as part as your SQL.

immulatin · Accepted Answer · 2013-10-21 15:25:13Z

You should just loop through the rows of your result once like so:

$result = mysql_query($query);
while ($row = mysql_fetch_assoc($result)) {
    // print_r($row); Debug here if you want.
    $Email = $row['Email'];
    $FName = $row['FName'];
    $CFName = $row['CFName'];
}

Note: You were providing a second parameter to mysql_fetch_assoc() which only takes one parameter (the result from a query). See the doc here.

mysql_fetch_array() takes another parameter that specifies what type of array to return.

DanMan · Accepted Answer · 2013-10-21 20:17:53Z

Generalized PDO example out of a project of mine ($sql would be your $Query string):

$qry = $pdo->query($sql);
$all=array();
$idx=0;
while($fields=$qry->fetch( 'PDO::FETCH_ASSOC' )){
    $all[key($fields)][$idx]=$fields[key($fields)];
    while(next($fields)!==false){
        $all[key($fields)][$idx]=$fields[key($fields)];
    }
    $idx++;
}

The $all variable will hold an array which in turn holds an array for each of the columns you've selected.

Like that you can do neat things like array_combine($all['Email'],$all['Fname']) and you'd get an array where the key is the Email column and the value would consist of the Fname column.

Collectives™ on Stack Overflow

Splitting a php array from mysql query

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related