How to connect to database and row rows from table?

Discussion in 'Subrion Open Source CMS Core' started by tomukasteris, Feb 23, 2015.

  1. tomukasteris

    tomukasteris New Member

    Hello,

    I have created table "free-directories" in database.

    Columns in "free-directories": "id", "directory_name" and "url".

    How I can connect to database to table "free-directories" and row all rows from table in my custom page test.tpl?

    What code I need in test.php and test.tpl?
  2. Vasily_B.

    Vasily_B. Project Manager

    Hi,

    In order to work with your DB we created a good set of method that simplify almost everything. You need to use iaDb class to work with your data. You can see the description of your methods in includes/classes/ia.base.db.php interface. They are properly documented, and all the method params have descriptions.

    Your case would be:
    PHP:

    $rows= $iaCore->iaDb->all(iaDb::ALL_COLUMNS_SELECTION, '', 0, null, 'free-directories');
     
    where method params are:
    1. iaDb::ALL_COLUMNS_SELECTION can be changed to '*' = fields list
    2. '' - empty string - condition, no condition for SELECT
    3. 0 - start position for SELECT
    4. null - limit - number of records to be selected
    5. table name, prefix auto added

    Please check ia.core.mysqli.php for all() function params. Your table should also have the same prefix like the other subrion tables.
  3. tomukasteris

    tomukasteris New Member

    Can't get working.
    Inserted this your code below to test.php. Added prefix for table in database.

    PHP:
    $rows= $iaCore->iaDb->all(iaDb::ALL_COLUMNS_SELECTION, '', 0, null, 'free-directories');
    I think that Smarty is not reading. Very complicated here.

    I have read book PHP & MySQL in easy steps and there is very clear how to connect to database, how to row rows and do many things, but here I don't understand.
  4. tomukasteris

    tomukasteris New Member

    What difference between $iaView->iaSmarty->assign('all_items', $rows); and $iaView->assign('all_items', $rows); ?
  5. Vasily_B.

    Vasily_B. Project Manager

    my code is not related to smarty in any way, as the question was - how to get rows from database, you got the answer.. :) We have debug functions you can use.

    So when you got the records from your table, they are assigned to a php var - $rows. I would recommend to use _v() function to print what you got. If you need to pass this to template and render it there you need to add the following line:
    $iaView->assign('rows', $rows);

    In your tpl file you can also use _v function - {_v($rows)}

    That's it.
  6. tomukasteris

    tomukasteris New Member

    What code would be to display "url" column all records from table "free-directories"? I want to get all url's and then to assign to code count Alexa rank.
  7. Vasily_B.

    Vasily_B. Project Manager

    Code (Text):

    {foreach $rows as $row}
    {$row.url}
    {/foreach}
     
  8. tomukasteris

    tomukasteris New Member

    I meek in php. I need to get all url's from url column and then do math than to display in .tpl
  9. Vasily_B.

    Vasily_B. Project Manager

    once you get table rows in php you have them in array - basic operations like foreach can help. you should always debug the values you have.

    for example _v($rows); in php will show it's an array. arrays can be cycled via foreach
    PHP:

    foreach ($rows as $row)
    {
    echo $row['url'];
    }
     
  10. tomukasteris

    tomukasteris New Member

    I have managed to pull all URLs from database and print out to test.tpl.

    My question is how then all these URLs in test.php I can to assign accordingly to this:
    PHP:
    $url = $row['url'];
        $xml = simplexml_load_file('http://data.alexa.com/data?cli=10&dat=snbamz&url='.$url);
        $rank = isset($xml->SD[1]->POPULARITY)?$xml->SD[1]->POPULARITY->attributes()->TEXT:0;
        $web = (string)$xml->SD[0]->attributes()->HOST;
        $backlink = (int)$xml->SD[0]->LINKSIN->attributes()->NUM;
    With this code above it prints out just the last URL's Alexa Rank.

    My test.php looks like this:
    PHP:
    <?php
     
    if (iaView::REQUEST_HTML == $iaView->getRequestType())
    {
        $rows = $iaCore->iaDb->all('`url`', '', 0, null, 'free_directories');

        foreach ($rows as $row);
        {
            $url = $row['url'];
            $xml = simplexml_load_file('http://data.alexa.com/data?cli=10&dat=snbamz&url='.$url);
            $rank = isset($xml->SD[1]->POPULARITY)?$xml->SD[1]->POPULARITY->attributes()->TEXT:0;
            $web = (string)$xml->SD[0]->attributes()->HOST;
            $backlink = (int)$xml->SD[0]->LINKSIN->attributes()->NUM;

            include('front/pr.php');
            $pr = pagerank($row['url']);
        }
     
        $iaView->assign('directories', $rows);
        $iaView->assign('url', $url);
        $iaView->assign('web', $web);
        $iaView->assign('rank', $rank);
        $iaView->assign('linksin', $backlink);
        $iaView->assign('page_rank', $pr);
     
        $iaView->display('test');
    }
  11. Vasily_B.

    Vasily_B. Project Manager

    In your cycle you try to get all these values, and pass them to smarty after your cycle. It won't work as your $web, $rank, $backlink vars will only have the latest value.

    Instead you should do something like this:
    PHP:
    include IA_INCLUDES . 'utils/pr.php';
    foreach ($rows as &$row)
    {
           $xml = simplexml_load_file('http://data.alexa.com/data?cli=10&dat=snbamz&url=' . $url);

           $row['rank'] = isset($xml->SD[1]->POPULARITY) ? $xml->SD[1]->POPULARITY->attributes()->TEXT : 0;
           $row['web'] = (string)$xml->SD[0]->attributes()->HOST;
           $row['backlink'] = (int)$xml->SD[0]->LINKSIN->attributes()->NUM;
           $row['pagerank'] = pagerank($row['url']);
    }
    $iaView->assign('directories', $rows);

    $iaView->display('test');
    1. You include pr.php one time instead of including it for each link. Please note correct URL for inclusion.
    2. You iterate all records
    3. Your iterated value is assigned by reference (&) so you update the same array during iteration.
  12. tomukasteris

    tomukasteris New Member

    This code above works fine. Thanks.

    How I can make Alexa Rank and Page Rank make sortable (ASC|DESC) for user? And how to print out a number of records (to display number) from my test table "free_directories"?
  13. Vasily_B.

    Vasily_B. Project Manager

Share This Page