Asked  1 Year ago    Answers:  5   Viewed   12 times

I have a simple question and answer section on my website that allows comments. I currently populate the answers using a loop and a $_GET['id'] value. Here is my query

<?php
try{
   $parent=$_GET['id'];
   $post_type = "2";
   $stmt = $dbh->prepare(
    "SELECT p.post_id, p.content, p.author, p.created, pc.comment_id AS comment_id, pc.content AS comment_content
     FROM posts AS p
     LEFT JOIN posts_comments AS pc
     ON p.post_id = pc.parent_id
     WHERE p.post_type = :post_type AND  p.parent = :parent ");

   $stmt->bindParam(':parent', $parent, PDO::PARAM_INT);
   $stmt->bindParam(':post_type', $post_type, PDO::PARAM_INT);
   $stmt->execute();

   $answers_array = array();
   while ($answers = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $answers_array[]= $answers;
   }
}

?>

This returns the following results in an array

   Array ( 
[0] => Array ( 
        [post_id] => 12 
        [content] => I have an answer
        [author] => Author1
        [created] => 2012-06-09 21:43:56
        [comment_id] =>
        [comment_content] => ) 
[1] => Array ( 
        [post_id] => 13
        [content] => My second answer
        [author] => Author1
        [created] => 2012-06-10 06:30:58
        [comment_id] => 35
        [comment_content] => 1st comment ) 
[2] => Array ( 
        [post_id] => 13
        [content] => My second answer
        [author] => Author2
        [created] => 2012-06-10 06:30:58
        [comment_id] => 36
        [comment_content] => 2nd comment ) 
[3] => Array ( 
        [post_id] => 13
        [content] => My second answer
        [author] => Author2 
        [created] => 2012-06-10 06:30:58
        [comment_id] => 37
        [comment_content] => 3rd comment ) 
)

On my question.php page I know I will need to loop through these results with something like

<?php $answer_count = count($answers_array);
 for($x = 0; $x < $answer_count; $x++) {
 $answers = $answers_array[$x];
}
?>

My question -

I don't know whether to use two separate queries to pull the comments associated with each answer or use the join I have above and build another array with php. I don't know how to do that honestly. If I use the join I would like an array that looks like this but I'm not sure how to build it using a loop in php.

     Array ( 
[0] => Array ( 
        [post_id] => 12 
        [content] => I have an answer
        [author] => Author1 
        [created] => 2012-06-09 21:43:56 
        [comment_id] => 
        [comment_content] => ) 
[1] => Array ( 
        [post_id] => 13 
        [content] => My second answer
        [author] => Author1 
        [created] => 2012-06-10 06:30:58 
            Array( 
                   [1] => Array (
                       [comment_id] => 35
                       [comment_content] => 1st comment)
                   [2] => Array (
                       [comment_id] => 36
                       [comment_content] => 2nd comment)
                   [3] => Array (
                       [comment_id] => 37
                       [comment_content] => 3rd comment))

Once I have an array like that I was thinking I could do a for each inside my original php loop on the question.php page.

 Answers

4

One query is fine. As you have it, and probably the better opton. You have to work out which is more efficient, to let MySQL take the strain, or the network and PHP take the strain. It's a lot better to let PHP take the strain than MySQL, but where MySQL has "inbuilt" features, such as the grouping you desire, then leave the MySQL and save the network traffic.

To make this work: add "ORDER BY p.post_id, pc.comment_id" to your query - this gets the results in order.

Then, if you must build into an array (although you may be able to process directly without using an array, the method would be similar):

$lastPostID = 0;
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    if ($lastPostID <> $row['post_id']) {
        $lastPostID  = $row['post_id'];
        $answers[$lastPostID] = array('post_id' => $row['post_id'],
                                      'author_id' => $row['author_id'],
                                      etc
                                      'comments' => array() );
    }
    $answers[$lastPostID]['comments'][] = array('comment_id' => $row['comment_id'], 'coment' => $row['comment'] etc);
}
Saturday, May 29, 2021
 
PHLAK
 
1

I would certainly suggest using a JOIN here that way you can get all this data in one go. I will add one caveat however - this would take more memory that querying the database in a loop like you are currently doing, so if you have a large number of rows in either table, this might not be a suitable approach.

The query would simply be:

SELECT * FROM opdrachen
INNER JOIN resultaten ON opdrachen.id = resultaten.opdrachenid

You can then set up an array that is keyed on each id very simply like this:

$array = array();
while ($row = mysql_fetch_array($result)) {
    $array[$row['id']][] = $row;
}

This give you an easy way to iterate through the results, one id at a time like this:

foreach ($array as $id => $rows_for_id) {
      // output stuff for row group
      foreach ($rows_for_id as $resultaten_row) {
          // output stuff for resultaten rows
      }
}

A few other suggestions:

  • Don't use mysql_* functions as they are deprecated. Learn how to use mysqli or PDO.
  • Don't use SELECT * queries. Its is lazy and wastes bandwidth, transfer time, and system memory. Only query for the specific fields you are actually interested in. So replace the * in my example with the actual field you need.
Thursday, April 1, 2021
 
qitch
 
5
<?php
include("regDBConnect.php");

// collect all the results
$rows = array();

$result1 = mysql_query("SELECT * FROM Phase where Pid = 1", $db) or die("cannot select");
while($row = mysql_fetch_array($result1)) {
  $rows []= array(
    'id' => $row['id'],
    'parent' => $row['parent'],
    'name' => $row['name'],
  );

  /*
     if you remove the line above and uncomment this instead,
     javascript objects will see all the properties you selected from the DB 
   */
  // $rows []= $row;
}
?>

<script type="text/javascript">
// now output the collected results
var treeData = <?php echo json_encode($rows); ?>;
</script>

Note that what I said about PDO/MySQLi still applies, this is just a minimal example to answer this specific question. (And in general, you should SELECT only those columns you will need, not *.)

Saturday, May 29, 2021
 
Raef
 
3

You simply need to specify on which tables to apply the DELETE.

Delete only the deadline rows:

DELETE `deadline` FROM `deadline` LEFT JOIN `job` ....

Delete the deadline and job rows:

DELETE `deadline`, `job` FROM `deadline` LEFT JOIN `job` ....

Delete only the job rows:

DELETE `job` FROM `deadline` LEFT JOIN `job` ....
Wednesday, June 9, 2021
 
Puneet
 
3

It sounds like you are trying to find values for all dates within a range regardless of whether there is a value or not. Supposing we have a Calendar table structured like so:

Create Table Calendar
(
    [Date] not null Primary Key
)

Your query might look like so (where X and Y represent the start and end date of the range in which you are investigating):

Select Year(C.Date), MonthName(C.Date) As Month
    , Coalesce(Sum(IP.paid_amount),0) As Total
From Calendar As C
    Left Join (Invoice As I
        Join Client As C1
            On C1.id = I.client_id
                And C.registered_id = 1
        Join Invoice_Payments As IP
            On IP.Invoice_id = I.Id)
        On IP.date_paid = C.date
Where C.Date Between X and Y
Group By Year(C.Date), MonthName(C.Date)

Technically, the above query should do the trick. However, another alternative is to use a derived table about which you inquired in the comments:

Select Year(C.Date), MonthName(C.Date) As Month
    , Coalesce(Sum(Z.paid_amount),0) As Total
From Calendar As C
    Left Join   (
                Select IP.date_paid, IP.paid_amount
                From Invoice As I
                        Join Client As C1
                            On C1.id = I.client_id
                                And C.registered_id = 1
                        Join Invoice_Payments As IP
                            On IP.Invoice_id = I.Id
                ) As Z
        On Z.date_paid = C.date
Where C.Date Between X and Y
Group By Year(C.Date), MonthName(C.Date)
Monday, November 29, 2021
 
Howard
 
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :