[Resolved] Error, query failed.. :(

goaman

Hi!
I'm trying to get the comments by image_id, I'm getting this error: query failed with from this line: "FROM xcomments WHERE image_id=".$_GET['view'].[/red]. It'd be great if anyone give me a hand on this one, please

$query = "SELECT id, name, email, ip, message, DATE_FORMAT(entry_date, '%d.%m.%Y') ".
         "FROM xcomments WHERE image_id=".$_GET['view'].
         "ORDER BY id DESC ".            

         "LIMIT $offset, $rowsPerPage";  

// execute the query
$result = mysql_query($query) or die('Error, query failed');

YappyDog

What do you see if you echo out $query before you execute it?

goaman

Originally posted by YappyDog
What do you see if you echo out $query before you execute it?

I'm getting..

SELECT id, name, email, ip, message, DATE_FORMAT(entry_date, '%d.%m.%Y') FROM xcomments WHERE image_id=ORDER BY id DESC LIMIT 0, 10Error, query failed

YappyDog

You don't have an id....

WHERE image_id=ORDER BY

should be something like

WHERE image_id= 456 ORDER BY

goaman

Originally posted by YappyDog
You don't have an id....

WHERE image_id=ORDER BY

should be something like

WHERE image_id= 456 ORDER BY

now I've fixed that one, it shows as:

SELECT id, name, email, ip, message, DATE_FORMAT(entry_date, '%d.%m.%Y') FROM xcomments WHERE image_id=cf5f84d362b163d2a1b42d02013e0214.jpgORDER BY id DESC LIMIT -10, 10Error, query failed

but query still fails... 🙁

YappyDog

SELECT id, name, email, ip, message, DATE_FORMAT(entry_date, '%d.%m.%Y') FROM xcomments WHERE image_id=cf5f84d362b163d2a1b42d02013e0214.jpgORDER BY id DESC LIMIT -10, 10

you'll need single quotes around the id, and at least one space after it (before the ORDER BY).

not sure if a negative number is okay for a limit or not.

goaman

Originally posted by YappyDog
SELECT id, name, email, ip, message, DATE_FORMAT(entry_date, '%d.%m.%Y') FROM xcomments WHERE image_id=cf5f84d362b163d2a1b42d02013e0214.jpgORDER BY id DESC LIMIT -10, 10

you'll need single quotes around the id, and at least one space after it (before the ORDER BY).

not sure if a negative number is okay for a limit or not.

Now I've a space between the id=ugasdgiwu7236.jpg and ORDER BY, but still I'm getting error 🙁

SELECT id, name, email, ip, message, DATE_FORMAT(entry_date, '%d.%m.%Y') FROM xcomments WHERE image_id=18f93a2f5a37fcc42b2ca456ae6e468d.jpg ORDER BY id DESC LIMIT -10, 10Error, query failed

YappyDog

As I said...........

you'll need single quotes around the id

YappyDog

also, if you are selecting out only the one row, you don't need the order by or the limit. Without knowing your database structure it is hard to tell if your query will return multiple rows or not.

goaman

Originally posted by YappyDog
also, if you are selecting out only the one row, you don't need the order by or the limit. Without knowing your database structure it is hard to tell if your query will return multiple rows or not.

Database Structure:

members(db)
xcomments(table)

It should get the comments by ireading image_id, the code seems to be ok, but getting Error, query failed....

YappyDog

What does your $query look like now?

goaman

Originally posted by YappyDog
What does your $query look like now?

this query from viewcomments.php

// prepare the query string
$query = "SELECT id, name, email, ip, message, DATE_FORMAT(entry_date, '%d.%m.%Y') ".
         "FROM xcomments WHERE image_id=".$_GET['view']." ".
         "ORDER BY id DESC ".            

         "LIMIT $offset, $rowsPerPage";  

echo $query;
// execute the query
$result = mysql_query($query) or die('Error, query failed');

This one is from addcomments.php:

    // prepare the query string
    $query = "INSERT INTO xcomments (name, email, ip, message, entry_date, image_id) " .
             "VALUES ('$txtName', '$email', '$ip', '$message', current_date, ".$_GET['view'].")";

// execute the query to insert the input to database
// if query fail the script will terminate        
mysql_query($query) or die('Error, query failed');

YappyDog

What does your $query look like now?

Before you execute it. echo it out to see what you are executing.

goaman

Originally posted by YappyDog
Before you execute it. echo it out to see what you are executing.

I cannot even see the echo or the addcomments inputs and textareas because it's showing this msg:

SELECT id, name, email, ip, message, DATE_FORMAT(entry_date, '%d.%m.%Y') FROM xcomments WHERE image_id= ORDER BY id DESC LIMIT 0, 10Error, query failed

YappyDog

then echo it out and do not execute it. You need to see what is being executed.

and it looks like you are back to missing the id again

goaman

Originally posted by YappyDog
then echo it out and do not execute it. You need to see what is being executed.

and it looks like you are back to missing the id again

I've tried to echo the id, it shows the image's id.

this is my viewcomments.php

I've tried to echo the query b4 executing it, shows : Error, query failed

<?php
$image_id = $_GET['view'];

// how many comments entries to show per page
$rowsPerPage = 10;

// by default we show first page
$pageNum = 1;

// if $_GET['page'] defined, use the value as page number
if(isset($_GET['page']))
{
    $pageNum = $_GET['page'];
}

// counting the offset ( where to start fetching the entries )
$offset = ($pageNum - 1) * $rowsPerPage;

// prepare the query string
$query = "SELECT id, name, email, ip, message, DATE_FORMAT(entry_date, '%d.%m.%Y') ".
         "FROM xcomments WHERE image_id=".$_GET['view']." ".
         "ORDER BY id DESC ".            

         "LIMIT $offset, $rowsPerPage";  

// execute the query
$result = mysql_query($query) or die('Error, query failed');

// if the commentboard is empty show a message
if(mysql_num_rows($result) == 0)
{
?>
<p><br>
  <strong>There are no any comments for selected picture!</strong></p>
<?php
}
else
{
echo $query;
    // get comments 
    while($row = mysql_fetch_array($result))
    {
        // list() is a convenient way of assign a list of variables
        // from an array values
        list($id, $name, $email, $ip, $message, $date, $image_id) = $row;

    // change all HTML special characters,
    // to prevent some nasty code injection
    $name    = htmlspecialchars($name);
    $message = htmlspecialchars($message);        

    // convert newline characters ( \n OR \r OR both ) to HTML break tag ( <br> )
    $message = nl2br($message);
?>
<table width="550" border="1" cellpadding="2" cellspacing="0">
  <!--DWLayoutTable-->
  <tr> 
    <td width="89" align="left" background="images/co1.jpg"> <a href="mailto:<?=$email;?>" class="email"> 
      <?=$name;?>
      <br>
     </td>
    <td width="447" align="right" background="images/co1.jpg"><small> 
      <?=$date;?>
      </small></td>
  </tr>
  <tr> 
    <td height="46" colspan="2" valign="top"> 
      <?=$message;?>
      </small> 
    </td>
  </tr>
</table>
<br>
<?php
    } // end while

// below is the code needed to show page numbers

// count how many rows we have in database
$query   = "SELECT COUNT(id) AS numrows FROM xcomments";
$result  = mysql_query($query) or die('Error, query failed');
$row     = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row['numrows'];

// how many pages we have when using paging?
$maxPage  = ceil($numrows/$rowsPerPage);
$nextLink = '';

// show the link to more pages ONLY IF there are
// more than one page
if($maxPage > 1)
{
    // this page's path
    $self     = $_SERVER['PHP_SELF'];

// we save each link in this array
$nextLink = array();

// create the link to browse from page 1 to page $maxPage
for($page = 1; $page <= $maxPage; $page++)
{
    $nextLink[] =  "<a href=\"$self?page=$page\">$page</a>";
}

// join all the link using implode()
$nextLink = "Go to page : " . implode(' &raquo; ', $nextLink);
}

// close the database connection since
// we no longer need it or just leave it

?>
<table width="550" border="0" cellpadding="2" cellspacing="0">
<tr>
  <td align="right" class="text">
   <?=$nextLink;?>
  </td>
</tr>
</table>
<?php
}
?>
</body>
</html>

YappyDog

Well, I would change

// prepare the query string
$query = "SELECT id, name, email, ip, message, DATE_FORMAT(entry_date, '%d.%m.%Y') ".
"FROM xcomments WHERE image_id=".$_GET['view']." ".
"ORDER BY id DESC ".

"LIMIT $offset, $rowsPerPage";

echo $query;
// execute the query
$result = mysql_query($query) or die('Error, query failed');

to something like

// prepare the query string
$query = "SELECT id, name, email, ip, message, DATE_FORMAT(entry_date, '%d.%m.%Y') ".
"FROM xcomments WHERE image_id= '$image_id' ".
"ORDER BY id DESC ".

"LIMIT $offset, $rowsPerPage";

echo $query;
// execute the query
$result = mysql_query($query) or die('Error, query failed [$query]');

so I added in the quotes around the id variable. I also used the variable you were getting at the top of the script rather than pulling it out of the $_GET array again. Lastly, I added the $query to your die statement.

goaman

Now it shows: SELECT id, name, email, ip, message, DATE_FORMAT(entry_date, '%d.%m.%Y') FROM xcomments WHERE image_id= 'cf5f84d362b163d2a1b42d02013e0214.jpg' ORDER BY id DESC LIMIT -10, 10

There are no any comments for this picture, you could be the first one. 🙂

but that picture really has a comment in db 🙁

YappyDog

Try changing the limit so it is not a negative number

goaman

Originally posted by goaman

Now it shows:

(this is the echo)
SELECT id, name, email, ip, message, DATE_FORMAT(entry_date, '%d.%m.%Y') FROM xcomments WHERE image_id= 'cf5f84d362b163d2a1b42d02013e0214.jpg' ORDER BY id DESC LIMIT -10, 10

There are no any comments for this picture, you could be the first one.

but that picture really has a comment in db , it doesn't grab those comments 🙁

sorry for the double post 🙁