MYSQL View_data

[eluser]mr daniel[/eluser]
Hello. I need to query data from mySQL database and have it appear on my website.

Currently I have,

Code:

$this->view_data['short'] = $short;
   $this->view_data['var1'] = $var1;
   $this->view_data['url'] = anchor($url);
   $this->view_data['var3'] = $var3;
   $this->load->view('view_redirect', $this->view_data);

Where var1, url and var3 are all 'varchar' within MYSQL database.

I need to show the time and date (both different fields) from my SQL database.

Date is 'date' datatype and time is 'time datatype.

I have been trying to add

Code:

$this->view_data['time'] = $time;

but this doesn't work, is there anyone that can help?

[eluser]InsiteFX[/eluser]
Load the database
Then you need to create a model to read the database record that you need the data from.

But you need to show more information for us to help you out.

[eluser]mr daniel[/eluser]
What info would you like? New to this, sorry.

[eluser]InsiteFX[/eluser]
Your model controller etc, we need to see what your doing to see if we can fix it for you.
Your database query to get the information.

[eluser]mr daniel[/eluser]
This is my model controller:

Code:

<?php

class Main_model extends CI_Model {

function Main_model()
{

  parent::__construct();

}


function store_url($full_url, $page_title)
{
  // create the short URL part
  $short_url = $this->create_url_part();
  $this->load->library('user_agent');
  $ip_address = $this->input->ip_address();
  
  $query_str = "INSERT INTO urls (full_url, short_url, ip_address, page_title, date, time) VALUES
         ( ?, ?, ?, ?, now(), now() )";
  
  $this->db->query($query_str, array($full_url, $short_url, $ip_address, $page_title));
  
  return $short_url;
}


function get_full_url($short_url)
{
  $query_str = "SELECT full_url FROM urls WHERE short_url = ?";
  
  $result = $this->db->query($query_str, $short_url);
  
  if ($result->num_rows () ==1)
  {
   return $result->row(0)->full_url;
  }
  else
  {
   return false;
  }
}


function create_url_part()
{
  $this->load->helper('string');

  $is_unique = false;
  do {
   $url_part = random_string('alpha', 4);
  
   $is_unique = $this->check_unique_url_part($url_part);
  } while (! $is_unique);

  return $url_part;
}

function check_unique_url_part($url_part)
{
  $query_str = "SELECT id FROM urls WHERE short_url = ?";
  
  $result = $this->db->query($query_str, $url_part);
  
  if($result->num_rows() > 0)
  {
   return false;
  }
  else
  {
   return true;
  }
}

function store_redirect_data($short_url, $ip_address, $browser, $referrer)
{
  $query_str = "SELECT id FROM urls WHERE short_url = ?";
  
  $result = $this->db->query($query_str, $short_url);
  
  if ($result->num_rows() ==1)
  {
   $url_id = $result->row(0)->id;
  
   $query_str = "INSERT INTO redirects (url_id, ip_address, browser, referrer, redirect_time) VALUES
            ( ?, ?, ?, ?, now() ) ";
            
   $this->db->query($query_str, array($url_id, $ip_address, $browser, $referrer));
  
   return true;
  }
  else
  {
   return false;
  }
  
}
  

}

This is my controller:

Code:

<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class G extends CI_Controller {

function __construct()
{
  parent::__construct();
  
  $this->load->model('main_model');

}

function index()
{
  $this->shorten_url();

}

function shorten_url()
{
  $this->form_validation->set_rules('url', 'Full URL', 'required|trim|xss_clean|max_length[500]|prep_url');

  if ($this->form_validation->run() == FALSE)
  {
   //not successful yet - show the view
  
   $this->load->view('view_main');
  }
  else
  {
   // form validation success - add short url and return to user
  
   $full_url = $this->input->post('url', TRUE);
   $page_title = $this->_get_page_title($full_url);
   $this->load->library('user_agent');
   $ip_address = $this->input->ip_address();
   $short_url = $this->main_model->store_url($full_url, $page_title);
  
   // load a view which shows the shortened url
   $this->view_data['short_url'] = anchor(base_url() . 'g/o/' . $short_url, base_url() . 'g/o/' . $short_url, array('title' => 'Click to try out your short URL'));
   $this->view_data['full_url'] = anchor($full_url);
   $this->view_data['ip_address'] = ($ip_address);
   $this->load->view('view_show_short_url', $this->view_data);
  
  }
  
}

function o()
{
  $short_url = $this->uri->segment(3);

  if ($short_url == '')
  {
   $this->load->view('view_404');
  }
  
  $full_url = $this->main_model->get_full_url($short_url);
  
  if(! $full_url)
  {
   $this->load->view('view_404');
  }
  else
  {
   // let's capture reporting
   $this->load->library('user_agent');
   $ip_address = $this->input->ip_address();
   $browser_type = $this->agent->browser();
   $referrer = $this->agent->referrer();
   $browser_version = $this->agent->version();
   $this->main_model->store_redirect_data($short_url, $ip_address, $browser_type . ' ' . $browser_version, $referrer) ;
  
  
   // redirect them
   $this->view_data['short_url'] = $short_url;
   $this->view_data['url'] = $full_url;
   $this->view_data['full_url'] = anchor($full_url);
   $this->view_data['ip_address'] = $ip_address;
   $this->load->view('view_redirect', $this->view_data);
  }
}

function _get_page_title($url)
{

  $file = @file($url);
  
  $file = @implode("",$file);
  
  if(preg_match("/&lt;title&gt;(.+)<\/title>/i",$file,$m))
      return $m[1];
  else
      return '';
}
}

/* End of file welcome.php */
/* Location: ./application/controllers/welcome.php */

An overview of my database is attached.


I need to insert the time and date in a form such that they can be echo'd in the

Code:

$this->load->view('view_redirect', $this->view_data);

frame.

[eluser]InsiteFX[/eluser]
With PHP 5+ you do not need the constructor unless you are setting things.

Code:

class Main_model extends CI_Model {

function Main_model()
{
  parent::__construct();
}

// but it should be like this
class Main_model extends CI_Model {

function __construct()
{
  parent::__construct();
}

I would use var_dump to check all your data and also check the parameters that you are passing into your model.

[eluser]mr daniel[/eluser]
Thank-you
Slowly slowly making a URL redirector and the last bit was to get the date and time data to show up in my

Code:

view_redirect

window