Jump to content

Strahan

Members
  • Posts

    80
  • Joined

  • Last visited

Everything posted by Strahan

  1. Ahh. OK, sounds good. Thanks!
  2. Hi. I made a website to keep track of where I am in various manga. I noticed that sometimes the title I grab looks OK when rendered on the page but in the database it's weird looking I did a for ($x=0; $x<strlen($title); $x++) loop and dumped $x, char at $ pos and ord of char: 0 = T = 84 1 = h = 104 2 = e = 101 3 = = 32 4 = D = 68 5 = e = 101 6 = m = 109 7 = o = 111 8 = n = 110 9 = = 32 10 = K = 75 11 = i = 105 12 = n = 110 13 = g = 103 14 = � = 226 15 = � = 128 16 = � = 153 17 = s = 115 18 = = 32 19 = D = 68 20 = a = 97 Is there some nifty built in function for converting that weird stuff to a regular apostrophe (and convert any other weird stuff)? I mean, I can write a filter to look for 226/128/153 and replace with ' but I'm sure there must be a proper way to handle that stuff. Thanks!
  3. Ahhhh thanks all, that clears it up. I put a UID criteria in the ON clause and now it properly gives me unread chapters for whatever user is polling. Nice. Funny thing is, I'm going to all this effort to build multiuser capability yet I'll probably hardly ever have other people using the site lol. I saw a drawing on DeviantArt by an artist, tidusyuna, of Shirayuki Mizore from Rosario+Vampire and really liked it. She was my favorite character from the show, and this guy drew her with her long hair before she chopped it off a few eps in. Decided to use it for an avatar. Also commissioned him to do a custom piece with her for my BMW's navigation boot screen, lol.
  4. This sounds easy, but I'm having headaches with it. Maybe I shouldn't be working at 1:20 AM lol. Anyway, I have two tables. One is chapters, one is readlog. I want to pull a list of chapters but only ones I haven't read. This is the schema: chapters: cid, int, index, autoinc id, int, the id of the book chapter, varchar, the chapter number url, varchar, link to the chapter text readlog: rlid, int, index, autoinc uid, int, relates to user table cid, int, relates to chapters dateread, datetime, self explanatory So I did: SELECT chapter,dateadded,dateposted,url FROM chapters c LEFT OUTER JOIN readlog r ON c.cid = r.cid WHERE id = ? AND dateread IS NULL ORDER BY chapter+0 Works great. Then I realized if my sister goes and reads a chapter of the same book, the query drops that chapter for me too. I tried adding AND r.uid = ? and pass it my UID, but then I get nothing at all. Makes sense, because if I'm saying r.uid must be 1 then it only matches chapters I've read (inverse of what I want) but I also say dateread is null so it matches nothing. I changed it to r.uid <> 1 but that didn't work either. My brain is fried, I can't figure out the right way to do this. Any suggestions? Thanks!
  5. Gotcha, I'll do some reading on the things you brought up. Sorry for being dense
  6. The problem I was having and wasn't sure about was whether or not it's safe to store their ID in a session variable and use that to determine if they are logged in, and logged in as whom. As I said in the first post, I just check if a session var is set and if not they get the login prompt and if so the system uses the id in the var when it needs to check if the person has rights to do something. I wasn't sure if session variables were exposed to the client, where they could change their ID in the session var and become someone else. I also see, looking at the examples, that yea I was way off base in regards to my exposed IDs on sites notion. I can't find a site user interaction that doesn't have them visible, as you guys said lol. My bad
  7. Hello. I have two tables and I want to get data back from both in one query. Table 1: media Fields fileid (int, index), parent (int - match to info 'id' field), filename (varchar[30]), filesize (bigint) Table 2: watchlog Fields wid (int, index), parent (int - match to media 'fileid' field), watched (datetime), user (int) So when I do a select to get all the files in a parent folder, I'd like to show if they were watched or not and have a mouseover to show when. Problem is, sometimes things are rewatched and there are multiple entries with the same parent in watchlog. If I do: SELECT m.fileid,m.filename,w.watched FROM media m LEFT OUTER JOIN watchlog w ON m.fileid = w.parent WHERE m.parent = 6816 ORDER BY filename I get all my files so I can list them, but ones with multiple watches are listed multiple times. I'd like it to return just one watchlog record per each media record. It should choose the latest "watched" value when it picks the one record to return. How do I do that? Oh, and I want the watchlog to only return data for the user in question. I tried the WHERE clause "m,parent = 6816 AND w.user = 1" but then it only returned the two records in media out of 24 that had been watched. ------------ Oops, nevermind. I posted this then a minute later figured out a way, lol. Is this correct: SELECT m.fileid,m.filename,(SELECT datewatched FROM watchlog w WHERE parent = m.fileid AND w.user = 1 ORDER BY datewatched DESC LIMIT 1) FROM media m WHERE m.parent = 6816 ORDER BY filename I mean, it works, but is that the "right" way to approach it? Want to be sure I'm keeping it efficient as possible. Thanks.
  8. I find it hard to believe you can put forth such a gross generalization. Just because IPBoard exposes a user's db ID doesn't mean every other PHP site in existence does. My problem (which I defined in my first post) was just that I wasn't sure if it was secure/acceptable to store a piece of data in a session variable to track whether or not a user was logged in, or if I should find some other method. requinix said "Unless you have a weird PHP configuration that's possible but one I've never actually seen anyone do (because it's so stupid) then data in $_SESSION is safe" which answers that question, ergo my closing this out having found my answer.
  9. Sounds good, thanks a lot. Well, yea, it basically is a bunch of ideas at the end there because I wasn't sure the best way to approach it. I explained what I am doing now in the first few sentences (storing IDs). When I say ID, I don't mean like "Jacques1" I mean the index value in the database. Nobody sees that. I just wasn't sure if that was a good approach or not.
  10. Hi. Question for you all. The way I authenticate now, I check if a session var "authUID" exists. If not, I present the auth form. If they log in correctly, I set $_SESSION["authUID"] = $dbrecordsetvar["uid"]; Is that secure? Session vars are not exposed to the client at all, correct? Just wanted to verify. Also, would it be better to store the password they entered on the form (encrypted) instead of the db's UID for their account then just check it against the DB every time I load sensitive pages? I figured that may be better as it would catch if the pwd changed on another client and boot them but I wasn't sure if I'm being over paranoid and wasting resources on a SQL round trip every time or not. It's a low traffic application though, so I think the resource hit would be negligible. Thanks!
  11. Thanks a lot, that's just what I needed
  12. I'm reading the docs for field, I'm a little lost. I don't want to pull back just one type, I want all the records I just want them to sort by cstype in the order listed. Those SQL commands do that kind of thing? I guess I need to keep reading heh.
  13. Hi. I have a web app to track shows I watch. I have it pulling data from a provider who gives API access to their database. The character database has a field to determine a character's status in the show. These are the values it sends: main character in secondary cast in appears in cameo appearance in I have them in my local database using the same value. I just went to view characters for a show and "cameo" is at the top of the list. That is annoying, as cameo characters are of low relevance. I'd like to sort the SQL result in the order I listed them above. I'm doing: $sql = $pdo->prepare("SELECT csname,csgender,csimage,csdesc,cstype,(SELECT GROUP_CONCAT(CONCAT(s.syid, CONCAT(':', seiyuu))) FROM anidb_seiyuu_cast sc INNER JOIN anidb_seiyuu s ON sc.syid = s.syid WHERE ca.csid = sc.csid) AS seiyuu FROM anidb_cast_aid ca INNER JOIN anidb_cast c ON ca.csid = c.csid WHERE ca.aid = ?"); $sql->execute(array($aid)); $rows = $sql->fetchAll(); Which gives me the $rows array with all the data. I thought about looking up some way to sort it in SQL but I have no idea how to do that. On the PHP side I figured I could sort $rows, but the only way I can come up with is very inefficient. What would be the best way to accomplish this?
  14. Hmm. But doesn't that SQL injection vulnerability depend on bad input being put into the array being used to create the query? The array I use to build the query is created from a SELECT against "DB.INFORMATION_SCHEMA.COLUMNS". Unless someone renames my table columns I'd think I'm reasonably safe, yes?
  15. Thanks. Interesting. I usually put ? for each piece of data then do $sql->execute(array("blah")). Is using the : format safer or is there some other reason to do that? Thanks again.
  16. Hi. I have an array of fields I'm using to create a SQL query. Imploding it works fine for building the field list, then I figured I'd str_repeat to build the ?s. I did: $sql = "INSERT INTO test (" . implode(",", $fields) . ") VALUES (" . str_repeat("?,", count($fields)) . ")"; ...but that gave me: INSERT INTO test (blah, blah, blah) VALUES (?,?,?,) So to fix it I did: $sql = "INSERT INTO test (" . implode(",", $fields) . ") VALUES (" . substr(str_repeat("?,", count($fields)), 0, strlen(str_repeat("?,", count($fields)))-1) . ")"; It works, but that's ugly as sin. I thought about just doing VALUES(" . str_repeat("?,", count($fields)-1) . "?) but I prefer to make things totally automatic with no hard coding. I'm sure there is a more appropriate way. Could someone enlighten me? Thanks!
  17. Hi. I have a table with file data and one of the fields is "episode" for episode number. If it was just plain numbers, I'd do it int and all would be happy. However, sometimes in a folder I may have eps 1-13 then some specials which I number as SP01, SP02, etc so the episode field is varchar(10). If I have these episodes in the db (2, 1, 9, 3, 4, 5, 6, 7, 8, 10, 11, 12, 13, SP01, SP02) when I list files, I do SELECT * FROM media ORDER BY disporder,episode,filename and I get: 1 10 11 12 13 2 3 4...etc I have to make the episodes in the db as 01, 02, 03 to get the ordering right. Is there a SQL trick wherein I can get it to order those number fields in the right order, putting the alpha stuff at the bottom or would I just have to bite the bullet and zero prefix all my single digit episodes? Thanks!
  18. Hi. I have a table "info" with fields "id" and "name" then I have a table "files" with fields "fileid", "parent", "filename". My data for example would be: INFO: id, name 1, Documents 2, Media MEDIA: fileid, parent, filename 1, 1, expenses.xls 2, 1, accounts.xls 3, 1, letter.doc 4, 2, music.mp3 I want to be able to return a list of names in info where there are less than 3 files in "files". I tried SELECT name FROM info i INNER JOIN files f ON f.parent = i.id WHERE COUNT(f.fileid) < 3 but it said "Invalid use of group function". What am I doing wrong? How could I achieve this? Thanks!
  19. Thanks, I appreciate the advise. I'll implement your suggestions. In regards to #5, in this particular case I am opening, querying, closing because nothing else on the site uses SQL. In my more DB heavy pages, I create the connection at the top of the page and null it at the end. The meat of the page calls functions when necessary to do DB IO and passes the connection variable to the function as a parameter. Is that OK as far as performance?
  20. Hello. I'm trying to figure out an easy and efficient way to insert records. Sometimes the schema changes, so I wanted something reasonably dynamic. This is what I'm doing: $pdo = new PDO("mysql:host=" . SQL_Server . ";dbname=" . SQL_Database, SQL_User, SQL_Pass); $sql = $pdo->query("SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'table' AND column_comment <> 'skip'"); $rows = $sql->fetchAll(); $head = "INSERT INTO table ("; $tail = ") VALUES ("; $data = array(); foreach ($rows AS $row) { $head .= "{$row["column_name"]}, "; $tail .= "?, "; $data[] = $_REQUEST[$row["column_name"]]; } $sql = $pdo->prepare(str_replace(", )", ")", "$head$tail)")); $sql->execute($data); $pdo = null; I know it's a bit of a kludge, heh, but it works. I'm just wondering what a more proper way would be?
  21. Hi. I want to build a SQL string based on my form fields so when I have a new field in the db, I can just add a new form field and the sql update will automatically work. For example I have: <input type=text name=t_something> <input type=text name=d_something> <input type=text name=n_something> (post submission) $data = array(); foreach ($_REQUEST AS $key=>$val) { if (substr($key, 0, 2) == "t_") $data[substr($key, 2, strlen($key)-2)] = $val; if (substr($key, 0, 2) == "n_") $data[substr($key, 2, strlen($key)-2)] = (trim($val)==""?"0":$val); if (substr($key, 0, 2) == "d_") $data[substr($key, 2, strlen($key)-2)] = (trim($val)==""?"NULL":date("Y-m-d", strtotime($val)); } $data["posted"] = "NOW()"; $sql = "INSERT INTO table ("; $cnt = 0; foreach ($data AS $key=>$val) { $cnt++; $sql .= $key . (($cnt < count($data))?", ":""); } But that's klunky. The part that annoys me most is the $cnt. I want to know if the element I'm on is the last one or not, if it isn't I put a comma. If it was a number based index, I could just do a for x=0 to count, but since it's not I just did the array as key/val. I inc a counter each loop through so I know where I am in the array vs the count. That can't be a good way to do this. What would be a more efficient way to tell when I get to the end, or rather, what would be a more efficient way to build the query based on a form? Security isn't a concern as this form is just for my use on an internal LAN. Thanks!
  22. Thanks a lot, I'll implement your code in place of mine. I appreciate the help
×
×
  • 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.