CODEWITHSUNDEEP
mysqlphparrays
UnderMyWheel
UnderMyWheel

Reputation: 241

What's the best way to fetch an array

Alright, so I believe that there is a better way that I can fetch an array from the database, here's the code right now that I have.

$id = 1;
$userquery = mysql_query("SELECT * FROM login WHERE id='$id'");

while($row = mysql_fetch_array($userquery, MYSQL_ASSOC)) {
    $username = $row['username'];
    $password = $row['password'];
    $email = $row['email'];
}

Upvotes: 0

Views: 140

Answers (2)

Mohd Abdul Mujib
Mohd Abdul Mujib

Reputation: 13908

So If I am not wrong, you want a better way to get all the returned rows from mysql in a single statement, instead of using the while loop.

If thats the case, then I must say mysql_ drivers do not provide any such functionality, which means that you have to manually loop through them using foreach or while.

BUT, since mysql_ is already depricated, you are in luck! you can actually switch to a much better and newer mysqli_ or the PDO drivers, both of which DO actually have functions to get all the returned rows.

For mysqli_: mysqli_result::fetch_all

For PDO : PDOStatement::fetchAll

Eg.

mysqli_fetch_all($result,MYSQLI_ASSOC); 
// The second argument defines what type of array should be produced 
// by the function. `MYSQLI_ASSOC`,`MYSQLI_NUM`,`MYSQLI_BOTH`.

Upvotes: 1

low_rents
low_rents

Reputation: 4481

Like the comments already told you: PHP's mysql driver is deprecated. And you should use prepared statements and parameters.

for example in PDO your code would look something like this:

//connection string:
$pdo= new PDO('mysql:host=localhost;dbname=my_db', 'my_user', 'my_password');

//don't emulate prepares, we want "real" ones:
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

//use exception-mode if you want to use exception-handling:
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$id = 1;

//it's always better to strictly use backticks for db-names (db, tables, fields):
$sql = "SELECT * FROM `login` WHERE `id` = :id";

try
{
    //create your prepared statement:
    $stmt = $pdo->prepare($sql);

    //bind a parameter and explicitly use a parameter of the type integer (in this case):
    $stmt->bindParam(":id", $id, PDO::PARAM_INT);

    //execute the query
    $stmt->execute();
}
catch(PDOException $e)
{
    exit("PDO Exception caught: " . $e->getMessage());
}


while($row = $stmt->fetch(PDO::FETCH_ASSOC))
{
    $username = $row['username'];
    $password = $row['password'];
    $email = $row['email'];
}

here you go: your PHP-MySQL routine is save against SQL-injections now and no longer uses deprecated PHP-functions! it's kinda state of the art ;)

Upvotes: 0

Related Questions