#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Location
    UK
    Posts
    7
    Rep Power
    0

    Adding info to the start of MySQL database from php form


    Hi all, im sure this is a small problem so here goes..... I have a form that will insert data into my MySQL database, but the data is added to the end, how can I get the data to be added at the start of the database.... code below from the submit bit of the form


    Thanks in advance
    Lee



    <?php

    if ($submit) {

    // process form

    $db = mysql_connect("XXXXX", "XXXXXX", "XXXXXXX") or die("Could Not Connect To The Database. <br>" . mysql_error());

    mysql_select_db("XXXXXXX",$db) or die("Could Not Select The Proper Database. <br>" . mysql_error());

    $ident = $_POST['ident'];
    $town = $_POST['town'];
    $name = $_POST['name'];
    $info = $_POST['info'];
    $url = $_POST['url'];
    $simg = $_POST['simg'];

    $sql = "INSERT INTO tablename(ident, town, name, info, url, simg) VALUES ('$ident','$town','$name','$info','$url','$simg')";

    $result = mysql_query($sql);

    if($result)

    {

    echo "Data Properly Inserted";

    }

    else

    {

    echo "An Error occured while trying to process your information.";

    print ("<br>" . mysql_error());

    }

    echo $sql ;

    echo "Thank you! Information entered.\n";

    }

    else

    {

    // display form

    ?>
  2. #2
  3. Java PHP Oracle Developer
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Location
    C-Bus OH-IO
    Posts
    204
    Rep Power
    15
    I believe that if the option to do this was available it would be a database configuration issue. I don't know if what you want is possible, I have never heard of anything like this, but I don't know MySql very well.

    If you want to retrieve rows in the way it appears that you do, I recommend adding a Timestamp column and doing an ORDER BY or if this table has a numeric key doing an ORDER BY if it saves space.

    Another reason this is a good idea even if MySql supports the ability to do what you are talking about, not all databases do, and therefore you are putting a restriction on your application.
  4. #3
  5. No Profile Picture
    Dissident
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2003
    Location
    New York
    Posts
    1,671
    Rep Power
    53
    Data in a mySql database is not stored in a linear fashion such as you are describing. It may seem as though data you are adding is being put at the "end" of the database, but that is not accurate and as your database grows in size, you will see that data is inserted wherever there is room for it, which will seem like it is being inserted randomly.

    Don't worry though, since records are stored as rows, the common practice is to add a field to each table called id (or something like it.) Furthermore, this ID field is generally set to be UNIQUE and AUTO-INCREMENT. This means that every time you add a record, this ID field will populate itself with the next highest number in the series and will guarantee that no other record in that table shares the same number.

    How can this help? you ask. I'll tell you. Once you are including IDs with all of your records, it becomes very easy to extract the information you want from your database.

    Let's say you want the last 5 records that were inserted:

    SELECT * FROM table ORDER BY id DEC LIMIT 5

    Let's say you want the first 5 records:

    SELECT * FROM table ORDER BY id ASC LIMIT 5

    Let's say you want the 12th record

    SELECT * FROM table WHERE id = 5

    As you become more experienced with databases, you will also want to start linking tables together so that you can "normalize" your data. The most common method of linking tables is to give a record in one of the two tables an id field, and then reference that id field in the next table.

    For example, let's say I have a table called users and a table called phone_numbers. I want to make sure that I can I identify specific users, I also want to be able to allow a user to have as many phone numbers as they want.

    My users table will look something like this:

    USERS
    --------
    id INT UNIQUE AUTO-INCREMENT PRIMARY KEY
    username VARCHAR(255)

    PHONE_NUMBERS
    ----------------------
    user_id INT
    phone_number VARCHAR(10)

    Now when I want to get a list of a user's phone numbers, I can do this:

    SELECT u.username, p.phone_number FROM users u, phone_numbers p WHERE u.id = p.user_id

    Lastly keep in mind that you don't have to use an id field for your tables. As already suggested, you could add a date field and put a timestamp in that field to indicate when that record was last INSERTED or UPDATED.
    Draelon


    PHP Manual :: MySQL Manual :: How to Ask Questions the Smart Way
    =======================================================
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Location
    UK
    Posts
    7
    Rep Power
    0
    Hi thanks for all the great advice... god i need it ), I have gone down the route of timestamp, what im trying to achieve is a sort of whats new section on my home page which will show the latest 3 files added to the database. This is where im at at the moment

    //connect to your database
    mysql_connect("XXXXXX","XXXXXX","XXXXXXX"); //(host, username, password)

    //specify database
    mysql_select_db("XXXXXX") or die("Unable to select database"); //select which database we're using


    // Build SQL Query
    $query = "SELECT * FROM gzdata1 ORDER BY dates ASC LIMIT 3";


    $numresults=mysql_query($query);

    $result = mysql_query($query) or die("X Couldn't execute query");

    // begin to show results set
    $count = 1 + $s ;

    // now you can display the results returned
    while ($row= mysql_fetch_array($result)) {
    $url = $row["url"];
    $name = $row["name"];
    $info = $row["info"];
    $town = $row["town"];
    $simg = $row["simg"];

    echo "<font size=2 face=Arial, Helvetica, sans-serif><strong><font color=000000>$name</font></strong></font><br>" ;
    echo "$simg" ;
    echo "<font size=1 face=Arial, Helvetica, sans-serif><font color=666666>$info<br>Area: $town</font></font><br>" ;
    echo "<font size=1 face=Arial, Helvetica, sans-serif><font color=6558a5><a href=\"" . $row["url"] . "\" target=\"_blank\">$url</a></font></font><p>";
    $count++ ;
    }
    ?>




    I have messed about with code I had for a search function, its getting me to the stage where its show 3 results but not the lastest additions, also it works when i use ASC but dies when I try DEC help! how do I get it to pull up the most recent 3 additions to the database using the timestamp

    Hope someone can help me )

    Cheers
    Lee
  8. #5
  9. Java PHP Oracle Developer
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Location
    C-Bus OH-IO
    Posts
    204
    Rep Power
    15
    When you say dies ... what is the error you get?

    Did you mean DESC rather than DEC?
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Location
    UK
    Posts
    7
    Rep Power
    0
    Ahhhhhhhhhh! that will fix it then "hangs head in shame" Thanks for your help

    Lee

IMN logo majestic logo threadwatch logo seochat tools logo