Jump to content

chewbears

Members
 View Profile  See their activity

  • Posts

    23

  • Joined

  • Last visited

    Never

 Content Type 

Posts posted by chewbears

    Auto Update Columns

    in MySQL Help

    Posted

    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

    Auto Update Columns

    in MySQL Help

    Posted

    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.

    Auto Update Columns

    in MySQL Help

    Posted

    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

    Preg_Match change " to * from searchbox

    in PHP Coding Help

    Posted

    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"%

    Preg_Match change " to * from searchbox

    in PHP Coding Help

    Posted

    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.

    Preg_Match change " to * from searchbox

    in PHP Coding Help

    Posted

    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";
}

?>

    search page coding help

    in PHP Coding Help

    Posted

    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

    Help finishing Search Function

    in PHP Coding Help

    Posted

    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']);

    Help finishing Search Function

    in PHP Coding Help

    Posted

    Looking over your code again and thinking.

     

    I thought that if you place the string into an array, and make each word unique, you could then maybe do a foreach loop and modify each as you like, checking for patterns using preg_match for each word if contains spaces or the +. Then would need to trim and remove the plus...run whatever query you wanted with the words.

     

    I did come across this, it may help or just confuse the situation more.

    http://www.joedolson.com/Search-Engine-in-PHP-MySQL.php

     

    I would use preg_match with a pattern like /and|or/\i if needed it to work for either one

     

    Pretty much you can do anything you want here, most things can be done in php if are slick enough about it.

     

    if wanna display OR plus also AND, then run a double query, if checking for exact matches...look for double whitespace, if is an include check for the +

     

    To me I'd rather just let the user select which type of search they want, after all, they are the only ones that know what they want.

     

    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. 

    Help finishing Search Function

    in PHP Coding Help

    Posted

    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.

    Help finishing Search Function

    in PHP Coding Help

    Posted

    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.

    Help finishing Search Function

    in PHP Coding Help

    Posted 

    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;
}

     

     

    Help finishing Search Function

    in PHP Coding Help

    Posted

    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!  :P

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.