mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given

[samnester]

Hey all. First I want to say that I'm very amateur when it comes to coding. Most of what I know has been self-taught so I don't have any formal education. My coding vocabulary is awful, but generally when I build code I understand what it's doing in laymen terms (ex. it's pulling data from the "users" row in a table and is displaying that data).

 

Anyway...that's my disclaimer to ask you to be easy on me.

 

So now to my actual issue. I've built a page where logged in users can share photos with their friends. I successfully built this without any issues. Now I want friends to be able to add comments to the photos. Not so easy. Problem is I have two tables built. One for the sharing of the photos the other for the comments. Here are how the tables are built out:

 

Photo Table
id | username | initiator | file | gallery | date_time

 

Comment Table

id | comment | user | file | date_time

 

Initially I had two queries. Both use a "fetch" array in order to grab and then display specific info in a specific order only for specific users. This was almost working but whichever query came first would only display one result. The query that came after would display all the appropriate results which is what I wanted out of both queries.

On another forum I was told that I needed to "join" or "union" the two queries together. Problem is that when I attempt to do that I get a single dead image, no comments other than the default stuff that is already entered in the PHP code and the error: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given...

 

Here is the actual query and code:

//WHERE I GATHER COMMENT & PHOTO DATA

$sql = "SELECT comment, user, file, date_time FROM comment
UNION ALL
SELECT DISTINCT initiator, file, gallery FROM photo WHERE username='$log_username' OR initiator='$log_username' ORDER BY date_time DESC";
$query = mysqli_query($db_conx, $sql); 
while ($row = mysqli_fetch_array($query, MYSQLI_ASSOC)) {
$c = $row["comment"];
$us = $row["user"];
$pf = $row["file"];
$time = $row["date_time"];
$u = $row["initiator"];
$file = $row["file"];
$gallery = $row["gallery"];
$image = 'user/'.$u.'/'.$file; 


}


//WHERE I DISPLAY THE PHOTO DATA
$imagelist .= '  <img height="200"
           onclick="this.height=500;"
           ondblclick="this.height=200;" src="'.$image.'" alt="'.$u.'" /><br />  Added to <a href="user.php?u='.$u.'"><b>'.$u.''s</b></a> '.$gallery.' gallery<br /><br />       
<form action="php_parsers/photocomments_system.php" enctype="multipart/form-data" method="post">
 <input type="text" name="comment">
<input type="hidden" name="photo_file" value="'.$file.'">
<input type="submit" class="submit" value=" Submit Comment " />
</form><br /><p style="border-bottom: 1px dotted #A0A0A0;"></p>';


//WHERE I DISPLAY THE PHOTO COMMENTS AND DICTATE WHAT COMMENT SHOWS WITH WHAT PHOTO
if ($pf == $file) {
$imagelist .='<p style="background-color:#E0E0E0;">  <b>'.$us.'</b>: '.$c.' | '.$time.'</p>
<br /><br /><hr/>';
}

For the HTML portion this is where it actually gets displayed:

 

  <div id="page Middle"><?php echo $imagelist; ?></div>

 

Anyway, I'm assuming there is something wrong with my query and that's why I'm getting the error. Problem is I'm not sure what is wrong with the query. If there is any easier way to do this and I was doing something wrong with the two separate queries that could easily be resolved I can show you how I built that as well. 

 

I would continue on the other forum with the guy that was helping me but he was a little bit too technical for me to understand his suggestions.

Anyway, any help you could give would be GREATLY appreciated.

Thanks!

[PravinS]

its problem with your query, echo the query and debug your query in phpmyadmin or any other MySQL client

[jazzman1]

 

In SQL the UNION clause combines the results of two SQL queries into a single table of all matching rows. The two queries must result in the same number of columns and compatible data types in order to unite. Any duplicate records are automatically removed unless UNION ALL is used.

 

http://en.wikipedia.org/wiki/Set_operations_%28SQL%29

[ebk]

When you attempted to run the query, the syntax of the query was bad and the function returned false. Somethings weird with your query.

[samnester]

Thanks for all the info everyone. So it looks like a UNION or even a UNION ALL clause isn't going to work. The two tables in question do not have the same number of columns or rows and not all data types are necessarily "compatible."

 

So I guess I'm sort of back to square one on this unless the JOIN clause will work for my situation. However, everything I've read on JOIN clauses gets really confusing and I'm unable to duplicate the examples I run across for my purposes.

So here is what I had set up before attempting the UNION clause and the issue I was having:

 

//WHERE I GATHER COMMENT DATA
$sql = "SELECT comment, user, file, date_time FROM comment ORDER BY id ASC";
$query = mysqli_query($db_conx, $sql); 
$commentlist = "";
while ($row = mysqli_fetch_array($query, MYSQLI_ASSOC)) {
$c = $row["comment"];
$us = $row["user"];
$pf = $row["file"];
$time = $row["date_time"];
} 
}

//WHERE I GATHER PHOTO DATA 
$sql = "SELECT DISTINCT initiator, file, gallery FROM photo WHERE username='$log_username' OR initiator='$log_username' ORDER BY date_time DESC";
$query = mysqli_query($db_conx, $sql);
$photolist = "";
while ($row = mysqli_fetch_array($query, MYSQLI_ASSOC)) {
$u = $row["initiator"];
$file = $row["file"];
$gallery = $row["gallery"];
$image = 'user/'.$u.'/'.$file;


//WHERE I DISPLAY THE PHOTO DATA
$imagelist .= '  <img height="200"
           onclick="this.height=500;"
           ondblclick="this.height=200;" src="'.$image.'" alt="'.$u.'" /><br />  Added to <a href="user.php?u='.$u.'"><b>'.$u.''s</b></a> '.$gallery.' gallery<br /><br />       
<form action="php_parsers/photocomments_system.php" enctype="multipart/form-data" method="post">
 <input type="text" name="comment">
<input type="hidden" name="photo_file" value="'.$file.'">
<input type="submit" class="submit" value=" Submit Comment " />
</form><br /><p style="border-bottom: 1px dotted #A0A0A0;"></p>';


//WHERE I DISPLAY THE PHOTO COMMENTS AND DICTATE WHAT COMMENT SHOWS WITH WHAT PHOTO
if ($pf == $file) {
$imagelist .='<p style="background-color:#E0E0E0;">  <b>'.$us.'</b>: '.$c.' | '.$time.'</p>
<br /><br /><hr/>';
}
}

You'll notice in the code above I'm using a "SELECT DISTINCT" clause for gathering the photos. This is necessary because the username that uploaded and the photo file names are duplicated multiple times in the table for notification purposes. If there was no "SELECT DISTINCT" clause the same photo would show multiple times.

 

Anyway, here is the issue and how it looks from a user perspective.

 

Because the photo query is being run second the photos show just as they should. 

 

 

When a user attempts to comment on a photo, the comment is shown for the specific picture however only one comment shows at a time:

 

 

 

Notice that the "Nice picture!" comment overrode the "This looks yummy!" comment above:

 

 

So basically the script is only displaying one comment from the comment table at a time rather than showing all comments at once. If I swap the queries only one photo will show at a time and all of the comments will show at once. Is there anything you can see in my script that I could change that would allow all comments to show at once along with the photos? I'm hoping it's as easy as altering the "if" statement at the end of the script. I'm REALLY hoping I can avoid having to do a JOIN clause since I have a hard time wrapping my head around the formatting but if that's what I need to do so be it.

 

Again, any help you could give would be GREATLY appreciated. 

Thanks!

 

[jazzman1]

Hey samnester,

before to provide a solution about your topic, I have few questions.

1. Why do you want to save a binary data (images) into the database instead in the filesystem? Or....I'm wrong?
2. Who will have permission to upload and later on to comment these photos? Every users or only those who are already registrated on the system?
3. Can you explain me in clear English, what do you expect to do the columns - initiator | file | gallery | ....and why do you have duplicated columns inside comment table.
In general, try to describe the whole idea.