CODEWITHSUNDEEP
phpmysqlarrays
r0skar
r0skar

Reputation: 8706

MYSQL - Select specific value from a fetched array

I have a small problem and since I am very new to all this stuff, I was not successful on googling it, because I dont know the exact definitions for what I am looking for.

I have got a very simple database and I am getting all rows by this:

while($row = mysql_fetch_array($result)){
    echo $row['id']. " - ". $row['name'];
    echo "<br />";
}

Now, my question is: how do I filter the 2nd result? I thought something like this could work, but it doesnt:

$name2= $row['name'][2];

Is it even possible? Or do I have to write another mysql query (something like SELECT .. WHERE id = "2") to get the name value in the second row?

What I am trying to is following:

-get all data from the database (with the "while loop"), but than individually display certain results on my page. For instance echo("name in second row") and echo("id of first row") and so on.

Upvotes: 5

Views: 29890

Answers (9)

IT-Dan
IT-Dan

Reputation: 565

I used the code from the answer and slightly modified it. Thought I would share.

$result = mysql_query( "SELECT name FROM category;", db_connect() );
$myrow = array();
while ($myrow[]  = mysql_fetch_array( $result, MYSQLI_ASSOC )) {}
$num = mysql_num_rows($result);

Example usage

echo "You're viewing " . $myrow[$view_cat]['name'] . "from a total of " . $num;

Upvotes: 0

Raz
Raz

Reputation: 605

If you always want the second row, no matter how many rows you have in the database you should modify your query thus:

SELECT * FROM theTable LIMIT 1, 1;

See: http://dev.mysql.com/doc/refman/5.5/en/select.html

Upvotes: 0

JJJ
JJJ

Reputation: 33153

If you would rather work with a full set of results instead of looping through them only once, you can put the whole result set to an array:

$row = array();

while( $row[] = mysql_fetch_array( $result ) );

Now you can access individual records using the first index, for example the name field of the second row is in $row[ 2 ][ 'name' ].

Upvotes: 12

Mick
Mick

Reputation: 695

If you want to be able to use 2 consecutive results in one loop, you can store the results of the first loop, and then loop through.

$initial = true;
$storedId = '';

while($row = mysql_fetch_array($result)) {

  $storedId = $row['id'];

  if($initial) {

    $initial = false;
    continue;
  }

  echo $storedId . $row['name'];
}

This only works for consecutive things though.Please excuse the syntax errors, i haven't programmed in PHP for a very long time...

Upvotes: 0

Frederik.L
Frederik.L

Reputation: 5620

Yes, ideally you have to write another sql query to filter your results. If you had :

SELECT * FROM Employes

then you can filter it with :

SELECT * FROM Employes WHERE Name="Paul";

if you want every names that start with a P, you can achieve this with :

SELECT * FROM Employes WHERE Name LIKE "P%";

The main reason to use a sql query to filter your data is that the database manager systems like MySQL/MSSQL/Oracle/etc are highly optimized and they're way faster than a server-side condition block in PHP.

Upvotes: 0

javi_moralesf
javi_moralesf

Reputation: 37

$result = mysql_query("SELECT * FROM ... WHERE 1=1");
while($row = mysql_fetch_array($result)){
/*This will loop arround all the Table*/
    if($row['id'] == 2){
    /*You can filtere here*/
    }

    echo $row['id']. " - ". $row['name'];
    echo "<br />";
}

Upvotes: 2

Nikoloff
Nikoloff

Reputation: 4160

Depends on what you want to do. mysql_fetch_array() fetches the current row to which the resource pointer is pointing right now. This means that you don't have $row['name'][2]; at all. On each iteration of the while loop you have all the columns from your query in the $row array, you don't get all rows from the query in the array at once. If you need just this one row, then yes - add a WHERE clause to the query, don't retrieve the other rows if you don't need them. If you need all rows, but you wanna do something special when you get the second row, then you have to add a counter that checks which row you are currently working with. I.e.:

$count = 0;
while($row = mysql_fetch_array($result)){
    if(++$count == 2)
    {
        //do stuff
    }
}

Upvotes: 0

vikas
vikas

Reputation: 735

This While loop will automatically fetch all the records from the database.If you want to get any other field then you will only need to use for this.

Upvotes: 0

Riz
Riz

Reputation: 10246

$counter = 0;
while($row = mysql_fetch_array($result)){
    $counter++;

    if($counter == 2){
        echo $row['id']. " - ". $row['name'];
        echo "<br />";
    }
}

Upvotes: 0

Related Questions