Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder
    Join Date
    Jun 2012
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    adding consecutive numbers to column

    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?


    PHP Code:
    <?php

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


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

    mysql_select_db('basename',$con);

    $sql "SELECT * FROM A1960";
    $myData=mysql_query($sql,$con);
    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>";

    ?>

  • #2
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,105
    Thanks
    23
    Thanked 594 Times in 593 Posts
    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:
    PHP Code:
    $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";
            
    $i++;
        }
    echo 
    "</table>"

  • #3
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,960
    Thanks
    120
    Thanked 76 Times in 76 Posts
    Yes this can be done via mysql:

    Code:
    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.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,210
    Thanks
    75
    Thanked 4,343 Times in 4,309 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,210
    Thanks
    75
    Thanked 4,343 Times in 4,309 Posts
    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:
    Code:
     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:
    Code:
     SET @cnt = 0; SELECT @cnt := @cnt + 1, fieldA, fieldB, fieldC FROM table;
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •