List data from table in Detail page

15 posts by 4 authors in: Forums > CMS Builder
Last Post: August 4, 2009   (RSS)

By eduran582 - July 29, 2009

I'm trying to "list" the contents of a couple of fields in a specific area of a "detail page" and can't seem to get it going.

Example: I've selected a "detail" page containing information. Within this page, I'd like to be able to go into the table where this record came from to see how many other times this same information was added using a specific "where" similar to: where 'a-date-in-this-record' = 'a-similar-field-with-a-date' and 'id-number' = 'a-similar-field-with-id-numbers'

I don't know if what I was trying is right (ya gotta start someplace) but I tried adding another instance similar to what appears at the top in a list page (without the "where" because I didn't have a clue as to syntax), then start a loop but all it did was show the same record information, even though there are different values, equal to the number of records in the table (only a few while I set this up):

<?php list($citationsRecords, $citationsMetaData) = getRecords(array('tableName' => 'citations', )); ?>
<?php foreach ($citationsRecords as $record): ?>
Date / Time:&nbsp;<?php echo date("M jS, Y g:i:s a", strtotime($citationsRecord['date_time_of_violation'])) ?><br/>
<?php endforeach; ?>
I would have thought if the date fields were different, I was on the right track. But that didn't happen. I've tried to explain it best I could so hope I'm not too confusing.

I tried searching for something like this and wasn't successful. Can anyone point me in the right direction?

TIA

Eric

Re: [eduran582] List data from table in Detail page

By Dave - July 30, 2009

Hi Eric,

Can you attach your viewer file that you have so far and the names of the fields you want to compare? And an example of the contents of those fields?

For example, I assume 'id-number' is something like 3. But is 'a-similar-field-with-id-numbers' the same or is it '1,2,3' or some other format?

Let me know, thanks!
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] List data from table in Detail page

By eduran582 - July 30, 2009

Hi Dave,

The 'detail' page that is accessed, contains several pieces of information regarding a citation (ticket). If the person that received the citation had any other citations, I'd like to be able to show the citation number (with a link to that detail), the date/time of the citation and possibly other minor information that would help the person who accessed the current citation (a hearing officer, for example). It would be listed in a row of a table that has the other information ("Other Citations").

My thought was to simply have a link that would go to a (search) viewer file thus eliminating the hassle of trying to incorporate a 'list' section into a 'detail' page. I would be using the Drivers License ('dl_number') AND Date of Birth ('birthdate') as an EXACT search. The search would go through the entire table and any match would be displayed in a separate 'list' page (using the link from citation number to a detail page).

Attached is the 'viewer' I was going to use but I'm not sure how to direct a link with the appropriate search information from the detail page (search by DL number = <?php echo $citationsRecord['dl_number'] ?> and search by birthdate = <?php echo $citationsRecord['birthdate'] ?>).

If you think I could still list the information in the initial 'detail' page, I'm all ears

Thanks again for all your help, Dave. [:)]
Attachments:

citationslist4prior.php 5K

Re: [eduran582] List data from table in Detail page

By Dave - July 31, 2009

Hi eduran582,

Yes you can have multiple viewers on one page. Just add the code for each to the same page.

Here's some code that would load relation citations. It assumes there's a variable defined above it called $citationsRecord with the specific citation details.

// get related citations
$where = "dl_number = '" .mysql_real_escape_string( $citationsRecord['dl_number'] ). "' AND ";
$where .= "birthdate = '" .mysql_real_escape_string( $citationsRecord['birthdate'] ). "'";
list($relatedCitations, $relatedCitationsMetaData) = getRecords(array(
'tableName' => 'citations',
'where' => $where,
'allowSearch' => false,
));

Then you can use the generated foreach loop to display the related citations. Just replace the generated variable name with $relatedCitations.

Hope that helps! Attach your detail viewer if you need more help.
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] List data from table in Detail page

By eduran582 - July 31, 2009

Thanks, Dave. As usual, you've come through with all the right answers! [;)]

Appreciative as always,

Eric

Re: [Dave] List data from table in Detail page

By sandimcp - August 1, 2009

Hi Dave,
I'm working with InstantRealty. What if you have three or more related tables that you want to display in the Listings page? I'm trying to do a LeftJoin but it only works with one table joining one table. I need to filter the listings by at least one field in the second table and also by another field in the third table.

Here is what I have so far.

require_once "cmsadmin/lib/viewer_functions.php";

list($residencesRecords, $residencesMetaData) = getRecords(array(
'tableName' => 'residences',
'perPage' => '10',
'orderBy' => 'residences.feature_end_date DESC, residences.listing_date DESC',
'allowSearch' => '0',
'leftJoin' => array(
'agencies' => 'ON (residences.agency_id= agencies.num)',
),
'leftJoin' .=> array(
'agents' => 'ON residences.agent_id= agents.num' ,
),
'where' => 'agents.currstatus=\'Active\' and agencies.currstatus=\'Active\' and residences.listing_type = \'Buy\'and residences.status = \'Active\'',
));
//Plain SQL that achieves the list of properties I want
//select * from irw_residences left join (irw_agents, irw_agencies) on (irw_residences.num=irw_agents.num and irw_residences.num=irw_agencies.num) where irw_agents.currstatus='Active' and irw_agencies.currstatus='Active' and irw_residences.listing_type='Buy' and irw_residences.status='Active'

Waiting to hear. Hope you can help.

Re: [sandimcp] List data from table in Detail page

By Dave - August 3, 2009

Try this:

list($residencesRecords, $residencesMetaData) = getRecords(array(
'tableName' => 'residences',
'perPage' => '10',
'orderBy' => 'residences.feature_end_date DESC, residences.listing_date DESC',
'allowSearch' => '0',
'leftJoin' => array(
'agencies' => 'ON residences.agency_id = agencies.num',
'agents' => 'ON residences.agent_id = agents.num',
),
'where' => "agents.currstatus = 'Active' AND
agencies.currstatus = 'Active' AND
residences.listing_type = 'Buy' AND
residences.status = 'Active'",
));

A couple of tips:
- You can only add 'leftJoin' once, just add multiple tables as in the example above.
- Add 'debugSql' => true, to see what SQL is being generated.
- Make sure 'Active' is actually the column value (if it's a checkbox the value will be 0 or 1
even though the field label is displayed in the program.
- For really complicated queries sometimes straight SQL is the simplest path.

Let me know how it goes.
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] List data from table in Detail page

By sandimcp - August 3, 2009

I get "MySQL Error: Unknown column 'agencies.numLEFT' in 'on clause' ". Any more ideas? So close but so far.

Re: [sandimcp] List data from table in Detail page

By Dave - August 3, 2009

Which version are you using? And what does 'debugSQL' => true, output?

Sounds like we're missing some spaces. Try adding some before and after the join values (before ON and after *.num):

'agencies' => ' ON residences.agency_id = agencies.num ',
'agents' => ' ON residences.agent_id = agents.num ',

And let me know if you're using the latest version (1.31) and I'll add those extra spaces to the code if needed.

Let me know what happens.
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] List data from table in Detail page

By sandimcp - August 3, 2009

I'm using 1.31 and adding the spaces has worked.
You're a lifesaver. :)