CODEWITHSUNDEEP
mysqlsorting
asgaines
asgaines

Reputation: 307

MySQL query involving multiple rows, access one based on max value

So I am trying to have a single script that accesses all values of a table based on a particular id. The script returns the values in an array using PHP:

Example:

// Select data from DB 
$query = 'SELECT * FROM experiences WHERE user_id = ' . $id; 
$result = mysqli_query($link, $query) or die (mysqli_error($link));

// Not sure this actually creates a 2D array...
$array = mysqli_fetch_array($result);

However, I realize that I need modified results for particular tasks, such as the row where a particular value is the highest.

How would I go about doing this, and does $array actually hold all the rows and respective fields?

Upvotes: 0

Views: 287

Answers (2)

Erik
Erik

Reputation: 2264

Assuming you have cleaned $id properly you can do this

// Select data from DB 
$query = 'SELECT * FROM experiences WHERE user_id = ' . $id; 
$result = mysqli_query($link, $query) or die (mysqli_error($link));

$data = array();
while($row = mysqli_fetch_array($result)) {
  $data[] = $row;
}

and $data will contain the 2D array you're looking for.

If you want the row where a particular value is highest I suggest either looking into ORDER BY or MAX() depending on your needs.

Upvotes: 1

xkeshav
xkeshav

Reputation: 54042

TRY

'SELECT * FROM experiences WHERE user_id ='.$id.' HAVING MAX(column_name)

OR

 "SELECT max(column_name), other column...
  FROM experiences 
  WHERE user_id =".(int)$id

Upvotes: 1

Related Questions