[RESOLVED] comparing dates in a multi dimentional array.

igorek

i got a multi dimentional array

array [ assoc array ]

i want to sort the array by date of the assoc array any suggestions?

I guess I can re-do my query to something like this

SELECT a.id, a.date, a.comment, b.id, b.date, b.types, b.symbols, b.comment 
FROM tbl_a
LEFT JOIN tbl_b USING(date) 
ORDER BY date DESC 
LIMIT 5

for some reason I thought I can do it with php.

bradgrafelman

igorek wrote:
for some reason I thought I can do it with php

You could, probably using [man]array_multisort[/man] or at the very least [man]usort/man, but if the data is already being sorted by SQL then it just makes sense to do the ordering in the query rather than in PHP.

igorek

http://ua2.php.net/manual/en/array.sorting.php - first place i looked but i cant figure out how to sort it by a certain item in array and infact a certain item in a multi array. i tried sorting all i get back is true lol.

bradgrafelman

Is there a reason you don't want to do the sorting in the SQL query itself? Can you show us the code you tried?

igorek

these two tables in database are completely un-relational they are both single 🙂

but the idea is to trow data from both of these tables 5 items from each table into a js marquee array.

at the same time the most current -either- a comment or signal to be shown, so they must be sorted by the dates of entry.

but on days that there are signals there could be no major comments.

my solution to this matter was to build a new table. that is filled or updated with the new values from the other 2 tables automatically upon either insertion of new signal or new comment basicly either one takes place of the previous one.

so i did a function for update from the multi dimensional array i got $array[0][0] etc.... but 10 rows at the same time upon either creation of a signal or creation of comment if the data has not changed it remains the same.

if you have any suggestions to simplify my road, more than grateful. Btw Brad I appreciate all your suggestions and responses you give.

igorek

<?php 

function update_comment($array){
	global $dbarfxuser;
	mysql_select_db(DBUSER, $dbarfxuser);
	$update['query'] ="";
	$countarray = count($array);
	for($count=0;$count<=7;$count++){   

		$update['query'][$count] .= "UPDATE tbl_tick SET ";
		$update['query'][$count] .= "comment_id='".$array[$count][0]."',"; 
		$update['query'][$count] .= "date='".$array[$count][1]."',"; 
		$update['query'][$count] .= "types='".$array[$count][2]."',"; 
		$update['query'][$count] .= "symbols='".$array[$count][3]."',"; 
		$update['query'][$count] .= "comment='".mysql_real_escape_string($array[$count][4])."' "; 
		$update['query'][$count] .= "WHERE id=".($count+'1'); 
		$update['query'][$count] .= " LIMIT 1"; 
		$update['result'][$count] = mysql_query($update['query'][$count], $dbarfxuser) or die("<pre>".$update['query'][$count]."</pre>" . mysql_error());
		$update['result'][$count] = "Total rows affected by query #".($count+'1').":".mysql_affected_rows($dbarfxuser);
		$update['result'][$count] .= " MySQL Info: "."<pre>".mysql_info($dbarfxuser)."</pre>";		
		// NEEDS to return an array of $query + $result
		// $array['query'] = ;
		// $array['result'] = ;

}
return $update;
}

// read to double check, even though I got the affected_rows + info
if($_GET['r']=='1'){
mysql_select_db(DBUSER, $dbarfxuser);
$query = "SELECT * FROM tbl_tick ORDER BY date DESC LIMIT 10";
$rstick = mysql_query($query, $dbarfxuser) or die(mysql_error());
$row_rstick = mysql_fetch_assoc($rstick);
}

// in body~~~
?>
<pre>
<?php 
if($_GET['u']=='1'){
	$ticker = array_merge($ticktok,$tickso);
	$ticker = update_comment($ticker);
	print_r($ticker);
}
?>
</pre>

Kristina went back to Kerch today, I am going to have so much free time finally =D

igorek

// #  sort data by date

// convert date
strtotime('1987-05-20 20:29:29');

// set up an multid - array with data by columns 
    [1] => Array
        (
            [0] => 1269306769
            [1] => 1269306769
            [2] => 1269306769
            [3] => 1269309124
            [4] => 1268917574
            [5] => 1269003974
            [6] => 1268920407
            [7] => 1268573783
        )

// sort
arsort($arr[1], SORT_NUMERIC);

// also used array_shift(); array_merge(); in my script
// working on the function right now will post asap :)

Weedpacket

Assuming the 0, 1, ..., 7 don't mean anything, then just use [man]sort[/man] Or [man]rsort[/man] if you want it in reverse order. (But frankly I'm not getting my head around the whole process here. Probably bradgrafelman is right and you should be looking at [man]usort[/man] (I suspect [man]array_multisort[/man] would be overkill if you're only sorting on one column)).

I'd suggest you rearrange that $update array a bit, to match more the way you have $array arranged: the fact that the 4th query is paired with the 4th result seems more important than the fact that the 4th and 5th queries are both queries.

$item = $array[$count];
$query = "Update tbl_tick SET
comment_id = '{$item[0]}',
      date = '{$item[1]}',
     types = '{$item[2]}',
   symbols = '{$item[3]}',
   comment = '" . mysql_real_escape_string($item[4]) . "'
Where
	id = " . ($count + 1) . " Limit 1";

mysql_query($query, $dbarfxuser) or die("<pre>$query</pre>".mysql_error());

$result = "Total rows affected by query #" .($count + 1). ":" . mysql_affected_rows($dbarfxuser) .".
MySQL Info: <pre>" . mysql_info($dbarfxuser) . "</pre>";

$update[$count] = array('query' => $query, 'result' => $result);

igorek

thank you for your reply Weedpacket. this is what I have came up with in the end. probably can even simplify this.

<?php
require_once('constants.php');

function switch_rows(){
	global $dbarfxuser;
	mysql_select_db(DBUSER, $dbarfxuser);
	// pick query
	$query = "SELECT * FROM tbl_signal WHERE col_exit > 0 ORDER BY col_ouID DESC LIMIT 5";
	$set1  = mysql_query($query) or die(mysql_error());
	$row1  = mysql_fetch_array($set1);
	// keys to set1
	$arr1  = array(15,6,10,9,14,1,4);
	// pick query 
	$query = "SELECT * FROM tbl_commentonticker ORDER BY id DESC LIMIT 5";
	$set2  = mysql_query($query) or die(mysql_error());
	$row2  = mysql_fetch_array($set2);	
	// keys to set2
	$arr2  = array(0,2,5,5,3,5,5);
	for($i=0;$i<=6;$i++){
		$p=$arr1[$i];
		do {			
				if($p=='6'){$mray1[]=strtotime($row1[$p]);}
				if($p=='14'){$mray1[]=htmlspecialchars($row1[$p]);}
				if($p=='1' || $p=='4'){$mray1[]=trim($row1[$p],0);}
				if($p <> '6' && $p <> '14' && $p <> '1' && $p <> '4'){$mray1[]=$row1[$p];}			
		} while($row1=mysql_fetch_array($set1)); mysql_data_seek($set1, 0);

	$p=$arr2[$i];
	do {			
			if($p=='2'){$mray2[]=strtotime($row2[$p]);}
			if($p=='3'){$mray2[]=htmlspecialchars($row2[$p]);}
			if($p <> '2' && $p <> '3'){$mray2[]=$row2[$p];}		

	} while($row2=mysql_fetch_array($set2)); mysql_data_seek($set2, 0);

	// reset arrays		
	if($i > 0){$e=array_shift($mray1);$e=array_shift($mray2);} 
	$mray[]=array_merge($mray1,$mray2);
	$mray1=array();$mray2=array();
}
return $mray;
}
?>
<html>
<head><title>sorted table</title></head>
<body>
<?php 
$new=switch_rows();

# SORT_NUMERIC - compare items numerically
$sort_by = SORT_NUMERIC;

# 1 - order by date
$order_by = 1;

// get the key order
$a = $new[$order_by];

// sort
arsort($a, $sort_by);

// start print
echo "<table cellpadding=\"5\" cellspacing=\"0\" border=\"1\" style=\"font:normal normal normal 11px/15px Arial;\">\n";
        echo "<tr style=\"font-weight:bold;\">\n";
          echo "<td>ID</td>\n";
          echo "<td>DATE</td>\n";
          echo "<td>TYPES</td>\n";
          echo "<td>SYMBOLS</td>\n";
          echo "<td>COMMENT</td>\n";
          echo "<td>ENTRY</td>\n";
          echo "<td>EXIT</td>\n";		  

		echo "</tr>\n";

// print by key order
foreach ($a as $k => $v) {
echo "<tr>\n";
   for($count=0;$count<=6;$count++){

  echo "<td>".$new[$count][$k]."</td>\n";	  
}
echo "</tr>\n";
}
echo "</table>\n";
?>
</body>
</html>

Weedpacket

I'm trying to puzzle through your switch_rows() function. I think it behaves like this:

function switch_rows()
{
	global $dbarfxuser;
	mysql_select_db(DBUSER, $dbarfxuser);
	$signal_query = "SELECT * FROM tbl_signal WHERE col_exit > 0 ORDER BY col_ouID DESC LIMIT 5"
	$signal_set = mysql_query($signal_query) or die(mysql_error());
	$signals = array();
	while($row = mysql_fetch_array($signal_set)
	{
		$row[1] = trim($row[1], 0);
		$row[4] = trim($row[4], 0);
		$row[6] = strtotime($row[6]);
		$row[14] = htmlspecialchars($signal[14]);
		$signals[] = $row;
	}

$comment_query = "SELECT * FROM tbl_commentonticker ORDER BY id DESC LIMIT 5";
$comment_set = mysql_query($comment_query) or die(mysql_error());
$comments = array();
while($row = mysql_fetch_array($comment_set)
{
	$row[2] = strtotime($row[2]);
	$row[3] = htmlspecialchars($row[3]);
	$comments[] = $row;
}

return array_merge($signals, $comments);
}

but I can't really be sure. I do know that most of it shouldn't even be there. The bulk of all that work could be done in the query itself, just by selecting which fields you want (in the order that you want, and even with the names that you want instead of having to use anonymous numbers) explicitly, instead of just writing "Select *". Use "Select somefield as id, someotherfield as date, athirdfield as types ....". And then later, use mysql_fetch_assoc() so that you can refer to the array elements by name instead of number. (Incidentally, did you know that [man]mysql_fetch_array[/man] contains the returned data keyed both by name and number?)

You can even do both queries at the same time, instead of doing them separately and combining the results: "Select something as id ... from onetable UNION Select somethingelse as id ... from twotable".

Some of the trimming etc. could be done in the query as well using the appropriate MySQL functions.

igorek

Weedpacket, if i am not mistaken you are getting rows of data stored in an array. I am picking 1 value from $row array by key looping it and getting back an array of tables column. Do the same for 2nd tables then I merge them together into new array.

Once merged I start over on the next column.

I use mysql_data_seek(); to reset the pointer.

array_shift(); to drop the first item in the array 1 ++ because for some reason after the pointer moves from 0 to 1 the first element in my multidimentional array is empty so I trim it.

i did know that I get both key and assoc by using mysql_fetch_array but i needed the key. I use print_r() to test and make sure I get the correct data stored in my array.

then i searched the manual for functions that gave me the exact arrays I wanted to see.

this UNION SELECT is interesting...
ex.

SELECT date
FROM tbl1 
UNION SELECT date
FROM tbl2 
ORDER BY date DESC

I am not sure if that is correct syntax I will look into it tomorrow and will test it in console but the whole idea is that these tables are not related, but need to get 5 records from each table and sort them by date.

result:

<?php
/*
Array
(
    [0] => Array
        (
            [0] => 59
            [1] => 52
            [2] => 51
            [3] => 46
            [4] => 45
            [5] => 26
            [6] => 25
            [7] => 24
        )

[1] => Array
    (
        [0] => 1269309124
        [1] => 1268917574
        [2] => 1269003974
        [3] => 1268920407
        [4] => 1268573783
        [5] => 1269306769
        [6] => 1269306769
        [7] => 1269306769
    )

[2] => Array
    (
        [0] => Sell Short
        [1] => Buy Long
        [2] => Sell Short
        [3] => Sell Short
        [4] => Sell Short
        [5] => NULL
        [6] => NULL
        [7] => NULL
    )

[3] => Array
    (
        [0] => AUD/USD
        [1] => EUR/USD
        [2] => USD/XAU
        [3] => NZD/USD
        [4] => AUD/USD
        [5] => NULL
        [6] => NULL
        [7] => NULL
    )

[4] => Array
    (
        [0] => no comment
        [1] => no comment
        [2] => no comment
        [3] => Short term trade as Kiwi still strong to upside on rate differential
        [4] => first target .9123 if breached looking for .9070
        [5] => --GOLD upda
        [6] => ... the euro/$ a
        [7] => ... Friday 
    )

[5] => Array
    (
        [0] => .9165
        [1] => 1.352
        [2] => 1123.5
        [3] => .7146
        [4] => .9169
        [5] => NULL
        [6] => NULL
        [7] => NULL
    )

[6] => Array
    (
        [0] => .9085
        [1] => 1.3545
        [2] => 1103.
        [3] => .7115
        [4] => .9123
        [5] => NULL
        [6] => NULL
        [7] => NULL
    )

)
*/

// i will omit the NULL values by using if in the marquee, btw these are unsorted
?>

P.S. btw because of your post I was able to get rid of 3 lines of code the row=mysql_fetch_array(); x2 and do {} into just while made me loose the empty array elements I was getting at beginning of my arrays + i did not need to use array_shift(); function anymore but still I know it now hehe 🙂 thx and now If I can do it one query I can probably simplify even more I was thinking of like if this then use this query but i cant think of it how i can specify to use this then switch to other, maybe use the switch(){case break }

Weedpacket

igorek wrote:
how i can specify to use this then switch to other, maybe use the switch(){case break }

Like I said: UNION. Look it up.

igorek

( 
SELECT col_ouid, col_toe, col_types, col_entry, col_exit 
FROM tbl_signal 
ORDER BY col_ouid DESC 
LIMIT 5
) UNION ( 
SELECT id, dateandtime, empty, empty, empty 
FROM tbl_commentonticker 
ORDER BY id DESC 
LIMIT 5 
) ORDER BY col_toe DESC;

thank you very much. I knew to go with SQL at the start but didnt know the UNION keyword.