Create a pseudo auto increment collumn in database?

TeamCIT · February 2, 2010

Hey guys, I was wondering if anyone knew of a way to create a "secondary auto-increment column", I guess is the best way to describe it. As of now I have 6 columns in my database, starting with an ID field that is my primary key, followed by a Selected column. The Selected collumn was used to change from a default 0 to a 1 depending on if that item way selected in the drop-down list from the form on my site. The items are chosen one at a time by selecting the item from the list then clicking a submit button that changes the default value to a "1", then there is a second submit button that pulls all the rows with the value "1" so that I can display a list back to them. My problem is that I want the list to be able to be displayed in order of selection, as apposed to the order they appear in the database. Is there a way to create an auto-incrementing value without submitting new entries to the database?

*tl;dr - I need a way to change the values in a column of existing data in my database to make a list in the order selected by the user, without submitting new entries to the database.

This is the important part of my code that I am trying to change to accomplish my task:

if($_POST['submitted'] == true) {
$destChose = $_POST['destList'];

if($destChose >= 1) {

**mysql_query("update destinations
    			set selected=1
    			where id={$destChose}");**

$result = mysql_query("SELECT dest_name, dest_addr 
			FROM destinations
			WHERE selected = '1'");			



while ($row = mysql_fetch_array($result)) {

			echo "<li>";
			echo $row["dest_name"];
			echo "</li>";

}

}else{
		mysql_query("update destinations
    			set selected=1
    			where id={$destChose}");

		$result = mysql_query("SELECT dest_name, dest_addr 
			FROM destinations
			WHERE selected = '1'");			



		while ($row = mysql_fetch_array($result)) {

			echo "<li>";
			echo $row["dest_name"];
			echo "</li>";

		}
		echo "<br />";
		echo "<br />";
		echo "Please select a destination.";
}
}
if($_POST['reset'] == true) {
mysql_query("update destinations
    			set selected=0
    			where selected='1'");
}

if($_POST['linkgen'] == true) {
$result = mysql_query("SELECT dest_addr 
			FROM destinations
			WHERE selected = '1'");			

$counter = mysql_num_rows($result);

The *starred* section of code is the part that I will be trying to manipulate to create an incrementing number in my database without submitting new entries. Thanks in advance for your input!

gwolgamott · February 2, 2010

could store that data in an array from you form and then store it in someplace special as a single string or however, but just keep track of it from the beginning using that method. Store it as a single string with delimiters if you want. Or add a field and store it's ranking according to some script that would insert it via the data from your form. Could do it several ways... depends on how you want to handle it.

TeamCIT · February 2, 2010

could store that data in an array from you form and then store it in someplace special as a single string or however, but just keep track of it from the beginning using that method. Store it as a single string with delimiters if you want. Or add a field and store it's ranking according to some script that would insert it via the data from your form. Could do it several ways... depends on how you want to handle it.

This sounds like it could work, however there is no actual data stored in my form. Would I be able to use this method after pulling the information from the database?

gwolgamott · February 2, 2010

Yeah, just add a special field that there to keep track of that. just like you do with the ID key. It's essentially what you already had in mind, I just elaborated on your original idea. For example if different users want to see something they selected in the order they selected (just for explanation purposes here), u could have a table that kept track of just the ranks of chosen items or what have you. Then when that user logged in or whatever... it'd pull the ranking information store it in an array then the other information from the data base would be displayed according to this ranking system.

hopefully that makes some sort of sense...

The items are chosen one at a time by selecting the item from the list then clicking a submit button that changes the default value to a "1", then there is a second submit button that pulls all the rows with the value "1" so that I can display a list back to them.

You could add values somewhere in that process to do it keep track of it.

TeamCIT · February 2, 2010

hopefully that makes some sort of sense...

I have to admit i'm not quite sure how I would go about doing this, but I do mostly understand what you're trying to say.

You could add values somewhere in that process to do it keep track of it.

This is what would be my preferred way to fix this, however I'm not sure how I would add incrementing values to the "Selected" column in my database when they chose an item from the list. Any ideas?

gwolgamott · February 2, 2010

What is you steps this program takes? To help me give a more specific answer

for example does it:

call the database store the data variables.... then a form is displayed to do something.... then write to database... then calls the database to display something....

or another example:

call the database store the data in variables.... then a form is displayed selects stuff.... then send selected data only to a program to be displayed only for one time use and never updated in the database

EDIT: I may end up suggesting you change how you are going about incrementing too... now that I reread ur original post.... just give me sudo code of you process and I may be able to try and navigate it to give a suggestion

TeamCIT · February 2, 2010

It is just a simple web page with a basic form and three buttons:

<form name="tourCreation" action="createTour_inorder1.php" method="post">
Please complete this form to create your tour:
<br />
<br />
Destinations:<br />
<select name="destList" size="10">
  <option value="1">National Gallery of Art</option>
  <option value="2">National Archives & Records Administration</option>
  <option value="3">National Law Enforcement Officers Memorial</option>
  <option value="4">Kenilworth Aquatic Gardens</option>
  <option value="5">White House Park</option>
  <option value="6">Washington Photo Safari</option>
  <option value="7">Federal Bureau of Investigation</option>
  <option value="8">Franciscan Monastery - Commissariat of the Holy Land in America</option>
  <option value="9">National Portrait Gallery Special Events Department</option>
  <option value="10">Rock Creek Park</option>
  <option value="11">Anacostia Community Museum</option>
  <option value="12">National Shrine of the Immaculate Conception </option>
  <option value="13">U.S. National Arboretum</option>
  <option value="14">Union Station</option>
  
</select>
<br />
<br />
<br />
<input type="submit" name="submitted" value="Select Destination" />
<input type="submit" name="reset" value="Reset" />
<input type="submit" name="linkgen" value="Get Link" />
</form>

All of the items in the list are stored in my database. When the user clicks "Select Destination" it changes the default value of "0" in the "selected" column of that item to a "1". When they click "Get Link", it calls all of the items with "1" in the "selected" column and displays them in a list, and a link is generated to google maps. This page is just a small part of a group project I am working on with a few classmates.

Let me know if I left something out of my explanation I will be more than happy to go into more detail

gwolgamott · February 2, 2010

Ok so if I'm following you then they choose a destination.... it does what you say it does... then they select another and so on and so forth... until they want it displayed... in order of selection mind u for a tour route. Ok I gotcha.

Interesting... well it's possible and take some time to think about how to do this.

The quick dirty way... and not sure what your restrictions are for this project.... but

Have a page asking how many first off how many tour spots they want to visit... then hit select diplaying a form with a series of drop down boxes (the number of drop down boxes being the number of spots they wanted to visit).

for example:

<html>
<body>
How many stops you want on this trip?
<form action="Get_my_stops.php">
<select name="stops">
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
<option value="6">6</option>
<option value="7">7</option>
<option value="8">8</option>
<option value="9">9</option>
<option value="10">10</option>
<option value="11">11</option>
<option value="12">12</option>
<option value="13">13</option>
<option value="14">14</option>
</select>
</form>
<input type="submit" name="submitted" value="Select Number of Destinations" />

</body>
</html>

Then your Get_my_stops.php

would display something like this:

<html>
<body>
<form name="tourCreation" action="createTour_inorder1.php" method="post">
Please complete this form to create your tour:
<br />
<br />
You wanted 2 stops on your tour. Great! Lets choose the stops below!<br>
Stop #1:
<select name="1">
  <option value="1" selected="selected>National Gallery of Art</option>
  <option value="2">National Archives & Records Administration</option>
  <option value="3">National Law Enforcement Officers Memorial</option>
  <option value="4">Kenilworth Aquatic Gardens</option>
  <option value="5">White House Park</option>
  <option value="6">Washington Photo Safari</option>
  <option value="7">Federal Bureau of Investigation</option>
  <option value="8">Franciscan Monastery - Commissariat of the Holy Land in America</option>
  <option value="9">National Portrait Gallery Special Events Department</option>
  <option value="10">Rock Creek Park</option>
  <option value="11">Anacostia Community Museum</option>
  <option value="12">National Shrine of the Immaculate Conception </option>
  <option value="13">U.S. National Arboretum</option>
  <option value="14">Union Station</option>
</select><br>
Stop #2:
<select name="1">
  <option value="1" >National Gallery of Art</option>
  <option value="2" selected="selected>National Archives & Records Administration</option>
  <option value="3">National Law Enforcement Officers Memorial</option>
  <option value="4">Kenilworth Aquatic Gardens</option>
  <option value="5">White House Park</option>
  <option value="6">Washington Photo Safari</option>
  <option value="7">Federal Bureau of Investigation</option>
  <option value="8">Franciscan Monastery - Commissariat of the Holy Land in America</option>
  <option value="9">National Portrait Gallery Special Events Department</option>
  <option value="10">Rock Creek Park</option>
  <option value="11">Anacostia Community Museum</option>
  <option value="12">National Shrine of the Immaculate Conception </option>
  <option value="13">U.S. National Arboretum</option>
  <option value="14">Union Station</option>
</select>
</form>
<input type="submit" name="submitted" value="Select Number of Destinations" />

</body>
</html>

Then you can store the data with if selected... "1" and then have a rank field and enter the name since your variable name of each drop down box is going to be 1...2...3..etc.... u can then run through a loop

for($i = 1; $i<=$MY_POST_VARIABLE; $i++)

That should get you started somewhat if not too overwhelming.

TeamCIT · February 2, 2010

I wish I could use this method as it would work very well in theory. The problem is that we cannot specify the number of destinations beforehand, it has to be very flexible so I am not sure if this will work, but thank you.

gwolgamott · February 2, 2010

Not a problem, but requires a bit more functions.

Ok don't have time at moment to give sample code but can give a process to follow that may get you started....

first off lets say your column with 0's that are changing to 1'

create another one

so two columns.....

well change your 0 as usual....

well at this point take all the values of the second column.... check each one...

0 | 0

1 | 0

0 | 0

so ok we changed our column first column

now a loop checking all the values of column two.... which will give us a value of zero (since first time) and then we insert that value plus one into the second column and then would get this

0 | 0

1 | 1

0 | 0

TeamCIT · February 2, 2010

so ok we changed our column first column

now a loop checking all the values of column two.... which will give us a value of zero (since first time) and then we insert that value plus one into the second column and then would get this

0 | 0

1 | 1

0 | 0

0 | 0

how would i go about adding the value plus one into the second column? If I can accomplish this there will be no need for two columns as I will be able to call them based on if they have a value greater than 0 and list them in ascending order, right?

gwolgamott · February 2, 2010

I editted this so its a little clearer btw:

exactly... with the except to not display if it is equal to 0 else all the 0's will be displayed as well ascending order. This was a lot simpler then it had to be... sorry mind somewhere else this morning... coffee is the blood of life btw.

ok thought some more on this... this is how to change your column.

$column_old = 0;
$column_value = 0;
For loop to go through the column
{
$column_value = (your column value from table);
if($column_value > $column_old){$column_old = $column_value}
}

//Column_old at this point should be the new ranking number (i.e. if third time forth...etc..)

so just as u used changed the 0 to 1 . Now u just change the 0 to $column_old

make sense... a lot clearer to me now lol sorry. I put you through a headache cause I was being stupid and not thinking earlier.

TeamCIT · February 2, 2010

I editted this so its a little clearer btw:

exactly... with the except to not display if it is equal to 0 else all the 0's will be displayed as well ascending order. This was a lot simpler then it had to be... sorry mind somewhere else this morning... coffee is the blood of life btw.

ok thought some more on this... this is how to change your column.
$column_old = 0;
$column_value = 0;
For loop to go through the column
{
$column_value = (your column value from table);
if($column_value > $column_old){$column_old = $column_value}
}

//Column_old at this point should be the new ranking number (i.e. if third time forth...etc..)
so just as u used changed the 0 to 1 . Now u just change the 0 to $column_old

make sense... a lot clearer to me now lol sorry. I put you through a headache cause I was being stupid and not thinking earlier.

It is no problem haha, understandable. I think I am understanding what you are saying to do, but can i put the $column_value if place of the 1 in my MySQL Query statement? like this?

	mysql_query("update destinations
    			set selected='$column_value'
    			where id={$destChose}");

gwolgamott · February 2, 2010

Yes and no...

Steps to do each time submit is hit from your form:

1st - you do the query to get the data from ur current column

(Store in array or whatever you comfortable doing)

2nd - then do that loop I described in my example checking what the highest number is

3rd at this point $column_old (poorly named variables sorry) is the highest rank when the loop is done.

4th do your update query just as you have it with using that variable holding the highest rank

It is no problem haha, understandable. I think I am understanding what you are saying to do, but can i put the $column_value if place of the 1 in my MySQL Query statement? like this?

   mysql_query("update destinations
             set selected='$column_old'   //<--------- changed that 
                                                      // but still you will need to do the loop I described before this query.
             where id={$destChose}");

gwolgamott · February 2, 2010

More efficient method perhaps

//Loop 14 times since we have 14 destinations
$High_column_rank = 0;
for($i = 1; $i<15;$i++)
{
$column_current_rank = MY_SQL query to get data for column $i;
if ($column_current_rank > $High_column_rank) {$High_column_rank = $column_current_rank;}
}

  mysql_query("update destinations
             set selected='$High_column_rank'   
             where id={$destChose}");

TeamCIT · February 2, 2010

More efficient method perhaps

//Loop 14 times since we have 14 destinations
$High_column_rank = 0;
for($i = 1; $i<15;$i++)
{
$column_current_rank = MY_SQL query to get data for column $i;
if ($column_current_rank > $High_column_rank) {$High_column_rank = $column_current_rank;}
}

  mysql_query("update destinations
             set selected='$High_column_rank'   
             where id={$destChose}");

I have to admit I am feeling a little lost here. Wouldn't "set selected='$High_column_rank' " apply the same numeric value to all of the entries? How would it increment?

gwolgamott · February 2, 2010

No but I did forget a step.... before updating u need to do this:

//Loop 14 times since we have 14 destinations
$High_column_rank = 0;
for($i = 1; $i<15;$i++)
{
$column_current_rank = MY_SQL query to get data for column $i;
if ($column_current_rank > $High_column_rank) {$High_column_rank = $column_current_rank;}
}

//////////////////////misssing step I forgot////////////////////////
$High_column_rank =  $High_column_rank + 1;
///////////////////////////////////////////////////////////////////////

  mysql_query("update destinations
             set selected='$High_column_rank'   
             where id={$destChose}");

No how this works is say we have a column like such:

0

2

1

3

0

5

4

Lets say we want to change the 1st value here of this list of 7, we want it to be 6th for it being the 6th choice.

Well first we say $high is zero by default

a loop of 7 times since our column has 7 values

1st loop - in side the loop we ask for the first number it is zero... not greater so $high is still zero

2nd loop - inside the loop yet we ask for the second number it is 2.... that is greater then $high that is 0 right now so lets make it equal to that so now $high is 2

3rd loop - next inside the loop it is 1.... that is not greater we do not change $high so it is still 2

4th - next loop it is 3.. greater then $high since it is 2 so now we change it to be 3. $high is 3 now

5th - next loop is 0 ... not greater then $high... so $high is still 3

6th - next loop is 5... is greater then $high that is 3 right now.... change it... $high is now 5

7th - last loop is 4 ... is not greater then $high... $high stays at 5.

Loop done

$high is "5"

Add one to that to make a new high number. So $high is now 6

replace our picked value from the form ( 1st value here of this list of 7 from above)...

so our column now looks like this:

6

2

1

3

0

5

4

gwolgamott · February 2, 2010

I answered wrong question... I just used you query code... um so yeah... anyways that explains what you want to do anyways.

But you want to update just the column value for the destination only not the entire database columns still just changing one value in the entire database as u were to begin with.... BUT instead of updating it to be one you are changing it to the New High number.

Lets step back a bit...

write some code to check what the value is right now....

write some code to update it to 1 as you are doing already...

give it to me and I'll set it in the loop like you need it.

Then You can test it and if it works reread all that mess and understand why it works.

gwolgamott · February 2, 2010

<?php

$Col_Val = 0;
$H_Col_Val = 0;

$con = mysql_connect(CONNECT CODE HERE);
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

for($i =1; $i < 15; $i++)
{
	$Col_Val = mysql_query("SELECT Rank_Column FROM Vaction_table");
	if($Col_Val > $H_Col_Val)
	{
		$H_Col_Val = $Col_Val;
	}
	else {
		//DO NOTHING
	}

}

mysql_query("UPDATE Vaction_table SET Rank_Column = $High_Col_Val
WHERE Key = $Value_for_destination_from_form ")

mysql_close($con);
?>

TeamCIT · February 3, 2010

More efficient method perhaps

//Loop 14 times since we have 14 destinations
$High_column_rank = 0;
for($i = 1; $i<15;$i++)
{
$column_current_rank = MY_SQL query to get data for column $i;
if ($column_current_rank > $High_column_rank) {$High_column_rank = $column_current_rank;}
}

  mysql_query("update destinations
             set selected='$High_column_rank'   
             where id={$destChose}");

sorry for the time it took me to reply, I was very busy once I left school yesterday. Could you help me out with this part?

$column_current_rank = MY_SQL query to get data for column $i;

I've tried several different ways to get this working and am having no luck.

gwolgamott · February 3, 2010

No problem I do this at work and wasn't looking here much anyways post, helps me work out my own problems. my mind works that way... anyways to explain that

$column_current_rank = MY_SQL query to get data for column $i;

that referes to code that would look like this:

$column_current_rank = mysql_query("SELECT Rank_Column FROM Vacation_table");

Where -> Rank_Column <- is your field and -> Vacation_table <- is your table

Now my example is used assuming that you have a unique key field from 1 to 14... i know that is probrably not your case. Give me an example of your table structure so I can give you a better example perhaps. But I can tell you this much... take my last example code... that will not work for you I know.... it was an example of working code. ...but actions speak louder then words here's an example that may pertain better to you.

gwolgamott · February 3, 2010

$column_array = all data from column field in the table
   //don't remeber how to do that off hand I'll get back to you on that. 

//Loop 14 times since we have 14 destinations
$High_column_rank = 0;
for($i = 0; $i<14;$i++) // this is adjusted since arrays start at 0 not 1 btw
{
$column_current_rank = column_array[$i]; // i changed this to be more relavent
if ($column_current_rank > $High_column_rank) {$High_column_rank = $column_current_rank;}
}

  mysql_query("update destinations
             set selected='$High_column_rank'   
             where id={$destChose}");

TeamCIT · February 3, 2010

My database structure is 6 columns long "ID"(primary key auto increment), "Selected" (numeric value I would like to place an incrementing number in to list them in ascending order), "dest_name"(Contains the name of the destination to be chosen), "dest_addr"(Contains the address of the chosen destination that will be used in generating a link to google maps), "dest_date"(The date the destination was established - unimportant here but this is an informative website so it is necessary for organization), and the last column is "dest_description"(Contains a description of the destination - also irrelevant).

So the outline of the table looks like this:

ID Selected dest_name dest_addr dest_date dest_description

1 0 National Gallery of Art 401+Constitution+Ave+ Northwest+Washington+DC N/A N/A

I tried building off the code you posted, however I am having problems modifying it. I keep getting parse errors and cannot figure out why, but this is what I have as of now:

$orderCounter = 0;

if($_POST['submitted'] == true) {
$destChose = $_POST['destList'];

if($destChose >= 1) {

for($destChose >=1; $destchose <15; $destChose++
{
mysql_query("update destinations
    			set selected= $orderCounter
    			where id= $destChose");
$orderCounter++;
}

$result = mysql_query("SELECT dest_name, dest_addr 
			FROM destinations
			WHERE selected >= '1'");			



while ($row = mysql_fetch_array($result)) {

			echo "<li>";
			echo $row["dest_name"];
			echo "</li>";

}

}else{
		mysql_query("update destinations
    			set selected= $orderCounter
    			where id= $destChose");

		$result = mysql_query("SELECT dest_name, dest_addr 
			FROM destinations
			WHERE selected >= '1'");			



		while ($row = mysql_fetch_array($result)) {

			echo "<li>";
			echo $row["dest_name"];
			echo "</li>";

		}

		echo "<br />";
		echo "<br />";
		echo "Please select a destination.";
}
}

gwolgamott · February 3, 2010

Just a quick glance you look like you know what you are doing then. Here's code that should be more relavent to what I was talking about but it's similiar almost exact to what you did, parse error could just be a typo make sure the code of mine you used makes sense in the context you were using it. Other then that I tihnk some of your logic is in the wrong order, but you've got the right parts there just not where they need to be to talk to each other. My variables are named different yet here, but look at the order I'm using. Check to see if this syntax works then build off of it if it does.

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
///This is what you need to drop into your code in place of the updating section you have now//
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
$result = mysql_query("SELECT selected,FROM destinations");
$column_array = mysql_fetch_array($result, MYSQL_NUM));  //Creates an array...

//Loop 14 times since we have 14 destinations
$High_column_rank = 0;
for($i = 0; $i<14;$i++) // this is adjusted since arrays start at 0 not 1 btw
{
$column_current_rank = column_array[$i]; //calls the column data array from array 0 to array 13
if ($column_current_rank > $High_column_rank) {$High_column_rank = $column_current_rank;}
}

  mysql_query("update destinations
             set selected='$High_column_rank' 
where id= $destChose");
///////////////////////////////////////////////////////////////////////////////////////////

TeamCIT · February 3, 2010

Oh ok, so it looks like i need to add another column. I am just confused about how these two columns are going to interact. Should I keep the "selected" column working with "0"s and "1"s and add an "in_order" column for the other numeric values (1, 2, 3, ect)?

Sign In

Create a pseudo auto increment collumn in database?

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information