Barand

Do you mean you can't access the keys of the outer array? $someArray = json_decode($newObject, true); foreach($someArray as $obj=>$value){ foreach ($value as $secondKey){ echo $obj . " " . $secondKey . "<br>"; } } gives firstObject red secondObject blue

Ask MySql why. Add this line to the above code if (!$result) die(mysql_error()); Note that mysql_ functions are deprecated - you should be using mysqli or PDO functions

try SELECT name FROM info i INNER JOIN files f ON f.parent = i.id GROUP BY name HAVING COUNT(f.fileid) < 3

Instead of naming them "textarea1", "textarea2" etc, name them "textarea[]" so they are posted as an array. To process foreach ($_POST['textarea'] as $text) { // process each $text item }

Not very helpful. I'm out.

And we don't have the knowledge of what your $questions variable contains. Post the output from var_export($questions);

When you use switch(TRUE) then the first case statement that evaluates to true (non-blank) will be used. You probably want switch ($events[0]) {

Definition Test data : that data for which the program works. Your solution loops through the values in the three arrays and counts the occurrence of each value. If the occurrence is >2 it assumes it is a value common common to the three arrays. If however an array contained repeating values that would also appear as "common" in the solution EG in $array1 = [1, 5, 5, 10, 20, 40, 80]; $array2 = [5, 6, 6, 6, 7, 20, 80, 100]; $array3 = [3, 4, 15, 20, 30, 70, 80, 120]; 5 and 6 would also be wrongly included in the $commonValues array

Use array_intersect $array1 = [1, 5, 10, 20, 40, 80]; $array2 = [6, 7, 20, 80, 100]; $array3 = [3, 4, 15, 20, 30, 70, 80, 120]; $common = array_intersect($array1, array_intersect($array2,$array3)); echo "Common values are " . join(', ', $common); //--> 20, 80

Instead of the sort you could find the highest value with max($all)

I've changed the join condition for pm2 SELECT DISTINCT pm1.meta_value AS 'ORDER No' , pm1.post_id AS 'TICKET No' , pm2.meta_value AS 'Guest Name' FROM $wpdb->postmeta pm INNER JOIN $wpdb->postmeta pm1 ON pm.post_id = pm1.post_id AND pm1.meta_key = '_tribe_wooticket_order' INNER JOIN $wpdb->postmeta pm2 ON pm1.meta_value = pm2.post_id AND pm2.meta_key LIKE '% Attendee Name' WHERE pm.meta_key = '_tribe_wooticket_event' AND pm.meta_value = 263;

What a mess! Sometimes the meta_value is the OrderNo, at other times the postID is the OrderNo. Then you need to match postID, which looks like an INT field, with integer values held in a char field. Ugh!

It would help if you posted some sample data so we can see what it looks like and help us navigate those meta-keys and meta-value fields

I think it should be something like this SELECT pm1.meta_value AS 'ORDER No' , pm1.post_id AS 'TICKET No' , pm2.meta_value AS 'Guest Name' FROM $wpdb->postmeta pm INNER JOIN $wpdb->postmeta pm1 ON pm.post_id = pm1.post_id AND pm1.meta_key = '_tribe_wooticket_order' INNER JOIN $wpdb->postmeta pm2 ON pm.post_id = pm2.post_id AND pm2.meta_key LIKE '% Attendee Name' WHERE pm.meta_key = '_tribe_wooticket_event' AND pm.meta_value = 263 LIMIT 0 , 30

Don't put the quotes around column names - SQL will treat them as strings. SELECT price, costs, cargodamage, price - costs - cargodamage as profit FROM drive_routes

I agree with ginerjm's basic tenet except I wouldn't restrict the advice to same table only. For example you wouldn't store a total when the total can be got by SUMming a related table. Don't store data that is derived from other data.

I wasn't aware bind_result() could have "?" placeholders

Just curious, Psycho, but would would you use the same argument in (2) above when it comes to using prepared statements and bind_result()?

Storing in the database as you do now is the correct way. Format the date as required on output using the code blacknight provided ($date in his code would be your date field from the database record). Alternatively, you can format the date in your sql query using the mysql function DATE_FORMAT http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_date-format

Corrected version $db = new mysqli(HOST,USERNAME,PASSWORD,DATABASE); $sql = "SELECT id, dogname, sire, dam FROM dogtable"; $dogs = $sires = $dams = array(); $res = $db->query($sql); while (list($id, $nm, $s, $d) = $res->fetch_row()) { $dogs[$id] = [$s,$d,$nm]; } function getAncestors($id, &$dogs, &$ancests, $dist) { if ($id==0) return; $ancests[$id] = $dist; if (isset($dogs[$id]) ) { getAncestors($dogs[$id][0], $dogs, $ancests, $dist+1); getAncestors($dogs[$id][1], $dogs, $ancests, $dist+1); } } $dogid = 1; getAncestors($dogs[$dogid][0], $dogs, $sires, 1); getAncestors($dogs[$dogid][1], $dogs, $dams, 1); ksort($sires); ksort($dams); $common = array_intersect_key($sires,$dams); echo "<pre>"; echo "| ID | NAME | SIRE | DAM |\n"; echo "| | | DIST | DIST |\n"; echo "|-----|--------------------|------|------|\n"; foreach ($common as $id => $dist) { printf("|%4d | %-18s | %4d | %4d |\n", $id, $dogs[$id][2], $sires[$id], $dams[$id]); } | ID | NAME | SIRE | DAM | | | | DIST | DIST | |-----|--------------------|------|------| | 8 | dog I | 3 | 2 | | 16 | dog Q | 4 | 3 | | 17 | dog R | 4 | 3 |

PS - apologies for the bisexual dog in the data, but you get the idea

A couple of years back I was working on dog pedigree charts so still had some test data. The chart of the data is attached and, as you can see, there are common ancestors. To find them you need to use a recursive function so I would load your data into an array and use that for the recursive search rather bombard your server with dozens of queries. This code will give the common ancestors and the generational distance on the sire and dam sides. You would then need to calc the IC value for each of these ancestors. $db = new mysqli(HOST,USERNAME,PASSWORD,DATABASE); $sql = "SELECT id, dogname, sire, dam FROM dogtable"; $dogs = $sires = $dams = array(); $res = $db->query($sql); while (list($id, $nm, $s, $d) = $res->fetch_row()) { $dogs[$id] = [$s,$d,$nm]; } function getAncestors($id, $key, &$dogs, &$ancests, $dist) { if ($id==0) return; $ancests[$id] = $dist; if (isset($dogs[$id]) ) { getAncestors($dogs[$id][$key], 0, $dogs, $ancests, $dist+1); getAncestors($dogs[$id][$key], 1, $dogs, $ancests, $dist+1); } } $dogid = 1; getAncestors($dogs[$dogid][0], 0, $dogs, $sires, 0); getAncestors($dogs[$dogid][1], 1, $dogs, $dams, 0); ksort($sires); ksort($dams); $common = array_intersect_key($sires,$dams); echo "<pre>"; echo "| ID | NAME | SIRE | DAM |\n"; echo "| | | DIST | DIST |\n"; echo "|-----|--------------------|------|------|\n"; foreach ($common as $id => $dist) { printf("|%4d | %-18s | %4d | %4d |\n", $id, $dogs[$id][2], $sires[$id], $dams[$id]); } Outputs | ID | NAME | SIRE | DAM | | | | DIST | DIST | |-----|--------------------|------|------| | 8 | dog I | 2 | 1 | | 16 | dog Q | 3 | 2 | | 17 | dog R | 3 | 2 |

Here is an example using the bookingscalendar table <?php include("db_inc.php"); $db = new mysqli(HOST,USERNAME,PASSWORD,'test'); $sql = "SELECT bookingdate , trainer , start_time , end_time , customer_id FROM bookingscalendar ORDER BY bookingdate,start_time"; $data = array(); $res = $db->query($sql); while ($row = $res->fetch_assoc()) { $data[$row['bookingdate']][] = array_slice($row,1,4,true); } $json = json_encode($data); // // write to file // file_put_contents('mybookings.json', $json); // // show results (for demo purposes only) // echo '<pre>',print_r($data, true),'</pre>'; echo $json; /**** RESULTS *************************************************************** The array: Array ( [2014-08-18] => Array ( [0] => Array ( [trainer] => 1 [start_time] => 10:00:00 [end_time] => 11:00:00 [customer_id] => 101 ) [1] => Array ( [trainer] => 1 [start_time] => 13:00:00 [end_time] => 14:30:00 [customer_id] => 102 ) [2] => Array ( [trainer] => 1 [start_time] => 16:00:00 [end_time] => 17:30:00 [customer_id] => 103 ) ) [2014-08-19] => Array ( [0] => Array ( [trainer] => 1 [start_time] => 09:00:00 [end_time] => 10:30:00 [customer_id] => 106 ) ) [2014-08-20] => Array ( [0] => Array ( [trainer] => 1 [start_time] => 09:00:00 [end_time] => 11:00:00 [customer_id] => 101 ) [1] => Array ( [trainer] => 1 [start_time] => 12:00:00 [end_time] => 13:00:00 [customer_id] => 105 ) [2] => Array ( [trainer] => 1 [start_time] => 15:00:00 [end_time] => 17:00:00 [customer_id] => 106 ) ) [2014-08-21] => Array ( [0] => Array ( [trainer] => 1 [start_time] => 10:00:00 [end_time] => 11:00:00 [customer_id] => 102 ) ) ) The JSON-encoded array data: {"2014-08-18":[{"trainer":"1","start_time":"10:00:00","end_time":"11:00:00","customer_id":"101"}, {"trainer":"1","start_time":"13:00:00","end_time":"14:30:00","customer_id":"102"}, {"trainer":"1","start_time":"16:00:00","end_time":"17:30:00","customer_id":"103"}], "2014-08-19":[{"trainer":"1","start_time":"09:00:00","end_time":"10:30:00","customer_id":"106"}], "2014-08-20":[{"trainer":"1","start_time":"09:00:00","end_time":"11:00:00","customer_id":"101"}, {"trainer":"1","start_time":"12:00:00","end_time":"13:00:00","customer_id":"105"}, {"trainer":"1","start_time":"15:00:00","end_time":"17:00:00","customer_id":"106"}], "2014-08-21":[{"trainer":"1","start_time":"10:00:00","end_time":"11:00:00","customer_id":"102"}]} *****************************************************************************/ ?>

When JOINing table it is not uncommon for the same column name to be used in both tables. In my tables (above) the "trainer" column is used in bookingscalendar and bookingavailabilty tables. When referencing trainer, therefore, it is necessary to define which one otherwise you will get an "ambiguous column name" error. This is done by prefixing the column name with the table name SELECT bookingavailability.trainer, bookingavailability.day ... FROM .... Also, if you wanted to reference another database in your query then you would need to specify the database name too SELECT myotherdatabasename.tablename.columnname , ... FROM ... The writing and reading of the query is improved if you use table aliases Thus SELECT bookingavailability.trainer, bookingavailability.day, myotherdatabasename.tablename.columnname FROM bookingavailability INNER JOIN myotherdatabasename.tablename ON bookingavailability.trainer = myotherdatabasename.tablename.trainer WHERE myotherdatabasename.tablename.trainer > 1 ORDER BY myotherdatabasename.tablename.trainer becomes this if you use table aliases SELECT a.trainer, a.day, t.columnname FROM bookingavailability a INNER JOIN myotherdatabasename.tablename t ON a.trainer = t.trainer WHERE t.trainer > 1 ORDER BY t.trainer http://en.wikipedia.org/wiki/Alias_%28SQL%29

First, my apologies - it looks as though I accidentally clipped a couple of the lines of code when I copied from the command line screen. Here is the correct query SELECT trainer, day, bookingdate, from_time, to_time, timeslot FROM ( SELECT a.trainer , a.day , bookingdate , TIMEDIFF(start_time, IF(bookingdate=@prevdate,@prevend,open_time )) as timeslot , IF(bookingdate=@prevdate,@prevend,open_time ) as from_time , start_time as to_time , @prevend := end_time as prevend , @prevdate := bookingdate as prevdate FROM bookingavailability a JOIN (SELECT @prevend:=null,@prevdate:=null) as init INNER JOIN bookingscalendar c ON a.trainer = c.trainer AND WEEKDAY(c.bookingdate) = a.day UNION SELECT a.trainer , day , bookingdate , TIMEDIFF(close_time, IFNULL(MAX(end_time),open_time) ) as timeslot , IFNULL(MAX(end_time),open_time) as from_time , close_time as to_time , null as prevend , null as prevdate FROM bookingavailability a LEFT JOIN bookingscalendar c ON a.trainer = c.trainer AND WEEKDAY(c.bookingdate) = a.day GROUP BY a.trainer,day,bookingdate ) as gaps WHERE timeslot > '00:00:00' ORDER BY trainer, day, bookingdate, from_time; @prevdate and @prevend are user variables that used to store values from each row in the resultset so they can be used in the following row. eg in row 1 store the end_time of the booking in @prevend. (line 10) In row2 the available gap between @prevend and the start_time of this booking is calculated (line 7) However, if the second record is not the same date as the first then the available gap is the time between the start of day (open_time) and the start_time of the booking. Therefore the date of each record is stored in @prevdate (line 11). , TIMEDIFF(start_time, IF(bookingdate=@prevdate,@prevend,open_time )) as timeslot , IF(bookingdate=@prevdate,@prevend,open_time ) as from_time Similarly in the second part (line 23), if there are no bookings for the day the available time at the end of the day is close_time minus open_time instead of the end_time of last booking minus close_time , TIMEDIFF(close_time, IFNULL(MAX(end_time),open_time) ) as timeslot