12 replies to this topic

[darren]

hi all, this is not an eto question, but you guys are so helpful I thought i'd ask anyway...

i have 2 databases, i want to check if they have a column in common and if they do, output a link. this is the general idea. I think I'm close but tell me if I'm way off.

     <?php 
              $row_db1['collection'] == '$a' 
              if ( $row_db2['collection'] == ${'$a'} ) { ?>
     
      <p> link goes here</p>
            
            <?php 
            } else { echo " ";    }?>  

basically trying to make a dynamic variable ($a) and check it against the other column. all selections are being filtered by url....

[Ralph]

By making use of the Etomite API you have a lot of flexibility when it comes to working with multiple databases and tables... Check the documentation for database API functions such as $etomite->getIntTableRows() and $etomite->getExtTableRows(), as well as others...

[Cris D.]

As I see it there are two main ways to do the check:

1) Using your SQL to build a $rs that returns ONLY links with the common variable I have had most success with this by using $etomite->dbQuery($sql); An example may be:

$sql= "SELECT * FROM ".$etomite->db."table1 AS a, ".$etomite->db."table2 AS b WHERE 'a.column1' = ".$var." AND 'b.column2' = ".$var.";";
$rs=$etomite->dbQuery($sql);
foreach($rs as $row){
$output .= $row['field'];//etc
}

I have been calling data from 5+ tables in this manner recently. Take note of the order tables are called though because later tables with the same column names overwrite the last in the $rs by default unless you specify otherwise by using SQL functions.

2) Get your data sets and interate through them to make the comparison: similar to what you are doing, except without more code I can't see if you actually ARE iterating through the data sets (it looks like you aren't at the moment).

Arrays are good, but you can't beat a good SQL query for speed and low overhead!
[EDIT: typo]

Edited by Cris D., 23 February 2008 - 09:20 PM.

[Ralph]

Cris, this method will work with multiple tables, but not multiple databases... I have coded several projects to pull from multiple tables as well but have only written one or two snippets that access multiple databases... One of them actually merged data from a remote server with the local server...

[Cris D.]

Quote

Cris, this method will work with multiple tables, but not multiple databases

Ahh, I missed that small word in Darren's original Post! I've had enough difficulty passing multiple table queries with the Etomite API's let alone multi-database queries. In fact I have tried to use them this way but eventually gave up (and I'm a very patient person) there is too little documentation on how to use them for this purpose. That's why I now resort to building individual arrays and processing them manually.

[darren]

I should have been more clear 'this is not an eto question'... meant this is a site not built with etomite. I tried but for the life of me couldn't think of how to integrate the two. delete this post if this is against some rules, but like I said you guys are very helpful....

some background: this is a home builder site. 1 database ( i was in a hurry and typed 2 but meant two tables. sorry ) 1 table for home collections 1 table for available homes. a column in the available homes table is for collections that the home belongs to. when i'm on the collection page, if there is a row in the available home table with the same collection name as that page (filtered by url as in /collections.php?2=neighborhood&3=collection, etc..) show a link to the available homes page, pass the url so we only see available homes for that collection.

in english, the 'if' statement is: if the collection column in the Collection table has the same data as the collection column in the Available Home table, show a link.

(in actuallity the select statement for the Available home table is SELECT * FROM AvailableHomes WHERE collection = '%s' (from url) so we wont select anything unless there actually is a row with that collection name in it..)

in php, the if statment is something like: if ( $row_collection['collection'] == $row_AvailableHomes['collection'] ) { link } else { do nothing }

but that doesnt work so i tried to create the dynamic variable '$a' and use that in the if statement...see original post. i dont get any sql errors but i dont get the link either.

I can get everything functioning by adding a column in the collection table that is have_available_homes and just check that in the if statement, but i'd rather remove that step in the maintenance process. Is there any gaps/errors in my logic or something technically wrong in the code posted above?

again, thanks, and sorry for the long post.

D

[Cris D.]

Hi Darren, it still looks like you are not iterating through the data sets. You can't expect the two rows to match unless you are 1) returning a single row for each, or 2) iterating through them to look for matches.

$collection=array('collection'=>'link', 'collection'=>'notalink');//get arrays however you want
$avaliableHome=array('collection'=>'link', 'collection'=>'notalink');

for($i=0; $i<count($avaliableHome); $i++){//iterate through each record to check against value
if($collection[0] == $avaliableHome[$i]['collection']{
$newResults[]=$collection[0]['collection'];}//record found, create something to return
}
if($newResults){//if a match
$output = $newResults[0];//output something
}else{
$output ="";
}

[darren]

that makes sense...since my select statement will only return rows with that specific data in the column why is iterating necessary?

the collection select statement will only return one row, the available home statement may return one, more than one or no rows. link should show as long as there is one row, i don't really care if there is more than one.

I'll take a look at your method above in the am with a clearer head. i took a few steps backward and useing this method the page says 'goodbye'. however i'm looking at both table in phpmyadmin and it should say 'hello'!

<?php if ( $row_collection['collection'] == $row_AvailableHomes['collection'] )  { echo "hello"; } else { echo " goodbye";    }?>  

[Cris D.]

Quote

Why is iterating necessary?

You havn't made it clear how you are getting your arrays and if they are mulit-dimentional or not or what is in your tables and what results are being returned, so it's hard to know exactly what the problem is. I would be doing a print_r() for each row to see what you are working with, it may be a simple case of adding a row instance eg
$row[0]['collection']...

[darren]

my select statements. just playing in phpmyadmin and with both these i get 1 row meaning i should be getting 'hello' instead of 'goodbye'

$colname_collection = "1";
if (isset($HTTP_GET_VARS['3'])) {
  $colname_collection = (get_magic_quotes_gpc()) ? $HTTP_GET_VARS['3'] : addslashes($HTTP_GET_VARS['3']);
  }
$colname1_collection = "1";
if (isset($HTTP_GET_VARS['2'])) {
  $colname1_collection = (get_magic_quotes_gpc()) ? $HTTP_GET_VARS['2'] : addslashes($HTTP_GET_VARS['2']);
}
mysql_select_db($database_rch, $rch);
$query_collection = sprintf("SELECT * FROM collections WHERE collection = '%s' AND hoodname = '%s'", $colname_collection, $colname1_collection);
$collection = mysql_query($query_collection, $rch) or die(mysql_error());
$row_collection = mysql_fetch_assoc($collection);
$totalRows_collection = mysql_num_rows($collection);

$colname_AvailableHomes = "1";
if (isset($HTTP_GET_VARS['3'])) {
  $colname_collection = (get_magic_quotes_gpc()) ? $HTTP_GET_VARS['3'] : addslashes($HTTP_GET_VARS['3']);
  }
$colname1_AvailableHomes = "1";
if (isset($HTTP_GET_VARS['2'])) {
  $colname1_AvailableHomes = (get_magic_quotes_gpc()) ? $HTTP_GET_VARS['2'] : addslashes($HTTP_GET_VARS['2']);
}
mysql_select_db($database_rch, $rch);
$query_AvailableHomes = sprintf("SELECT collection FROM AvailableHomes WHERE collection = '%s' AND hoodname = '%s'", $colname_AvailableHomes, $colname1_AvailableHomes);
$AvailableHomes = mysql_query($query_AvailableHomes, $rch) or die(mysql_error());
$row_AvailableHomes = mysql_fetch_assoc($AvailableHomes);
$totalRows_AvailableHomes = mysql_num_rows($AvailableHomes);

[Cris D.]

OK, I vote delete the thread!

No wonder I like coding in etomite, it's just sooo much easier!

[Ralph]

Actually, considering how this is a stand-alone PHP script issue, and not one directly related to the Etomite API, and due to the fact that we haven't been presented with the entire script, it is beyond the scope of these forums to answer your specific question... It might be possible that one of the memebrs might be willing to give some help and I would encourage them to contact you personally via PM... We're here to help in merging PHP programming copncepts with those integrated into the API and it doesn't benefit the communinty as a whole to bounce back into programming outside the API... Like Cris stated, more or less, more difficult than it needs to be...

I will leave this thread open, temporarily, just in case someone wishes to lend a hand...

[darren]

Ralph, on Feb 25 2008, 02:29 PM, said:

Actually, considering how this is a stand-alone PHP script issue, and not one directly related to the Etomite API, and due to the fact that we haven't been presented with the entire script, it is beyond the scope of these forums to answer your specific question... It might be possible that one of the memebrs might be willing to give some help and I would encourage them to contact you personally via PM... We're here to help in merging PHP programming copncepts with those integrated into the API and it doesn't benefit the communinty as a whole to bounce back into programming outside the API... Like Cris stated, more or less, more difficult than it needs to be...

I will leave this thread open, temporarily, just in case someone wishes to lend a hand...

i understand entirely, Ralph.