Jump to content

hoopplaya4

Members
  • Posts

    161
  • Joined

  • Last visited

    Never

Everything posted by hoopplaya4

  1. Hi, I currently have a string that is well over 100 characters long, and I'm using preg_split to split the array at a whitespace, so that words are not cut in the middle. However, my problem is, that the preg_split is only splitting the first 100 characters, and not any subsequent 100 characters after that. For example: <?php $myString = "This is 350 characters long, let's say"; $split = preg_split('/^(.{0,100})\s/', $myString, 0, PREG_SPLIT_DELIM_CAPTURE); //Notice, I want to split every 100 characters. $array_shift($split); print_r($split); // This outputs something like: Array ( [0] => First 100 characters [1] => All other characters, no matter the length of the string.) I am looking for it split in 100 character increments. Any ideas on how I might fix my preg_replace? Thanks!
  2. Hello all, I currently have 3 tables, which I'm using for people to make reservations for certain pieces of equipment. Here are my tables: tblEquipment: id name description 1 Camera Takes pictures 2 Projector Projects pictures 3 Laptop Portable Computer tblEvents: id start end first_name last_name email 1 2009-08-10 2009-08-11 John Doe jd@email.com 2 2009-08-15 2009-08-16 Jane Doe jd@email.com tblEventData: id eventID equipmentID 1 1 1 2 1 2 Right now, a user will submit a query with their requested times, then they will see all available equipment. So, using the exampe above, if a user is looking for equipment between 8/10-8/11, he will see that the only equipment that is available is: equipmentID 3 (Laptop). How can I create my query to return only the available equipment based on the requested times? This is what I've come up with so far, but can't get it to work: SELECT tblequipment.id as name, tblEvents.start as start, tblEvents.end as end FROM tblEquipment INNER JOIN tblEventData on tblEventData.equipmentID = tblEquipment.id INNER JOIN tblEvents on tbleventdata.eventID = tblEvents.id WHERE NOT EXISTS(SELECT * FROM tblEvents WHERE $end >= start AND $start <= end) Any ideas? Thanks!
  3. Hi, I'm wondering if there's something similar to array_unique, but for strings? For example, I have a string: $ids = "23,24,25,24,24,24"; Is there anyway to strip the string down so that it keeps the unique numbers separated by the commas? Thus, I would retrieve from the $ids above: 23,24,25. Thanks!
  4. Thanks for the reply lonewolf. So, I think that was close, but it now displays something like this: John Smith, John Smith, John Doe, Jane Doe Note: The <and> does not seem to appear and John Smith shows up twice.
  5. Hello, I currently have a SELECT statement (note, this is a simplified version): SELECT group_concat(DISTINCT receiver.usrFirst, ' ', receiver.usrLast) as receiver_name, COUNT(DISTINCT receiver.usrFirst) as receiver_count //etc.... That outputs the following: <?php echo $row['receiver_name']; //Which outputs: John Smith,John Doe,Jane Doe echo $row['receiver_count']; //Which outputs: 3 I'd like to see if I can get some help writing the PHP to display it like: John Smith, John Doe, and Jane Doe 1) Notice the <space> after the comma 2) Notice the <and> before the last person. Any direction on this one would be very helpful.
  6. Hi Keith: That seemed to do the trick. I was trying to place the COUNT is the first 'group_concat(DISTINCT receiver.usrFirst). Also, not sure what I was thinking with the 'is_read' portion--probably just put it in there during the dark hours of the night. Thanks again, you've helped me learn tremendously!
  7. Hey Keith: Thanks for the reply. After some finagling, I came up with the following query: SELECT pm_info.is_read, group_concat(DISTINCT receiver.usrFirst) as receiver_name, pm_data.date_sent, pm_data.title, pm_data.thread_id, pm_data.id as data_id, MAX(date_sent) AS thread_max_date_sent FROM pm_info INNER JOIN pm_data ON pm_info.message_id = pm_data.id INNER JOIN tblUsers AS receiver ON pm_info.receiver_id = receiver.usrID WHERE pm_data.sender_id = '$usrID' GROUP BY pm_data.thread_id ORDER BY thread_max_date_sent DESC This works properly in that it groups the threads, and it actually works great. There's only one thing that I'd like to see if I can get some help on. It currently displays a row as this: To: Joe, Steve, Ed, Bob Subject: Whatever However, if a thread has 30 recipients, I don't want it to display every single name. Instead, I'd like to add a COUNT somewhere (not sure where?) in the SELECT statement that counts the recipients. This way, I could implement in my PHP that say: <?php if ($row['count_recipients'] > 3) { echo "To: ".$row['count_recipients']." people."; } //Which would output: To 4 people Any idea on how to add a COUNT to my current statement to get that data? Thanks!
  8. Hi Keith, I think you got it on this one! I made a slight correction to your query: SELECT pm_info.is_read, sender.usrFirst as sender_name, pm_data.date_sent, pm_data.title, pm_data.thread_id, pm_data.id as dataid, thread_max_date_sent FROM pm_info INNER JOIN pm_data ON pm_info.message_id = pm_data.id INNER JOIN tblUsers AS sender ON pm_data.sender_id = sender.usrID INNER JOIN (SELECT thread_id, receiver_id, MAX(date_sent) AS thread_max_date_sent FROM pm_data a INNER JOIN pm_info b ON a.id = b.message_id GROUP BY thread_id, receiver_id) deriv1 ON pm_data.thread_id = deriv1.thread_id AND pm_data.date_sent = deriv1.thread_max_date_sent AND pm_info.receiver_id = deriv1.receiver_id WHERE pm_info.receiver_id = '$usrID' ORDER BY deriv1.thread_max_date_sent DESC This appears to be working pretty good! Also, I do have one additional question: I'm looking for some help on setting up a "sent messages" query as well. It would be the same as sent messages, except, if a message is sent to multiple people (more than 1), it will not display every sent message. Instead, it would shrink it down into one row. I currently have this: SELECT pm_info.*, sender.usrFirst as receiver_name, pm_data.*, thread_max_date_sent FROM pm_data INNER JOIN pm_info ON pm_info.message_id = pm_data.id INNER JOIN tblUsers AS sender ON pm_data.sender_id = sender.usrID INNER JOIN (SELECT thread_id, sender_id, MAX(date_sent) AS thread_max_date_sent FROM pm_data GROUP BY thread_id, sender_id) deriv1 ON pm_data.thread_id = deriv1.thread_id AND pm_data.date_sent = deriv1.thread_max_date_sent AND pm_data.sender_id = deriv1.sender_id WHERE pm_data.sender_id = '$usrID' ORDER BY deriv1.thread_max_date_sent DESC And it displays something like this: To: Ed (thread_id=22) Subject: Hello There To: Joe (thread_id=22) Subject: Hello There Instead, I'd like it to display something like: To: 2 Users Subject: Hello There If I need to put this request in a new thread, I can do so. Thanks again for your help and get some rest!
  9. Okay, so I thought I had this figured out, but it appears there's something I missed out on. I've just found that if there are "more than 2" people involved in a thread (e.g., multiple recipients), it no longer groups the rows by "thread_id". I'm thinking this might be due to the fact that when a 3rd person "replies" to the thread, there's a new "sender_id" in the mix. Here's a quick example, adding on to what we've been talking about: From: Ed (pm_data.id=12) RE: single message (thread_id=13587) From: Ed (pm_data.id= RE: Test Number 2 (thread_id=16256) So, if another user named Joe (let's assume they were one of the original recipients of a message), replies to the same thread (RE: single message (thread_id-13587), I'd want the inbox to look like this: From: Joe (pm_data.id=13) RE: single message (thread_id=13587) //Note: same thread_id but new pm_data id. From: Ed (pm_data.id= RE: Test Number 2 (thread_id=16256) //This remains unchanged. From: Ed (pm_data.id= RE: Test Number 2 (thread_id=16256) Therefore, what I am looking to display is: "The latest message from each thread, no matter who the sender is." I don't want to have two rows that have the same thread_id but different sender_id. Instead, just the most recent out of the two. Is what I'm asking for possible with my current db structure? Thank you!
  10. WOW! That seems like it worked! I think that's one of the most complicated SELECT statements I've ever used. It certainly helped. Thanks again for your help!
  11. Yes, you are correct, but actually, the latest message from each thread would look like- From: Ed (pm_data.id=12) RE: single message (thread_id=13587) From: Ed (pm_data.id= RE: Test Number 2 (thread_id=16256) Nonetheless, I tried your new edit, and now it only displays: From: Ed (pm_data.id=12) RE: single message (thread_id=13587) Interestingly enough, it's not even displaying (pm_data.id=. Any ideas? My brain is starting to hurt.
  12. Ok, so I went ahead and implemented your edits. No errors or typos, that I could see. However, it now outputs the following: From: Ed (pm_data.id=10) single message (thread_id=13587) From: Ed (pm_data.id=12) RE: single message (thread_id=13587) From: Ed (pm_data.id= RE: Test Number 2 (thread_id=16256) Any other ideas on how to fix this?
  13. Hey Keith, Thanks for the reply. That definitely makes more sense. Do you have any suggestions for fixing/re-arranging my statement to yield the results I'm looking for? Thanks! EDIT: Nevermind, just saw your edit. I'll take a look at it and report back.
  14. Hello all, I need some help finding the 'leak' in my SELECT statement. I currently have 3 tables (only two will be shown, because the other is not pertinent). (uploaded images so I wouldn't have to type entire table). What I'm trying to do is output an "inbox" that displays the most recent thread at the top and group by thread (meaning, you don't see the same thread twice in your inbox). My query works for the most part, except for the fact that it does not appear to be displaying the "most recent thread" based on the timestamp. Here's my SELECT statement: <?php $data = mysql_query("SELECT pm_info.is_read, sender.usrFirst as sender_name, pm_data.date_sent, pm_data.title, pm_data.thread_id, pm_data.id as dataid FROM pm_info INNER JOIN pm_data ON pm_info.message_id = pm_data.id INNER JOIN tblUsers AS sender ON pm_data.sender_id = sender.usrID WHERE pm_data.date_sent IN(SELECT MAX(date_sent) FROM pm_data WHERE pm_info.message_id = pm_data.id GROUP BY pm_data.thread_id) AND pm_info.receiver_id = '$usrID' GROUP BY pm_data.thread_id ORDER BY date_sent DESC") or die(mysql_error()); And here's what the incorrect output looks like: Any help on figuring out what's wrong with my statement is greatly appreciated!!
  15. Hey all, I need a bit of help with my MySQL Select Statement. Basically, I have an "inbox" with several items, and some of the items are "threaded", thus, I want it to show the most recent thread at the top. My current MySQL statement works in the fact that it moves the most recent "thread_id" to the top. However, it is not displaying the most recent "message subject" (called the 'title' field), which should be associated with the thread_id. Instead, it's showing the "message subject" from the first thread_id. Here is my current SELECT statement: <?php $data = mysql_query("SELECT tblpm.thread_id, tblpm.receiver_id, tblpm.title, MAX(tblpm.content), tblpm.date_sent, tblpm.sender_id, tblpm.read_pref, MAX(tblpm.id) as unique_id, tblusers.usrFirst AS first_name, tblusers.usrLast AS last_name FROM tblpm LEFT OUTER JOIN tblusers ON tblpm.sender_id=tblusers.usrID WHERE receiver_id ='$usrID' GROUP BY thread_id ORDER BY unique_id DESC") or die(mysql_error()); I guess what I am looking for is a way to also display the most recent 'tblpm.content' field. As you can see above, I tried using MAX(tblpm.content), but apparently that does not work. Any ideas? Thanks.
  16. Thanks akitchin, that certainly worked. I knew I had to use a JOIN, but I need someone to explain it to me for me to understand how to use them. I'll check out the Wikipedia entries. Thanks again.
  17. Hi All, I've got a MySQL table: tblpm: id message_id receiver_id title content date_sent sender_id Now, what I am currently doing is retrieving all the data from this table based on the following criteria: <?php require("../connection.php"); $rs = mysql_db_query($DBname,$sql,$link); $data = mysql_query("SELECT * FROM tblpm WHERE receiver_id ='$usrID' ORDER BY id DESC") or die(mysql_error()); while($row= mysql_fetch_array( $data )) { echo "The id is:"; echo $row['id']; echo "The receiverID is:"; echo $row['receiver_id']; echo "The Sender ID is:"; echo $row['sender_id']; // <--- This is what I would like to use to grab the sender's First and Last name from another table called "tblusers." } How could I use the "sender_id" field to match it with the "usrID" field in another table (called tblusers), so that I can retrieve the First and Last name of the sender and place it in the "while loop"? Hope this makes sense.
  18. That's a good point, I hadn't thought about having it "threaded" when one user replies. How would I go about doing that? Because the 'id' field is 'incremental', how would I give it the same id? Any ideas on that?
  19. Back on topic, sort of, I do have one question when working with the example provided above. With the pm_info table, how would I insert the "message_id" in my MySQL statement? Currently, I'm using: <?php mysql_query("INSERT INTO `pm` (title, content, sender_id) VALUES ('$subject', '$message', '$sender')" ); I notice that in 'pm' the id is auto-increment. Should I set the 'pm_info' to auto increment as well? Thanks.
  20. Thanks for the replies everyone. This is what I have as a basic idea thus far: private_messages tbl: id date_sent title content status ENUM ('unread', 'read') DEFAULT 'unread' private_message_relation tbl: id message_id sender_id receiver_id Anything that you can think of that I might be missing and/or should remove? Thanks!
  21. Yeah, actually it does. I'm not trying to copy facebook, I'm just trying to create something for myself. Thus, I want to be the best that I can be, I want to know the best way to structure my db, so why not learn from the best?
  22. Hi All, I'm currently working on building a messaging system, similar to a typical email system or Facebook's messaging system, complete with an Inbox, Sent Items, and basic features such as "Unread" and "Read." Do you have any tips/advice on how to properly structure a MySQL database table for this type of endeavor? For example, what types of columns should I be sure to include, etc. If you need more information, let me know. Thanks in advance for your help!
  23. Okay, thanks. No, the "extra" code was hard-coded into the file. I've gone ahead and alerted my host provider's security team. Hopefully they will provide some further insight on the matter.
×
×
  • 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.