EternalSorrow Posted February 24, 2010 Share Posted February 24, 2010 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>'; ?> Quote Link to comment Share on other sites More sharing options...
FUEL Posted February 24, 2010 Share Posted February 24, 2010 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()); Quote Link to comment Share on other sites More sharing options...
EternalSorrow Posted February 24, 2010 Author Share Posted February 24, 2010 $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. Quote Link to comment Share on other sites More sharing options...
trq Posted February 24, 2010 Share Posted February 24, 2010 You need to pass any variables being used in ANY queries through mysql_real_escape_string first. Quote Link to comment Share on other sites More sharing options...
EternalSorrow Posted February 24, 2010 Author Share Posted February 24, 2010 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. Quote Link to comment Share on other sites More sharing options...
trq Posted February 24, 2010 Share Posted February 24, 2010 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.