[PHP-DB] Aliasing and Grouping (multiple keyword search)
I followed an online tutorial (url below) to create a simple indexed keyword search. The sample as outlined in the tutorial was designed to work with one keyword only. I am an experienced PHP programmer with some SQL experience but only simple queries. This Aliasing and Grouping is brand new to me. What I would like to learn how to do is expand this SQL query to search for multiple keywords. The PHP side of things is already taken care of. Any help (code, other tutorials, book references, whatever) with the SQL will be greatly appreciated. Tutorial at: http://www.onlamp.com/pub/a/php/2002/10/24/simplesearchengine.html ?php ## Table structure for table `search_occurrence` ## /* CREATE TABLE search_occurrence ( occurrence_id int(10) unsigned NOT NULL auto_increment, word_id int(10) unsigned NOT NULL default '0', page_id int(10) unsigned NOT NULL default '0' PRIMARY KEY (occurrence_id) ) TYPE=MyISAM; */ ## Table structure for table `search_page` ## /* CREATE TABLE search_page ( page_id int(10) unsigned NOT NULL auto_increment, page_url varchar(200) NOT NULL default '', PRIMARY KEY (page_id) ) TYPE=MyISAM; */ ## Table structure for table `search_word` ## /* CREATE TABLE search_word ( word_id int(10) unsigned NOT NULL auto_increment, word_word varchar(50) NOT NULL default '', PRIMARY KEY (word_id), KEY word_word_ix (word_word) ) TYPE=MyISAM; */ include(../includes/db.php); // opens my database $query = SELECT p.page_url AS url, COUNT(*) AS occurrences FROM search_page p, search_word w, search_occurrence o WHERE p.page_id = o.page_id AND w.word_id = o.word_id AND w.word_word = \$keyword\ GROUP BY p.page_id ORDER BY occurrences DESC LIMIT $start_row, $num_results; $result = mysql_query($query, $db); ? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] permissions system
Very simply I protect whole pages on my member's website by include()'ing a script called auth.php into the top of each page. Rather than using a binary string to turn functions 'on' and 'off', I do things a bit more general than that. I have defined levels of access (ie, L1, L2, L3, etc..) stored in the member's table in the database. At the top of each page I wish to protect I write ($min_access = 'L2') to define a minimum level of access. If the user has L2 access or greater they'll fall right through the auth.php and end up on the page. Otherwise one of the conditionals will catch them like a net and dump them into the logout() function which kills the session and displays the login screen with a lovely ACCESS DENIED (or other appropriate text) message. In this way I can protect any number and type of webpage (so long as I rename it .php) without registering the filename. And all of my staff and admin scripts can remain public instead of behind some .htaccess file. As for the complexity of auth.php. It's hardly complex but I'm fairly thurough in my check. The auth.php script is actually about 150 lines of code and does timeout, tracks the member's movement and more... but it could be as simple as a single db query. This is the best continuous pervasive authentication scheme I have devised. It's probably not as good as other systems out there but it does the job in the limited scope of protecting my member's site. :) -Keivn - Original Message - From: Jason Markantes [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, June 04, 2002 10:43 PM Subject: [PHP-DB] permissions system Howdy All- I've poked around the net for some snippets, and have done my own in the past, but wanted to get some more ideas on how you do a permissions system. This means, in your application, how do you control and enforce different permissions for different users? Simple example: An image repository. You have users who can download images and/or upload images. And certain users can only search for images within a certain criteria. What I've done in the past is created a permission_flag column for each user. Zero or One determines whether or not the user can do that function, and the various functions are indicated by position (or index). So if you had upload and download functions, you can have: 01 = can download, but can't upload 11 = can download and upload. If I add a new function, I have to add another digit (and potentially increase the column size if things grow faster than planned). To enforce this, as a user attempts each function, I simply check the function index and see if it's one or zero. With me so far? Now, for restricting database access: What I've done is created a user_where column for each user. In this column I add a where clause that's appended to each and every search the user attempts (with the usual input safety checks for common db exploits). For example, if a user can only see Approved images (in my pretend application example here), the user_where value might be category = 'Approved'. For multiple values, it could be category IN ('Approved', 'Pending'). Does this make sense? How are other people doing things? I've given a little bit of thought to it, but not enough. With all the applications out there everyone at some point has to come up with their own system. Ideas? Thanks, Jason = Just Another Fu@#in' Adventure http://markantes.com/jason __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] PHP Session End.
There is no way to know if the user has left the website. About all you can do is save a timestamp in the database for every recordable action the user performs then if no actions take place for say 10 minutes that user is said to be 'logged out' and you delete the database row and deallocate any PIN's they may have accumulated. But let me ask you a question... Why exactly does the system allocate a PIN when the user adds it to their cart? Wouldn't you solve your problem by simply waiting until the transaction is complete? -Kevin - Original Message - From: Hayan Al Mamoun [EMAIL PROTECTED] To: PHPList (E-mail) [EMAIL PROTECTED] Sent: Monday, June 03, 2002 1:49 AM Subject: [PHP-DB] PHP Session End. Dear ALL, I'm programming a web site that has a small shopping cart to sell virtual information, I must allocate the information I'm selling (Phone Card PIN Codes) and block it from others, once a user adds it to his cart, and on the other hand, I must set it free (DeAllocate it) when the user leaves the site without checking out. I'm using session to figure out the users, but I donno how to sense when the user has left the site, and his session is closed (automatically). Any Body Can Help?? Thanx in Advanced bst rgds Hayan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Mutiple inserts with one query?
To be honest I have no idea (just learning SQL myself) but the MySQL manual does offer at least a few tips to help opitmize your insert statements in looped scenarios... http://www.mysql.com/doc/I/n/Insert_speed.html -Kevin - Original Message - From: Leif K-Brooks [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, May 31, 2002 1:12 PM Subject: [PHP-DB] Mutiple inserts with one query? Is there a way to insert multiple identical rows in one mysql query? I'm using a looped query right now, which uses more server load than it should... Thanks! -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Using functions in SELECT statements
Your problem may be the use of single quotes around your wildcard search string. I haven't tested it but SQL probably works like PHP in that single quotes denote exact values while double quotes allows evaluation of the quoted string. -Kevin Robin S McKenzie [EMAIL PROTECTED] wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... I'm trying perform a case-insensitive test for a name. These are stored like Association of ..., and I want to convert both the filter and the test data to lower- (or upper-) case. Why doesn't this work: ? SELECT * FROM [Organisation Membership] WHERE lower(organisation) LIKE lower('%$SearchBox$%') Robin McKenzie Department of Mechanical Engineering University of Bristol e:[EMAIL PROTECTED] e:[EMAIL PROTECTED] m:+44(0)7970 058712 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Using functions in SELECT statements
(in response to my own response) By the way it just occured to me that the default SELECT statement is already case insensitive. So is any of this is even necessary? If you want to display the selected items in lower case, then do that in your script with strtolower(); -Kevin Robin S McKenzie [EMAIL PROTECTED] wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... I'm trying perform a case-insensitive test for a name. These are stored like Association of ..., and I want to convert both the filter and the test data to lower- (or upper-) case. Why doesn't this work: ? SELECT * FROM [Organisation Membership] WHERE lower(organisation) LIKE lower('%$SearchBox$%') Robin McKenzie Department of Mechanical Engineering University of Bristol e:[EMAIL PROTECTED] e:[EMAIL PROTECTED] m:+44(0)7970 058712 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php