CODEWITHSUNDEEP
phpmysqlsqlmysqli
Algernop K.
Algernop K.

Reputation: 477

Save the first three results in a while loop with given variable names?

I wanna save the first three results from my SQL-query in the variables $row1, $row2, and $row3 and return them to ajax. The SQL-query sorts the order so that the first three looped will be the ones I wanna save.

Thing is, I can only figure out how to save one of them in $row1, but making the other two individual variables equal ex.nu and lol.de I can't.

id | url
---+------------
 1 | www.hi.com //Save
 2 | www.ex.nu  //Save
 3 | www.lol.de //Save
 4 | www.mo.ae  //Skip
//DB-setup

-

while ($row = mysqli_fetch_array($result)) {
    $row1 = $row['url']; //Works
    //$row2 = $row['url']; Second result, tried using [1]
    //$row3 = $row['url']; Third result, tried using [2]
}

echo 
  json_encode(array(
    'row1' => $row1,
    'row2' => $row2,
    'row3' => $row3
  ))
;

Simply, collect the first three items and save them in the given variables. Is there a simple solution to doing this?

Any help/tips/links will be much appreciated.

Upvotes: 2

Views: 88

Answers (2)

Kamrul Khan
Kamrul Khan

Reputation: 3360

If you want to store them to $row1, $row2, $row3 you need to use variable variables. Try the below:

$query = "select * from url_list order by url asc limit 3";
$result = mysql_query($query);
$i = 1;
while ($data = mysql_fetch_array($result)) {
   $variable = 'row'.$i++;
   $$variable = $data['url'];
}

echo 
  json_encode(array(
     'row1' => $row1,
     'row2' => $row2,
     'row3' => $row3
  ));

Upvotes: 3

Alexander Guz
Alexander Guz

Reputation: 1364

You can do it in several ways.

Use LIMIT in SQL query

$query = "SELECT ... LIMIT 3";
$result = <execute_query>;

$rows = [];
$rowIndex = 0;
while ($row = mysqli_fetch_array($result)) {
    $rows[sprintf("row%d", ++$rowIndex)] = $row['url'];
}

echo json_encode($rows);

Limit the number of rows in code

$query = "SELECT ... "; // without limit
$result = <execute_query>;

$rows = [];
$rowIndex = 0;
$limit = 3;
while ($row = mysqli_fetch_array($result)) {
    if (++$rowIndex >= $limit) {
        break;
    }

    $rows[sprintf("row%d", $rowIndex)] = $row['url'];
}

echo json_encode($rows);

Use LIMIT and array_column

This variant will work, if you don't care about the keys in the resulting array.

$query = "SELECT ... LIMIT 3";
$result = <execute_query>;

$rows = mysqli_fetch_all($result);
$urls = array_column($rows, 'url');

echo json_encode($urls);

If you want to place the URLs in variables, then after you get the array just do:

// works only with numerical arrays
list($row1, $row2, $row3) = $urls;

Upvotes: -1

Related Questions