Store MySQL results in seperate PHP variables

Question

I have a MySQL query that returns 3 random rows whenever it is ran. I want to store each row in an array, with each column of the row a different part of the array. Hopefully Im being clear. Here is where Im at so far.

$result = mysql_query("SELECT NAME,AUTHOR,CITY FROM TESTIMONIALS ORDER BY RAND() LIMIT 3");
$row = mysql_fetch_array($result);

The problem with this is it stores EVERYTHING in $result, and I do not know how to reference different columns once it is there. I'm doing this so I can ultimately print the specified random entries that I would like into 9 different DIVs.

$result isn't the data from the query. it's simply a result handle. you don't get actual data until you do a fetch call. — Marc B
– Marc B, Commented Oct 7, 2012 at 22:54

thelastshadow · Accepted Answer · 2012-10-07 22:21:18Z

2

Try:

$array = Array();

$result = mysql_query("SELECT NAME,AUTHOR,CITY FROM TESTIMONIALS ORDER BY RAND() LIMIT 3");
while ($row = mysql_fetch_array($result)) {
    $array[] = $row;
}

answered Oct 7, 2012 at 22:21

thelastshadow

3,6624 gold badges37 silver badges37 bronze badges

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

1 Comment

Rick Calder Over a year ago

To expand a bit you would then be able to do the following echo $array['AUTHOR']; for example.

Terry Seidler · Accepted Answer · 2012-10-07 22:25:09Z

1

You can use $row['COLUMNAME'] to get the column value.
Note that COLUMNAME will be case-sensitive.

For example:

$result = mysql_query("SELECT NAME,AUTHOR,CITY FROM TESTIMONIALS ORDER BY RAND() LIMIT 3");
while($row = mysql_fetch_array($result)) {
    echo 'Name: ' . $row['NAME'] . '<br/>';
    echo 'Author: ' . $row['AUTHOR'] . '<br/>';
}

Also, read the big red notice on the php.net manual page:
Use of this extension is discouraged.
Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information.

answered Oct 7, 2012 at 22:25

Terry Seidler

2,04316 silver badges28 bronze badges

Comments

anditpainsme · Accepted Answer · 2012-10-07 22:51:53Z

Consider using prepared statements as mysql_ is being deprecated. It is also susceptible to SQL injection if you are careless or inexperienced and don't escape your query strings properly.

Here is the solution using mysqli_:

$link = mysqli_connect("localhost", "user", "password", "database") or die(mysqli_error($link));
$stmt = mysqli_stmt_init($link);
$query = 'SELECT NAME,AUTHOR,CITY FROM TESTIMONIALS ORDER BY RAND() LIMIT 3';
$arrTestimonials = array();   

if (mysqli_stmt_prepare($stmt, $query)) {
    mysqli_stmt_bind_result($stmt, $out_name, $out_author, $out_city);
    mysqli_stmt_execute($stmt);

    while (mysqli_stmt_fetch($stmt)) {
        $arrTestimonials[]['name'] = $out_name;
        $arrTestimonials[]['author'] = $out_author;
        $arrTestimonials[]['city'] = $out_city;
    }
} // error preparing statement

The array will be structured as follows:

Array
(
    [0] => Array
        (
            [name] => Name 1
            [author] => Author 1
            [city] => City 1
        )

    [1] => Array
        (
            [name] => Name 2
            [author] => Author 2
            [city] => City 2
        )

    [2] => Array
        (
            [name] => Name 3
            [author] => Author 3
            [city] => City 3
        )

)

Collectives™ on Stack Overflow

Store MySQL results in seperate PHP variables

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related