chewbears

Think I got it. Found a MySQL book from a training class that answered it. A trigger is just an object within the database. Created by giving it a name an event and then a statement. Displayed by... show triggers

Where is the trigger stored? Not really understanding the syntax http://dev.mysql.com/doc/refman/5.0/en/trigger-syntax.html

I want a trigger because currently I have it running in a cron, so every night at mid night I populate the old fields with the new fields using these lines. Update TABLE_NAME_HERE SET Lat_DD = (substr(EXIF_lat,1, locate('° ', EXIF_lat) - 1) + substr(EXIF_lat,locate('° ', EXIF_lat) + 7, locate('\'', EXIF_lat) - 1)/60 + substr(EXIF_lat,locate('\'', EXIF_lat) + 2,locate('\"', EXIF_lat) - 1)/3600) Was just wondering if there is a way to make the above happen on its own whenever a record is say added to the database.

I have this query to convert file paths to image directory paths and Degree's minutes seconds to Decimal Degrees. "select *, concat('mywebsitehere',path) as URL, (substr(EXIF_lat,1, locate('° ', EXIF_lat) - 1) + substr(EXIF_lat,locate('° ', EXIF_lat) + 7, locate('\'', EXIF_lat) - 1)/60 + substr(EXIF_lat,locate('\'', EXIF_lat) + 2,locate('\"', EXIF_lat) - 1)/3600) as Latitude, concat('-',(substr(EXIF_long,1,locate('° ', EXIF_long) - 1) + substr(EXIF_long, locate('° ',EXIF_long) + 7, locate('\'', EXIF_long) - 1)/60 + substr(EXIF_long, locate('\'', EXIF_long) + 2, locate('\"', EXIF_long) - 1)/3600)) as Longitude from plogger_pictures where EXIF_lat is not null and EXIF_long is not null;" I want it to automatically do it whenever a record is added to the table. So basically Latitude, Longitude, URL, would be autocreated and populated when a record is added. How do I make this code, either all together or in pieces work to achieve my goal? Thanks

Have you looked at Wordpress?

I manage and program photodatabases and for all that the open source products do its worth your while to take another look. Whats your site and why do you feel its design is too complex to integrate?

Have you considered going with an open source gallery like plogger or zenphoto? They do everything you want to do and you don't need to code anything yourself unless you want features that do not exist out of the install.

fixed it. Just changed all instances of the variable search terms with str_replace and all is well. Thanks for the help, sorry for my near sighted problem solving ability

htmlentities doesn't work as it breaks the search as it passes the qoutes properly, which I need to drop in order for the string to exist in the database. Because I do not have "Big Bend". I have Big Bend in a field. So the Query before passed %'big bend'% with the htmlentities it passes %"big bend"%

The above does not break only when I switch the * to a double qoute ". I can not str_replace because searchterms is not a global variable so when the pagination goes to get it it uses a get and does not look at what has been done to the searchterms in the search function. Kinda not smart coding but it works for the time being. Htmlenties may work but I am not sure how to use it within the get of the pagination builder. Working on that now.

So I have a search function (part posted below). I have it set up that a user passes asterisk in the way that quotes would work in a normal search function. Due to the scope of the search function a pagination function can not handle quotes because it breaks the html. So what I need to do is transform the quotes a user would type into the search box into asterisks so the function will handle everything. I am assuming this is done with preg_match, but I am not familiar with preg_match at all other then what is written in manuals. <?php $arr['searchterms'] = trim($arr['searchterms']); if (strstr($arr['searchterms'],'*+')) { $terms = explode('*+*',substr($arr['searchterms'],1,-1)); $case = "AND"; } else if (strstr($arr['searchterms'],'*')) { $terms = explode('* *',substr($arr['searchterms'],1,-1)); $case = " OR"; } else { $terms = explode(" ",$arr['searchterms']); $case = " OR"; } ?>

That is a form drop down with options. <html> <body> <form action=""> <select name="cars"> <option value="1">Field1</option> <option value="2">Field2</option> <option value="3">Field3</option> <option value="4">Field4</option> </select> </form> </body> </html> Set the values to match up with your database and then pass a search. Here is a tutorial on these forms for a search http://www.phpfreaks.com/tutorial/simple-sql-search

Anything not Javascript based? Was looking for something PHP with a Database backend, these seem mostly to cater to individuals who want streaming video, but not necessarily a gallery of there media.

Why in the below could I not add the ability to also have the code look for areas with qoutes and an addition sign? This would add that last bit that I need without really changing the overall scheme. I do realize I need to clean this up but for demoing purposes isn't there a quick and dirty fix? $arr['searchterms'] = trim($arr['searchterms']); if (strstr($arr['searchterms'],'"')) $terms = explode('" "',substr($arr['searchterms'],1,-1)); else $terms = explode(" ",$arr['searchterms']);

I have looked over that link previous to posting here and was considering it if I needed to do a complete rewrite of the expression, but really my expression works in all cases that I need it based on user feedback and in house testing. The only case it failed is where a user wanted to look for photos along a specific lat and long. When they looked for say "33.2" and "78.2" they were getting images at "33.2" or 78.2" because I am not EXACT MATCH totally 33.2 could be 33.23432 which I wanted. This allows them to naturally wildcard without thinking about it.

Didn't know that about the = and - or the boolean search mode. I learn as I go so I will be looking this up : P I have looked into preg_match but really couldn't find a snippet to code from as I failed trying to start from scratch with that idea, hence why I use the above. It gets the job done, but probably could be way more efficient.

Thank you for the post, but do you not feel there is anyway to include my idea of having the exact phrase match include the AND usage via the + sign which is how I have it set up now? Meaning that "Hello"+"world" would be a exact phrase match on BOTH hello and world? Currently that fails as you know due to the explode. If that is not possible I will start reworking the search based around your suggestion.

I had to make a new account : ( This is the one I made to post my new questions. I was getting the same error on that account over and over and Friday night I gave up made this new account and everything worked.

Something youtub'ish would be awesome. I know Zenphoto, and a few others are out there but looking for something specifically for videos.

Well it isn't fixed but I found I could make a 1 sentence 1st post and then quick reply the other two portions.

function search($arr) { $arr['searchterms'] = trim($arr['searchterms']); if (strstr($arr['searchterms'],'"')) $terms = explode('" "',substr($arr['searchterms'],1,-1)); else $terms = explode(" ",$arr['searchterms']); $from = 0; $limit = 20; if (isset($arr['from']) && $arr['from'] > 0) { $from = $arr['from']; } // enforce hard-coded max limit if (isset($arr['limit']) && $arr['limit'] > 0 && $arr['limit'] <= 100) { $limit = $arr['limit']; } $query = " FROM `".TABLE_PREFIX."pictures` p LEFT JOIN `".TABLE_PREFIX."comments` c ON p.`id` = c.`parent_id` "; if ((count($terms) != 1) || ($terms[0] != '')){ $query .= " WHERE ( "; foreach ((array)$terms as $term) { $term = mysql_real_escape_string($term); $multi_term = explode("+",$term); if (count($multi_term)>1) { $path = implode("%' AND `path` LIKE '%",$multi_term); $description = implode("%' AND `description` LIKE '%",$multi_term); $comment = implode("%' AND `comment` LIKE '%",$multi_term); $caption = implode("%' AND `caption` LIKE '%",$multi_term); $EXIF_subj_ref = implode("%' AND `EXIF_subj_ref` LIKE '%",$multi_term); $EXIF_cont_loc_name = implode("%' AND `EXIF_cont_loc_name` LIKE '%",$multi_term); $EXIF_city = implode("%' AND `EXIF_city` LIKE '%",$multi_term); $EXIF_state = implode("%' AND `EXIF_state` LIKE '%",$multi_term); $EXIF_country = implode("%' AND `EXIF_country` LIKE '%",$multi_term); $EXIF_credit = implode("%' AND `EXIF_credit` LIKE '%",$multi_term); $EXIF_source = implode("%' AND `EXIF_source` LIKE '%",$multi_term); $EXIF_contact = implode("%' AND `EXIF_contact` LIKE '%",$multi_term); $EXIF_caption = implode("%' AND `EXIF_caption` LIKE '%",$multi_term); $EXIF_make = implode("%' AND `EXIF_make` LIKE '%",$multi_term); $EXIF_model = implode("%' AND `EXIF_model` LIKE '%",$multi_term); $EXIF_copyright = implode("%' AND `EXIF_copyright` LIKE '%",$multi_term); $EXIF_artist = implode("%' AND `EXIF_artist` LIKE '%",$multi_term); $EXIF_lat_ref = implode("%' AND `EXIF_lat_ref` LIKE '%",$multi_term); $EXIF_long_ref = implode("%' AND `EXIF_long_ref` LIKE '%",$multi_term); $EXIF_lat = implode("%' AND `EXIF_lat` LIKE '%",$multi_term); $EXIF_long = implode("%' AND `EXIF_long` LIKE '%",$multi_term); $EXIF_date_time_dig = implode("%' AND `EXIF_date_time_dig` LIKE '%",$multi_term); $EXIF_date_time_orig = implode("%' AND `EXIF_date_time_orig` LIKE '%",$multi_term); $EXIF_img_hist = implode("%' AND `EXIF_img_hist` LIKE '%",$multi_term); $EXIF_usr_comment = implode("%' AND `EXIF_usr_comment` LIKE '%",$multi_term); $EXIF_comment = implode("%' AND `EXIF_comment` LIKE '%",$multi_term); $EXIF_keyword = implode("%' AND `EXIF_keyword` LIKE '%",$multi_term); } else { $path = $description = $comment = $caption = $EXIF_subj_ref = $EXIF_cont_loc_name = $EXIF_city = $EXIF_state = $EXIF_country = $EXIF_credit = $EXIF_source = $EXIF_contact = $EXIF_caption = $EXIF_make = $EXIF_model = $EXIF_copyright = $EXIF_artist = $EXIF_lat_ref = $EXIF_long_ref = $EXIF_lat = $EXIF_long = $EXIF_date_time_dig = $EXIF_date_time_orig = $EXIF_img_hist = $EXIF_usr_comment = $EXIF_comment =$EXIF_keyword = $term; } $query .= " `path` LIKE '%$path%' OR `description` LIKE '%$description%' OR `comment` LIKE '%$comment%' OR `caption` LIKE '%$caption%' OR `EXIF_subj_ref` LIKE '%$EXIF_subj_ref%' OR `EXIF_cont_loc_name` LIKE '%$EXIF_cont_loc_name%' OR `EXIF_city` LIKE '%$EXIF_city%' OR `EXIF_state` LIKE '%$EXIF_state%' OR `EXIF_country` LIKE '%$EXIF_country%' OR `EXIF_credit` LIKE '%$EXIF_credit%' OR `EXIF_source` LIKE '%$EXIF_source%' OR `EXIF_contact` LIKE '%$EXIF_contact%' OR `EXIF_caption` LIKE '%$EXIF_caption%' OR `EXIF_make` LIKE '%$EXIF_make%' OR `EXIF_model` LIKE '%$EXIF_model%' OR `EXIF_copyright` LIKE '%$EXIF_copyright%' OR `EXIF_artist` LIKE '%$EXIF_artist%' OR `EXIF_lat_ref` LIKE '%$EXIF_lat_ref%' OR `EXIF_long_ref` LIKE '%$EXIF_long_ref%' OR `EXIF_lat` LIKE '%$EXIF_lat%' OR `EXIF_long` LIKE '%$EXIF_long%' OR `EXIF_date_time_dig` LIKE '%$EXIF_date_time_dig%' OR `EXIF_date_time_orig` LIKE '%$EXIF_date_time_orig%' OR `EXIF_img_hist` LIKE '%$EXIF_img_hist%' OR `EXIF_usr_comment` LIKE '%$EXIF_usr_comment%' OR `EXIF_comment` LIKE '%$EXIF_comment%' OR `EXIF_keyword` LIKE '%$EXIF_keyword%' OR "; } $query = substr($query, 0, strlen($query) - 3) .") "; } else { // no search terms? no results either $query .= " WHERE 1 = 0"; } $sort_fields = array('date_submitted','id'); $sortby = 'date_submitted'; if (isset($arr['sortby']) && in_array($arr['sortby'],$sort_fields)) { $sortby = $arr['sortby']; } $sortdir = ' ASC'; if (isset($arr['sortdir']) && 'desc' == $arr['sortdir']) { $sortdir = ' DESC'; } $result = run_query("SELECT COUNT(DISTINCT p.`id`) AS cnt " . $query); $row = mysql_fetch_assoc($result); $GLOBALS["total_pictures"] = $row["cnt"]; // and I need sort order here as well // from and limit too $result = run_query("SELECT p.*,c.`comment`, UNIX_TIMESTAMP(`date_submitted`) AS `unix_date_submitted` ".$query . " GROUP BY p.`id` ORDER BY `$sortby` $sortdir LIMIT $from,$limit"); $GLOBALS["available_pictures"] = mysql_num_rows($result); $GLOBALS["picture_counter"] = 0; $GLOBALS["picture_dbh"] = $result; }

I have written a search function which I am moderately happy with, but I feel I can improve on 2 things. One there has to be a better way. Now I am not asking anyone to tackle that for me so I would for now hope to just improve the code so it does that last part in what I need it to do. The below code is for a general search box. It currently does 3 things. Allows for OR, Exact phrace match, and AND. Additionally I have it set up to allow a user to type 2 exact phrases BUT I would like the exact phrases to be OR or AND. Right now it only does OR because I explode on a space. So the search "Dog man" "Cat woman" searches all records that have "Dog man" OR "Car Woman" I want to set it up to also do AND. Now here is the tricky part. My AND is done through a multiterm variable linked by a +. So where the search sees a + it goes through an IF statement to read the + sign how I need it to be read to come up as an AND. Now when you look at the code I would love suggestions how to make it easier, but most importantly would love to know how to use the + in the exact phrase case mentioned above. Well thanks and I wish you all luck, as I am (stumped) and need your help!

Won't let me type in this box more then 1 line . See Post 3,4 Thanks