Searching single value from database

2 years ago

IdenticalWorlds
Member | 12
+
0
-

Hi!

I'm playing around with the Nette framework and I can't seem to find an answer to the following question.

What I'm trying to do is search in a table in my database for a single value from a column. I created a search form and searchFormSucceeded (code below) and I want to get the location from $_POST[‘location’] and then return a single column, like ‘temp’, but how this is done is unclear.

In the code below in the searchFormSucceeded method, you'll see $selection and $row, this was to try a few ways to do this, I've gone with $row and this returns the first row of the database, regardless of which location I enter. If I enter the location that's listed in the first row I get the same data as when I enter the location from any other row. I know this is because I'm missing some parameters in my code, but I don't know where as it has become rather confusing.

My database columns: ** id, location, countryCode, description, temp, requestDate, country.**

I created a presenter called SearchPresenter and this is the class:

<?php
class SearchPresenter extends BasePresenter {
  private $database;

  public function __construct(Nette\Database\Context $database) {
    $this->database = $database;
  }

  public function renderDefault() {
    $this->template->weather = $this->database
                                ->table('weather');
  }

  public function renderSearch() {
    $this->template->weather = $this->database->table('weather')
                                              ->where('location = ?', $location);
    $this->selection = $selection;
  }


  public function createComponentSearchForm() {
    $form = new Form;
    $form->addText('city', 'City:')
      ->setRequired();
    $form->addSubmit('search', 'Search');
    $form->onSuccess[] = [$this, 'searchFormSucceeded'];
    return $form;
  }

  public function searchFormSucceeded($form, $values) {
    $location = $_POST['city'];
    //$loc = $this->getParameter('location');
    $selection = $this->database->table('weather')->select('*');
    $row = $this->database->table('weather')->fetch();
      foreach ($row as $v) {
        echo $v;
      }
    return $selection;
  }
?>

2 years ago

CZechBoY
Member | 3408
+
0
-

Hi,
I don't understand where the $selection is retrieved. It seems like synax error?

This should be ok.
Selection::fetchAll() returns collection of all rows, ->fetch() returns only one row.

class SearchPresenter extends BasePresenter {
  private $database;

  private $selection;

  public function __construct(Nette\Database\Context $database) {
    $this->database = $database;
  }

  public function renderDefault() {
    $this->template->weather = $this->database
                                ->table('weather');
  }

  public function renderSearch() {
    $this->template->weather = $this->database->table('weather')
                                              ->where('location = ?', $location);
  }


  public function createComponentSearchForm() {
    $form = new Form;
    $form->addText('city', 'City:')
      ->setRequired();
    $form->addSubmit('search', 'Search');
    $form->onSuccess[] = [$this, 'searchFormSucceeded'];
    return $form;
  }

  public function searchFormSucceeded($form, $values) {
    $location = $values->city;
    //$loc = $this->getParameter('location');
    $selection = $this->database->table('weather')->select('*');
    $rows = $this->database->table('weather')->fetchAll();
      foreach ($rows as $v) {
        echo $v;
      }

    $this->selection = $selection;
  }

2 years ago

IdenticalWorlds
Member | 12
+
0
-

Hi,

Yes, ignore $selection, $row and return $selection. I should have removed them from my code before posting as I've gone with the variable $row to test it out. Ignore the name of $row as well, I don't want to return an entire row.

I don't want to fetchAll, though. I just want to fetch a single column.

The standard SQL would be something like SELECT ‘temp’ FROM weather WHERE location = ‘location’;

And ‘temp’ is just an example as well, it's just so I know how I can return a single column from the database rather than an entire row.

Let me know if my explanation is confusing, I've been breaking my head over this all morning.

Last edited by IdenticalWorlds (2017-06-07 15:13)

2 years ago

CZechBoY
Member | 3408
+
0
-

You want to return column? Column of first row? Or all rows with that one column?

2 years ago

IdenticalWorlds
Member | 12
+
0
-

Just a single column, but yes if the location exists multiple times in the table then all columns containing that location.

Let's say I have these columns:
id location temp
1 amsterdam 15
2 stockholm 12
3 paris 20

I want to catch the location, so for example ‘amsterdam’ from my form (which I did through $_POST[‘city’] and then return ‘temp’.

So if I fill in amsterdam in the form, it returns ‘15’ and if I fill in stockholm it returns ‘12’.

My code is really incomplete and definitely has some errors that you'll have to forgive, for now.

Last edited by IdenticalWorlds (2017-06-08 09:12)

2 years ago

IdenticalWorlds
Member | 12
+
0
-

I think there might be a problem in my renderSearch? I don't know where to add a ->where clause and what the syntax is in this scenario.

This echos only the ids from my table, no other columns and nothing related to what is in post.

<?php

  public function searchFormSucceeded($form, $values) {
    $location = $_POST['city'];
    $row = $this->database->table('weather')->fetchAll();
      foreach ($row as $v) {
        echo $v;
      }
  }

?>

So in short what I'm trying to do, is have a search bar that can search the database for the entered value and then return or echo one single column. In my example it works with temperatures. I know how to do this in PHP, Nette syntax is quite straightforward – yet I can't seem to find out how to do this. Seems to be missing in the documentation too.

2 years ago

CZechBoY
Member | 3408
+
+1
-

Sorry, I wanted to reply yesterday but…

public function searchFormSucceeded($form, $values) {
    $location = $values->city;
    $rows = $this->database->table('weather')->select('temp')->where('location = ?', $location);
      foreach ($rows as $row) {
        echo $row->temp;
      }
  }

2 years ago

IdenticalWorlds
Member | 12
+
0
-

Geez, really that simple huh? I tried something similar before trying what I posted. I came so close.

Anyway, thanks a lot for your help! That works perfectly.

2 years ago

CZechBoY
Member | 3408
+
0
-

2 years ago

IdenticalWorlds
Member | 12
+
0
-

One more question.

I want to show the results in .latte, with an echo it echos it at the top of the page. This might be a beginner question, so I hope you don't mind.

If I want to output $row->temp; in default.latte, how do I do that? I'd probably change echo $row->temp to return $row->temp, but how do I call it in default.latte? In a different project we created a foreach and then it returned all the results from the table, since now it's a single result and echoing it isn't ideal I need a different way.

2 years ago

IdenticalWorlds
Member | 12
+
0
-

CZechBoY wrote:

Yes, pls read https://doc.nette.org/…se-selection :-)

I couldn't find what I needed there, unfortunately. There is a bit of a gap in the documentation, a lot of things are there, but then a lot of little things aren't. Though most are easy to figure out, but other things aren't as easy. Like my last comment, I'm sure the solution is easy, but I can't find documentation on it.

2 years ago

matopeto
Member | 400
+
0
-
public function searchFormSucceeded($form, $values) {
    $location = $values->city;
    $rows = $this->database->table('weather')->select('temp')->where('location = ?', $location);
    $this->template->rows = $rows;
  }

And in latte

{foreach $rows as $row}
 {$row->temp}
{/foreach}

2 years ago

IdenticalWorlds
Member | 12
+
0
-

Hi matopeto,

I tried that, but the error “Undefined variable: rows” comes up when I do that. Judging from the tutorial, I need to add a render method?

2 years ago

IdenticalWorlds
Member | 12
+
0
-

The following solved it.

{block content}
{control searchForm}
{ifset $rows}{foreach $rows as $row}
  {$row->temp}
{/foreach}
{/ifset}
{/block content}

2 years ago

CZechBoY
Member | 3408
+
0
-

but remember that the success method is called only if form is submitted. its better to save it in query string. when you paginate, you only add this parameter.