Array Iteration in a Multi-Dimensional

[geudrik]

I'm not entirely sure how to phrase the title, so I hope it's appropriate...

 

I have an array which looks like the following..

Array
(
    [contact] => Array
        (
            [0] => Array
                (
                    [name] => Someone Cool
                    [number] => 1234567890
                )

        )

    [messages] => Array
        (
            [0] => Array
                (
                    [message] => A message that was recieved
                    [date] => 1234567890
                    [type] => 1
                )

            [1] => Array
                (
                    [message] => A message which was sent
                    [date] => 1322175702616
                    [type] => 2
                )
        )
    [calls] => Array
        (
            [0] => Array
                (
                    [datetime] => 1320674980836
                    [type] => 1
                    [duration] => 7
                    [number] => 1234567890
                )

            [1] => Array
                (
                    [datetime] => 1320675327541
                    [type] => 2
                    [duration] => 638
                    [number] => 1234567890
                )
       )

)

 

[messages] can have anywhere between 40 and 3000 children

[calls] can have anywhere from 0 to 200ish children

** These are generals, but realistically, either can have any number.

 

What I'm trying to do is dump all messages and calls.

 

The catch is this: When dumping a call, a check needs to be run against datestamps and only dump the next call coming out where the datestamp of the call is NEWER than the LAST message dumped, AND OLDER than the message that is to be dump in this cycle of the loop.  What I have so far is the following...

foreach($vardump['messages'] as $message) {

// So we have a reference point for the first call we're going to dump
if(!is_numeric($last_message_stamp)) { $last_message_stamp	=	$message['date']; }

            if( trim($vardump['calls'][$calls_counter]['datetime']) > trim($last_message_stamp) &&
		trim($vardump['calls'][$calls_counter]['datetime']) < trim($message['date'])) {

		// Dump the calls row!
		echo("
			<div class=\"chatbubble-call\">
            		<div class=\"chatbubble-person\">".$vardump['contact'][0]['name']."</div>
            		<p>".$vardump['calls'][$calls_counter]['number']."</p>
				<p>".date("h:i:s", $vardump['calls'][$calls_counter]['number'])."</p>
            		<div class=\"chatbubble-datetime\">".date("l, j F, Y -- h:i:s", $vardump['calls'][$calls_counter]['datetime'] / 1000)."</div>
        		</div>
		");

		// Increase our calls index
		$calls_counter++;


	}


	// Begin dumping Texts out 
	// Let's see which style type we're dumping out...
	if($message['type'] == 2) { // Message Sent 

		echo("
			<div class=\"chatbubble-sent\">
				<div class=\"chatbubble-person\">Pat Litke</div>
				<p>".$message['message']."</p>
				<div class=\"chatbubble-datetime\">".date("l, j F, Y -- h:i:s", $message['date'] / 1000)."</div>
			</div>
		");


	} else { // Message Recieved

		echo("
			<div class=\"chatbubble-recieved\">
				<div class=\"chatbubble-person\">".$vardump['contact'][0]['name']."</div>
				<p>".$message['message']."</p>
				<div class=\"chatbubble-datetime\">".date("l, j F, Y -- h:i:s", $message['date'] / 1000)."</div>
			</div>
		");

	}

	// Reset our $last_messge_stamp variable
	$last_message_stamp	=	$message['date'];




}

 

I should also add that I am using the following queries to pull this date out of my database...

$sql_get_messages			=	"SELECT * FROM messages 
										WHERE thread_number LIKE '%$the_thread%'
										ORDER BY epoch_date ASC";

$sql_get_calls				=	"SELECT * FROM calls 
										WHERE number LIKE '%$the_thread%'
										ORDER BY datetime ASC";								

$sql_get_contact			=	"SELECT * FROM contacts
										WHERE number LIKE '%$the_thread%'
										LIMIT 1";

 

But, I feel that this is horribly inefficient, and it doesn't work right... I only ever get a single call dumped.  What am I doing wrong?

 

[Psycho]

I'm not really understanding your criteria of

. . . only dump the next call coming out where the datestamp of the call is NEWER than the LAST message dumped, AND OLDER than the message . .

 

I *think* you are wanting to output all the messages and calls in a single, date ordered list. If so, then you can do that in your query with a UNION clause. Or you could put all the records into a single array and then sort the arrya by the date element of the records.

 

I think it would be helpful for you to show two or three real-world records for the elements and how you want the output for those records.

[geudrik]

The array posted at the top are real-world records, simply replaced the actual number and message with jibberish.  When dumped out in HTML format, it would show messages with a call showing up between messages where it's datestamp is next in line.  Note that the datestamps are in epoch format (in ms) for ease of comparison.

 

As for the union.. I've not used a union before, but from everything I'm reading, I'm unsure how I can use it over two different tables that don't have identical fields.. Eg: my calls table doesn't (for obvious reasons) have a message field. 

[geudrik]

I think I found my answer

 

http://stackoverflow.com/questions/1198124/combine-two-tables-that-have-no-common-fields

[geudrik]

Well, I'm making progress but now it's breaking... I get no messages dumped and my calls are in my message columns...

 

select * from (
select
message as message_message, 
message_type as message_type, 
epoch_date as message_date, 
'' as call_duration, 
'' as call_datetime,
'' as call_type
from messages
where thread_number='8028816610'
order by message_date asc
) as message_table

union

select * from (
select
duration as call_duration,
datetime as call_datetime,
type as call_type,
'' as message_message,
'' as message_type,
'' as message_date
from calls
where number='8028816610'
order by call_datetime asc
) as call_table

 

Where am I going wrong here :S

 

Here's a schema for you of both tables.

CREATE TABLE IF NOT EXISTS `calls` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `number` varchar(255) NOT NULL,
  `duration` int( NOT NULL,
  `datetime` varchar(255) NOT NULL,
  `type` int(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=178 ;

CREATE TABLE IF NOT EXISTS `messages` (
  `id` int(9) NOT NULL AUTO_INCREMENT COMMENT 'internal id of the message',
  `user_id` int(5) DEFAULT NULL COMMENT 'The UserID within this DB that that the message belongs to ',
  `thread_number` varchar(255) NOT NULL COMMENT 'The phonenumber of the thread that the message is part of',
  `message` text NOT NULL COMMENT 'The Message',
  `message_type` int(1) NOT NULL COMMENT '1: Send BY the other person, 2: Sent by YOU, the user',
  `epoch_date` bigint(255) NOT NULL COMMENT 'The Date/Time in Epoch Format.. much easier for comparison',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=16650 ;

[Psycho]

Well, you should really never use '*' in your SELECT queries unless you really need everything. In this case you should definitely NOT use '*' when you have a UNION. If you ever need to update the columns for one of the two tables this query would break.

 

Anyway, for UNION to work you must be SELECTING the same number columns and the columns (in the order given) must be of the same general type. So, if field 1 of the first SELECT is a varchar, then field 1 in the second SELECT should also be a varchar.

 

You look to be violating the second condition. The first field in the first query is 'message' (which I would assume is a varchar or text type), whereas the first field in the second query is duration, which I would assume is a numeric type.

 

Also, I don't know why you are creating the two queries as sub-queries. I don't know if that is also causing problems, but it is unnecessary. Plus, since you want the data ordered between tables (not independently in each table) the where clause needs to be made outside the union. Lastly, your field types are illogical. For one date field you are using a BigInt and for another you are using a varchar. Use one of the MySQL date type fields.

 

[Psycho]

Here are a revised table structures and UNION query that works how you need it

 

CREATE TABLE `calls` (
  `id` int(10) NOT NULL auto_increment,
  `number` varchar(255) NOT NULL,
  `duration` int( NOT NULL,
  `call_date` datetime NOT NULL,
  `type` int(1) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=180 DEFAULT CHARSET=latin1 AUTO_INCREMENT=180 ;

CREATE TABLE `messages` (
  `id` int(9) NOT NULL auto_increment COMMENT 'internal id of the message',
  `user_id` int(5) default NULL COMMENT 'The UserID within this DB that that the message belongs to ',
  `thread_number` varchar(255) NOT NULL COMMENT 'The phonenumber of the thread that the message is part of',
  `message` text NOT NULL COMMENT 'The Message',
  `message_type` int(1) NOT NULL COMMENT '1: Send BY the other person, 2: Sent by YOU, the user',
  `message_date` datetime NOT NULL COMMENT 'The Date/Time in Epoch Format.. much easier for comparison',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=16652 DEFAULT CHARSET=latin1 AUTO_INCREMENT=16652 ;

 

SELECT message AS message_message,
       message_type,
       0 AS call_duration,
       0 as call_type,
       message_date AS record_date
FROM messages
WHERE thread_number = '8028816610'

UNION

SELECT '' as message_message,
       0 as message_type,
       duration as call_duration,
       type as call_type,
       call_date AS record_date
FROM calls
WHERE number = '8028816610'

ORDER BY record_date

[geudrik]

Thank you very much for your help, it's much appreciated

 

That's does exactly the trick! 

 

 

[geudrik]

I thought that it'd be easy to sort the returned array by record_date simply by turning it into a sub query, but I wind up with an error when I do that

Every derived table must have its own alias

 

So I give the sub query a name of derrived_table, but it's still not sorting properly. I wind up with all of my calls at the end of the table, not interspersed through the messages based on their datetime. 

 

Where am I going wrong now? :S

 

The whole query looks like this:

SELECT * FROM (

								SELECT 
										message AS message_message,	message_type,
										0 AS call_duration,
										0 AS call_type,
										message_date AS record_date

								FROM new_messages
								WHERE thread_number = '$the_thread'

								UNION

								SELECT
										'' AS message_message,
										0 AS message_type,
										duration AS call_duration,
										type AS call_type,
										call_date AS record_date

								FROM new_calls
								WHERE NUMBER = '$the_thread'

								) 
								AS derrived_table
								ORDER BY 'record_date' DESC

[geudrik]

Never mind, solved it. Had to remove the single quotes off the order by column name

[Psycho]

Did you even run the query I provided before trying to modify it? It was ALREADY ordering the results as you needed. All you did was complicate the query. The ORDER BY in the query I provided was being interpreted against the UNION results, not the individual tables results. To be honest, I didn't realize it worked that way until I tried it out.

[geudrik]

It actually was not ordering the results properly which is why I modified it.  Attached is a screenshot of a live result set using your query.