Query help

[Mekaboo]

Hey there😊

Im trying to connect this table:

Comment(Id,userId,commentId,comment)

to this code so they can loop together. Now images dont show and only one comment shows up. Goal is to have images and comment loop on a descend like in a typical social media page :

<?php $mysqli = new mysqli(""); if ($mysqli -> connect_errno) { echo "Failed to connect to MySQL: " . $mysqli -> connect_error; exit(); } $sql = "SELECT imageId FROM output_images ORDER BY imageId DESC;"; $sql .= "SELECT * FROM comment"; // Execute multi query if ($mysqli -> multi_query($conn, $sql)) { do { // Store first result set if ($result = $mysqli -> store_result()) { while ($row = $result -> fetch_row()) { printf("%s (%s)\n", $row["username"],$row["imageId"]); } $result -> free_result(); } // if there are more result-sets, the print a divider if ($mysqli -> more_results()) { printf("%s (%s)\n", $row["username"],$row["comment"]); } //Prepare next result set } while ($mysqli -> next_result()); } $mysqli -> close(); ?>

 

The output_images is from a script I implemented. This is the DB connection for that:

<?php require_once "conn.php"; if(isset($_GET['image_id'])) { $sql = "SELECT imageType,imageData FROM output_images WHERE imageId=" . $_GET['image_id']; $result = mysqli_query($conn, $sql) or die("<b>Error:</b> Problem on Retrieving Image BLOB<br/>" . mysqli_error($conn)); $row = mysqli_fetch_array($result); header("Content-type: " . $row["imageType"]); echo $row["imageData"]; } mysqli_close($conn); ?>

 

This has been the toughest part to accomplish..if you can help I would greatly appreciate it❤️

[niche]

What's your question?

[Mekaboo]

@niche

I don't how to connect the Comment table to the loop. As of now I can comment post works but don't descend and the images don't show at all. Im trying to connect multiple queries within the loop to function together. How do I create a connection is what Im trying to do☹️

This is an image from my landing page:

 

[niche]

Let's deal with what might be the first challenge. How you want to do to do your mult queries? Actual separate multiple queries, sub queries, JOINS, etc. If something else needs to happen first, let us know.

[Mekaboo]

@niche

Separate queries more so...this is what Im trying to accomplish with the loop:

https://www.w3schools.com/php/func_mysqli_multi_query.asp

There are 2 seperate forms but what happens is the query will acknowledge both within the loop. Goal is to have users either post comments or images and are shown in descending order...like a social media wall post.

 

The buttons represent 2 different forms but both will be connected to same query loop.

Edited November 11, 2021 by Mekaboo
edit words

[niche]

Take the results from both queries and do array_push to combine results. Then, do what ever you want in a loop.

https://www.w3schools.com/php/func_array_push.asp

EDIT:

You can also do a UNION if that makes sense.

Edited November 11, 2021 by niche

[Mekaboo]

@niche

First off THANK YA for the help and understanding❤️

As far as the Push Array my interpretation is that I combine tables within Comment and push onto imageId (Output_images). This is updated code with push array..wanted to get your insight before I stamp as completed😁:

       

  <?php
 $mysqli = new mysqli("");

$a=array("imageId");
array_push($a,"userId","commentId","comment");
print_r($a);

if ($mysqli -> connect_errno) {
  echo "Failed to connect to MySQL: " . $mysqli -> connect_error;
  exit();
}

$sql = "SELECT imageId FROM output_images ORDER BY imageId DESC;";
$sql .= "SELECT * FROM comment";

// Execute multi query
if ($mysqli -> multi_query($conn, $sql)) {
  do {
    // Store first result set
    if ($result = $mysqli -> store_result()) {
      while ($row = $result -> fetch_row()) {
        printf("%s (%s)\n", $row["username"],$row["imageId"],$row["comment"]);
      }
     $result -> free_result();
    }
    // if there are more result-sets, the print a divider
    if ($mysqli -> more_results()) {
    printf("-------------\n");
    }
     //Prepare next result set
  } while ($mysqli -> next_result());
}

$mysqli -> close();
 ?>      

         

 

As far as UNION that makes me think of INNER, LEFT, RIGHT JOIN. Is it possible to do both(push and union)?

[niche]

Never wrote a do while Loop. So, I looked it up! Looks like it should work Pretty simple concept.. If not post the error, if you can't resolve it and we'll deal with it then.

Great example of the student teaching the teacher, which is frequently the case.  Thanks!  

[Mekaboo]

@niche

Thank ya😊

Someone in another forum helped me a few weeks with the while loop. My thing is I'm still learning and I grasp the concept somewhat but lack the knowledge to know where certain code goes in order to function especially if I'm working with pre made scripts.

Will keep informed if there are errors..THANK YA again💙

[Mekaboo]

Hey @niche

Through research Ive noticed that folks are not too keen on using multi query and say use prepared statements or separate the queries. Singular query (original setup) works perfectly. Is there a way to duplicate this code so it can connect to the comment DB table the same way this connects to the image DB table?

 <?php
 require_once "db.php";
   $sql = "SELECT imageId FROM output_images ORDER BY imageId DESC;";
    $result = mysqli_query($conn, $sql);
 
//  Loop thru comments and display all of them
while($row = mysqli_fetch_array($result)) {
    printf("%s (%s)\n", $row["username"], $row["comment"],$row["imageId"]);
 ?>      

 

[dsonesuk]

Id's,  a user table with singular data username, email etc should have there own unique id. If you have a table with multiple comments for each user, you would have a column containing userid that would refer to users table id, this is a one to many relationship. Then its just a matter listing columns required from both tables WHERE userid in users table equals user userid in comments table.

A simple method:

Loop each userid with if condition checking if it is the same as previous if not! show next user username email data from singular user table, and continue to loop through comments related to that current user id.

[Mekaboo]

Hey @dsonesuk😊

 

Ive realized that I just need to do an INNER JOIN to connect the tables. My loop is right just need to do a query connect. This is what I came up with:

<?php
$sql = "SELECT imageId FROM output_images INNER JOIN comment ON imageId = comment.imageId ORDER BY imageId DESC;"; 
?>

Also @niche you are more than welcome to chime in on this😊

Edited November 12, 2021 by Mekaboo
add words

[niche]

I don't think that will do anything.  You need to SELECT more columns than than just the ones you want to JOIN ON to get anything back.  Also, I recommend changing imageid to output_images.imageid   

[Mekaboo]

@niche Okie Dokie