Databases
StackOverflow2013
Postsdbo
POCan I use a php function to perform the equivalent of a mySQL left join?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCan I use a php function to perform the equivalent of a mySQL left join?
    primarykey
    data
    text
    <p>The problem I am having is that using the current function below is taking much too long to load. The two tables that are being joined consist of thousands of rows apiece. The specific function that holds the table join is the <code>&amp;getFields()</code> function in the model section below. </p> <p>I was thinking that I could possibly have a function that initially checked whether the request was to edit or add new and then deal with separating the table join on only edit queries (No clue as to how to accomplish this).</p> <p><br /> <b>Call From Page Initial Page</b> - using an html form with post method</p> <pre> if(count($this->fields)){ for($i=0;$ifields);$i++){ $field =& $this->fields[$i]; $se = $field->section; $ti = $field->title; $ft = $field->type; $na = $field->name; $fva = $field->field_value; $nu = $field->unit; switch($se){ case 'header' : echo 'input type="blah... </pre> <p><br /> <b>Controller Directing Traffic To Model</b><br /></p> <pre> function __construct() { parent::__construct(); $this->registerTask('add','edit'); JRequest::setVar('view','cpanel'); } function edit() { JRequest::setVar('layout','form'); parent::display(); } function save() { $model = $this->getModel('cpanel'); $msg = $model->store(); $link = ; $this->setRedirect($link, $msg); } </pre> <p><br /> <b>Model That Holds The Actual Functions</b><br /></p> <pre> function __construct() { parent::__construct(); global $mainframe; $array = JRequest::getVar('cid', 0, '', 'array'); $this->setId((int)$array[0]); } function setId($id) { // Set id and wipe data $this->_id = $id; $this->_data = null; } function &getItem() { if(empty($this->_data)) { $query = ' SELECT * FROM #__directory_entry WHERE id = '.$this->_id; $this->_db->setQuery( $query ); $this->_data = $this->_db->loadObject(); } if(!$this->_data) { $session =& JFactory::getSession(); $post = $session->get('post'); $this->_data->id = 0; $this->_data->title = isset($post['title'])?$post['title']:null; } return $this->_data; } <b>function &getFields() { $query = 'select f.*, ef.field_value from #__directory_field as f left join<br />#__directory_enf as ef on (f.id = ef.field_id and ef.entry_id = '.$this->_id.') where<br />f.published = 1 order by f.ordering asc';</b> $this->_db->setQuery( $query ); $fields = $this->_db->loadObjectList(); $session =& JFactory::getSession(); if($session->has('post') and !$this->_id) { $post = $session->get('post'); for($i=0;$ifield_value = isset($post['field_'.$fields[$i]->name])?$post['field_'.$fields[$i]->name]:null; } return $fields; } function store() { // Check for request forgeries JRequest::checkToken() or jexit( JText::_('Invalid Token') ); $post = JRequest::get( 'post' ); $session =& JFactory::getSession(); $session->set('post', $post); if($post["title"] == "") return JText::_('Please enter the title'); $query = 'select * from #__directory_field where published = 1'; $this->_db->setQuery( $query ); $fields = $this->_db->loadObjectList(); for($i=0;$irequired and $post['field_'.$fields[$i]->name] == "") return JText::_($fields[$i]->title . ' is required field and can not be left blank.'); } if(!$post["id"]) { $date =& JFactory::getDate(); $post['create_date'] = $date->toMySQL(); $post['ordering'] = 1; $query = 'select ordering from #__directory_entry order by ordering desc limit 1'; $this->_db->setQuery( $query ); $post['ordering'] += $this->_db->loadResult(); $post['published'] = 1; } $me =& JFactory::getUser(); JTable::addIncludePath(JPATH_ADMINISTRATOR.DS.'components'.DS.'com_joomd'.DS.'tables'); $row =& JTable::getInstance('entry', 'Table'); if (!$row->bind( $post )) return JText::_('Sorry some Error Occurred.'); if (!$row->store()) return $this->_db->getErrorMsg(); if(!$post['id']) { $post['id'] = $this->_db->insertid(); JRequest::setVar('id', $post['id']); $msg = JText::_('Entry successfully added!'); $query = 'insert into #__directory_enu (entry_id, user_id) values ('.$post['id'].', '.$me->id.')'; $this->_db->setQuery( $query ); $this->_db->query(); } else $msg = JText::_('Entry successfully Updated!'); $max_size = 10000000; jimport('joomla.filesystem.file'); $time = time(); for($i=0;$itype == "image") $allowed = array('.jpg', '.jpeg', '.gif', '.png'); elseif($field->type == "file") $allowed = array('.doc', '.docx', '.pdf', '.txt', '.exl', '.xls', '.xlsx', '.jpg', '.jpeg', '.gif', '.png', '.zip'); $flag = true; $query = 'select count(*) from #__directory_enf where entry_id = ' . $post["id"] .' and field_id = ' . $field->id; $this->_db->setQuery( $query ); $count = $this->_db->loadResult(); if($field->type == "image" or $field->type == "file") { $image = JRequest::getVar("field_".$field->name, null, 'FILES', 'array'); $image_name = str_replace(' ', '', JFile::makeSafe($image['name'])); $image_tmp = $image["tmp_name"]; if($image_name "") { $ext = strrchr($image_name, '.'); if(!in_array($ext, $allowed)) return sprintf(JText::_('File type for %s is not allowed.'), $field->title); if(filesize($image_tmp) > $max_size) return sprintf(JText::_('File size for %s exceeds the maximum file size.'), $field->title); if(move_uploaded_file($image_tmp, JPATH_SITE.'/files/'.$time.$image_name)) $post["field_".$field->name] = $time.$image_name; else return sprintf(JText::_('Sorry File for %s could not be uploaded.'), $field->title); } else $flag = false; } if($flag) { if($count) { $query = 'select id from #__directory_enf where entry_id = '.$post["id"].' and field_id = ' . $field->id; $this->_db->setQuery( $query ); $field_id = $this->_db->loadResult(); $update = new stdClass(); $update->id = $field_id; $update->field_value = $post["field_".$field->name]; $this->_db->updateObject('#__directory_enf', $update, 'id'); } else { $insert = new stdClass(); $insert->id = null; $insert->field_id = $field->id; $insert->entry_id = $post["id"]; $insert->field_value = $post["field_".$field->name]; $this->_db->insertObject('#__directory_enf', $insert, 'id'); } } } $session->clear('post'); return $msg; } </pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USOMG Ponies
    1. USAdam
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POCan I use a php function to perform the equivalent of a mySQL left join?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POCan I use a php function to perform the equivalent of a mySQL left join?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COMaybe you could try narrowing the question down to something more specific than "here's a few hundred lines of code, fix it". Joins on tables with millions of rows can be fast in MySQL, if you have the right indexes - thousands of rows shouldn't be any sort of trouble.
      singulars
      1. POCan I use a php function to perform the equivalent of a mySQL left join?
      1. USceejayoz
    2. COAre you using indexes on the keys in the ON clause?
      singulars
      1. POCan I use a php function to perform the equivalent of a mySQL left join?
      1. USalxbl
    3. COPHP, or any other application language, will never scale (1K, 10K, 100K, etc records) to perform as well as SQL.
      singulars
      1. POCan I use a php function to perform the equivalent of a mySQL left join?
      1. USOMG Ponies
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload