About the mysql_query -> mysql_fetch_array() procedure

Question

Sample code:

$infoArray = array();
require_once("connectAndSelect.php");
// Connects to mysql and selects the appropriate database
$sql = "SOME SQL";
if($results = mysql_query($sql))
{
    while($result = mysql_fetch_array($results, MYSQL_ASSOC))
    {
        $infoArray[] = $result;
    }
}
else
{
    // Handle error
}
echo("<pre>");
print_r($infoArray);
echo("</pre>");

In this sample code, I simply want to get the result of my query in $infoArray. Simple task, simple measures... not. I would have enjoyed something like this:

$sql = "SOME SQL";
$infoArray = mysql_results($sql);

But no, as you can see, I have two extra variables and a while loop which I don't care for too much. They don't actually DO anything: I'll never use them again. Furthermore, I never know how to call them. Here I use $results and $result, which kind of represents what they are, but can also be quite confusing since they look so much alike. So here are my questions:

Is there any simpler method that I don't know about for this kind of task?
And if not, what names do you give those one-use variables? Is there any standard?

The while looping is ensure you will fetch all results return by mysql IF your previous sql return more than one match. So you cannot skip them, otherwise is incomplete — ajreal
– ajreal, Commented Dec 1, 2010 at 17:21

drewster · Accepted Answer · 2010-12-01 17:16:33Z

1

The while loop is really only necessary if you are expecting multiple rows to be returned. If you are just getting one row you can simply use mysql_fetch_array().

$query = "SOME SQL";
$result = mysql_query($query);
$row = mysql_fetch_array($result);

For single line returns is the standard I use. Sure it is a little clunky to do this in PHP, but at least you have the process broken down into debug-able steps.

answered Dec 1, 2010 at 17:16

drewster

531 silver badge4 bronze badges

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

Comments

Benny · Accepted Answer · 2010-12-01 17:18:35Z

0

Use PDO:

<?php

/*** mysql hostname ***/
$hostname = 'localhost';

/*** mysql username ***/
$username = 'username';

/*** mysql password ***/
$password = 'password';

try {
  $dbh = new PDO("mysql:host=$hostname;dbname=mysql", $username, $password);
  $sql = "SELECT * FROM myTable";
  $result = $dbh->query($sql)
  //Do what you want with an actual dataset
}
catch(PDOException $e) {
  echo $e->getMessage();
}
?>

answered Dec 1, 2010 at 17:18

Benny

3,9378 gold badges49 silver badges82 bronze badges

Comments

prodigitalson · Accepted Answer · 2010-12-01 17:27:46Z

0

Unless you are legacied into it by an existing codebase. DONT use the mysql extension. Use PDO or Mysqli. PDO being preferred out of the two.

Your example can be come a set of very consise statements with PDO:

// create a connection this could be done in your connection include
$db = new PDO('mysql:host=localhost;dbname=your_db_name', $user, $password);

// for the first or only result
$infoArray = $db->query('SOME SQL')->fetch(PDO::FETCH_ASSOC);

// if you have multiple results and want to get them all at once in an array
$infoArray = $db->query('SOME SQL')->fetchAll(PDO::FETCH_ASSOC);

   // if you have multiple results and want to use buffering like you would with mysql_result
   $stmt =  $db->query('SOME SQL');
   foreach($stmt as $result){
     // use your result here
   }

However you should only use the above when there are now variables in the query. If there are variables they need to be escaped... the easiest way to handle this is with a prepared statement:

$stmt = $db->prepare('SELECT * FROM some_table WHERE id = :id');
$stmt->execute(array(':id' => $id));

// get the first result
$infoArray = $stmt->fetch(PDO::FETCH_ASSOC);

// loop through the data as a buffered result set
while(false !== ($row = $stmt->fetch(PDO::FETCH_ASSOC))){
   // do stuff with $row data
}

edited Dec 1, 2010 at 17:27

answered Dec 1, 2010 at 17:13

prodigitalson

60.6k10 gold badges106 silver badges118 bronze badges

1 Comment

Shawn Over a year ago

I've never heard of PDO. Could you provide a link?

Collectives™ on Stack Overflow

About the mysql_query -> mysql_fetch_array() procedure

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related