[preg_split (probably easy)]

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!

[Reservation System - Semi-Complicated SELECT Statement]

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!

[[SOLVED] array_unique for strings?]

Sweet, thanks! 

[[SOLVED] array_unique for strings?]

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!

[[SOLVED] For Loop, Commas and SELECT Statement]

That works great.  Thanks!

[[SOLVED] For Loop, Commas and SELECT Statement]

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. 

[[SOLVED] For Loop, Commas and SELECT Statement]

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.

[[SOLVED] Finding the Issue With My SELECT Statement]

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!

[[SOLVED] Finding the Issue With My SELECT Statement]

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!

[[SOLVED] Finding the Issue With My SELECT Statement]

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!

 

[[SOLVED] Finding the Issue With My SELECT Statement]

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!

[[SOLVED] Finding the Issue With My SELECT Statement]

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!

[[SOLVED] Finding the Issue With My SELECT Statement]

Hi

 

Take it you just want the latest message from each thread. Like:-

 

From: Ed (pm_data.id=10)  single message      (thread_id=13587)
From: Ed (pm_data.id=   RE: Test Number 2   (thread_id=16256)

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. 

 

[[SOLVED] Finding the Issue With My SELECT Statement]

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?

 

[[SOLVED] Finding the Issue With My SELECT Statement]

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.

[[SOLVED] Finding the Issue With My SELECT Statement]

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

[MySQL SELECT Statement]

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.

 

 

[[SOLVED] Multiple MySQL Select]

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.

[[SOLVED] Multiple MySQL Select]

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.

[Best Way to Database for Messaging]

Also; remember that mail messages are usually threaded. So starting a mail message would be much like starting a forum topic, except for the fact that you have to change the "thread" from read to unread, based on who replies. I've done this before. It's not magic, once you get your head around it.

 

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?

[Best Way to Database for Messaging]

Honestly, this is how I'd do it, but I'm no database engineer by any means.

 

CREATE TABLE `pm` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `title` varchar(100) NOT NULL,
  `content` text NOT NULL,
  `date_sent` timestamp NOT NULL default '0000-00-00 00:00:00',
  `sender_id` mediumint( unsigned NOT NULL,
  PRIMARY KEY  (`id`)
)

CREATE TABLE `pm_info` (
  `message_id` int(10) unsigned NOT NULL,
  `receiver_id` mediumint( unsigned NOT NULL,
  `read` tinyint(1) NOT NULL default '0',
  `date_read` timestamp NOT NULL default '0000-00-00 00:00:00',
  KEY `message_id` (`message_id`)
)

 

This way, you could send a PM to multiple people and see when they first read it.

 

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.

[Best Way to Database for Messaging]

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!

[Best Way to Database for Messaging]

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? 

[Best Way to Database for Messaging]

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!

[[SOLVED] PHP/MySQL Injection Question]

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.