View Full Version : adding consecutive numbers to column

03-17-2013, 02:35 AM
Now that this script is finally working the way I want, I just need one more item.
The far left column will have only a number, from 1 to 100.
What is the proper way of doing that?


/* connect to the db */
$con = mysql_connect('localhost','user','pass');

if (!$con){
die("can not connect: " . mysql_error());


$sql = "SELECT * FROM A1960";
echo "<table border='1'>";
echo "<tr><th></th><th>Title/Flip</th><th>Artist/Label</th></tr>\n";

while ($record = mysql_fetch_array($myData)) {

echo "<tr>";
echo "<td></td><td>" . $record[atitle] . "<br>" . $record[btitle] . "</td>";
echo "<td>" . $record[artist] . "<br>" . $record[label] . "</td>";
echo "</tr>\n";


echo "</table>";


03-17-2013, 04:22 PM
Was gonna say this is not a MySql problem; it's a php problem, but it maybe both.
$record[atitle] and the others won't work. They need to be $record['atitle']

Try this:

$i = 1;
echo "<table border='1'>";
echo "<tr><th>Need Title</th><th>Title/Flip</th><th>Artist/Label</th></tr>\n";
while($record = mysql_fetch_array($myData))
echo "<tr>";
echo "<td>$i</td><td>" . $record["atitle"] . "<br>" . $record["btitle"] . "</td>";
echo "<td>" . $record["artist"] . "<br>" . $record["label"] . "</td>";
echo "</tr>\n";
echo "</table>";

03-17-2013, 11:32 PM
Yes this can be done via mysql:

SELECT t.*, @num := @num + 1 b from table t, (SELECT @num := 0) d;

This is a copy from my stash, so don't ask me how it works. It just does.:thumbsup:

Old Pedant
03-18-2013, 02:38 AM
That works because MySQL allows you to introduce new variables into a query if you precede them with the @ sign.

It's a cute trick. Usually, you would do something like that in a Stored Procedure--and it's possibly the only place you could do it in other databases--but MySQL is nothing if not flexible.

The d there (which I would have coded as AS d) is needed because MySQL insists that any sub-select that you JOIN to must have an alias.

And you *ARE* joining table AS t and pseudo-table d, make no mistake about that: It's a cartesian join, meaning that ever record both t and d are joined together. But since there is only one record in d, that's exactly what you want.

Very sneaky and effective trick, one just invented for MySQL.

Old Pedant
03-18-2013, 02:46 AM
By the by...

If you don't use the PHP mysql library--if you code directly in MySQL code or in ASP or ASP.NET or JSP *OR* if you use PHP's mysqli library, there's another way to do this that may seem more intuitive and simpler:

SET @cnt = 0; SELECT *, @cnt := @cnt + 1 FROM table;

Notice that now we are using *TWO* SQL statements (which is why the older mysql library can't use this--it only allows one SQL statement per query). But it may be simpler to understand this way.

Note the fairly stupid limitation of MySQL: If you use * ("all fields") in a SELECT list, it must be the last item in the list. (No other DB I know of has this limitation...even Access.) If you specify the fields by name, though, they can be in an order. So you could do:

SET @cnt = 0; SELECT @cnt := @cnt + 1, fieldA, fieldB, fieldC FROM table;