0

I have a table that contains webcomics and has the following columns: id, title, date, comic, thumbnail, and hits.

I am trying to sort the comics with the most hits in a one month time range by using the following code:

$oneMonth = mysql_query("CREATE VIEW ['oneMonthSort'] AS SELECT * FROM comics WHERE date_time = date_sub(now() interval 1 month)");
$oneMonthSort = mysql_query("SELECT * FROM oneMonthSort ORDER BY 'hits' DESC");
$topOfOne = array();
while($row = mysql_fetch_array($oneMonthSort)){
$thumb = $row["thumbnail"];
echo $thumb;
}

I get the error mysql_fetch_array() expects parameter 1 to be resource, boolean given

Pouria
  • 117
  • 1
  • 12
  • But what actually happens is...? – durrrutti Jan 03 '14 at 02:35
  • 1
    And don't use `mysql_*` in your codes. Use `mysqli_*` or `PDO`. – Mark Jan 03 '14 at 02:36
  • sorry I'll include it in my main post @durrrutti – Pouria Jan 03 '14 at 02:36
  • Your query is returning no results. – SenorAmor Jan 03 '14 at 02:40
  • Add behind your query or die(mysql_error()); To view your error and post it here :), if you need help. – user3152069 Jan 03 '14 at 02:43
  • 1
    Square brackets `[]` are not syntactically valid for MySQl identifiers. Use backticks, or nothing at all. `[]` are used by MSSQL and Access – Michael Berkowski Jan 03 '14 at 02:44
  • In your example, `oneMonthSort` should have no quotes at all. – Michael Berkowski Jan 03 '14 at 02:45
  • @MichaelBerkowski I tried taking the quotes out, it didn't work – Pouria Jan 03 '14 at 02:48
  • The statement should look like `CREATE VIEW oneMonthSort AS SELECT * FROM comics WHERE date_time = date_sub(now() interval 1 month)` It is advisable to specify your columns rather than `SELECT *` when creating a view. – Michael Berkowski Jan 03 '14 at 02:52
  • @MichaelBerkowski copied and pasted that in, still not working. I do appreciate your help though. – Pouria Jan 03 '14 at 02:57
  • I wasn't looking at the second query. `hits` should also be unquoted in the `ORDER BY.` See http://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks – Michael Berkowski Jan 03 '14 at 02:58
  • You need to do some error checking on those. `if (!$oneMonthSort) echo mysql_error();` to debug... As has been previously commented, now's the time to invest in learning [PDO](http://us1.php.net/pdo). `mysql_*()` are deprecated and to be removed. I recommend http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers – Michael Berkowski Jan 03 '14 at 03:00

1 Answers1

0

The error message tells you that what you are passing to mysql_fetch_array() is a boolean (duh), hence it's probably the value false which means your query failed. You need to review your SQL query. Try to run it in your terminal or your GUI and investigate at which step it fails.

This could be due to some naming error, which is impossible to debug without access to your actual database.

UPDATE: Actually, looking closer at your SQL, I see that you're wrapping the view name in square brackets. Why? As far as I know, you're supposed to just name the view as is, like so: CREATE VIEW MyView AS....

durrrutti
  • 970
  • 1
  • 7
  • 17