row_number SQL Help

[tillabong]

Hi ive been trying to fix this but i keep getting the error
mysql_fetch_array(): supplied argument is not a valid MySQL result resource. can someone help me with this? please.

when i remove the Row_Number() OVER (ORDER BY shopname ASC) AS rownumber part the script works just fine. im trying the number my results when they display.



$query = "SELECT * , Row_Number() OVER (ORDER BY shopname ASC) AS rownumber
FROM table
ORDER BY shopname";
$result = mysql_query($query);
while($data = mysql_fetch_array($result))

thank you very much.

 

[marshian]

I'm not that good with sql, but there must be an error in there.
Try using the function mysql_error() after you executed the query, it should give some details on the error.

 

[tillabong]

thanks. i've tried mysql_error() but it still returns the same error message.

 

[misson]

MySQL doesn't have a ROW_NUMBER function. Generally speaking, the concept of a row number doesn't apply in relational databases. You can keep a count of rows as you access them in PHP, if you wish. Better yet, display the list as an <ol> and the browser will number the items for you.

A bit of advice: selecting all columns (SELECT *) is generally a bad idea because it introduces a dependence on the order of columns and pulls unnecessary information from the database, which uses resources you don't need to use. The result is brittle code and requires more work, should you change the order of the columns or insert additional columns into the table. Also, the code that handles the response depends on specific columns; selecting all columns makes this dependency implicit rather than explicit. Better to explicitly name the columns you need in the SELECT statement.

 

[tillabong]

thank you very very much for the advice.