Solved

mysql query for alternating update

Posted on 2012-03-27
3
265 Views
Last Modified: 2012-03-27
I need to update a table so that the field 'series' alternates between 'a' and 'b'.

Something like this:
------------
include ("/home/mysite/connect.inc");
mysql_select_db(oys) or die("Unable to load update into mysql");

$query = "UPDATE oys_sites SET `series`=

//at this point, I need the 'series' field in the first row set to 'a', the next row set to 'b', then 'a', then 'b', alternating, until all rows are done.
-------

This will be entered into a php script so I can set it when needed.

Thanks for any help writing this up,

Chris
0
Comment
Question by:St_Aug_Beach_Bum
  • 2
3 Comments
 
LVL 83

Accepted Solution

by:
Dave Baldwin earned 500 total points
ID: 37774736
You haven't specified an ORDER BY so you can't tell which rows are getting what.  Any deletions and inserts will alter the 'natural' order of the rows in the table because SQL databases don't preserve that order.
0
 

Author Comment

by:St_Aug_Beach_Bum
ID: 37775121
Ok, I've got it

Worked it out by building a loop and incrementing through it and updating one row at a time, using the increment count as a way to switch back and forth between 'a' and 'b'.

Thanks for the suggestion Dave, I added a note here to say I wasn't quite following you on that and to say it was existing entries I was trying to affect - but that note didn't seem to show up here. Maybe I messed something up.

Thank you for the attempt though and I will award you points for trying to help :)  

Chris
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 37775233
Since you posted an UPDATE statement, I assumed existing entries.  What I was trying to point out was that without using an ORDER BY statement to set the order, you are unable to 'guarantee' that the alternating order will be preserved in the future.  On the other hand, if the table isn't too big, you can just run that code again to reset it.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Wordpress Pagination 1 47
Simple function not working 7 44
Ajax and PHP 4 49
How to show popup no matter what a user clicks on navigation wise 10 36
Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question