SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Member
    Join Date
    Feb 2005
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Moving a row within a table???

    Hi,

    I am using a PHP script to access a MySQL database with concert listing. I need to be able to insert a new concert listing say between row #16 and 17.

    is this possible and if so, how do I do that? I cant seem to find any information on how to either move rows up or down within the table or to insert a new row between two existing rows.

    Thanks!!!!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    rows don't have numbers, rows don't have positions, and you can't "move" rows

    perhaps with some more information we can help you achieve what you want

    see also this thread
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard silver trophy Jelena's Avatar
    Join Date
    Feb 2005
    Location
    Universum, 3rd Corner
    Posts
    3,000
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    You cannot do something like that, but you can always add another column say with date and then just sort your results by that.
    The other solution is to add one column that is only used for sorting.

    However, this is just my guess. We would need more information in order to help you more and find the best solution.
    -- Jelena --

  4. #4
    SitePoint Member
    Join Date
    Feb 2005
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I need to be able to update my bands concert listings which I have entered into a MySQL database.

    Here is a link to the output that I have from the database.

    http://skarrd.inkmachine.com/concerts/concerts.php

    My table is set up like this....

    id (Auto increment) Primary Key
    venue
    info
    location
    dates
    time
    admission
    directions
    website

    I enter this information using a standard web form.

    Now, if you're looking at my concert listing page with the link provided above, I would like to be able to do the following.

    Let's say my band needs to add a show between the Hickory Lounge and the Jamins Lounge dates.As it is right now, all I can do is add a new date to the top of the list but then the shows would be out of order.

    Please forgive me for my ignorance on this if it is a simple matter. I am very new to MySql and PHP and am doing my best to learn it. I really do appreciate any help you might want to provide...or...you can tell me to shove it LOL

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    does the query you are using to produce that page have an ORDER BY clause?

    it should be .... ORDER BY somedate DESC

    the problem here is that you have a column called dates but it appears to be storing a string

    change your table to have two columns, date1 and date2, where each is DATE datatype and the 2nd is nullable (for 1-night gigs)

    then ORDER BY date1 DESC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Member
    Join Date
    Feb 2005
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Below is the code used to generate the page that I provided a link for in my previous post.

    Again, I apologise if this is badly written as I am still learning the ropes and might not know of other more efficient was to construct the code.

    ++++++++++cut n paste+++++++++++++++++++

    <?php

    $server = ""; // server to connect to.
    $database = ""; // the name of the database.
    $db_user = ""; // mysql username to access the database with.
    $db_pass = ""; // mysql password to access the database with.
    $table = "concerts"; // database table
    $rows = "9";

    // connect to the mysql server
    $link = mysql_connect($server, $db_user, $db_pass)
    or die ("Could not connect to mysql because ".mysql_error());

    // select the database
    mysql_select_db($database)
    or die ("Could not select database because ".mysql_error());

    // read data from database
    $result = mysql_query("select * from $table order by id desc limit $rows", $link)
    or die ("Could not read data because ".mysql_error());

    // print the data in a table
    if (mysql_num_rows($result)) {
    print "<table align=center bgcolor=#000000 width=400 cellspacing=2 cellpadding=2 border=1>\n";
    print "<tr>\n";
    print "<td colspan=2 align=center><img src=../concerts/upcoming.jpg width=337 height=30></td>\n";
    print "</tr>\n";
    while ($qry = mysql_fetch_array($result)) {
    print "<tr>\n";
    print "<td colspan=2 align=center bgcolor=#222222>\n";
    print "<strong>\n";
    print "<font face=Verdana,Geneva,Arial,Helvetica,sans-serif size=+2 color=#ff6600>$qry[venue]</font>\n";
    print "</strong>\n";
    print "<br>\n";
    print "<font face=Verdana,Geneva,Arial,Helvetica,sans-serif size=-1 color=#FFFFFF>$qry[info]</font>\n";
    print "</td>\n";
    print "</tr>\n";
    print "<tr>\n";
    print "<td align=right width=150>\n";
    print "<font face=Verdana,Geneva,Arial,Helvetica,sans-serif size=-1 color=#FFFFFF>Location/font>\n";
    print "</td>\n";
    print "<td width=250>\n";
    print "<font face=Verdana,Geneva,Arial,Helvetica,sans-serif size=-1 color=#FFFFFF>$qry[location]</font>\n";
    print "</td>\n";
    print "</tr>\n";
    print "<td align=right width=150>\n";
    print "<font face=Verdana,Geneva,Arial,Helvetica,sans-serif size=-1 color=#FFFFFF>Dates/font>\n";
    print "</td>\n";
    print "<td width=250>\n";
    print "<font face=Verdana,Geneva,Arial,Helvetica,sans-serif size=-1 color=#FFFFFF>$qry[dates]</font>\n";
    print "</td>\n";
    print "</tr>\n";
    print "<td align=right width=150>\n";
    print "<font face=Verdana,Geneva,Arial,Helvetica,sans-serif size=-1 color=#FFFFFF>Time/font>\n";
    print "</td>\n";
    print "<td width=250>\n";
    print "<font face=Verdana,Geneva,Arial,Helvetica,sans-serif size=-1 color=#FFFFFF>$qry[time]</font>\n";
    print "</td>\n";
    print "</tr>\n";
    print "<td align=right width=150>\n";
    print "<font face=Verdana,Geneva,Arial,Helvetica,sans-serif size=-1 color=#FFFFFF>Admission/font>\n";
    print "</td>\n";
    print "<td width=250>\n";
    print "<font face=Verdana,Geneva,Arial,Helvetica,sans-serif size=-1 color=#FFFFFF>$qry[price]</font>\n";
    print "</td>\n";
    print "</tr>\n";
    print "<td align=right width=150>\n";
    print "<font face=Verdana,Geneva,Arial,Helvetica,sans-serif size=-1 color=#FFFFFF>Directions/font>\n";
    print "</td>\n";
    print "<td width=250>\n";
    print "<font face=Verdana,Geneva,Arial,Helvetica,sans-serif size=-1 color=#FFFFFF>\n";
    print "<a href=$qry[directions] target=_blank>Map & Directions</a></font>\n";
    print "</td>\n";
    print "</tr>\n";
    print "<td align=right width=150>\n";
    print "<font face=Verdana,Geneva,Arial,Helvetica,sans-serif size=-1 color=#FFFFFF>Website/font>\n";
    print "</td>\n";
    print "<td width=250>\n";
    print "<font face=Verdana,Geneva,Arial,Helvetica,sans-serif size=-1 color=#FFFFFF>\n";
    print "<a href=$qry[website] target=_blank>$qry[website]</a></font>\n";
    print "</td>\n";
    print "</tr>\n";
    }
    print "</table>\n";
    }

    mysql_close();

    ?>

  7. #7
    SitePoint Member
    Join Date
    Feb 2005
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I forgot to mention this in my last post..

    The form that I use to enter the information in the table will enter the date for the show as a text string. So where the form asks for Date: [Text box] I would then simply type in March 24-25, 2005.

    I am not sure on how to implement the suggestions that you've made for the two new columns into my code that I posted above.

    LOL I feel bad for asking for so much help. I feel like most of you are probably thinking I should just hire someone to do it LOL :P

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the code doesn't help (some of us don't use that particular language)

    change the form so that it asks for --

    First Date: [ text box ]
    Last Date: [ text box ]

    then convert the input in each box into a valid date format to insert into the date1 and date2 columns which you will add to the table

    if the Last Date box is empty, then store NULL into date2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Member
    Join Date
    Feb 2005
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So I would still enter the First Date [text area] as a string by typing in the dates march 24 2005 then in Last Date [ text area] I would type in march 25, 2005?

    If thats correct, how would I go about converting that typed in data into a 'valid date format' as you say?

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you would type it in using the only format that is compatible with mysql, i.e. 2005-03-24

    that is, unless you are good at writing the code to reformat whatever you type in into that format



    converting can be done by inspection, depending on how many rows we're talking about
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Member
    Join Date
    Feb 2005
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    WWWEEEEEEEEEEE!!!!

    I Love you!!!!!!! LOL

    I got it to work :P

    I really can't thank you enough for helping me with this :P

    Thank you Thank you Thank you soooo much!

    You're a lifesaver :P

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    thanks for the kind words

    congrats for making the change on your own, i figured it would've taken a couple more exchanges here on this thread, but if you got it to work by yourself, you're doing really great

    good luck with your site
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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