Moving a row within a table???

**inkmachine** · Feb 2, 2005, 19:24

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!!!!

**r937** · Feb 2, 2005, 20:37

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

**Jelena** · Feb 3, 2005, 05:03

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.

**inkmachine** · Feb 3, 2005, 07:49

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

**r937** · Feb 3, 2005, 07:59

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

**inkmachine** · Feb 3, 2005, 09:00

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 "\n";
print "$qry[venue]\n";
print "\n";
print " \n";
print "$qry[info]\n";
print "</td>\n";
print "</tr>\n";
print "<tr>\n";
print "<td align=right width=150>\n";
print "Location

/font>\n";
print "</td>\n";
print "<td width=250>\n";
print "$qry[location]\n";
print "</td>\n";
print "</tr>\n";
print "<td align=right width=150>\n";
print "Dates

/font>\n";
print "</td>\n";
print "<td width=250>\n";
print "$qry[dates]\n";
print "</td>\n";
print "</tr>\n";
print "<td align=right width=150>\n";
print "Time

/font>\n";
print "</td>\n";
print "<td width=250>\n";
print "$qry[time]\n";
print "</td>\n";
print "</tr>\n";
print "<td align=right width=150>\n";
print "Admission

/font>\n";
print "</td>\n";
print "<td width=250>\n";
print "$qry[price]\n";
print "</td>\n";
print "</tr>\n";
print "<td align=right width=150>\n";
print "Directions

/font>\n";
print "</td>\n";
print "<td width=250>\n";
print "\n";
print "<a href=$qry[directions] target=_blank>Map & Directions</a>\n";
print "</td>\n";
print "</tr>\n";
print "<td align=right width=150>\n";
print "Website

/font>\n";
print "</td>\n";
print "<td width=250>\n";
print "\n";
print "<a href=$qry[website] target=_blank>$qry[website]</a>\n";
print "</td>\n";
print "</tr>\n";
}
print "</table>\n";
}

mysql_close();

?>

**inkmachine** · Feb 3, 2005, 09:03

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

**r937** · Feb 3, 2005, 09:07

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

**inkmachine** · Feb 3, 2005, 09:17

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?

**r937** · Feb 3, 2005, 09:47

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

**inkmachine** · Feb 3, 2005, 13:27

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

**r937** · Feb 3, 2005, 15:33

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

The SitePoint Forums have moved.

User Tag List

Thread: Moving a row within a table???

Thread Tools

Display

Moving a row within a table???

Bookmarks

Bookmarks

Posting Permissions