Jump to content

Mr Hyde

Members
  • Posts

    22
  • Joined

  • Last visited

    Never

Everything posted by Mr Hyde

  1. I must not be understanding the question right... is there any reason why the string returned by the link I submitted above wouldn't work? just for reference: REGEXP '[[:<:]]some[[:>:]]' = 1 AND `` REGEXP '[[:<:]]words[[:>:]]' = 1
  2. Not to the best of my knowledge, but I do have a php query builder class that parses search strings like: this_is_treated_as_an_OR_string "this is a quote" +this_is_something_that_has_to_be_in_the_results -i_dont_want_this wildca%rd -"I don't want this quote in my results" and automatically converts them into a regex string. It's a module in what will be an open source searching class I'm building that I can send you. That would obviously have way more functionality than you require, but it's a nice little bit of code. I just whipped up a really basic interface to it here: http://fiftyoff.phpwebhosting.com/business/sql_search_query_builder.php?test I can't guarantee that I'll have that up permanently. I'd be happy to send you the code if you like. Otherwise, the style query that you are looking for is like this: SELECT * FROM `table` WHERE `column` REGEXP '[[:<:]]or1[[:>:]]' = 1 OR `column` REGEXP '[[:<:]]or2[[:>:]]' = 1
  3. Yeah, if you can use them, regular expressions are the way to go. I've given up on fulltext and %like% searches for the bulk of my searching for a while now.
  4. modify your REGEX statement to look like this: "WHERE $column REGEXP '[[:<:]]" . $number . "[[:>:]]' = 1" Using "[[:<:]]" makes the engine look for non alphanumeric characters as word boundaries. It's pretty darn fast, and it should tear into those comma delimited columns without a problem... especially if they are indexed.
  5. Sorry, mispoke... it looks like it's joining ONE table to itself. If I were you I would print off the values of these variables: $glob['dbprefix'] $_GET['productId'] and look at that sprintf function page to see how it inserts them into the output. OR you could just print off $query and find out the same thing.
  6. Can't tell you what the query is doing besides joining tables and selecting a couple columns, but sprintf is a native formating function for php. A lot of developers use it as a way to verify the input into a query. Once you figure out what the tables contain, and what values the columns hold, the answer to your question should be more obvious. I however, have no idea what those tables contain. I'll also give you another hint... when you're writing queries, or looking at them, I find it's MUCH easier to figure out what's going on if you format them a little so they're easier on the eyes like so: $query = sprintf(" SELECT B.*, T.option_name, T.option_type, M.value_name FROM %1\$s_options_bot AS B LEFT JOIN %1\$s_options_mid AS M ON B.value_id = M.value_id, %1\$s_options_top AS T WHERE B.option_id = T.option_id AND B.product = %2\$d ORDER BY T.option_name, M.value_name ASC", $glob['dbprefix'], $_GET['productId']); Using extra lines doesn't effect MySQL any, but it sure as hell makes it a lot easier to read for me.
  7. Can you login to MySQL as the root user, using that password?
  8. Here is a good launchpad for building a CHAP login system. Basically, the idea is to store the encrypted password in your table, and when a client tries to login, you encrypt their credentials client side via javascript (now their password is the same as what you actually have in your database), pass a salt, then you encrypt again on both sides via the shared salt and compare the results. It's certainly not an impervious method, but if ssl is cost prohibitive that's a good way to go. My thinking is that if a client trusts you with their password, the least you can do is try to protect it. It's very likely that they use the same password on their banking website, email etc.
  9. Don't know if this will help or not, but I am kind of picky about my connections in my code. I always up up any function call like this: sql_function($fake_var, $fake_var2, $conn = FALSE){ if($conn==FALSE){$conn = conn();} ... Where conn() is a mysqli connection helper function. This way, when I call an object it could have a million pages included, but I keep on passing the connection to each method or function individually so they will only use one connection. Php is supposed to manage connections real well... but I would rather avoid the whole problem in the first place. Of course, in a php class, I usually create a single connection in the constructor, set it to be a class constant , then just access it, or pass it on to other called classes like $this->conn(); Don't forget that you can set php ini variables temporarily in your script like: ini_set('mysql.connect_timeout', $int); So if you think that REALLY is the problem, you can call that and straighten it out. Hope that helps.
  10. Ah, okay... I assume the table with staff has different columns then the one for the players, otherwise you would probably just combine the two tables and add another column for group ("staff" or "player") then order your results by group.
  11. Not tested: SELECT rooms.visit_data, rooms.areaid, rooms.room FROM visit_data LEFT JOIN rooms ON rooms.room = visit_data.room Go through this join tutorial, it'll help teach you how to do it yourself.
  12. You mean you want two ROWS right... not two lines? You'll have to do something like AbraCadaver suggested using a UNION, or you could just concatenate two sql strings togther and use mysqli_multi_query() (which would be REALLY close to simultaneous). If you don't mind me asking, why do you want two rows returned if you can have them both returned in one?
  13. Yeah, that will work. I found through further research that you can't reuse an alias in the select statement. My only recourse is to duplicate queries... which offends my sense of aesthetics, but it works. I could also create a view, I guess, but I would like to avoid locking the table. Thanks for the response.
  14. It sounds like you need something like this: SELECT * FROM table1 as t1 LEFT JOIN table2 as t2 ON t1.field1 = t2.field1 WHERE t1.field1 LIKE \"%$trimmed%\"; This joins the two columns where the names are the same. It should return results like: John, 18, male all on one row. Also, because we are joining the two tables on field1, where they are equal, there is no use searching both columns... only searching one would be sufficient.
  15. It's pretty secure... BUT I would still either salt it by concatenating a secret word on to the front or back of the password before encrypting it, or use php's md5 function and salt it, and then pass it to password() in MySQL. With the wide proliferation of rainbow tables now, you can't be too careful. Of course if you are passing your user's password via clear text then it really doesn't matter that much anyway. Worrying about password security when you pass a password like that would be like worrying about your deposit in the bank, but giving it to a 10 year old to take across the street and drop it off first.
  16. Got a question, it may be stupid, but regardless I don't know the answer. Background: I'm trying to develop a system that searches product names for matches and ranks by relevance. I plan on calculating the relevance value by totaling the occurrences of each word in the query and giving a higher rank to matches that occur less frequently in my data set. So, if I want to calculate the relative word rank for "foo" when search "foo bar" I will find something like (count(foo) + count(bar))/count(foo)) (obviously this is pseudo code). My problem is that I can find count(foo) and count(bar) easy enough, but I can't get MySQL to add the two columns... here is the query I ran: mysql> SELECT -> id, -> name, -> ( -> SELECT -> count(*) -> FROM `search_index` -> WHERE -> `name` REGEXP '[[:<:]]burton[[:>:]]' = 1 AND -> `table` = 'c5' -> ) as w1, -> ( -> SELECT -> count(*) -> FROM `search_index` -> WHERE -> `name` REGEXP '[[:<:]]bindings?[[:>:]]' = 1 AND -> `table` = 'c5' -> ) as w2, -> (w1+w2) as total -> FROM `search_index` -> WHERE -> `name` REGEXP '[[:<:]]burton[[:>:]]' = 1 AND -> `table` = 'c5' -> OR `name` REGEXP '[[:<:]]bindings?[[:>:]]' = 1 AND -> `table` = 'c5' LIMIT 10; ERROR 1054 (42S22): Unknown column 'w1' in 'field list' The error is pretty explicit as far as what is going wrong. I just want to know how to restructure my query to fix it. Pseudo code, and untested answers are perfectly acceptable, if pointed the right direction I'm more than capable of figuring things out by myself. Btw, I'm not interested in fulltext searches for a multitude of reasons... I'm happy to explain why, but that would probably be a good subject for a different thread.
  17. I don't like to write code when I can just use an open source project: http://www.phpclasses.org/package/4718-PHP-Manage-knockout-tournament-games.html phpclasses.org is an awesome site...
  18. Oooowwww fun... tested: CREATE TABLE `db`.`test` ( `test_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `website` INT NOT NULL , `keyword` VARCHAR( 30 ) NOT NULL ) ENGINE = MYISAM ; INSERT INTO `db`.`test` (`test_id`, `website`, `keyword`) VALUES (null, 1, 'cabbage'); INSERT INTO `db`.`test` (`test_id`, `website`, `keyword`) VALUES (null, 1, 'brussel'); INSERT INTO `db`.`test` (`test_id`, `website`, `keyword`) VALUES (null, 2, 'cabbage'); SELECT t1.keyword, t1.website, t2.keyword, t2.website FROM db.test as t1 LEFT JOIN db.test as t2 ON t1.website = t2.website WHERE t1.keyword = 'cabbage' AND t2.keyword = 'brussel'; I basically just joined the table to itself
  19. <?php $allowed_groups = array(1,5); $can_see = FALSE; foreach ($allowed_groups as $allowed) if (in_array($allowed, $user_info['groups'])) { $can_see = TRUE; break; } if ($can_see) { echo ' <div> <font class="font"><div>' . ssi_welcome() . '</div> </div></font> </div>'; } else { //message or section to show if they are not allowed. echo ''; } ?> That should work...
  20. Definitely check to make sure magic quotes aren't on like BlueSky mentioned.... and NEVER EVER EVER EVER use word as an editor. It inserts all kinds of random characters that screw things up that you often can't even see. Word is not a code editor. At least use notepad instead.
  21. What kind of output are you expecting from ssi_welcome()? Does it print something to the screen? Does it return a variable? Do you know?
×
×
  • 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.