Database relults help (select a specific row)

narked

Hello, I need some help with the query reluts...

For displayng the contents of a table I use the following code:

$sql = "select * from table";
$result = $db->query($sql);

for ($i=0 ; $i < count($items); $i++) {
$row = $db->getRow("SELECT id, name FROM table WHERE id='$items[$i]'");
if (DB::iserror($row)) { die($row->getMessage()); }

echo "$row[0], $row[1]";
}

This code makes a loop with all the rows in the table and then diplays the id and name.

My question is: how can I select a specific row of the query result.
for example I want to echo the id and name of the row number 5 of the result.

I am asking this because I need to display the contents in two rows (in a table). and I dont know how to make a loop for that.

kristianblom

If i undestand you right you want to build a complete array of all the rows you select from the database...

If so... do this.

$sql = "select * from table";
$result = $db->query($sql);

$rows = array();

for ($i=0 ; $i < count($items); $i++) {
$row = $db->getRow("SELECT id, name FROM table WHERE id='$items[$i]'");
if (DB::iserror($row)) { die($row->getMessage()); }

array_push($rows, $row); //This is new

echo "$row[0], $row[1]";
}

Don't now if you're familiar with the concept of a stack, but array_push adds an element to an array as you loop through your rows.

So in the end you'll have all your rows in one big array... And you can use it just like usual.

$id = $rows[4][0];
$name = $rows[4][1];

Elena_Mitovska

You can use PEAR function getALL to populate the reuslt array qwith ALL records from table, instead of retriveing records one by one with getRow().

$a_result = $db->getAll($sql);

after you can with PHP array $a_result whatever you want, for instance
print_r($a_entity[4]);
will output 5th record of table (records are counted starting from 0).

Elena_Mitovska

You can use PEAR function getALL to populate the reuslt array qwith ALL records from table, instead of retriveing records one by one with getRow().

$a_result = $db->getAll($sql);

after you can do with PHP array $a_result whatever you want, for instance
print_r($a_entity[4]);

will output 5th record of table (records are counted starting from 0).

narked

I have another questions:

1) If I use the array_push, It will return a multidimensional array.
let me know if Im correct:
count($rows) --> will return the number of rows?
count($rows[0]) --> will return the number of columns in te record #1?

2) If i use the $a_result = $db->getAll($sql);
can I write:
echo "$a_result[0][0]"; //to display the first column the forst row ??
if not, how can I count the number of rows in the table by using the getAll method.

PD- thanks for your answers. They helped me very much

🙂

kristianblom

To your first question: Yes!

//Just a question about this line...
count($rows[0]) --> will return the number of columns in te record #1?
//why do you want to count it... You know how many fields you slect from the database... hehe... But I guess it's ok to know as a general rule.

To the second... I have never used the pear module...

Elena_Mitovska

function getALL() created 2 deminsional array from recordset,
so when you use
$res=$db->getALL($sql);
you receive array $res:

Array
(
[0] => Array
(
[id] => 4
[type] => 345345

    )

[1] => Array
    (
        [id] => 1
        [type] => entity type
     )

[2] => Array
    (
        [id] => 6
        [type] => erte
       )

count($res) - amount of records in recordset
count($res[0]) - amount of columns in recordset (actually count($res[0])=count($res[1])=....=count($res[n]);

To apply to values in recordset you should use names of fields instead of indexes, i.e. echo $res[0]['id'] - to get the id from first row.

narked

Thanks Elena, It worked perfectly!

I used numeric values for the arrays ($res[0][0]), because using the name of the field ($res[0]['id']) didnt worked.

I needed to make a two column list and I wrote something like this:

$sql = "select * from table"
$res = $db->getALL($sql);
$number = count($res);

<table>

 for ($i; $i > $number; i=i+2) {
     <tr>
            <td>    <?= $res[$i][0] ?>         // record in forst column
            <td>    <?= $res[$i+1][0] ?>     // next record in 2nd column
     </tr>
  }               // repeat

</table>