Change Field Name to Current Date in MySQL DB

[anonymoose]

Hi there. I'm new to the forum, and like all new people I'm here with questions.

 

I've recently built a website for my company that stores prices of items on a day to day basis. Well, it's going to if I can figure this out. What I want to do is rename the daily imported column "Price" to the current date. Everyday my script adds the column "Price" and imports the values from a similarly named field in a daily exported CSV file. After the values are imported (I have this working) I want to change the column name to the current date. My reason for this is to build a comparative table where the end-users (management) can look at the fluctuation in prices over a period of time. I am just missing this simple thing! Below is what I have thus far. I've tried using variables, PHP and MySQL DATE functions. Has anyone been able to do this?

 

<?php
mysql_connect("host", "username", "password") or die(mysql_error());
echo "Connected to MySQL<br />";
mysql_select_db("mydb") or die(mysql_error());
echo "Connected to Database<br />";
$mydatevar = $yearvalue . "_" . $monthvalue . "_" . $dayvalue;
mysql_query("ALTER TABLE mytable CHANGE price `$mydatevar' VARCHAR(30)") or die(mysql_error()); 
echo "Price Field Changed to Current Date";
?>

 

The Error I get is...

 

Connected to MySQL

Connected to Database CEDB

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

 

Which is valid, but I'm at a loss. I can change the column to anything with an operator in front of it. I've used the `, ', and " with no luck either.

[TeNDoLLA]

I think you should have something like a table

 

prices

-------

id

item

date

price

 

and compare the price changes with the date field and not change everyday the field names.

 

But in the actual problem change this

mysql_query("ALTER TABLE mytable CHANGE price `$mydatevar' VARCHAR(30)") or die(mysql_error()); 

to

mysql_query("ALTER TABLE mytable CHANGE price $mydatevar VARCHAR(30)") or die(mysql_error()); 

[anonymoose]

I tried it without the "'" and it just inputs the two underscores "_". I already considered your suggestion, but the boss man really wants it done this way. I tried CurDate as well with no luck...

[PFMaBiSmAd]

You would NEVER do, what you are trying to do, to a DATABASE table.

[EdwinPaul]

Besides all other remarks: your code needs improvement:

 

<?php

$connection=mysql_connect("host", "username", "password") or die(mysql_error());

if($connection === false){

  echo "Connection failed: ".mysql_error();

  exit();

}

echo "Connected to MySQL<br />";

$selectdb=mysql_select_db("mydb") or die(mysql_error());

if($selectdb === false){

  echo "mysql_select_db mydb failed: ".mysql_error();

  exit();

}

echo "Connected to Database<br />";

$mydatevar = $yearvalue . "_" . $monthvalue . "_" . $dayvalue;

$result=mysql_query("ALTER TABLE mytable CHANGE price `$mydatevar' VARCHAR(30)") or die(mysql_error());

if($result === false){

  echo "ALTER TABLE mytable failed: ".mysql_error();

  exit();

}

echo "Price Field Changed to Current Date";

?>

[gizmola]

I tried it without the "'" and it just inputs the two underscores "_". I already considered your suggestion, but the boss man really wants it done this way. I tried CurDate as well with no luck...

 

That is not a reason to do something so fundamentally wrong.  If you were a carpenter and your boss said:  "I want you to build an inverted pyramid out of glass with a bandsaw" and you went to the world's largest carpentry forum where experts with decades of experience and expertise explained to you that what you were talking about was not only ridiculous, but suicidal and they would not endorse or support the idea, to which you replied:  "the boss told me to do this" do you think that they'd suddenly change their tune?

 

I'm sorry that your "boss" is pulling your strings, but that does not effect us or our integrity. 

 

If you proceed with this idea you'll be sorry.  Ad-hoc changes to the data dictionary/structure of a table are not what rdbms were made to do.  Having multiple tables instead of multiple rows, or multiple columns instead of multiple rows is not how you design or use an rdbms.  Database tables are optimized and have features that allow them to do amazing things, but only when you structure them properly.  There are names for these rules (normalization) that come right out of the theory that was used to create them by the originator (Dr. Ted Codd).

 

TenDolla provided you a simple effective structure that could be used as a starting point.  It's clear you have not even begun to consider what the SQL statements would be or how you would compute them, nor do you have any understanding of how your planned structure will not perform when data is loaded. 

 

With a proper structure you could easily do all sorts of things like graphing prices, comparing prices for individual dates and even date ranges that will be nearly impossible to accomplish with a table with 10's to hundreds of columns (nearly all of which for any given row that will be empty).  Truly, what you're saying you want to do is bizzarro.

 

If this seems harsh, it's meant to be.  There is nothing more infuriating to the experts in this community, who offer sage advice to people for free, as when our advice is ignored by people for silly reasons who don't know any better.  I sincerely hope this is a wakeup call for you. 

[anonymoose]

First off, thanks Edwin for the code improvement suggestions. I appreciate that. I've set up the database so each record has a date stamp with it and we're going to proceed forth in that fashion. I also have more information. We only want to keep two weeks worth of data in our DB. So 14 days worth. We have approximately 3,000 items with prices the change daily, so that's about 42,000 records of data to sort through and then do calculations on every day. It's not an issue of course, but just for curiousities sake, could I print the CurDate() and then grab that printed text and import it to the field name? we're not taking this approach anymore, but it'd be neat to know if it could be done. Could I print the variable as text and grab the text somehow?

[EdwinPaul]

Of course you can. You need to SELECT your table-row first and put it in array $row with mysql_fetch_array(), and then use it in your form with:

 

<input type="text" name="orderdate" value="<?php echo isset($row['orderdate']) ? $row['orderdate'] : '';?>" />

[anonymoose]

Alright. Gizmola, you can suck it. I knew there was another way to do this, but I also knew it could be done this way as well. I thought of that and to be honest this is more efficient. Period. Because I believe solutions should be shared and worked toward (and not nay say'd by certain stagnant and archaic forum members)...

 

$today = date("mdY");
mysql_query("ALTER TABLE cookies CHANGE CurPrice `$today` VARCHAR(10)") or die(mysql_error());

 

Those `, not ' around `today` btw. Thanks guys and especially TenDolla for actually taking the time to broaden my horizons. That solution is a good one and I'm sure I'll need for something different in the future.

[gizmola]

Alright. Gizmola, you can suck it. I knew there was another way to do this, but I also knew it could be done this way as well. I thought of that and to be honest this is more efficient. Period. Because I believe solutions should be shared and worked toward (and not nay say'd by certain stagnant and archaic forum members)...

 

Yes, keep telling yourself it's because I don't understand the syntax to alter a table.  Do you even understand your own approach? 

 

-You have a table named cookie, that you are changing from the name of CurPrice to 'date'. 

 

What happens tomorrow when you run this script and the column is no longer named CurPrice.  What value do you think your script will have?

 

 

[xyph]

Alright. Gizmola, you can suck it. I knew there was another way to do this, but I also knew it could be done this way as well. I thought of that and to be honest this is more efficient. Period. Because I believe solutions should be shared and worked toward (and not nay say'd by certain stagnant and archaic forum members)...

Those `, not ' around `today` btw. Thanks guys and especially TenDolla for actually taking the time to broaden my horizons. That solution is a good one and I'm sure I'll need for something different in the future.

 

Excuse me?

 

If I was the admin, you'd be banned on the spot. Everything gizmola said was 100% correct. He wrote a HUGE chunk of text in his own free time to try to explain WHY PFMaBiSmAd said what he did.

 

Then you tell two senior members, those who have written more lines of code and have dealt with bigger systems than you ever will, to suck it.

 

You will fail in programming. Your logic will bomb, your script will fail, and you'll come crawling back to get help from those you just insulted. I know I will ignore any more pleas from you, as any type of REAL WORLD advice I might give will just be see as 'wrong' and 'less efficient' with no basis.

 

Your code is less efficient. Period. Not only is it less efficient, but every single person with even remotely intricate knowledge of database engines will tell you to NEVER alter a live table. Ever. This is what temporary tables are for.

 

You're doing it wrong, bud. I hope your boss hires a competent programmer. I'd hate to see the swiss cheese that is your script if you allow these kinds of practices in live code.

[DavidAM]

Alright. Gizmola, you can suck it.

While, Gizmola may have been harsher than I would have been, his statements are 100% accurate. Any time you find yourself trying to alter a table definition in the middle of an application, you have a poorly designed database. It is vital to stop and reconsider the design before too much effort is wasted on coding. Make no mistake, any effort spent on coding to a poor database design is wasted. At some point, in the not too distant future -- and usually before the application is even finished -- it will all have to be re-done.

 

I knew there was another way to do this, but I also knew it could be done this way as well.

This is always another way to do it. But not all of the ways are valid, or even maintainable.

 

I thought of that and to be honest this is more efficient.

Never in a million years!! The database design you started with would likely be less efficient than using index cards in a card file.

 

Because I believe solutions should be shared ...

I too believe in sharing solutions. The problem with your original code:

$mydatevar = $yearvalue . "_" . $monthvalue . "_" . $dayvalue;
$result=mysql_query("ALTER TABLE mytable CHANGE price `$mydatevar' VARCHAR(30)") or die(mysql_error());

and the reason the new code "works":

$today = date("mdY");
mysql_query("ALTER TABLE cookies CHANGE CurPrice `$today` VARCHAR(10)") or die(mysql_error());

is that $yearvalue, $monthvalue, and $dayvalue were never defined. Therefore, $mydatevar was being set to "__" (two underscores) which is exactly what you said you were getting. So, the code did work - in that it was doing exactly what you told it to do.

 

As to your original database design, did anybody ever give any consideration to how you would write the queries to get the data or summarize the data? Did anyone ever consider the unnecessary load you would be putting on the database server? If my memory serves me correctly, an ALTER TABLE statement completely copies and rebuilds the table. Since you said you were renaming the "Price" column every day, presumably, you were ADDing the Price column in some code somewhere. So, ALTER TABLE ... ADD COLUMN ... ... ALTER TABLE ... (rename column) -- that's TWO copy and rebuilds of the entire table. Every Day!

 

Edit:Actually, three rebuilds, since you are also dropping the oldest column every day.

 

Did anybody ever consider using NUMERIC or DECIMAL (or even INTEGER) for the datatype? Using VARCHAR for a Price column is another terrible idea. This will completely prevent you from manipulating the data in SQL in any way -- SELECT MAX(), MIN(), AVERAGE() will NOT provide accurate results on numbers stored as VARCHAR.

 

In the design suggested by TeNDoLLA, it is a simple matter to get the lowest, highest or average value over a range of dates (one-week, two-weeks, three-days, whatever). To get that same information from your suggested design, would mean building a new query using dates to build column names.

 

By the way, what are you going to do when you get to work one day and find out that the process failed TWO days ago? Your table will be missing a column. If you run it now and it uses the current date, it will put the wrong date as a column name. Then when the process runs tonight, it will fail again because the column name already exists.

 

There's a reason we call it "database design". It needs to be designed, and all of its currently known uses need to be considered, and potential future questions need to be considered. What happens in the original design when the boss walks in your office and says, "I need to see the trend of pricing over the past month"? You don't even have the data, and your table structure will not support it until you rewrite code to allow one month of data as column names instead of two weeks. In the suggested design, you can keep the data longer than the boss suggests so you have it when he later asks for it. Then you just change the date range on the query. In my experience, one of the first questions the boss asks when an application is "finished" is directly contrary to the requirements he gave when you started building the system.

[PFMaBiSmAd]

The DB Gods sacrifice a kitten every time someone tries to use a Database like it is a spreadsheet. Several kittens died for this thread.  :'(

[mikosiko]

The DB Gods sacrifice a kitten every time someone tries to use a Database like it is a spreadsheet. Several kittens died for this thread.  :'(

.

 

and you can see tons dying everyday in the vicinity .... poor kittens

 

"Two things are infinite: the universe and human stupidity; and I'm not sure about the the universe" A.Einstein

[xyph]

DavidAM - You're sure offering a ton of help for someone who obviously doesn't care he's doing things wrong. Kudos to you, a lot more patient than I.

[DavidAM]

"Kill 'em with kindness", my dad always said.

 

Sometimes people don't learn until they hit the brick wall.  

I just hate having to cleanup all the blood on the wall. 

 

In reality, all these hinky database designs I keep seeing here drive me batty. Sometimes, I just have to dump on them. Maybe the OP, or someone later reading this thread, will at least learn some questions to ask.

 

@Gizmola: I hope you did not take offense with my reply. I meant none!

[gizmola]

@Gizmola: I hope you did not take offense with my reply. I meant none!

 

Of course not. Plus we share an awesome albeit common first name apparently