Apostrophe w/ Query within Query - supplied argument error

[EternalSorrow]

I'm working on a code which has to allow apostrophes in the names of authors.  The trick is I have a query embedded within another, and I keep receiving this error:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's' GROUP BY year ORDER BY year ASC' at line 1

 

The line is this one:

 

$select_year = mysql_query("SELECT * FROM archives WHERE `author` = '$author' GROUP BY year ORDER BY year ASC") or die(mysql_error());

 

I know it's the problem with the apostrophe in the author's name, and I'm able to fix the problem in the first query with sprintf but I am not sure how to fix the embedded second query.  I've tried various uses of sprintf, but then I end up with a 'resource error' with the WHILE statement for row3 (which is the WHILE for the second query).

 

Anyone have any ideas?  The code is below, minus of course the connection.

 

<?php

$query=sprintf("SELECT * FROM archives WHERE `author`= '%s' LIMIT 1", mysql_real_escape_string($author)) or die(mysql_error());
$result = mysql_query( $query ) or die(mysql_error());

while ($row1 = mysql_fetch_array($result))
{
extract($row1);

$select_year = mysql_query("SELECT * FROM archives WHERE `author` = '$author' GROUP BY year ORDER BY year ASC") or die(mysql_error());

$ye = "";
while ($row3 = mysql_fetch_array($select_year)) {
   $ye .= "$row3[year], ";
}

$ye = substr($ye,0,-2);

if (mysql_num_rows($select_year) == 0)
{
echo '';
}
else {
echo '<li>Years won:</li>
<li class="under_nav">';
}

echo ''.$ye.'';

}
echo '</li>';
?>

[FUEL]

Hi im new so not an experainced programmer! is this the fix?

 

$select_year = mysql_query("SELECT * FROM archives WHERE `author` = ' . $author . ' GROUP BY year ORDER BY year ASC") or die(mysql_error());

 

 

 

[EternalSorrow]

$select_year = mysql_query("SELECT * FROM archives WHERE `author` = ' . $author . ' GROUP BY year ORDER BY year ASC") or die(mysql_error());

 

I'm afraid I still receive the same error message as before.

 

 

 

[trq]

You need to pass any variables being used in ANY queries through mysql_real_escape_string first.

[EternalSorrow]

You need to pass any variables being used in ANY queries through mysql_real_escape_string first.

 

I've actually used that real_escape in the first query, but when trying to place the string into the second, like so:

 

$select_year = mysql_query("SELECT * FROM archives WHERE `author` = '%s' GROUP BY year ORDER BY year ASC", mysql_real_escape_string($author)) or die(mysql_error());

 

I receive this error message:

 

Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource

 

The error line is the $select_year line.  Placing the sprintf instead of the mysql_query receives this error:

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource

 

The new error line is the WHILE statement for the $select_year, and is the error problem I had mentioned in my first post.  So any way I can think of, I'm still getting an error in some portion of the $select_year query.

[trq]

mysql_query expects a string as its first argument, and optionally a resource identifier, so your code doesn't make much sense. Looks like your missing the string formatting function sprintf in the above code.