Barand
-
Posts
24,566 -
Joined
-
Last visited
-
Days Won
822
Everything posted by Barand
-
Using GROUP BY but cannot get zero totals to show
Barand replied to joesoaper's topic in PHP Coding Help
How does a teacher check which pupils are absent from a class? S/he has a register of the pupils that should be there. You are trying to count things that aren't there, so you need a "model" table containing a row for each model - your register. You then query SELECT i.status , m.model , COUNT(i.model) FROM model m LEFT JOIN club_inventory i ON m.model = i.model AND status="In-Stock" AND prod="Jets" GROUP BY m.model -
Using GROUP BY but cannot get zero totals to show
Barand replied to joesoaper's topic in PHP Coding Help
Your code is like walking into a room and asking "Will anyone who isn't here please raise their hand". -
How can i get/query/list sub/nested comments from comment table?
Barand replied to colap's topic in PHP Coding Help
try something like this $db = new mysqli(HOST,USERNAME,PASSWORD,'test'); $sql = "SELECT comment_id , username , post_id , content , IFNULL(parent_comment_id,0) , created FROM comment c INNER JOIN user u ON c.user_id = u.id ORDER BY post_id, created"; $comments = []; $res = $db->query($sql); while (list($cid,$user,$pid,$content,$parent,$created) = $res->fetch_row()) { $comments[$pid][$parent][$cid] = [ 'content' => $content, 'user' => $user, 'date' => $created ]; } // // recursive print function // function printReplies(&$comments, $parent, $level) { if (!isset($comments[$parent])) return; foreach ($comments[$parent] as $cid => $comdata) { $dval = date('F jS Y g:ia', strtotime($comdata['date'])); // print comment echo "<div class='comdiv lev$level'> <b>{$comdata['user']}</b><br>$dval<br> {$comdata['content']} </div>\n"; // print replies to the comment printReplies($comments, $cid, $level+1); } } ?> <!DOCTYPE html> <html> <head> <title>Sample sub-comments</title> <style type='text/css'> .comdiv { border: 1px solid gray; width: 400px; margin-top: 5px; padding: 10px; } .lev0 { margin-left: 50px; background-color: #cfc; } .lev1 { margin-left: 100px; background-color: #ccf; } .lev2 { margin-left: 150px; background-color: #ffc; } </style> </head> <body> <?php foreach ($comments as $post => $comms) { echo "<h3>Post $post</h3>"; printReplies($comms, 0, 0); } ?> </body> </html> results attached
-
How can i get/query/list sub/nested comments from comment table?
Barand replied to colap's topic in PHP Coding Help
see this reply from earlier today http://forums.phpfreaks.com/topic/301437-form-not-submitting-if-text-field-is-too-longlarge-php-mysql/?do=findComment&comment=1534295 -
How can i get/query/list sub/nested comments from comment table?
Barand replied to colap's topic in PHP Coding Help
The subquery will need to find the username for each of the 1000 records. Use select c.*, u.username from comments c inner join users u ON c.user_id = u.id where post_id=$post_id order by created ASC A couple more observations: don't use SELECT *, specify the columns required use a prepared query instead of putting user-submitted data directly into the query -
How can i get/query/list sub/nested comments from comment table?
Barand replied to colap's topic in PHP Coding Help
Use a JOIN, so you only execute a single query, and not the subquery. If you have 1000 comments your query will generate 1000 queries. Not good for server performance. You will need a recursive function to process the nested comments. Read the data into an an array (indexed on parent comment). Don't call queries recursively. -
You specify the records you want to LEFT join to. If you put those conditions in the WHERE clause, as you had originally, then the LEFT join behaves like an INNER JOIN.
-
When you LEFT JOIN to a table then any conditions regarding its data cannot be in the WHERE clause, they must be in the ON conditions in the join. SELECT * FROM user as u LEFT OUTER JOIN event as e ON u.id = e.user_id AND e.event_date = '2016-07-05' AND e.event = 'Arrival' WHERE u.group_id = 6 GROUP BY u.id And don't use SELECT *. Specify the columns you want.
-
The chances of the id matching a timestamp value are pretty slim
-
I tested my query before posting - it does work. Provided it is unique, you can substitute the timestamp for the id.
-
try something like this DELETE benchmarks FROM benchmarks LEFT JOIN ( SELECT id FROM benchmarks ORDER BY id desc LIMIT 6 ) lastsix USING (id) WHERE lastsix.id IS NULL;
-
And normalize your tables. When you have fields named xxx1, xxx2, xxx3, ..., xxxN, you are doing it wrong.
-
Telling us what you don't want isn't helpful, we need to know what you are expecting.
-
How do you show php foreach loop results as ajax mousehover?
Barand replied to imgrooot's topic in PHP Coding Help
You also need a mouseout to set the display back to "none" -
You probably want something along these lines SELECT dep.month , deptotal , baltotal , deptotal + baltotal as total FROM ( SELECT EXTRACT(YEAR_MONTH FROM date_deposit_paid) as ym MONTHNAME(date_deposit_paid) as month , SUM(deposit_paid) as deptotal FROM projects GROUP BY ym ) dep JOIN ( SELECT EXTRACT(YEAR_MONTH FROM date_rembl_paid) as ym , SUM(rembl_paid_amount) as baltotal FROM projects GROUP BY ym ) bal USING (ym)
-
Paste variables onto a Word doc on order submission
Barand replied to jimleeder123's topic in PHP Coding Help
Another approach is use Word's mail merge feature and create a data file (.csv perhaps) with the data that needs to be inserted. -
If they're talking about me, it's probably nothing good. I'd rather not know.
-
HINT: if you are using print_r, put it between <pre>..</pre> tags. It makes it so much easier to understand the array structure. print_r($array); /* OUTPUT : Array ( [0] => Array ( [0] => 1 [1] => 2 [2] => 3 ) [1] => Array ( [0] => 2 [1] => 2 [2] => Array ( [0] => 45 [1] => 22 [2] => 34 ) ) [2] => Array ( [0] => 4 [1] => 5 [2] => 6 [3] => 7 ) ) */ echo '<pre>' . print_r($array, true) . '</pre>'; /* OUTPUT : Array ( [0] => Array ( [0] => 1 [1] => 2 [2] => 3 ) [1] => Array ( [0] => 2 [1] => 2 [2] => Array ( [0] => 45 [1] => 22 [2] => 34 ) ) [2] => Array ( [0] => 4 [1] => 5 [2] => 6 [3] => 7 ) ) */
-
I can't understand why more people don't do that instead of using free tools like Workbench.
-
$age = (new DateTime($dob))->diff(new DateTime())->y; should work with PHP 5.3 or later. What is in $dob? Has the format that is being passed in $dob changed?
-
Since the query is aggregating totals, individual gametimes are irrelevant, so why the useless ORDER BY? You should also note that the data is currently not limited to a specific season. I don't know what your method queryForColumn() returns but this looks a little odd to me $missingPreds = " " . $this->dbo->queryForColumn($query) . ")";
-
Similarly, for the last part SELECT userid , lastgame - lastprediction as missed FROM ( SELECT MAX(id) as lastgame FROM games ) games CROSS JOIN ( SELECT userid , MAX(gameid) as lastprediction FROM prediction GROUP BY userid ) users
-
You would use a subquery to combine EG SELECT p.userid , COUNT(*) as predictions , totgames - COUNT(*) as missing , totgames FROM prediction p CROSS JOIN ( SELECT COUNT(*) as totgames FROM games ) tot GROUP BY p.userid
-
It's a recursive solution I had both open at once and meant to link to this other one - with a very similar title. http://forums.phpfreaks.com/topic/292827-display-data-in-tabular-format-by-year-and-month/?do=findComment&comment=1498183
-
You could apply a method similar to this one http://forums.phpfreaks.com/topic/301314-displaying-data-in-tabular-form/?do=findComment&comment=1533481 I'd also start by tidying up the SQL code. You wouldn't write your php in one single line. $sql = "SELECT student_exam.id as sid , student_exam.student_id , student_exam.enroll_no , student_exam.exam_id , student_exam.subject_id , student_exam.obtained_marks , exam_time_table.exam_name , exam_time_table.class , exam_time_table.section , exam_time_table.total_marks , subjects.subject , exam_type.exam , student.enroll_no , student.stud_name FROM student_exam INNER JOIN exam_time_table ON student_exam.exam_id=exam_time_table.id INNER JOIN subjects ON student_exam.subject_id=subjects.sub_id INNER JOIN exam_type ON exam_time_table.exam_name=exam_type.eid INNER JOIN student ON student_exam.enroll_no=student.enroll_no WHERE exam_time_table.class='$class' AND exam_time_table.section='$section' AND student_exam.enroll_no='$enroll_no' ";
